Thread: return query/composite types

create table foo(a int, b int);
postgres=# create function rfoo() returns setof foo as $$ begin return
query select foo from foo; end; $$ language plpgsql;
CREATE FUNCTION
Time: 25.606 ms
postgres=#
postgres=#
postgres=# select rfoo();
ERROR:  structure of query does not match function result type
DETAIL:  Number of returned columns (1) does not match expected column
count (2).


Any reason why the above shouldn't work?  Why does 'return query'
assume that returned composite types are expanded?

merlin

Merlin Moncure <mmoncure@gmail.com> writes:
> create table foo(a int, b int);
> postgres=# create function rfoo() returns setof foo as $$ begin return
> query select foo from foo; end; $$ language plpgsql;

Use "select * from ..." instead.
        regards, tom lane

On Fri, Feb 20, 2009 at 3:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Merlin Moncure <mmoncure@gmail.com> writes:
>> create table foo(a int, b int);
>> postgres=# create function rfoo() returns setof foo as $$ begin return
>> query select foo from foo; end; $$ language plpgsql;
>
> Use "select * from ..." instead.

Yeah...I was thinking maybe that shouldn't be required:
1. it's illogical and conflicts with regular non 'returns query'
semantics (declare foo, assign, return)
2. if 'foo' is result of set returning function (like unnest), you
need to make extra subquery to prevent that function from executing
lots of extra times.
e.g.
select unnest(foo) from <something> will unnest the set six times if
foo has six fields.  This is a bit of a landmine since type returning
functions are _fairly_ common use for composite types.

These aren't really complaints since the workarounds are trivial, just
casual wondering if the behavior is correct.

merlin

On Fri, Feb 20, 2009 at 3:25 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Fri, Feb 20, 2009 at 3:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Merlin Moncure <mmoncure@gmail.com> writes:
>>> create table foo(a int, b int);
>>> postgres=# create function rfoo() returns setof foo as $$ begin return
>>> query select foo from foo; end; $$ language plpgsql;
>>
>> Use "select * from ..." instead.
>
> Yeah...I was thinking maybe that shouldn't be required:
> 1. it's illogical and conflicts with regular non 'returns query'
> semantics (declare foo, assign, return)
> 2. if 'foo' is result of set returning function (like unnest), you
> need to make extra subquery to prevent that function from executing
> lots of extra times.
> e.g.
> select unnest(foo) from <something> will unnest the set six times if

er,
select (unnest(foo)).* from <something> will unnest the set six times if                                ^^^

Merlin Moncure <mmoncure@gmail.com> writes:
> On Fri, Feb 20, 2009 at 3:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Use "select * from ..." instead.

> Yeah...I was thinking maybe that shouldn't be required:

If you allow both interpretations then you create a syntactic ambiguity
(at least for the case of single-column composite types).  We have
historically allowed both in SQL functions, but I think that's a design
error that shouldn't be repeated in other PLs.
        regards, tom lane