Thread: a way to generate functions dynamically ?

a way to generate functions dynamically ?

From

"Marc Mamin"

Date:

11 January 2007, 07:31:34

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

Re: a way to generate functions dynamically ?

From

Richard Huxton

Date:

11 January 2007, 09:22:42

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