Dynamic binding in plpgsql function

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

regards

Pavel Stehule

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 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.

merlin

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!

merlin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
// Dmitriy.

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

2011/3/2 Merlin Moncure <mmoncure@gmail.com>

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).

Ahh, thanks to Tom Lane then !
And thank you for the link of interesting topic from past :-)

merlin

--
// Dmitriy.

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

Is EXECUTE slower than a direct assignment call?

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