Re: [PATCHES] WITH RECURSIVE patch V0.1

create table test_connect_by (
parent integer,
child integer,
constraint uq_tcb unique (child)
);

insert into test_connect_by values ( 5, 2);
insert into test_connect_by values ( 5, 3);

insert into test_connect_by values (18,11);
insert into test_connect_by values (18, 7);

insert into test_connect_by values (17, 9);
insert into test_connect_by values (17, 8);

insert into test_connect_by values (26,13);
insert into test_connect_by values (26, 1);
insert into test_connect_by values (26,12);

insert into test_connect_by values (15,10);
insert into test_connect_by values (15, 5);

insert into test_connect_by values (38,15);
insert into test_connect_by values (38,17);
insert into test_connect_by values (38, 6);

insert into test_connect_by values (null, 38);
insert into test_connect_by values (null, 26);
insert into test_connect_by values (null, 18);

David Fetter írta:
> On Sun, May 18, 2008 at 08:51:29PM +0900, Tatsuo Ishii wrote:
>
>> WITH RECURSIVE patch V0.1
>>
>> Here are patches to implement WITH RECURSIVE clause. There are some
>> limitiations and TODO items(see the "Current limitations" section
>> below). Comments are welcome.
>>
>> 1. Credit
>>
>> These patches were developed by Yoshiyuki Asaba (y-asab@sraoss.co.jp)
>> with some discussions with Tatsuo Ishii (ishii@sraoss.co.jp).
>>
>
> This is really great! Kudos to all who made this happen :)
>
> I tried a bunch of different queries, and so far, only these two
> haven't worked. Any ideas what I'm doing wrong here?
>
> WITH RECURSIVE t(n) AS (
> SELECT 1
> UNION ALL
> SELECT n+1
> FROM t
> WHERE n < 100
> )
> SELECT * FROM t;
> ERROR: cannot extract attribute from empty tuple slot
>
> WITH RECURSIVE t(n) AS (
> VALUES (1)
> UNION ALL
> SELECT n+1
> FROM t
> WHERE n < 100
> )
> SELECT * FROM t;
> ERROR: cannot extract attribute from empty tuple slot
>
> Cheers,
> David.
>

Here's a test case attached shamelessly stolen from
http://www.adp-gmbh.ch/ora/sql/connect_by.html

This query (without naming toplevel columns) works:

# with recursive x as (select * from test_connect_by where parent is
null union all select base.* from test_connect_by as base, x where
base.parent = x.child) select * from x;
parent | child
--------+-------
| 38
| 26
| 18
18 | 11
18 | 7
26 | 13
26 | 1
26 | 12
38 | 15
38 | 17
38 | 6
17 | 9
17 | 8
15 | 10
15 | 5
5 | 2
5 | 3
(17 rows)

It even works when I add my "level" column:

# with recursive x(level, parent, child) as (select 1::bigint, * from
test_connect_by where parent is null union all select x.level + 1,
base.* from test_connect_by as base, x where base.parent = x.child)
select * from x;
level | parent | child
-------+--------+-------
1 | | 38
1 | | 26
1 | | 18
2 | 18 | 11
2 | 18 | 7
2 | 26 | 13
2 | 26 | 1
2 | 26 | 12
2 | 38 | 15
2 | 38 | 17
2 | 38 | 6
3 | 17 | 9
3 | 17 | 8
3 | 15 | 10
3 | 15 | 5
4 | 5 | 2
4 | 5 | 3
(17 rows)

But I have a little problem with the output.
If it's not obvious, here is the query tweaked a little below.

# with recursive x(level, parent, child) as (select 1::integer, * from
test_connect_by where parent is null union all select x.level + 1,
base.* from test_connect_by as base, x where base.parent = x.child)
select lpad(' ', 4*level - 1) || child from x;
?column?
------------------
38
26
18
11
7
13
1
12
15
17
6
9
8
10
5
2
3
(17 rows)

Can we get the rows in tree order, please? I.e. something like this:

?column?
------------------
38
15
10
5
2
3
17
9
8
6
26
13
1
12
18
11
7
(17 rows)

After all, I didn't specify any ORDER BY clauses in the base, recursive
or the final queries.

Also, it seems there are no infinite recursion detection:

# with recursive x(level, parent, child) as (
select 1::integer, * from test_connect_by where parent is null
union all
select x.level + 1, base.* from test_connect_by as base, x where
base.child = x.child
) select * from x;
... it waits and waits and waits ...

Also, there's another rough edge:

# with recursive x as (select * from test_connect_by where parent is
null) select * from x;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/