Обсуждение: PL/SQL: function call like $1($2)

I want to create a function in PL/SQL as
CREATE OR REPLACE FUNCTION "f_do" (text, double precision[]) RETURNS
double precision AS '
DECLARE
     f text := $1;
     p double precision[] := $2;
     res double precision;
BEGIN
     SELECT f(p) into res;
     RETURN res;
END;' LANGUAGE "plpgsql"
STABLE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER

But it does not work.
I try EXECUTE f || ' ( ' || p || ' );' INTO res
But is does not work too.
There is no function to convert double precision[] to text.

Is it possible to do this without converting the array of double to
text ?

Pailloncy Jean-Gerard

On Sun, Oct 08, 2006 at 05:49:11PM +0200, Jean-Gerard Pailloncy wrote:
> I want to create a function in PL/SQL as
> CREATE OR REPLACE FUNCTION "f_do" (text, double precision[]) RETURNS
> double precision AS '
> DECLARE
>     f text := $1;
>     p double precision[] := $2;
>     res double precision;
> BEGIN
>     SELECT f(p) into res;
>     RETURN res;
> END;' LANGUAGE "plpgsql"
> STABLE
> RETURNS NULL ON NULL INPUT
> SECURITY INVOKER
>
> But it does not work.
> I try EXECUTE f || ' ( ' || p || ' );' INTO res
> But is does not work too.
> There is no function to convert double precision[] to text.
>
> Is it possible to do this without converting the array of double to
> text ?

Here's one (WARNING! UNTESTED!) thing you might try.

CREATE OR REPLACE FUNCTION f_do (f text, p double precision[])
RETURNS double precision
LANGUAGE plpgsql
AS $$
DECLARE
    res double precision;
BEGIN
    CREATE FUNCTION f_do_inner (inner_p double precision[]) /* Do not attempt to replace */
    RETURNS double precision
    LANGUAGE plpgsql
    AS $q$
    BEGIN
        RETURN $q$ || quote_ident(f) || $q$(inner_p);
    END;
    $q$;

    SELECT INTO ret f_do_inner(p);

    DROP FUNCTION f_do_inner(inner_p double precision[]);

    RETURN ret;
END;
$$;

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!

On 10/8/06, Jean-Gerard Pailloncy <jg@rilk.com> wrote:
> I want to create a function in PL/SQL as
> CREATE OR REPLACE FUNCTION "f_do" (text, double precision[]) RETURNS
> double precision AS '
> DECLARE
>      f text := $1;
>      p double precision[] := $2;
>      res double precision;
> BEGIN
>      SELECT f(p) into res;
>      RETURN res;
> END;' LANGUAGE "plpgsql"
> STABLE
> RETURNS NULL ON NULL INPUT
> SECURITY INVOKER
>
> But it does not work.
> I try EXECUTE f || ' ( ' || p || ' );' INTO res
> But is does not work too.
> There is no function to convert double precision[] to text.
>
> Is it possible to do this without converting the array of double to
> text ?


what is are the arguments for function f()?
Also, if you are using 8.0+, you can do dollar quoting and named input
parameters.  If not, i think using alias is preferred to assignment
for giving names to arguments.

merlin

On Sun, Oct 08, 2006 at 05:49:11PM +0200, Jean-Gerard Pailloncy wrote:
> I try EXECUTE f || ' ( ' || p || ' );' INTO res
> But is does not work too.
> There is no function to convert double precision[] to text.

You could use the types' input and output functions or perhaps
array_to_string().

stmt := 'SELECT ' || quote_ident(f) || '(' || quote_literal(textin(array_out(p))) || ')';
EXECUTE stmt INTO res;

--
Michael Fuhr

On 10/8/06, Jean-Gerard Pailloncy <jg@rilk.com> wrote:

I want to create a function in PL/SQL as
CREATE OR REPLACE FUNCTION "f_do" (text, double precision[]) RETURNS
double precision AS '
DECLARE
     f text := $1;
     p double precision[] := $2;
     res double precision;
BEGIN
     SELECT f(p) into res;
     RETURN res;
END;' LANGUAGE "plpgsql"
STABLE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER

But it does not work.
I try EXECUTE f || ' ( ' || p || ' );' INTO res
But is does not work too.
There is no function to convert double precision[] to text.

Is it possible to do this without converting the array of double to
text ?

what is are the arguments for function f()?
Also, if you are using 8.0+, you can do dollar quoting and named input
parameters.  If not, i think using alias is preferred to assignment
for giving names to arguments.

merlin

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

              http://www.postgresql.org/docs/faq

On 10/9/06, Michael Fuhr <mike@fuhr.org> wrote:
> On Sun, Oct 08, 2006 at 05:49:11PM +0200, Jean-Gerard Pailloncy wrote:
> > I try EXECUTE f || ' ( ' || p || ' );' INTO res
> > But is does not work too.
> > There is no function to convert double precision[] to text.
>
> You could use the types' input and output functions or perhaps
> array_to_string().
>
> stmt := 'SELECT ' || quote_ident(f) || '(' || quote_literal(textin(array_out(p))) || ')';
> EXECUTE stmt INTO res;

thinking about this some more, it looks like the basic issue is
passing in function to operate over a defined type, sort of like a sql
closure.  This can be rigged in dynamic sql but this has couple
problems.  Complex types are a pain (record types are even worse) and
dynamic sql feels like a kludge anyhow.  Here is maybe another
solution:

you can make a make an oid from a string:
esilo=# create function foo() returns void as $$ ; $$ language sql;
CREATE FUNCTION
esilo=# select 'foo()'::regprocedure::oid;
  oid
--------
 300009
(1 row)

whierle the is no way to call a function via its oid in pl/pgsql, it
is fairly trivial from C and somewhat generic using
OidFunctionCall1(): (or 2, etc)

http://www.nabble.com/improvise-callbacks-in-plpgsql-tf476925.html#a1299810

I have tested this approach, and it works!

merlin