Thread: Dynamic binding in plpgsql function

Hi,

I would like to write a generic plpgsql function with a text parameter being a callback function name so that my
generalfunction can call this callback function. e.g.: 

CREATE OR REPLACE FUNCTION ST_MyCallbackFunction(y int)
    RETURNS int AS $$
    DECLARE
    BEGIN
        RETURN someCalculationBasedOnY;
    END;
    $$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION ST_MyGeneralFunction(callback text)
    RETURNS SETOF geomval AS $$
    DECLARE
        x integer;
        y integer;
    BEGIN
        y := somecalculation;
        x := 'callback'(y);  --This is what I need
        RETURN x;
    END;
    $$ LANGUAGE 'plpgsql';

I don't want to do an EXECUTE statement since I have no table to put after the FROM clause. I want to assign the
resultingvalue directly to a variable like in my example. 

Can I/How can I achieve this?

Thanks,

Pierre

Hello

2011/3/2 Pierre Racine <Pierre.Racine@sbf.ulaval.ca>:
> Hi,
>
> I would like to write a generic plpgsql function with a text parameter being a callback function name so that my
generalfunction can call this callback function. e.g.: 
>
> CREATE OR REPLACE FUNCTION ST_MyCallbackFunction(y int)
>    RETURNS int AS $$
>    DECLARE
>    BEGIN
>        RETURN someCalculationBasedOnY;
>    END;
>    $$ LANGUAGE 'plpgsql';
>
> CREATE OR REPLACE FUNCTION ST_MyGeneralFunction(callback text)
>    RETURNS SETOF geomval AS $$
>    DECLARE
>        x integer;
>        y integer;
>    BEGIN
>        y := somecalculation;
>        x := 'callback'(y);  --This is what I need

EXECUTE 'SELECT ' || callback || '($1)' USING y INTO x;

there are no other way than EXECUTE

attention - there is a sql injection risk

regards

Pavel Stehule

>        RETURN x;
>    END;
>    $$ LANGUAGE 'plpgsql';
>
> I don't want to do an EXECUTE statement since I have no table to put after the FROM clause. I want to assign the
resultingvalue directly to a variable like in my example. 
>
> Can I/How can I achieve this?
>
> Thanks,
>
> Pierre
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Mar 2, 2011, at 4:31 AM, Pierre Racine wrote:

> CREATE OR REPLACE FUNCTION ST_MyGeneralFunction(callback text)
>    RETURNS SETOF geomval AS $$
>    DECLARE
>        x integer;
>        y integer;
>    BEGIN
>        y := somecalculation;
>        x := 'callback'(y);  --This is what I need
>        RETURN x;
>    END;
>    $$ LANGUAGE 'plpgsql';
>
> I don't want to do an EXECUTE statement since I have no table to put after the FROM clause. I want to assign the
resultingvalue directly to a variable like in my example. 


You don't need any table to assign value of function to Variable, if function returning single value. You can use
EXECUTE'SELECT '||$1||'(y)' into x; 

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com

On Tue, Mar 1, 2011 at 5:17 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
> Hello
>
> 2011/3/2 Pierre Racine <Pierre.Racine@sbf.ulaval.ca>:
>> Hi,
>>
>> I would like to write a generic plpgsql function with a text parameter being a callback function name so that my
generalfunction can call this callback function. e.g.: 
>>
>> CREATE OR REPLACE FUNCTION ST_MyCallbackFunction(y int)
>>    RETURNS int AS $$
>>    DECLARE
>>    BEGIN
>>        RETURN someCalculationBasedOnY;
>>    END;
>>    $$ LANGUAGE 'plpgsql';
>>
>> CREATE OR REPLACE FUNCTION ST_MyGeneralFunction(callback text)
>>    RETURNS SETOF geomval AS $$
>>    DECLARE
>>        x integer;
>>        y integer;
>>    BEGIN
>>        y := somecalculation;
>>        x := 'callback'(y);  --This is what I need
>
> EXECUTE 'SELECT ' || callback || '($1)' USING y INTO x;
>
> there are no other way than EXECUTE
>
> attention - there is a sql injection risk

another way if you are willing to write some C is to wrap and expose
OidFunctionCall1 so it takes oid of function you want to call.

merlin

On Wed, Mar 2, 2011 at 8:34 AM, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
> 2011/3/2 Merlin Moncure <mmoncure@gmail.com>
>> On Tue, Mar 1, 2011 at 5:17 PM, Pavel Stehule <pavel.stehule@gmail.com>
>> wrote:
>> > Hello
>> >
>> > 2011/3/2 Pierre Racine <Pierre.Racine@sbf.ulaval.ca>:
>> >> Hi,
>> >>
>> >> I would like to write a generic plpgsql function with a text parameter
>> >> being a callback function name so that my general function can call this
>> >> callback function. e.g.:
>> >>
>> >> CREATE OR REPLACE FUNCTION ST_MyCallbackFunction(y int)
>> >>    RETURNS int AS $$
>> >>    DECLARE
>> >>    BEGIN
>> >>        RETURN someCalculationBasedOnY;
>> >>    END;
>> >>    $$ LANGUAGE 'plpgsql';
>> >>
>> >> CREATE OR REPLACE FUNCTION ST_MyGeneralFunction(callback text)
>> >>    RETURNS SETOF geomval AS $$
>> >>    DECLARE
>> >>        x integer;
>> >>        y integer;
>> >>    BEGIN
>> >>        y := somecalculation;
>> >>        x := 'callback'(y);  --This is what I need
>> >
>> > EXECUTE 'SELECT ' || callback || '($1)' USING y INTO x;
>> >
>> > there are no other way than EXECUTE
>> >
>> > attention - there is a sql injection risk
>>
>> another way if you are willing to write some C is to wrap and expose
>> OidFunctionCall1 so it takes oid of function you want to call.
>
> As always, Merlin shows the uncommon thinking! :-)
> Respect!

Thank you, and I would like to be able to claim original invention of
this idea, however I cannot: it came from none other than Tom
Lane...http://postgresql.1045698.n5.nabble.com/improvise-callbacks-in-plpgsql-td2052928.html
(for whom nary a day goes by that I am not thankful for his tireless
efforts).

merlin

Is EXECUTE slower than a direct assignment call?

>-----Original Message-----
>From: Vibhor Kumar [mailto:vibhor.kumar@enterprisedb.com]
>Sent: 1 mars 2011 18:24
>To: Pierre Racine
>Cc: pgsql-general@postgresql.org
>Subject: Re: [GENERAL] Dynamic binding in plpgsql function
>
>
>On Mar 2, 2011, at 4:31 AM, Pierre Racine wrote:
>
>> CREATE OR REPLACE FUNCTION ST_MyGeneralFunction(callback text)
>>    RETURNS SETOF geomval AS $$
>>    DECLARE
>>        x integer;
>>        y integer;
>>    BEGIN
>>        y := somecalculation;
>>        x := 'callback'(y);  --This is what I need
>>        RETURN x;
>>    END;
>>    $$ LANGUAGE 'plpgsql';
>>
>> I don't want to do an EXECUTE statement since I have no table to put after the FROM clause. I want
>to assign the resulting value directly to a variable like in my example.
>
>
>You don't need any table to assign value of function to Variable, if function returning single value.
>You can use EXECUTE 'SELECT '||$1||'(y)' into x;
>
>Thanks & Regards,
>Vibhor Kumar
>EnterpriseDB Corporation
>The Enterprise PostgreSQL Company
>vibhor.kumar@enterprisedb.com
>Blog:http://vibhork.blogspot.com

On Wed, Mar 2, 2011 at 8:54 AM, Dmitriy Igrishin <dmitigr@gmail.com> wrote:
> 2011/3/2 Merlin Moncure <mmoncure@gmail.com>
>> Lane...http://postgresql.1045698.n5.nabble.com/improvise-callbacks-in-plpgsql-td2052928.html
>> (for whom nary a day goes by that I am not thankful for his tireless
>> efforts).
>
> Ahh, thanks to Tom Lane then !
> And thank you for the link of interesting topic from past :-)

yes...in the old days there was a lot more reason to do this because
there was no 'using' for execute and it was a lot more difficult to
coerce record types, arrays, etc into text format for passing.  today,
I would probably use execute unless I already had C library in the
backend and was *really* concerned about speed.

merlin

On Wed, Mar 2, 2011 at 9:06 AM, Pierre Racine
<Pierre.Racine@sbf.ulaval.ca> wrote:
> Is EXECUTE slower than a direct assignment call?

It is going to be slower, but how much slower and if it justifies the
mechanism is going to be a matter of your requirements, definition of
'slow', and willingness to experiment.

merlin