Hello, I need to make a recursive query in a template fragment into PostgreSQL. I found out that EA is doggedly refusing all SQL statements that don't start with SELECT (oh well, guys, there is 2022 AD!). I also found here in past discussions that I can wrap up the WITH RECURSIVE with SELECT (allegedly working with MySQL). Well, it doesn't work at all with PostgreSQL. Do you have any experience with this SQL server?
Pure SQL statement:
WITH RECURSIVE requirements_tree (depth) AS
(
SELECT 0, object_id, name, alias, stereotype, note, ea_guid
FROM T_OBJECT
WHERE OBJECT_TYPE = 'Requirement'
AND PACKAGE_ID = 110
AND PARENTID = 0
UNION ALL
SELECT depth+1, T.object_id, T.name, T.alias, T.stereotype, T.note, T.ea_guid
FROM requirements_tree rt
JOIN T_OBJECT T ON T.parentid = rt.object_id
) SEARCH DEPTH FIRST BY OBJECT_id SET ordercol
SELECT *
FROM requirements_tree
ORDER BY ordercol
If I use select envelope:
SELECT * FROM (
WITH RECURSIVE requirements_tree (depth) AS
(
SELECT 0, object_id, name, alias, stereotype, note, ea_guid
FROM T_OBJECT
WHERE OBJECT_TYPE = 'Requirement'
AND PACKAGE_ID = 110
AND PARENTID = 0
UNION ALL
SELECT depth+1, T.object_id, T.name, T.alias, T.stereotype, T.note, T.ea_guid
FROM requirements_tree rt
JOIN T_OBJECT T ON T.parentid = rt.object_id
) SEARCH DEPTH FIRST BY OBJECT_id SET ordercol
SELECT *
FROM requirements_tree
) as Result
ORDER BY ordercol
I get error: operator does not exist: integer || integer.
Ok, I temporarily removed 0 and depth from the SELECT, but then I got error: column rt.object_id does not exist.
That is pretty awkward situation.
