DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=ONIuqKthd/if6yWdlctNqhAcdPq73em98klicOgx6frgATSRnzMZZJb34u3ywu+tAmZD2/PSx7vgyLz5p72NuIlw9xqMddvu+P6fMM5chMJ5JwRhtx167D2rbMwpMR1W2s1gusFCpzO/bt0Lz+/K4ogkwc6h7ofrQt6ejsp2KRU=
Message-ID: <5d0f60990709040238i1bd78235md22663f8bd2f2700@mail.gmail.com>
Date: Tue, 4 Sep 2007 11:38:29 +0200
From: "Dirk Jagdmann" <jagdmann@gmail.com>
To: "Marc Mamin" <M.Mamin@intershop.de>
Subject: Re: EXECUTE 'EXECUTE ...' or how to use dynamic prepared statements ?
Cc: pgsql-sql@postgresql.org
In-Reply-To: 
 <CA896D7906BF224F8A6D74A1B7E54AB301750B8A@JENMAIL01.ad.intershop.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
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