From: | imad <immaad(at)gmail(dot)com> |
---|---|
To: | "Robert Wimmer" <seppwimmer(at)hotmail(dot)com> |
Cc: | pgsql-interfaces(at)postgresql(dot)org |
Subject: | Re: temporary table / recursion |
Date: | 2007-02-11 17:27:31 |
Message-ID: | 1f30b80c0702110927g74385f97sc8a1e4d26a29076e@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-interfaces |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-02-11 17:41:16 | Re: temporary table / recursion |
Previous Message | Robert Wimmer | 2007-02-11 16:50:59 | Re: temporary table / recursion |