Wednesday, June 18, 2008

recursive queries only with SQL procedures

I tested possibility of recursive queries only with SQL language (and SQL procedures)
postgres=# select * from rec_src;
id | parent | v
----+--------+--------
2 | 1 | rodic
3 | 1 | rodic
4 | 2 | dite
5 | 2 | dite
6 | 3 | dite
7 | 6 | vnouce
1 | 0 | root
(7 rows)
It's ugly, but it works :)
create or replace function empty2null(anyarray)
returns anyarray as $$
select case
when array_upper($1,1) is null
then null
else $1 end;
$$ language sql strict immutable;

create or replace function search_path(int)
returns varchar as $$
select case
when $1 is null
then ''
else search_path((select parent
from rec_src
where id = $1)) || '.'|| $1 end;
$$ language sql;

create or replace function ls(int[], int,
out id int, out parent int, out path varchar, out v varchar )
returns setof record as $$
select id, parent, search_path(id), repeat(' ',$2)|| v
from rec_src
where parent = any($1)
union all
select id, parent, path, v from ls(empty2null(array(select id
from rec_src
where parent = any($1))),
$2 + 1);
$$ language sql volatile strict;
postgres=# select * from ls(array[0], 0) order by path;
id | parent | path | v
----+--------+------------+--------------
1 | 0 | .0.1 | root
2 | 1 | .0.1.2 | rodic
4 | 2 | .0.1.2.4 | dite
5 | 2 | .0.1.2.5 | dite
3 | 1 | .0.1.3 | rodic
6 | 3 | .0.1.3.6 | dite
7 | 6 | .0.1.3.6.7 | vnouce
(7 rows)

1 Comments:

At November 23, 2010 at 12:37 PM , Blogger padamCZ said...

I feel that if the hierarchy is formed by Inherits (internal pg functionality), then this is the wrong example.

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home