From: Robert Wimmer <seppwimmer@hotmail.com>
To: immaad@gmail.com
Cc: pgsql-interfaces@postgresql.org
Subject: Re: temporary table / recursion
Date: Sun, 11 Feb 2007 16:50:59 +0000
Message-ID: <BAY122-F253B3EBBE4C111F1919AF7D0920@phx.gbl> (raw)
In-Reply-To: <1f30b80c0702110639v3ff4b9b1pcb7a4d849b4f2928@mail.gmail.com>
>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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: seppwimmer@hotmail.com, immaad@gmail.com
Subject: Re: temporary table / recursion
In-Reply-To: <BAY122-F253B3EBBE4C111F1919AF7D0920@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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