pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedEXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?
3+ messages / 2 participants
[nested] [flat]
* EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?
@ 2007-09-04 08:35 Marc Mamin <M.Mamin@intershop.de>
0 siblings, 1 reply; 3+ messages in thread
From: Marc Mamin @ 2007-09-04 08:35 UTC (permalink / raw)
To: 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;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?
@ 2007-09-04 09:38 Dirk Jagdmann <jagdmann@gmail.com>
parent: Marc Mamin <M.Mamin@intershop.de>
0 siblings, 1 reply; 3+ messages in thread
From: Dirk Jagdmann @ 2007-09-04 09:38 UTC (permalink / raw)
To: Marc Mamin <M.Mamin@intershop.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?
@ 2007-09-05 08:22 Marc Mamin <M.Mamin@intershop.de>
parent: Dirk Jagdmann <jagdmann@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Marc Mamin @ 2007-09-05 08:22 UTC (permalink / raw)
To: Dirk Jagdmann <jagdmann@gmail.com>; +Cc: pgsql-sql
Hello Dirk,
I have to disagree.
Your first update query is very low. It probably implies to run the sub
select statement for each row to be updated.
Following update statement is already much faster: (using UPDATE FROM)
update test_table
set mygroup= t.mygroup
from test_table as t
where t.family = test_table.family
and t.rang = 1
and table.rang=0
-- perform the updte only when required
and mygroup <> t.mygroup;
But when you are dealing with "parent - child" relations within a
single table as in my case,
a single table scan with SELECT DISTINCT ON and a row by row comparison
on the result set appears to be faster.
I tested both approaches on tables with ca. 14'000'000 rows where 25% of
them needed to be updated.
The above update statement run in 5H30' where my function did the job in
2H.
(as my tables are very large, much time is lost in i/o wait)
Cheers,
Marc
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-09-05 08:22 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-09-04 08:35 EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ? Marc Mamin <M.Mamin@intershop.de>
2007-09-04 09:38 ` Dirk Jagdmann <jagdmann@gmail.com>
2007-09-05 08:22 ` Marc Mamin <M.Mamin@intershop.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox