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 where id = $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 a prepared statement
EXECUTE 'update '||tablename||' set mygroup=
'||rec.mygroup||' where id = '||top_id;
*/
-- This works, but the command has to be computed for each
iteration
EXECUTE 'EXECUTE
update_stmt('||rec.mygroup||','||top_id||')';
/*
Following syntax would be fine
PERFORM EXECUTE update_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;
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
