From: imad <immaad@xxxxxxxxx>
To: "Robert Wimmer" <seppwimmer@xxxxxxxxxxx>
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/
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| 
