Re: EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?

Hello Marc,

at first I tried to solve your update of the tables. The example you
gave should be done with an update statement like the following:

update test_table
set mygroup=(select t.mygroup
from test_table as t
where t.family = test_table.family
and t.rang = test_table.rang+1)
where rang=0;

If you have to write a function which receives the tablename as an
argument it would look like:

CREATE OR REPLACE FUNCTION test_function(tablename text)
RETURNS integer AS $BODY$
BEGIN
EXECUTE 'update ' || tablename || '
set mygroup=(select t.mygroup
from ' || tablename || ' as t
where t.family = test_table.family
and t.rang = test_table.rang+1)
where rang=0;'
RETURN 0;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;

Generally you should avoid using explicit for/loop constructs in your
stored procedures if the action can be solved by a single SQL
statement, because the optimizer can make a better execution plan.

--
---> Dirk Jagdmann
----> http://cubic.org/~doj
-----> http://llg.cubic.org