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

From: "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?
Date: 2007-09-04 08:35:55
Message-ID: CA896D7906BF224F8A6D74A1B7E54AB301750B8A@JENMAIL01.ad.intershop.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 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;

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2007-09-04 08:43:52 Re: Difference between "foo is false" and "foo=false"? Partial index on boolean.
Previous Message Bryce Nesbitt 2007-09-04 07:51:29 Partial index on boolean - Sometimes fails to index scan