[SQL] Cloning hierarchical data

Hi,

let's assume i have a self referencing hierarchical table like this one:

CREATE TABLE test
(name text,id serial primary key,parent_id integer
references test)

insert into test (name,id,parent_id) values
('root1',1,NULL),('root2',2,NULL),('root1sub1',3,1),('root1sub2',4,1),('root
2sub1',5,2),('root2sub2',6,2)

testdb=# select * from test;
name | id | parent_id
-----------+----+-----------
root1 | 1 |
root2 | 2 |
root1sub1 | 3 | 1
root1sub2 | 4 | 1
root2sub1 | 5 | 2
root2sub2 | 6 | 2

What i need now is a function that would take the id of a test record and
clone all attached records (including the given one). The desired result
would like this for example:

Select * from cloningfunction(2);

name | id | parent_id
-----------+----+-----------
root2 | 7 |
root2sub1 | 8 | 7
root2sub2 | 9 | 7

Any pointers?

thanks!

Maximilian Tyrtania

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql