From: Robert Wimmer <seppwimmer@hotmail.com>
To: immaad@gmail.com
To: pgsql-interfaces@postgresql.org
Subject: Re: temporary table / recursion
Date: Sun, 11 Feb 2007 21:46:51 +0000
Message-ID: <BAY122-F3521B403CDCC1417091F34D0920@phx.gbl> (raw)
In-Reply-To: <1f30b80c0702111305m7c04ffe4hbb58d780561eb606@mail.gmail.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@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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: seppwimmer@hotmail.com, immaad@gmail.com
Subject: Re: temporary table / recursion
In-Reply-To: <BAY122-F3521B403CDCC1417091F34D0920@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox