Postgres Pro
Downloads
Home   >   mailing lists

Thread: set returning function with variable argument - possible?

set returning function with variable argument - possible?

From
Frank Miles
Date:
I have a function that returns a set of a defined type, something like:

     CREATE OR REPLACE FUNCTION f(arg) RETURNS SETOF return_type AS $$
         ...
     $$ LANGUAGE 'plpgsql' STABLE with (isstrict);

Ordinarily this function is called:

     SELECT * FROM f(constant);

But what I want to do is more like:
     SELECT * FROM f(var) WHERE var IN (SELECT var FROM xtable WHERE ...);

This fails, as does
     SELECT * FROM f(var) WHERE var IN (constant);

->> Is there some syntax that will allow me to perform this operation?

Note that there are differing ways for "arg" to be determined, so I'd rather not
merge this operation into f() - I would have to have a number of f()'s, each with
its own mechanism for developing its "arg"s.

-------------
Running Postgresql 8.4.7 on Debian 'squeeze'.

Thanks for any pointers to documentation that I have overlooked in my search!

     -f

Re: set returning function with variable argument - possible?

From
Pavel Stehule
Date:
Hello

I don't know if I understand well, but you can do

select (f).* from (select f(var) from xtable where ...) x;

Regards

Pavel Stehule

2011/6/16 Frank Miles <fpm@u.washington.edu>:
> I have a function that returns a set of a defined type, something like:
>
>        CREATE OR REPLACE FUNCTION f(arg) RETURNS SETOF return_type AS $$
>                ...
>        $$ LANGUAGE 'plpgsql' STABLE with (isstrict);
>
> Ordinarily this function is called:
>
>        SELECT * FROM f(constant);
>
> But what I want to do is more like:
>        SELECT * FROM f(var) WHERE var IN (SELECT var FROM xtable WHERE ...);
>
> This fails, as does
>        SELECT * FROM f(var) WHERE var IN (constant);
>
> ->> Is there some syntax that will allow me to perform this operation?
>
> Note that there are differing ways for "arg" to be determined, so I'd rather
> not
> merge this operation into f() - I would have to have a number of f()'s, each
> with
> its own mechanism for developing its "arg"s.
>
> -------------
> Running Postgresql 8.4.7 on Debian 'squeeze'.
>
> Thanks for any pointers to documentation that I have overlooked in my
> search!
>
>        -f
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: set returning function with variable argument - possible?

From
"David Johnston"
Date:
The only way to feed another relation into a function is to place the
function in the SELECT list.  If you then want to expand the result of the
function call you alias the function result and then ".*" against it.

WITH feeder AS (
SELECT f(var) AS result FROM xtable WHERE ...
)
SELECT (feeder.result).*
FROM feeder
;

A similar query form:

SELECT f(var).* FROM xtable WHERE ...

Will cause "f" to be executed once for every column that "f" would expand
into.  For SELECT only functions this causes a performance hit (though you
do get the correct results) but if the function executes any INSERTS (or
other DDL) the result of executing each statement multiple times in the same
transaction can either cause the function to simply fail or, even worse,
succeed but cause unintended effects.

Supposedly, due to sub-query inlining, the following form is not truly safe:

SELECT (sub.field).*
FROM ( SELECT f(var) AS result FROM xtable WHERE ... ) sub

David J.


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Frank Miles
> Sent: Thursday, June 16, 2011 12:18 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] set returning function with variable argument -
possible?
>
> I have a function that returns a set of a defined type, something like:
>
>      CREATE OR REPLACE FUNCTION f(arg) RETURNS SETOF return_type
> AS $$
>          ...
>      $$ LANGUAGE 'plpgsql' STABLE with (isstrict);
>
> Ordinarily this function is called:
>
>      SELECT * FROM f(constant);
>
> But what I want to do is more like:
>      SELECT * FROM f(var) WHERE var IN (SELECT var FROM xtable WHERE
> ...);
>
> This fails, as does
>      SELECT * FROM f(var) WHERE var IN (constant);
>
> ->> Is there some syntax that will allow me to perform this operation?
>
> Note that there are differing ways for "arg" to be determined, so I'd
rather
> not merge this operation into f() - I would have to have a number of
f()'s,
> each with its own mechanism for developing its "arg"s.
>
> -------------
> Running Postgresql 8.4.7 on Debian 'squeeze'.
>
> Thanks for any pointers to documentation that I have overlooked in my
> search!
>
>      -f
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Re: set returning function with variable argument - possible?

From
Frank Miles
Date:
On Thu, 16 Jun 2011, David Johnston wrote:

> The only way to feed another relation into a function is to place the
> function in the SELECT list.  If you then want to expand the result of the
> function call you alias the function result and then ".*" against it.
>
> WITH feeder AS (
> SELECT f(var) AS result FROM xtable WHERE ...
> )
> SELECT (feeder.result).*
> FROM feeder
> ;

[snip]

Thanks, David, this worked perfectly!  {thanks also to Pavel}

     -Frank