Re: UNNEST with multiple args, and TABLE with multiple funcs

Tom Lane-2 wrote
> Andrew Gierth <

> andrew(at)(dot)org

> > writes:
>> "Tom" == Tom Lane <

> tgl(at)(dot)pa

> > writes:
>> Tom> and this would result in producing the array elements as a table
>> Tom> column. There is nothing in there about a function returning
>> Tom> set.
>
>> In the spec, there is no such thing as a function returning a set of
>> rows in the sense that we use.
>
> Right, but they do have a concept of arrays that's similar to ours,
> and AFAICS the spec demands different behavior for an array-returning
> function than what we've got here.
>
> We could conceivably say that we'll implicitly UNNEST() if the function
> returns array, and not otherwise --- but that seems pretty inconsistent
> and surprise-making to me. I'm not too sure what to do if a function
> returns setof array, either.

If a function returns a scalar array (RETURNS text[]) we would unnest the
array per-spec. If it returns a set (RETURN setof anything {including a
single array}) we would not unnest it since set returning functions are
non-spec - instead we'd use our SRF processing routine. If the function
returns a scalar non-array the implicit single-row returned by the function
would be output.

How would the spec interpret:

CREATE FUNCTION f(IN text, OUT text[]) RETURNS record AS $$ ...

TABLE( f('id_123') )

If that is illegal because the result is not just a single array value then
we would not unnest the component array and would also output the implicit
single-row.

My $0.02, quickly gathered

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/UNNEST-with-multiple-args-and-TABLE-with-multiple-funcs-tp5767280p5779515.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.