Re: temporary table / recursion
От | Robert Wimmer |
---|---|
Тема | Re: temporary table / recursion |
Дата | |
Msg-id | BAY122-F253B3EBBE4C111F1919AF7D0920@phx.gbl обсуждение исходный текст |
Ответ на | temporary table / recursion ("Robert Wimmer" <seppwimmer@hotmail.com>) |
Ответы |
Re: temporary table / recursion
|
Список | pgsql-interfaces |
>From: imad <immaad@gmail.com> >To: "Robert Wimmer" <seppwimmer@hotmail.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/
В списке pgsql-interfaces по дате отправления: