Thread: Note on scalar subquery syntax

I thought this might be helpful in the future to other duffers such as
myself.

The following is my big contribution to the documentation of the use of
scalar subqueries ;-):

You have to wrap a scalar subquery in its own parentheses even where you
might think it to be unnecessary, such as when the subquery is the sole
argument to a function.

As an example, I wrote a function to explode, or unpack, the elements of
an array onto separate rows (its return type is SETOF whatever), but it
took me a while to figure out how to use it effectively in queries.

You have to use it like this:

RIGHT--> select * from array_explode((select array_col from table1 where
col2 = 'something'));

Note the "extra" set of parentheses.  These are crucial: the syntax is
invalid without these, as in:

WRONG--> select * from array_explode(select array_col from table1 where
col2 = 'something');

And no, as mentioned in many archived list messages, you can NOT do the
following, which is what a lot of people (including me) seem to try first:

WRONG--> select array_explode(array_col) from table1 where col2 =
'something');

(The previous command results in the error message: "set-valued function
called in context that cannot accept a set").

-Kevin Murphy

Kevin Murphy wrote:
> I thought this might be helpful in the future to other duffers such as
> myself.
>
> The following is my big contribution to the documentation of the use of
> scalar subqueries ;-):
>
> You have to wrap a scalar subquery in its own parentheses even where you
> might think it to be unnecessary, such as when the subquery is the sole
> argument to a function.
>
> As an example, I wrote a function to explode, or unpack, the elements of
> an array onto separate rows (its return type is SETOF whatever), but it
> took me a while to figure out how to use it effectively in queries.

Mind posting it?  I know I've had need of such I thing & IIRC others
have asked as well...

On Wed, Aug 03, 2005 at 09:40:26AM -0400, Kevin Murphy wrote:
> You have to wrap a scalar subquery in its own parentheses even where you
> might think it to be unnecessary, such as when the subquery is the sole
> argument to a function.

It first guess I imagine it is because the syntax becomes ambiguous,
expecially if you have multiple arguments to the function.

Say you a function "func" and your query was: SELECT * FROM x ORDER BY y

Then this isn't parsable obviously:

SELECT func( SELECT * FROM x ORDER BY y, 1, 1 ) )

Since you don't know where the ORDER BY ends and the function list
continues. Adding parenthesis at the appropriate point removes the
ambiguity.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Peter Fein wrote:

>Kevin Murphy wrote:
>
>
>>As an example, I wrote a function to explode, or unpack, the elements of
>>an array onto separate rows (its return type is SETOF whatever), but it
>>took me a while to figure out how to use it effectively in queries.
>>
>>
>
>Mind posting it?  I know I've had need of such I thing & IIRC others
>have asked as well...
>
>
I'm no expert, but per Peter's request, here is a generic
array-unpacking function that works in PostgreSQL 8.0.  It can't be
invoked if the argument doesn't have an explicit type.  I.e. you would
have to use it as: "select * from
array_explode_generic('{apple,banana,cherry}'::text[]);" or "select *
from array_explode_generic('{1,2,3}'::integer[]);".

CREATE OR REPLACE FUNCTION array_explode(an_array anyarray) RETURNS
SETOF anyelement AS $$
DECLARE
        idx integer;
BEGIN

        FOR idx IN 1 .. ARRAY_UPPER(an_array, 1) LOOP
                RETURN NEXT an_array[idx];
        END LOOP;
        RETURN;
        END;
$$ LANGUAGE plpgsql;

I would imagine that a type-specific version would be faster.  For that,
replace "anyarray" with, e.g. "integer[]", and "anyelement" with, e.g.
"integer".

-Kevin Murphy

Martijn van Oosterhout wrote:

>On Wed, Aug 03, 2005 at 09:40:26AM -0400, Kevin Murphy wrote:
>
>
>>You have to wrap a scalar subquery in its own parentheses even where you
>>might think it to be unnecessary, such as when the subquery is the sole
>>argument to a function.
>>
>>
>
>It first guess I imagine it is because the syntax becomes ambiguous,
>expecially if you have multiple arguments to the function.
>
>
Thanks, Martijn.  Yes, I don't consider it to be entirely unreasonable;
it was just surprising and strange-looking to me.

-Kevin

Kevin Murphy <murphy@genome.chop.edu> writes:
> I'm no expert, but per Peter's request, here is a generic
> array-unpacking function that works in PostgreSQL 8.0.
> [snip]

> I would imagine that a type-specific version would be faster.

No, actually it'd be exactly the same.  What happens under the hood with
a plpgsql "anyarray" function is that Postgres instantiates a copy for
each specific datatype you call it with during the life of your session.
So there's no real point in doing the same thing manually.

            regards, tom lane