pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedtemporary table / recursion
9+ messages / 3 participants
[nested] [flat]
* temporary table / recursion
@ 2007-02-11 09:19 Robert Wimmer <seppwimmer@hotmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Robert Wimmer @ 2007-02-11 09:19 UTC (permalink / raw)
To: pgsql-interfaces
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: temporary table / recursion
@ 2007-02-11 11:52 imad <immaad@gmail.com>
parent: Robert Wimmer <seppwimmer@hotmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: imad @ 2007-02-11 11:52 UTC (permalink / raw)
To: Robert Wimmer <seppwimmer@hotmail.com>; +Cc: pgsql-interfaces
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
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: temporary table / recursion
@ 2007-02-11 16:50 Robert Wimmer <seppwimmer@hotmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Robert Wimmer @ 2007-02-11 16:50 UTC (permalink / raw)
To: immaad@gmail.com; +Cc: pgsql-interfaces
>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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: temporary table / recursion
@ 2007-02-11 17:27 imad <immaad@gmail.com>
parent: Robert Wimmer <seppwimmer@hotmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: imad @ 2007-02-11 17:27 UTC (permalink / raw)
To: Robert Wimmer <seppwimmer@hotmail.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: temporary table / recursion
@ 2007-02-11 17:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robert Wimmer <seppwimmer@hotmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Tom Lane @ 2007-02-11 17:41 UTC (permalink / raw)
To: Robert Wimmer <seppwimmer@hotmail.com>; +Cc: pgsql-interfaces
"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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: temporary table / recursion
@ 2007-02-11 20:57 Robert Wimmer <seppwimmer@hotmail.com>
parent: imad <immaad@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Robert Wimmer @ 2007-02-11 20:57 UTC (permalink / raw)
To: immaad@gmail.com; pgsql-interfaces
>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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: temporary table / recursion
@ 2007-02-11 21:05 imad <immaad@gmail.com>
parent: Robert Wimmer <seppwimmer@hotmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: imad @ 2007-02-11 21:05 UTC (permalink / raw)
To: Robert Wimmer <seppwimmer@hotmail.com>; +Cc: pgsql-interfaces
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/
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: temporary table / recursion
@ 2007-02-11 21:46 Robert Wimmer <seppwimmer@hotmail.com>
parent: imad <immaad@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Robert Wimmer @ 2007-02-11 21:46 UTC (permalink / raw)
To: immaad@gmail.com; pgsql-interfaces
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: temporary table / recursion
@ 2007-02-11 21:52 imad <immaad@gmail.com>
parent: Robert Wimmer <seppwimmer@hotmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: imad @ 2007-02-11 21:52 UTC (permalink / raw)
To: Robert Wimmer <seppwimmer@hotmail.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2007-02-11 21:52 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-11 09:19 temporary table / recursion Robert Wimmer <seppwimmer@hotmail.com>
2007-02-11 11:52 ` imad <immaad@gmail.com>
2007-02-11 17:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-02-11 16:50 Re: temporary table / recursion Robert Wimmer <seppwimmer@hotmail.com>
2007-02-11 17:27 ` imad <immaad@gmail.com>
2007-02-11 20:57 ` Robert Wimmer <seppwimmer@hotmail.com>
2007-02-11 21:05 ` imad <immaad@gmail.com>
2007-02-11 21:46 ` Robert Wimmer <seppwimmer@hotmail.com>
2007-02-11 21:52 ` imad <immaad@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox