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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
