From: imad <immaad@gmail.com>
To: Robert Wimmer <seppwimmer@hotmail.com>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: temporary table / recursion
Date: Mon, 12 Feb 2007 02:05:57 +0500
Message-ID: <1f30b80c0702111305m7c04ffe4hbb58d780561eb606@mail.gmail.com> (raw)
In-Reply-To: <BAY122-F27175E11C886523BF17E44D0920@phx.gbl>
References: <1f30b80c0702110927g74385f97sc8a1e4d26a29076e@mail.gmail.com>
<BAY122-F27175E11C886523BF17E44D0920@phx.gbl>
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/
>
>
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: immaad@gmail.com, seppwimmer@hotmail.com
Subject: Re: temporary table / recursion
In-Reply-To: <1f30b80c0702111305m7c04ffe4hbb58d780561eb606@mail.gmail.com>
* 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