Skip site navigation (1) Skip section navigation (2)

Re: temporary table / recursion

From: "Robert Wimmer" <seppwimmer(at)hotmail(dot)com>
To: immaad(at)gmail(dot)com
Cc: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: temporary table / recursion
Date: 2007-02-11 16:50:59
Message-ID: BAY122-F253B3EBBE4C111F1919AF7D0920@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-interfaces


>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);
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=# EXECUTE recurs_func(1);
ERROR:  relation with OID 2084485 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=# DEALLOCATE recurs_func;
DEALLOCATE
recurs=# PREPARE recurs_func(INT) AS SELECT * FROM recurs.scan($1);
PREPARE
recurs=# EXECUTE recurs_func(1);
ERROR:  relation with OID 2084485 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


sepp


>BTW, this issue is being worked on for 8.3.
>

are there intentions to implement recursive queries like WITH or CONNECT BY 
in postgres ?

>--Imad
>www.EnterpriseDB.com
>
>

_________________________________________________________________
Was halten Sie von einer Seite, die all Ihre Lieblingsthemen beinhaltet? 
http://at.msn.com/


Responses

pgsql-interfaces by date

Next:From: imadDate: 2007-02-11 17:27:31
Subject: Re: temporary table / recursion
Previous:From: imadDate: 2007-02-11 11:52:14
Subject: Re: temporary table / recursion

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group