Re: temporary table / recursion

[imad <immaad(at)gmail(dot)com>]

On 2/11/07, Robert Wimmer <seppwimmer(at)hotmail(dot)com> wrote:
> >From: imad <immaad(at)gmail(dot)com>
> >To: "Robert Wimmer" <seppwimmer(at)hotmail(dot)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