multiple function execute using (func()).*

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

On Dec 8, 2008, at 5:15 PM, Merlin Moncure 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:
>

I ran into this exact problem a week or two ago.

I didn't dig too far into it but I figured (func()).* was being
expanded by the parser into func().a, func().b and friends. marking it
stable didn't help.

It can be quite surprising, especially if func() is expensive (as was
my case) or has side effects.

--
Jeff Trout <jeff(at)jefftrout(dot)com>
http://www.stuarthamm.net/
http://www.dellsmartexitin.com/

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