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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | imad | 2007-02-11 21:05:57 | Re: temporary table / recursion |
Previous Message | Tom Lane | 2007-02-11 17:41:16 | Re: temporary table / recursion |