Hello,
I've been bit by this about a million times:
select (func()).* executes the function once per each field in the
returned tuple. See the example below:
create function foo_func() returns foo as
$$ declare f foo; begin raise notice '!'; return f; end;
$$ language plpgsql;
postgres=# select (foo_func()).*;
NOTICE: !
NOTICE: !
NOTICE: !a | b | c
---+---+--- | |
(1 row)
This is an anathema to any query trying to use composite types to
circumvent single field subquery restrictions (for example, when using
a record aggregate to choose a row). Normally you can work around
this by writing it like this:
select (foo_func()).*; -> select * from foo_func();
Now, aside from the fact that these to forms should reasonably produce
the same result, there are a couple of cases where the shorter,
without 'from' version is easier to write. One example is in 'CREATE
RULE', since you can't use 'new' in queries using the long form:
postgres=# create or replace rule ins_foo as on insert to foo
postgres-# do instead select * from add_foo(new);
ERROR: subquery in FROM cannot refer to other relations of same query level
The point of all this is to be able to multi-table views that support
'returning', which is much, much harder than it should be.
merlin