#1126 Using ARRAY in with recursive

[mackentosh]

If you create the following table 'RECUR' and execute the provided 'with recursive' statement the error
' [Error Code: -5593, SQL State: 42593] column count mismatch in column name list'
will be thrown. The same statement works with PostgreSQL. I used HSQLDB 2.1.0.

CREATE CACHED TABLE RECUR
(ID INTEGER NOT NULL PRIMARY KEY,
PARENT_ID INTEGER NOT NULL);

with recursive cte (id, parent_id) as
(select id, parent_id, ARRAY[id] as branch from recur where id = 15143
UNION ALL
select r.id, r.parent_id, branch || id from recur r
inner join cte c on (r.id = c.parent_id)
)
select * from cte
order by branch desc;

[Fred Toussi]

The syntax implemented in PostgreSQL is wrong.

The Standard says the additional array is an optional feature which needs an extra clause added to the WITH RECURSIVE statement, and PostgreSQL does not support the extra clause. You should simply add the BRANCH column to the main column list.

In a future version, we will implement the extra clause that names the extra array column .