DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=Gj5HAOXs+x2bFL9r8XfMCsSHnNQr0g/TO/OY7qSlRow75DzsFAyrbU+31Pn/fIej5qhqNyXRf1A+AtmbVjPuLrXsW6JGb6PA5airhm+LXC3cm3g2n60q6sWiwAN8FGz6teQXvjgjkZrqYwE9tJODRPhZ8CECS6u/yg0qEdHqtY4=
Message-ID: <1f30b80c0702110352icb20bebs3a1d23d515920f9b@mail.gmail.com>
Date: Sun, 11 Feb 2007 16:52:14 +0500
From: imad <immaad@gmail.com>
To: "Robert Wimmer" <seppwimmer@hotmail.com>
Subject: Re: temporary table / recursion
Cc: pgsql-interfaces@postgresql.org
In-Reply-To: <BAY122-F365C119161ABEB19E76AE6D0920@phx.gbl>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <BAY122-F365C119161ABEB19E76AE6D0920@phx.gbl>

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 th=
e
> 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 d=
oes
> 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 =3D 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 =3D 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 =3D 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.tre=
e
> WHERE id =3D  $1 "
> PL/pgSQL function "scan" line 6 at SQL statement
>
> *****************************************/
>
> _________________________________________________________________
> Nur die MSN Suche sorgt bei einer Web-Recherche f=FCr optimale Ergebnisse=
.
> http://search.msn.at/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>