Message-ID: <BAY122-F3521B403CDCC1417091F34D0920@phx.gbl>
In-Reply-To: <1f30b80c0702111305m7c04ffe4hbb58d780561eb606@mail.gmail.com>
From: "Robert Wimmer" <seppwimmer@hotmail.com>
To: immaad@gmail.com, pgsql-interfaces@postgresql.org
Subject: Re: temporary table / recursion
Date: Sun, 11 Feb 2007 21:46:51 +0000
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1; format=flowed

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@gmail.com>
>To: "Robert Wimmer" <seppwimmer@hotmail.com>
>CC: pgsql-interfaces@postgresql.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@hotmail.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/