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

Search Discussions

  • Tom Lane at Feb 20, 2009 at 8:01 pm

    Merlin Moncure 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
  • Merlin Moncure at Feb 20, 2009 at 8:26 pm

    On Fri, Feb 20, 2009 at 3:00 PM, Tom Lane 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
  • Merlin Moncure at Feb 20, 2009 at 8:27 pm

    On Fri, Feb 20, 2009 at 3:25 PM, Merlin Moncure wrote:
    On Fri, Feb 20, 2009 at 3:00 PM, Tom Lane 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
    ^^^
  • Tom Lane at Feb 20, 2009 at 8:55 pm

    Merlin Moncure writes:
    On Fri, Feb 20, 2009 at 3:00 PM, Tom Lane 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedFeb 20, '09 at 7:53p
activeFeb 20, '09 at 8:55p
posts5
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Merlin Moncure: 3 posts Tom Lane: 2 posts

People

Translate

site design / logo © 2021 Grokbase