Обсуждение: temporary table / recursion
hi,
i cannot drop a temporary table that was created in a plpgsql function.
I cannot drop it inside the function and also not from "outside" using the
command line. even using the qualified table name "pg_temp_1.recurs_temp"
has no effect.
As far see the temporary table does not exist from the "user view" - it does
not exist if i search for the table in pg_class - but it still exists in the
"system view".
*** snippet ***
CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS $$
DECLARE tmp RECORD;
BEGIN
CREATE TEMPORARY TABLE recurs_temp (id INTEGER, parent_id INTEGER,label
TEXT) ON COMMIT DROP;
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;
RETURN;
END; $$
LANGUAGE plpgsql;
thanks for help
sepp wimmer
*** whole script ***
DROP SCHEMA recurs CASCADE;
CREATE SCHEMA recurs;
CREATE TABLE recurs.tree ( id INTEGER NOT NULL, parent_id INTEGER, label TEXT );
CREATE FUNCTION recurs.walk(p_start INTEGER) RETURNS VOID AS $$
DECLARE child RECORD;
BEGIN
FOR child IN SELECT * FROM recurs.tree WHERE parent_id = p_start LOOP PERFORM recurs.walk(child.id); INSERT INTO
recurs_temp(id,parent_id,label)
VALUES(child.id,child.parent_id,child.label); END LOOP; RETURN;
END; $$
LANGUAGE plpgsql;
CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS $$
DECLARE tmp RECORD;
BEGIN
CREATE TEMPORARY TABLE recurs_temp (id INTEGER, parent_id INTEGER,label
TEXT) ON COMMIT DROP;
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;
RETURN;
END; $$
LANGUAGE plpgsql;
INSERT INTO recurs.tree(id,parent_id,label) VALUES(1,NULL,'1');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(2,1,'1.1');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(3,1,'1.2');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(4,2,'1.1.1');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(5,2,'1.1.2');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(6,2,'1.1.3');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(7,2,'1.1.4');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(8,2,'1.1.5');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(9,2,'1.1.6');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(10,9,'1.1.6.1');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(11,10,'1.1.6.2');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(12,10,'1.1.6.3');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(13,12,'1.1.6.3.1');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(14,3,'1.2.1');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(15,3,'1.2.2');
INSERT INTO recurs.tree(id,parent_id,label) VALUES(16,3,'1.2.3');
SELECT * FROM recurs.scan(1);
/* output as expected ****************
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)
****************************************/
SELECT * FROM recurs.scan(1); -- once again > will fail
/* output as NOT expected **************
psql:recurs.schema:58: ERROR: relation with OID 2080891 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
*****************************************/
_________________________________________________________________
Nur die MSN Suche sorgt bei einer Web-Recherche für optimale Ergebnisse.
http://search.msn.at/
Try creating your temp table outside any PLpgSQL function. --Imad www.EnterpriseDB.com On 2/11/07, Robert Wimmer <seppwimmer@hotmail.com> wrote: > hi, > > i cannot drop a temporary table that was created in a plpgsql function. > I cannot drop it inside the function and also not from "outside" using the > command line. even using the qualified table name "pg_temp_1.recurs_temp" > has no effect. > > As far see the temporary table does not exist from the "user view" - it does > not exist if i search for the table in pg_class - but it still exists in the > "system view". > > *** snippet *** > > CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS $$ > DECLARE tmp RECORD; > BEGIN > > CREATE TEMPORARY TABLE recurs_temp (id INTEGER, parent_id INTEGER,label > TEXT) ON COMMIT DROP; > > 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; > > RETURN; > > END; $$ > LANGUAGE plpgsql; > > thanks for help > > sepp wimmer > > > > *** whole script *** > > DROP SCHEMA recurs CASCADE; > CREATE SCHEMA recurs; > > CREATE TABLE recurs.tree ( > id INTEGER NOT NULL, > parent_id INTEGER, > label TEXT > ); > > CREATE FUNCTION recurs.walk(p_start INTEGER) RETURNS VOID AS $$ > DECLARE child RECORD; > BEGIN > > FOR child IN SELECT * FROM recurs.tree WHERE parent_id = p_start LOOP > PERFORM recurs.walk(child.id); > INSERT INTO recurs_temp(id,parent_id, label) > VALUES(child.id,child.parent_id,child.label); > END LOOP; > RETURN; > > END; $$ > LANGUAGE plpgsql; > > CREATE FUNCTION recurs.scan(p_start INTEGER) RETURNS SETOF recurs.tree AS $$ > DECLARE tmp RECORD; > BEGIN > > CREATE TEMPORARY TABLE recurs_temp (id INTEGER, parent_id INTEGER,label > TEXT) ON COMMIT DROP; > > 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; > > RETURN; > > END; $$ > LANGUAGE plpgsql; > > INSERT INTO recurs.tree(id,parent_id,label) VALUES(1,NULL,'1'); > INSERT INTO recurs.tree(id,parent_id,label) VALUES(2,1,'1.1'); > INSERT INTO recurs.tree(id,parent_id,label) VALUES(3,1,'1.2'); > INSERT INTO recurs.tree(id,parent_id,label) VALUES(4,2,'1.1.1'); > INSERT INTO recurs.tree(id,parent_id,label) VALUES(5,2,'1.1.2'); > INSERT INTO recurs.tree(id,parent_id,label) VALUES(6,2,'1.1.3'); > INSERT INTO recurs.tree(id,parent_id,label) VALUES(7,2,'1.1.4'); > INSERT INTO recurs.tree(id,parent_id,label) VALUES(8,2,'1.1.5'); > INSERT INTO recurs.tree(id,parent_id,label) VALUES(9,2,'1.1.6'); > INSERT INTO recurs.tree(id,parent_id,label) VALUES(10,9,'1.1.6.1'); > INSERT INTO recurs.tree(id,parent_id,label) VALUES(11,10,'1.1.6.2'); > INSERT INTO recurs.tree(id,parent_id,label) VALUES(12,10,'1.1.6.3'); > INSERT INTO recurs.tree(id,parent_id,label) VALUES(13,12,'1.1.6.3.1'); > INSERT INTO recurs.tree(id,parent_id,label) VALUES(14,3,'1.2.1'); > INSERT INTO recurs.tree(id,parent_id,label) VALUES(15,3,'1.2.2'); > INSERT INTO recurs.tree(id,parent_id,label) VALUES(16,3,'1.2.3'); > > SELECT * FROM recurs.scan(1); > > /* output as expected **************** > > 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) > > ****************************************/ > > SELECT * FROM recurs.scan(1); -- once again > will fail > > /* output as NOT expected ************** > > psql:recurs.schema:58: ERROR: relation with OID 2080891 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 > > *****************************************/ > > _________________________________________________________________ > Nur die MSN Suche sorgt bei einer Web-Recherche für optimale Ergebnisse. > http://search.msn.at/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
>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/
On 2/11/07, Robert Wimmer <seppwimmer@hotmail.com> wrote: > > > > >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); 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
"Robert Wimmer" <seppwimmer@hotmail.com> writes:
> i cannot drop a temporary table that was created in a plpgsql function.
It was created with "on commit drop". Are you sure it still exists?
The problem looks to me like the well-known issue that plpgsql caches
plans, and so you can't have it refer to different temp tables on
successive executions (in the same session) unless you use EXECUTE.
regards, tom lane
>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/
Your INSERT statement is still missing EXECUTE command :-) --Imad www.EnterpriseDB.com On 2/12/07, Robert Wimmer <seppwimmer@hotmail.com> wrote: > > >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/ > >
dear imad, thank you very much for your help and your patience. after "executing" really every statement it works. even the FOR tmp IN statement has to be "executed" sepp *** solution *** 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)'; EXECUTE 'INSERT INTO recurs_temp SELECT * FROM recurs.tree WHERE id = ' || p_start; -- first node EXECUTE recurs.walk(p_start); FOR tmp IN EXECUTE 'SELECT * FROM recurs_temp' LOOP RETURN NEXT tmp;END LOOP; EXECUTE 'DROP TABLE recurs_temp'; RETURN; END; $$ LANGUAGE plpgsql; >From: imad <immaad@gmail.com> >To: "Robert Wimmer" <seppwimmer@hotmail.com> >CC: pgsql-interfaces@postgresql.org >Subject: Re: [INTERFACES] temporary table / recursion >Date: Mon, 12 Feb 2007 02:05:57 +0500 > >Your INSERT statement is still missing EXECUTE command :-) > >--Imad >www.EnterpriseDB.com > >On 2/12/07, Robert Wimmer <seppwimmer@hotmail.com> wrote: >> >> >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/ >> >> _________________________________________________________________ Nur die MSN Suche sorgt bei einer Web-Recherche für optimale Ergebnisse. http://search.msn.at/
On 2/12/07, Robert Wimmer <seppwimmer@hotmail.com> wrote: > dear imad, > > thank you very much for your help and your patience. after "executing" > really every statement it works. even the FOR tmp IN statement has to be > "executed" Good to see this. --Imad www.EnterpriseDB.com