Re: temporary table / recursion

Try creating your temp table outside any PLpgSQL function.

--Imad
www.EnterpriseDB.com

On 2/11/07, Robert Wimmer <seppwimmer(at)hotmail(dot)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
>