Skip site navigation (1) Skip section navigation (2)

temporary table / recursion

From: "Robert Wimmer" <seppwimmer(at)hotmail(dot)com>
To: pgsql-interfaces(at)postgresql(dot)org
Subject: temporary table / recursion
Date: 2007-02-11 09:19:58
Message-ID: BAY122-F365C119161ABEB19E76AE6D0920@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-interfaces
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/


Responses

pgsql-interfaces by date

Next:From: imadDate: 2007-02-11 11:52:14
Subject: Re: temporary table / recursion
Previous:From: Andy Shellam (Mailing Lists)Date: 2007-02-07 14:25:22
Subject: Re: Help needed

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group