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 ([EMAIL PROTECTED])
with some discussions with Tatsuo Ishii ([EMAIL PROTECTED]).
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/
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);
--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches
