From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
---|---|
To: | "PostgreSQL Hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | multiple function execute using (func()).* |
Date: | 2008-12-08 22:15:01 |
Message-ID: | b42b73150812081415y6f772343tec2cc13d43c57067@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Wong | 2008-12-09 00:06:03 | Re: Simple postgresql.conf wizard |
Previous Message | Magnus Hagander | 2008-12-08 20:49:49 | Re: [COMMITTERS] pgsql: Properly unregister OpenSSL callbacks when libpq is done with |