Re: temporary table / recursion

["Robert Wimmer" <seppwimmer(at)hotmail(dot)com>]

dear imad,

thank you very much for your help and your patience. after "executing" 
really every statement it works. even the FOR tmp IN statement has to be 
"executed"

sepp

*** solution ***

CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS $$
DECLARE tmp RECORD;
BEGIN

 EXECUTE 'CREATE TEMP TABLE recurs_temp (id INT, parent_id INT, label 
TEXT)';
 EXECUTE 'INSERT INTO recurs_temp SELECT * FROM recurs.tree WHERE id = ' || 
p_start;  -- first node
 EXECUTE recurs.walk(p_start);
 FOR tmp IN EXECUTE 'SELECT * FROM recurs_temp' LOOP RETURN NEXT tmp; END 
LOOP;
 EXECUTE 'DROP TABLE recurs_temp';
 RETURN;

END; $$
LANGUAGE plpgsql;

> From: imad <immaad(at)gmail(dot)com>
> To: "Robert Wimmer" <seppwimmer(at)hotmail(dot)com>
> CC: pgsql-interfaces(at)postgresql(dot)org
> Subject: Re: [INTERFACES] temporary table / recursion
> Date: Mon, 12 Feb 2007 02:05:57 +0500
>
> Your INSERT statement is still missing EXECUTE command :-)
>
> --Imad
> www.EnterpriseDB.com
>
> On 2/12/07, Robert Wimmer <seppwimmer(at)hotmail(dot)com> wrote:
> > >Not like that, use the execute command inside your function.
> > >Here is the description and example.
> > >http://www.postgresql.org/docs/8.0/static/plpgsql-statements.html
> > >
> >
> > that was what I tried before the PREPARE EXECUTE example and it did not
> > work.
> > so i will try it again
> >
> > *** snippet ***
> >
> > CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS 
> > $$
> > DECLARE tmp RECORD;
> > BEGIN
> >
> >   EXECUTE 'CREATE TEMP TABLE recurs_temp (id INT, parent_id INT, label
> > TEXT)';
> >
> >   INSERT INTO recurs_temp SELECT * FROM recurs.tree WHERE id = p_start;  
> > --
> > first node
> >   EXECUTE recurs.walk(p_start); -- create tree
> >   FOR tmp IN SELECT * FROM recurs_temp LOOP RETURN NEXT tmp; END LOOP;
> >
> >   EXECUTE 'DROP TABLE recurs_temp';
> >
> >   RETURN;
> >
> > END; $$
> > LANGUAGE plpgsql;
> >
> > ****
> >
> > and the output ...
> >
> > ****
> >
> > recurs=# SELECT * FROM recurs.scan(1);
> > 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)
> >
> > recurs=# \dt
> > No relations found.
> > recurs=# SELECT * FROM recurs.scan(1);
> > ERROR:  relation with OID 2084590 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
> > recurs=# \dt
> > No relations found.
> >
> > ****
> >
> >
> > so i dont know what went wrong now ...
> >
> > i am using Postgres 8.0.3 on Windows XP
> >
> > nevertheless thanx for your help
> >
> > >
> > >--Imad
> > >www.EnterpriseDB.com
> > >
> > >---------------------------(end of broadcast)---------------------------
> > >TIP 6: explain analyze is your friend
> >
> > _________________________________________________________________
> > Die MSN Homepage liefert Ihnen alle Infos zu Ihren Lieblingsthemen.
> > http://at.msn.com/

_________________________________________________________________
Nur die MSN Suche sorgt bei einer Web-Recherche für optimale Ergebnisse. 
http://search.msn.at/