Re: multiple function execute using (func()).*

On Mon, Dec 8, 2008 at 5:15 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 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

CTE to the rescue.

my wider problem was that I was trying to set up a rule like this:
create table foo(...);
create table bar(...);
create view foobar as select * from foo join bar using (...);

create function add_foobar(foobar) returns foobar as $$...$$ language plpgsql;
create or replace rule ins_foobar as on insert to foobar
do instead select (add_foobar(new)).*;

The idea is that the rule calls the add function but returns the
adjusted composite so that insertions to foobar behave properly in
queries using 'returning'. This turned out to be quite a bugaboo. I
simply refused on principle to have add_foobar() explicitly list the
fields for foobar, that is, not use the composite type. The longer
form, select * from func(), was completely blocked because of subquery
prohibitions on touching 'new'.

However, this works:
create or replace rule ins_foobar as on insert to foobar
do instead with fb as (select add_foobar(new) as n) select (n).* from fb;

Another great use of the already awesome CTE feature! :-D

merlin

p.s. I still think the SQL standard is wrong, and invalidation events
should re-attempt the source sql (or, the '*' concept needs get to get
pushed into the plan). oh well...