Re: temporary table / recursion - Mailing list 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
>