Thread: temporary table / recursion

temporary table / recursion

From

"Robert Wimmer"

Date:

11 February 2007, 05:20:17

hi,

i cannot drop a temporary table that was created in a plpgsql function.
I cannot drop it inside the function and also not from "outside" using the 
command line. even using the qualified table name "pg_temp_1.recurs_temp" 
has no effect.

As far see the temporary table does not exist from the "user view" - it does 
not exist if i search for the table in pg_class - but it still exists in the 
"system view".

*** snippet ***

CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS $$
DECLARE tmp RECORD;
BEGIN
 CREATE TEMPORARY TABLE recurs_temp (id INTEGER, parent_id INTEGER,label 
TEXT) ON COMMIT DROP;
 INSERT INTO recurs_temp SELECT * FROM recurs.tree WHERE id = p_start;  -- 
first node PERFORM recurs.walk(p_start); FOR tmp IN SELECT * FROM recurs_temp LOOP RETURN NEXT tmp; END LOOP;
 RETURN;

END; $$
LANGUAGE plpgsql;

thanks for help

sepp wimmer



*** whole script ***

DROP SCHEMA recurs CASCADE;
CREATE SCHEMA recurs;

CREATE TABLE recurs.tree ( id        INTEGER NOT NULL, parent_id INTEGER, label        TEXT );

CREATE FUNCTION recurs.walk(p_start INTEGER) RETURNS VOID AS $$
DECLARE child RECORD;
BEGIN
 FOR child IN SELECT * FROM recurs.tree WHERE parent_id = p_start LOOP   PERFORM recurs.walk(child.id);   INSERT INTO
recurs_temp(id,parent_id,label) 
 
VALUES(child.id,child.parent_id,child.label); END LOOP; RETURN;

END; $$
LANGUAGE plpgsql;

CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS $$
DECLARE tmp RECORD;
BEGIN
 CREATE TEMPORARY TABLE recurs_temp (id INTEGER, parent_id INTEGER,label 
TEXT) ON COMMIT DROP;
 INSERT INTO recurs_temp SELECT * FROM recurs.tree WHERE id = p_start;  -- 
first node PERFORM recurs.walk(p_start); FOR tmp IN SELECT * FROM recurs_temp LOOP RETURN NEXT tmp; END LOOP;
 RETURN;

END; $$
LANGUAGE plpgsql;

INSERT INTO recurs.tree(id,parent_id,label) VALUES(1,NULL,'1');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(2,1,'1.1');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(3,1,'1.2');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(4,2,'1.1.1');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(5,2,'1.1.2');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(6,2,'1.1.3');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(7,2,'1.1.4');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(8,2,'1.1.5');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(9,2,'1.1.6');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(10,9,'1.1.6.1');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(11,10,'1.1.6.2');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(12,10,'1.1.6.3');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(13,12,'1.1.6.3.1');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(14,3,'1.2.1');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(15,3,'1.2.2');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(16,3,'1.2.3');

SELECT * FROM recurs.scan(1);

/* output as expected ****************

id | parent_id |   label
----+-----------+----------- 1 |           | 1 4 |         2 | 1.1.1 5 |         2 | 1.1.2 6 |         2 | 1.1.3 7 |
    2 | 1.1.4 8 |         2 | 1.1.5
 
11 |        10 | 1.1.6.2
13 |        12 | 1.1.6.3.1
12 |        10 | 1.1.6.3
10 |         9 | 1.1.6.1 9 |         2 | 1.1.6 2 |         1 | 1.1
14 |         3 | 1.2.1
15 |         3 | 1.2.2
16 |         3 | 1.2.3 3 |         1 | 1.2
(16 rows)

****************************************/

SELECT * FROM recurs.scan(1); -- once again > will fail

/* output as NOT expected **************

psql:recurs.schema:58: ERROR:  relation with OID 2080891 does not exist
KONTEXT:  SQL statement "INSERT INTO recurs_temp SELECT * FROM recurs.tree 
WHERE id =  $1 "
PL/pgSQL function "scan" line 6 at SQL statement

*****************************************/

_________________________________________________________________
Nur die MSN Suche sorgt bei einer Web-Recherche für optimale Ergebnisse. 
http://search.msn.at/

Re: temporary table / recursion

From

imad

Date:

11 February 2007, 07:52:21

Try creating your temp table outside any PLpgSQL function.

--Imad
www.EnterpriseDB.com


On 2/11/07, Robert Wimmer <seppwimmer@hotmail.com> wrote:
> hi,
>
> i cannot drop a temporary table that was created in a plpgsql function.
> I cannot drop it inside the function and also not from "outside" using the
> command line. even using the qualified table name "pg_temp_1.recurs_temp"
> has no effect.
>
> As far see the temporary table does not exist from the "user view" - it does
> not exist if i search for the table in pg_class - but it still exists in the
> "system view".
>
> *** snippet ***
>
> CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS $$
> DECLARE tmp RECORD;
> BEGIN
>
>   CREATE TEMPORARY TABLE recurs_temp (id INTEGER, parent_id INTEGER,label
> TEXT) ON COMMIT DROP;
>
>   INSERT INTO recurs_temp SELECT * FROM recurs.tree WHERE id = p_start;  --
> first node
>   PERFORM recurs.walk(p_start);
>   FOR tmp IN SELECT * FROM recurs_temp LOOP RETURN NEXT tmp; END LOOP;
>
>   RETURN;
>
> END; $$
> LANGUAGE plpgsql;
>
> thanks for help
>
> sepp wimmer
>
>
>
> *** whole script ***
>
> DROP SCHEMA recurs CASCADE;
> CREATE SCHEMA recurs;
>
> CREATE TABLE recurs.tree (
>   id            INTEGER NOT NULL,
>   parent_id INTEGER,
>   label         TEXT
>   );
>
> CREATE FUNCTION recurs.walk(p_start INTEGER) RETURNS VOID AS $$
> DECLARE child RECORD;
> BEGIN
>
>   FOR child IN SELECT * FROM recurs.tree WHERE parent_id = p_start LOOP
>     PERFORM recurs.walk(child.id);
>     INSERT INTO recurs_temp(id,parent_id, label)
> VALUES(child.id,child.parent_id,child.label);
>   END LOOP;
>   RETURN;
>
> END; $$
> LANGUAGE plpgsql;
>
> CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS $$
> DECLARE tmp RECORD;
> BEGIN
>
>   CREATE TEMPORARY TABLE recurs_temp (id INTEGER, parent_id INTEGER,label
> TEXT) ON COMMIT DROP;
>
>   INSERT INTO recurs_temp SELECT * FROM recurs.tree WHERE id = p_start;  --
> first node
>   PERFORM recurs.walk(p_start);
>   FOR tmp IN SELECT * FROM recurs_temp LOOP RETURN NEXT tmp; END LOOP;
>
>   RETURN;
>
> END; $$
> LANGUAGE plpgsql;
>
> INSERT INTO recurs.tree(id,parent_id,label) VALUES(1,NULL,'1');
> INSERT INTO recurs.tree(id,parent_id,label) VALUES(2,1,'1.1');
> INSERT INTO recurs.tree(id,parent_id,label) VALUES(3,1,'1.2');
> INSERT INTO recurs.tree(id,parent_id,label) VALUES(4,2,'1.1.1');
> INSERT INTO recurs.tree(id,parent_id,label) VALUES(5,2,'1.1.2');
> INSERT INTO recurs.tree(id,parent_id,label) VALUES(6,2,'1.1.3');
> INSERT INTO recurs.tree(id,parent_id,label) VALUES(7,2,'1.1.4');
> INSERT INTO recurs.tree(id,parent_id,label) VALUES(8,2,'1.1.5');
> INSERT INTO recurs.tree(id,parent_id,label) VALUES(9,2,'1.1.6');
> INSERT INTO recurs.tree(id,parent_id,label) VALUES(10,9,'1.1.6.1');
> INSERT INTO recurs.tree(id,parent_id,label) VALUES(11,10,'1.1.6.2');
> INSERT INTO recurs.tree(id,parent_id,label) VALUES(12,10,'1.1.6.3');
> INSERT INTO recurs.tree(id,parent_id,label) VALUES(13,12,'1.1.6.3.1');
> INSERT INTO recurs.tree(id,parent_id,label) VALUES(14,3,'1.2.1');
> INSERT INTO recurs.tree(id,parent_id,label) VALUES(15,3,'1.2.2');
> INSERT INTO recurs.tree(id,parent_id,label) VALUES(16,3,'1.2.3');
>
> SELECT * FROM recurs.scan(1);
>
> /* output as expected ****************
>
> id | parent_id |   label
> ----+-----------+-----------
>   1 |           | 1
>   4 |         2 | 1.1.1
>   5 |         2 | 1.1.2
>   6 |         2 | 1.1.3
>   7 |         2 | 1.1.4
>   8 |         2 | 1.1.5
> 11 |        10 | 1.1.6.2
> 13 |        12 | 1.1.6.3.1
> 12 |        10 | 1.1.6.3
> 10 |         9 | 1.1.6.1
>   9 |         2 | 1.1.6
>   2 |         1 | 1.1
> 14 |         3 | 1.2.1
> 15 |         3 | 1.2.2
> 16 |         3 | 1.2.3
>   3 |         1 | 1.2
> (16 rows)
>
> ****************************************/
>
> SELECT * FROM recurs.scan(1); -- once again > will fail
>
> /* output as NOT expected **************
>
> psql:recurs.schema:58: ERROR:  relation with OID 2080891 does not exist
> KONTEXT:  SQL statement "INSERT INTO recurs_temp SELECT * FROM recurs.tree
> WHERE id =  $1 "
> PL/pgSQL function "scan" line 6 at SQL statement
>
> *****************************************/
>
> _________________________________________________________________
> Nur die MSN Suche sorgt bei einer Web-Recherche für optimale Ergebnisse.
> http://search.msn.at/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Re: temporary table / recursion

From

"Robert Wimmer"

Date:

11 February 2007, 12:51:11


>From: imad <immaad@gmail.com>
>To: "Robert Wimmer" <seppwimmer@hotmail.com>
>Subject: Re: [INTERFACES] temporary table / recursion
>Date: Sun, 11 Feb 2007 19:39:25 +0500
>
>The problem is the plan which is stored against the INSERT statement
>in PLpgSQL function. The next time it is called, it uses the same plan
>and fails to locate the table based on the OID because CREATE temp
>table has been called again and a new table exists now with a
>different OID.

i changed the code as you suggested

*** snippet ***

CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS $$
DECLARE tmp RECORD;
BEGIN
 CREATE TEMP TABLE recurs_temp (id INT, parent_id INT, label TEXT);
 INSERT INTO recurs_temp SELECT * FROM recurs.tree WHERE id = p_start;  -- 
first node PERFORM recurs.walk(p_start); FOR tmp IN SELECT * FROM recurs_temp LOOP RETURN NEXT tmp; END LOOP;
 DROP TABLE recurs_temp;
 RETURN;

END; $$
LANGUAGE plpgsql;

>
>Another way might be to now attach your temp table with the
>transaction i.e. omit the clause ON COMMIT DROP. And drop the table at
>the end of transaction manually.
>
>If this doesn't help too, see the execute command in PLpgSQL. This
>will not save the plan against any command and this is what you need.
>

then i used the function with a prepared statement and EXECUTE and i had the 
same problems as before ...

*** output ***

recurs=# PREPARE recurs_func(INT) AS SELECT * FROM recurs.scan($1);
PREPARE
recurs=# EXECUTE recurs_func(1);
id | parent_id |   label
----+-----------+----------- 1 |           | 1 4 |         2 | 1.1.1 5 |         2 | 1.1.2 6 |         2 | 1.1.3 7 |
    2 | 1.1.4 8 |         2 | 1.1.5
 
11 |        10 | 1.1.6.2
13 |        12 | 1.1.6.3.1
12 |        10 | 1.1.6.3
10 |         9 | 1.1.6.1 9 |         2 | 1.1.6 2 |         1 | 1.1
14 |         3 | 1.2.1
15 |         3 | 1.2.2
16 |         3 | 1.2.3 3 |         1 | 1.2
(16 rows)

recurs=# EXECUTE recurs_func(1);
ERROR:  relation with OID 2084485 does not exist
KONTEXT:  SQL statement "INSERT INTO recurs_temp SELECT * FROM recurs.tree 
WHERE id =  $1 "
PL/pgSQL function "scan" line 6 at SQL statement
recurs=# DEALLOCATE recurs_func;
DEALLOCATE
recurs=# PREPARE recurs_func(INT) AS SELECT * FROM recurs.scan($1);
PREPARE
recurs=# EXECUTE recurs_func(1);
ERROR:  relation with OID 2084485 does not exist
KONTEXT:  SQL statement "INSERT INTO recurs_temp SELECT * FROM recurs.tree 
WHERE id =  $1 "
PL/pgSQL function "scan" line 6 at SQL statement


sepp


>BTW, this issue is being worked on for 8.3.
>

are there intentions to implement recursive queries like WITH or CONNECT BY 
in postgres ?

>--Imad
>www.EnterpriseDB.com
>
>

_________________________________________________________________
Was halten Sie von einer Seite, die all Ihre Lieblingsthemen beinhaltet? 
http://at.msn.com/

Re: temporary table / recursion

From

imad

Date:

11 February 2007, 13:27:40

On 2/11/07, Robert Wimmer <seppwimmer@hotmail.com> wrote:
>
>
>
> >From: imad <immaad@gmail.com>
> >To: "Robert Wimmer" <seppwimmer@hotmail.com>
> >Subject: Re: [INTERFACES] temporary table / recursion
> >Date: Sun, 11 Feb 2007 19:39:25 +0500
> >
> >The problem is the plan which is stored against the INSERT statement
> >in PLpgSQL function. The next time it is called, it uses the same plan
> >and fails to locate the table based on the OID because CREATE temp
> >table has been called again and a new table exists now with a
> >different OID.
>
> i changed the code as you suggested
>
> *** snippet ***
>
> CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS $$
> DECLARE tmp RECORD;
> BEGIN
>
>   CREATE TEMP TABLE recurs_temp (id INT, parent_id INT, label TEXT);
>
>   INSERT INTO recurs_temp SELECT * FROM recurs.tree WHERE id = p_start;  --
> first node
>   PERFORM recurs.walk(p_start);
>   FOR tmp IN SELECT * FROM recurs_temp LOOP RETURN NEXT tmp; END LOOP;
>
>   DROP TABLE recurs_temp;
>
>   RETURN;
>
> END; $$
> LANGUAGE plpgsql;
>
> >
> >Another way might be to now attach your temp table with the
> >transaction i.e. omit the clause ON COMMIT DROP. And drop the table at
> >the end of transaction manually.
> >
> >If this doesn't help too, see the execute command in PLpgSQL. This
> >will not save the plan against any command and this is what you need.
> >
>
> then i used the function with a prepared statement and EXECUTE and i had the
> same problems as before ...
>
> *** output ***
>
> recurs=# PREPARE recurs_func(INT) AS SELECT * FROM recurs.scan($1);
> PREPARE
> recurs=# EXECUTE recurs_func(1);

Not like that, use the execute command inside your function.
Here is the description and example.
http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html

Recursive queries are also planned for 8.3.

--Imad
www.EnterpriseDB.com

Re: temporary table / recursion

From

Tom Lane

Date:

11 February 2007, 13:41:33

"Robert Wimmer" <seppwimmer@hotmail.com> writes:
> i cannot drop a temporary table that was created in a plpgsql function.

It was created with "on commit drop".  Are you sure it still exists?

The problem looks to me like the well-known issue that plpgsql caches
plans, and so you can't have it refer to different temp tables on
successive executions (in the same session) unless you use EXECUTE.
        regards, tom lane

Re: temporary table / recursion

From

"Robert Wimmer"

Date:

11 February 2007, 16:57:43

>Not like that, use the execute command inside your function.
>Here is the description and example.
>http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html
>

that was what I tried before the PREPARE EXECUTE example and it did not 
work.
so i will try it again

*** snippet ***

CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS $$
DECLARE tmp RECORD;
BEGIN
 EXECUTE 'CREATE TEMP TABLE recurs_temp (id INT, parent_id INT, label 
TEXT)';
 INSERT INTO recurs_temp SELECT * FROM recurs.tree WHERE id = p_start;  -- 
first node EXECUTE recurs.walk(p_start); -- create tree FOR tmp IN SELECT * FROM recurs_temp LOOP RETURN NEXT tmp; END
LOOP;
 EXECUTE 'DROP TABLE recurs_temp';
 RETURN;

END; $$
LANGUAGE plpgsql;

****

and the output ...

****

recurs=# SELECT * FROM recurs.scan(1);
id | parent_id |   label
----+-----------+----------- 1 |           | 1 4 |         2 | 1.1.1 5 |         2 | 1.1.2 6 |         2 | 1.1.3 7 |
    2 | 1.1.4 8 |         2 | 1.1.5
 
11 |        10 | 1.1.6.2
13 |        12 | 1.1.6.3.1
12 |        10 | 1.1.6.3
10 |         9 | 1.1.6.1 9 |         2 | 1.1.6 2 |         1 | 1.1
14 |         3 | 1.2.1
15 |         3 | 1.2.2
16 |         3 | 1.2.3 3 |         1 | 1.2
(16 rows)

recurs=# \dt
No relations found.
recurs=# SELECT * FROM recurs.scan(1);
ERROR:  relation with OID 2084590 does not exist
KONTEXT:  SQL statement "INSERT INTO recurs_temp SELECT * FROM recurs.tree 
WHERE id =  $1 "
PL/pgSQL function "scan" line 6 at SQL statement
recurs=# \dt
No relations found.

****


so i dont know what went wrong now ...

i am using Postgres 8.0.3 on Windows XP

nevertheless thanx for your help

>
>--Imad
>www.EnterpriseDB.com
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: explain analyze is your friend

_________________________________________________________________
Die MSN Homepage liefert Ihnen alle Infos zu Ihren Lieblingsthemen. 
http://at.msn.com/

Re: temporary table / recursion

From

imad

Date:

11 February 2007, 17:06:07

Your INSERT statement is still missing EXECUTE command :-)

--Imad
www.EnterpriseDB.com

On 2/12/07, Robert Wimmer <seppwimmer@hotmail.com> wrote:
>
> >Not like that, use the execute command inside your function.
> >Here is the description and example.
> >http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html
> >
>
> that was what I tried before the PREPARE EXECUTE example and it did not
> work.
> so i will try it again
>
> *** snippet ***
>
> CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS $$
> DECLARE tmp RECORD;
> BEGIN
>
>   EXECUTE 'CREATE TEMP TABLE recurs_temp (id INT, parent_id INT, label
> TEXT)';
>
>   INSERT INTO recurs_temp SELECT * FROM recurs.tree WHERE id = p_start;  --
> first node
>   EXECUTE recurs.walk(p_start); -- create tree
>   FOR tmp IN SELECT * FROM recurs_temp LOOP RETURN NEXT tmp; END LOOP;
>
>   EXECUTE 'DROP TABLE recurs_temp';
>
>   RETURN;
>
> END; $$
> LANGUAGE plpgsql;
>
> ****
>
> and the output ...
>
> ****
>
> recurs=# SELECT * FROM recurs.scan(1);
> id | parent_id |   label
> ----+-----------+-----------
>   1 |           | 1
>   4 |         2 | 1.1.1
>   5 |         2 | 1.1.2
>   6 |         2 | 1.1.3
>   7 |         2 | 1.1.4
>   8 |         2 | 1.1.5
> 11 |        10 | 1.1.6.2
> 13 |        12 | 1.1.6.3.1
> 12 |        10 | 1.1.6.3
> 10 |         9 | 1.1.6.1
>   9 |         2 | 1.1.6
>   2 |         1 | 1.1
> 14 |         3 | 1.2.1
> 15 |         3 | 1.2.2
> 16 |         3 | 1.2.3
>   3 |         1 | 1.2
> (16 rows)
>
> recurs=# \dt
> No relations found.
> recurs=# SELECT * FROM recurs.scan(1);
> ERROR:  relation with OID 2084590 does not exist
> KONTEXT:  SQL statement "INSERT INTO recurs_temp SELECT * FROM recurs.tree
> WHERE id =  $1 "
> PL/pgSQL function "scan" line 6 at SQL statement
> recurs=# \dt
> No relations found.
>
> ****
>
>
> so i dont know what went wrong now ...
>
> i am using Postgres 8.0.3 on Windows XP
>
> nevertheless thanx for your help
>
> >
> >--Imad
> >www.EnterpriseDB.com
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 6: explain analyze is your friend
>
> _________________________________________________________________
> Die MSN Homepage liefert Ihnen alle Infos zu Ihren Lieblingsthemen.
> http://at.msn.com/
>
>

Re: temporary table / recursion

From

"Robert Wimmer"

Date:

11 February 2007, 17:47:02

dear imad,

thank you very much for your help and your patience. after "executing" 
really every statement it works. even the FOR tmp IN statement has to be 
"executed"

sepp

*** solution ***

CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS $$
DECLARE tmp RECORD;
BEGIN
 EXECUTE 'CREATE TEMP TABLE recurs_temp (id INT, parent_id INT, label 
TEXT)'; EXECUTE 'INSERT INTO recurs_temp SELECT * FROM recurs.tree WHERE id = ' || 
p_start;  -- first node EXECUTE recurs.walk(p_start); FOR tmp IN EXECUTE 'SELECT * FROM recurs_temp' LOOP RETURN NEXT
tmp;END 
 
LOOP; EXECUTE 'DROP TABLE recurs_temp'; RETURN;

END; $$
LANGUAGE plpgsql;

>From: imad <immaad@gmail.com>
>To: "Robert Wimmer" <seppwimmer@hotmail.com>
>CC: pgsql-interfaces@postgresql.org
>Subject: Re: [INTERFACES] temporary table / recursion
>Date: Mon, 12 Feb 2007 02:05:57 +0500
>
>Your INSERT statement is still missing EXECUTE command :-)
>
>--Imad
>www.EnterpriseDB.com
>
>On 2/12/07, Robert Wimmer <seppwimmer@hotmail.com> wrote:
>>
>> >Not like that, use the execute command inside your function.
>> >Here is the description and example.
>> >http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html
>> >
>>
>>that was what I tried before the PREPARE EXECUTE example and it did not
>>work.
>>so i will try it again
>>
>>*** snippet ***
>>
>>CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS 
>>$$
>>DECLARE tmp RECORD;
>>BEGIN
>>
>>   EXECUTE 'CREATE TEMP TABLE recurs_temp (id INT, parent_id INT, label
>>TEXT)';
>>
>>   INSERT INTO recurs_temp SELECT * FROM recurs.tree WHERE id = p_start;  
>>--
>>first node
>>   EXECUTE recurs.walk(p_start); -- create tree
>>   FOR tmp IN SELECT * FROM recurs_temp LOOP RETURN NEXT tmp; END LOOP;
>>
>>   EXECUTE 'DROP TABLE recurs_temp';
>>
>>   RETURN;
>>
>>END; $$
>>LANGUAGE plpgsql;
>>
>>****
>>
>>and the output ...
>>
>>****
>>
>>recurs=# SELECT * FROM recurs.scan(1);
>>id | parent_id |   label
>>----+-----------+-----------
>>   1 |           | 1
>>   4 |         2 | 1.1.1
>>   5 |         2 | 1.1.2
>>   6 |         2 | 1.1.3
>>   7 |         2 | 1.1.4
>>   8 |         2 | 1.1.5
>>11 |        10 | 1.1.6.2
>>13 |        12 | 1.1.6.3.1
>>12 |        10 | 1.1.6.3
>>10 |         9 | 1.1.6.1
>>   9 |         2 | 1.1.6
>>   2 |         1 | 1.1
>>14 |         3 | 1.2.1
>>15 |         3 | 1.2.2
>>16 |         3 | 1.2.3
>>   3 |         1 | 1.2
>>(16 rows)
>>
>>recurs=# \dt
>>No relations found.
>>recurs=# SELECT * FROM recurs.scan(1);
>>ERROR:  relation with OID 2084590 does not exist
>>KONTEXT:  SQL statement "INSERT INTO recurs_temp SELECT * FROM recurs.tree
>>WHERE id =  $1 "
>>PL/pgSQL function "scan" line 6 at SQL statement
>>recurs=# \dt
>>No relations found.
>>
>>****
>>
>>
>>so i dont know what went wrong now ...
>>
>>i am using Postgres 8.0.3 on Windows XP
>>
>>nevertheless thanx for your help
>>
>> >
>> >--Imad
>> >www.EnterpriseDB.com
>> >
>> >---------------------------(end of broadcast)---------------------------
>> >TIP 6: explain analyze is your friend
>>
>>_________________________________________________________________
>>Die MSN Homepage liefert Ihnen alle Infos zu Ihren Lieblingsthemen.
>>http://at.msn.com/
>>
>>

_________________________________________________________________
Nur die MSN Suche sorgt bei einer Web-Recherche für optimale Ergebnisse. 
http://search.msn.at/

Re: temporary table / recursion

From

imad

Date:

11 February 2007, 17:52:16

On 2/12/07, Robert Wimmer <seppwimmer@hotmail.com> wrote:
> dear imad,
>
> thank you very much for your help and your patience. after "executing"
> really every statement it works. even the FOR tmp IN statement has to be
> "executed"

Good to see this.

--Imad
www.EnterpriseDB.com