Postgres Pro
Downloads
Home   >   mailing lists

Thread: Sub-query as function argument

Sub-query as function argument

From
Michael Burke
Date:
Is it possible to execute a SELECT query as an argument to a function?

Example:

SELECT my_func('Sample', NULL, SELECT MIN(year) FROM audio);

In my particular case, my_func inserts columns into another table; I wish to 
use values from another table as the arguments.  The interior SELECT will 
return only a single value; perhaps it needs to be wrapped in another 
function?

TIA.
Mike.

-- 
Michael Burke
michael@engtech.ca

Re: Sub-query as function argument

From
Jaime Casanova
Date:
On 12/20/05, Michael Burke <michael@engtech.ca> wrote:
> Is it possible to execute a SELECT query as an argument to a function?
>

have you tried?

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Re: Sub-query as function argument

From
Michael Burke
Date:
On December 20, 2005 10:52 am, Jaime Casanova wrote:
> have you tried?

Yes:

=> SELECT my_func('Sample', NULL, SELECT MIN(year) FROM audio);
ERROR:  syntax error at or near "SELECT" at character 32

PostgreSQL 7.4.9, myfunc is pl/pgsql.

Just found a working method, though:

=> SELECT my_func('Sample', NULL, MIN(year)) FROM audio [ WHERE ... ];

-- 
Michael Burke
michael@engtech.ca

Re: Sub-query as function argument

From
Tom Lane
Date:
Michael Burke <michael@engtech.ca> writes:
> Is it possible to execute a SELECT query as an argument to a function?

> SELECT my_func('Sample', NULL, SELECT MIN(year) FROM audio);

You need parentheses around the sub-SELECT.
 SELECT my_func('Sample', NULL, (SELECT MIN(year) FROM audio));

This is generally true everywhere in expressions, not just in
function arguments.  Without the parens, it's often ambiguous
what's subselect and what's outer query.
        regards, tom lane