Message-ID: <45A60F4D.8080305@archonet.com>
Date: Thu, 11 Jan 2007 10:19:57 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: IceDove 1.5.0.9 (X11/20061220)
MIME-Version: 1.0
To: Marc Mamin <M.Mamin@intershop.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: a way to generate functions dynamically ?
References: 
 <CA896D7906BF224F8A6D74A1B7E54AB30175095D@JENMAIL01.ad.intershop.net>
In-Reply-To: 
 <CA896D7906BF224F8A6D74A1B7E54AB30175095D@JENMAIL01.ad.intershop.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Marc Mamin wrote:
> Hello, 
>  
> I need to generate some procedures that depend  data models stored in my
> DBs. 
> As I have different models in different databases, the stored procedures
> will differ.
>  
> My idea is to generate the required stored procedures dynamically once a
> model is defined.
> I will probably do this within the application. But is there a way to
> achieve this with plpgsql ?
>  
> here a naive try to illustrate  my idea: 
>  
> CREATE OR REPLACE FUNCTION test(p1 int)
>   RETURNS integer AS
> $BODY$
>  
> EXECUTE'
>     CREATE OR REPLACE FUNCTION generated(p2 int)
>     RETURNS integer AS
>     $BODY$
>      BEGIN

>  ERROR: syntax error at or near "BEGIN"
> SQL state: 42601

The only mistake is in the $$ quoting. It thinks the second $BODY$ is 
closing the first. You'll need to generate a different quote-identifier 
(e.g. $CUSTOM$).

-- 
   Richard Huxton
   Archonet Ltd