From: Dirk Jagdmann <jagdmann@gmail.com>
To: Marc Mamin <M.Mamin@intershop.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?
Date: Tue, 4 Sep 2007 11:38:29 +0200
Message-ID: <5d0f60990709040238i1bd78235md22663f8bd2f2700@mail.gmail.com> (raw)
In-Reply-To: <CA896D7906BF224F8A6D74A1B7E54AB301750B8A@JENMAIL01.ad.intershop.net>
References: <CA896D7906BF224F8A6D74A1B7E54AB301750B8A@JENMAIL01.ad.intershop.net>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jagdmann@gmail.com, M.Mamin@intershop.de
Subject: Re: EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?
In-Reply-To: <5d0f60990709040238i1bd78235md22663f8bd2f2700@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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