pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

a way to generate functions dynamically ?
2+ messages / 2 participants
[nested] [flat]

* a way to generate functions dynamically ?
@ 2007-01-11 08:29  Marc Mamin <M.Mamin@intershop.de>
  0 siblings, 1 reply; 2+ messages in thread

From: Marc Mamin @ 2007-01-11 08:29 UTC (permalink / raw)
  To: pgsql-sql

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
       return p2 + ' || p1 || '; 
     END;
    
    $BODY$
  
    LANGUAGE ''plpgsql'' VOLATILE
  ';
 
 ERROR: syntax error at or near "BEGIN"
SQL state: 42601
Character: 156 
 
 
Thanks,
 
Marc 

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: a way to generate functions dynamically ?
@ 2007-01-11 10:19  Richard Huxton <dev@archonet.com>
  parent: Marc Mamin <M.Mamin@intershop.de>
  0 siblings, 0 replies; 2+ messages in thread

From: Richard Huxton @ 2007-01-11 10:19 UTC (permalink / raw)
  To: Marc Mamin <M.Mamin@intershop.de>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2007-01-11 10:19 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-01-11 08:29 a way to generate functions dynamically ? Marc Mamin <M.Mamin@intershop.de>
2007-01-11 10:19 ` Richard Huxton <dev@archonet.com>

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