Thursday, May 8, 2014

PostgreSQL gotcha of the week, week 19

local:marko=# create table foo();
CREATE TABLE
local:marko=#* insert into foo default values;
INSERT 0 1
local:marko=#* create function getdata(foo)
returns table (a int, b int)
as $$
begin

if random() < 0.5 then
    a := 1; b := 1;
    return next;
else
    a := 2; b := 2;
    return next;
end if ;
end
$$ language plpgsql
;
CREATE FUNCTION
local:marko=#* select (getdata(foo)).* from foo;
 a | b 
---+---
 2 | 1
(1 row)

If you didn't figure out how that happened: first, (getdata(foo)).*  gets expanded to  (getdata(foo)).a, (getdata(foo)).b.  Then both expressions are evaluated separately, where they happen to enter different arms of the IF.

To fix, on versions earlier than 9.3:

select (getdata).*
from
(
    select getdata(foo)
    from foo
    offset 0
) ss;
The OFFSET 0 prevents the subquery from being flattened, which also ensures that the function is only called once for every row in "foo".

On 9.3 and later:

select getdata.* from foo, getdata(foo);

2 comments:

  1. In all supported versions you can also move the SELECT getdata call sub-query into a CTE.

    I would suggest using the, optional, LATERAL keyword in example queries like this just to point out what 9.3 feature actually makes this possible.

    ReplyDelete
  2. nice gotcha. but why on earth it's possible to create tables w/o columns and insert there. very confusing.

    postgres=# select * from foo;
    (No rows)

    postgres=# select null from foo;
    ?column?
    ──────────
    ¤
    (1 row)

    ReplyDelete