PostgreSQL SQL - Sub-query as function argument

Author

Sub-query as function argument

Michael Burke

2005-12-20, 9:24 am

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Jaime Casanova

2005-12-20, 9:24 am

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Michael Burke

2005-12-20, 9:24 am

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane

2005-12-20, 9:24 am

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links