From: Robert Wimmer <seppwimmer@hotmail.com>
To: pgsql-interfaces@postgresql.org
Subject: temporary table / recursion
Date: Sun, 11 Feb 2007 09:19:58 +0000
Message-ID: <BAY122-F365C119161ABEB19E76AE6D0920@phx.gbl> (raw)
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/
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
Subject: Re: temporary table / recursion
In-Reply-To: <BAY122-F365C119161ABEB19E76AE6D0920@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