From: imad <immaad@gmail.com>
To: Robert Wimmer <seppwimmer@hotmail.com>
Cc: pgsql-interfaces@postgresql.org
Subject: Re: temporary table / recursion
Date: Sun, 11 Feb 2007 22:27:31 +0500
Message-ID: <1f30b80c0702110927g74385f97sc8a1e4d26a29076e@mail.gmail.com> (raw)
In-Reply-To: <BAY122-F253B3EBBE4C111F1919AF7D0920@phx.gbl>
References: <1f30b80c0702110639v3ff4b9b1pcb7a4d849b4f2928@mail.gmail.com>
<BAY122-F253B3EBBE4C111F1919AF7D0920@phx.gbl>
On 2/11/07, Robert Wimmer <seppwimmer@hotmail.com> wrote:
>
>
>
> >From: imad <immaad@gmail.com>
> >To: "Robert Wimmer" <seppwimmer@hotmail.com>
> >Subject: Re: [INTERFACES] temporary table / recursion
> >Date: Sun, 11 Feb 2007 19:39:25 +0500
> >
> >The problem is the plan which is stored against the INSERT statement
> >in PLpgSQL function. The next time it is called, it uses the same plan
> >and fails to locate the table based on the OID because CREATE temp
> >table has been called again and a new table exists now with a
> >different OID.
>
> i changed the code as you suggested
>
> *** snippet ***
>
> CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS $$
> DECLARE tmp RECORD;
> BEGIN
>
> 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
> PERFORM recurs.walk(p_start);
> FOR tmp IN SELECT * FROM recurs_temp LOOP RETURN NEXT tmp; END LOOP;
>
> DROP TABLE recurs_temp;
>
> RETURN;
>
> END; $$
> LANGUAGE plpgsql;
>
> >
> >Another way might be to now attach your temp table with the
> >transaction i.e. omit the clause ON COMMIT DROP. And drop the table at
> >the end of transaction manually.
> >
> >If this doesn't help too, see the execute command in PLpgSQL. This
> >will not save the plan against any command and this is what you need.
> >
>
> then i used the function with a prepared statement and EXECUTE and i had the
> same problems as before ...
>
> *** output ***
>
> recurs=# PREPARE recurs_func(INT) AS SELECT * FROM recurs.scan($1);
> PREPARE
> recurs=# EXECUTE recurs_func(1);
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
Recursive queries are also planned for 8.3.
--Imad
www.EnterpriseDB.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: <1f30b80c0702110927g74385f97sc8a1e4d26a29076e@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