EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ? - Mailing list pgsql-sql

Hello,
I have a large upddate to perform on tables which are dynamically
generated (dynamic names).
In this simplified example, the operation should replace in each family
the "mygroup" of each item of rang=0 with the "mygroup" value of the
element of rang=1 :
(the * indicate the modified values)
id    family  rang     mygroup
1      1        0      1
2      1        1      2
3      1        2      3
4      1        3      4
5      2        0      6
6      2        1      6
7      2        2      7
8      2        3      7
9      3        0      10
10     3        1      20
11     3        2      21
After the update:
1     1        0       2 *
2     1        1       2
3     1        2       3
4     1        3       4
5     2        0       6
6     2        1       6
7     2        2       7
8     2        3       7
9     3        0       20 *
10    3        1       20
11    3        2       21

In the following function, I would like to use a prepared statement for
the update command but I get stuck with the tho different meanings of
EXECUTE ...
Is there a way to achieve this ?
Thanks,
Marc

CREATE OR REPLACE FUNCTION test_function(tablename varchar) RETURNS integer AS
$BODY$
DECLARE
rec record;
top_group int;
top_family character(16);
top_id int;
BEGIN  /*  the prepared statement must be generated dynamically in order to
include the table name.  */  EXECUTE 'PREPARE update_stmt (int, int) AS      update '||tablename||' set mygroup= $1
whereid = $2'; 
    /*  using "select distinct on" allows to retrieve and sort the required
information for the update.  this is faster than a self join on the table  */  for rec in execute      'select DISTINCT
on (family,rang)      family, rang, mygroup, id     from '||tablename||'     where  rang < 2     order by family, rang'
  loop    IF rec.rang = 0 THEN      top_group  := rec.mygroup;      top_family := rec.family;      top_id     :=
rec.id;   ELSIF rec.family = top_family AND rec.mygroup <> top_group THEN              /*          Update without using
aprepared statement          EXECUTE 'update '||tablename||' set mygroup= 
'||rec.mygroup||' where id = '||top_id;          */                    -- This works, but the command has to be
computedfor each 
iteration          EXECUTE 'EXECUTE
update_stmt('||rec.mygroup||','||top_id||')';              /*          Following syntax would be fine          PERFORM
EXECUTEupdate_stmt(rec.mygroup,top_id);          */        END IF; 
  end loop;  DEALLOCATE update_stmt;  RETURN 0;

END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE;

/* ============================================
test data:
=============================================== */
--drop table test_table;
create table test_table(id int,family int,rang int,mygroup int);
insert into test_table values (1,1,0,1);
insert into test_table values (2,1,1,2);
insert into test_table values (3,1,2,3);
insert into test_table values (4,1,3,4);
insert into test_table values (5,2,0,6);
insert into test_table values (6,2,1,6);
insert into test_table values (7,2,2,7);
insert into test_table values (8,2,3,7);
insert into test_table values (9, 3,0,10);
insert into test_table values (10,3,1,20);
insert into test_table values (11,3,2,21);
select test_function('test_table');
select * from test_table order by id;