From: | "Robert Wimmer" <seppwimmer(at)hotmail(dot)com> |
---|---|
To: | immaad(at)gmail(dot)com, pgsql-interfaces(at)postgresql(dot)org |
Subject: | Re: temporary table / recursion |
Date: | 2007-02-11 21:46:51 |
Message-ID: | BAY122-F3521B403CDCC1417091F34D0920@phx.gbl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-interfaces |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | imad | 2007-02-11 21:52:10 | Re: temporary table / recursion |
Previous Message | imad | 2007-02-11 21:05:57 | Re: temporary table / recursion |