Re: Dynamic binding in plpgsql function

2011/3/2 Merlin Moncure <mmoncure(at)gmail(dot)com>

> On Wed, Mar 2, 2011 at 8:34 AM, Dmitriy Igrishin <dmitigr(at)gmail(dot)com>
> wrote:
> > 2011/3/2 Merlin Moncure <mmoncure(at)gmail(dot)com>
> >> On Tue, Mar 1, 2011 at 5:17 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> >> wrote:
> >> > Hello
> >> >
> >> > 2011/3/2 Pierre Racine <Pierre(dot)Racine(at)sbf(dot)ulaval(dot)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.