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, pgsql-interfaces(at)postgresql(dot)org
Subject: Re: temporary table / recursion
Date: 2007-02-11 20:57:35
Message-ID: BAY122-F27175E11C886523BF17E44D0920@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-interfaces
>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/


In response to

Responses

pgsql-interfaces by date

Next:From: imadDate: 2007-02-11 21:05:57
Subject: Re: temporary table / recursion
Previous:From: Tom LaneDate: 2007-02-11 17:41:16
Subject: Re: temporary table / recursion

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