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)whererang=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