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

Tom Lane-2 wrote
> Andrew Gierth <

> andrew@.org

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

> tgl@.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.