Re: Function Column Expansion Causes Inserts To Fail

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Tom Lane
> Sent: Monday, May 30, 2011 11:10 PM
> To: David Johnston
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Function Column Expansion Causes Inserts To Fail
>
> "David Johnston" <polobo(at)yahoo(dot)com> writes:
> > SELECT ( createpkrecord('1') ).*;
> > [ results in function being called more than once ]
>
> Yeah. Don't do that. Better style is
>
> SELECT * FROM createpkrecord('1');
>
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org) To make
> changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

From syntax works fine for literals but how would you then get table.column
values into the function call - where you want to evaluate multiple rows
from the source table? In order to feed rows to a function you need the
function in the SELECT column-list so that it can see the columns in
question.

If using the described syntax results in the odd situation where a function
is called twice I would consider that a bug. Either it needs to be fixed or
the system should disallow that syntax from being used. I haven't tried
serial pk creation or other side-effects that would not result in such an
obvious error but it is reasonable to believe that if the duplicate key
exception is being thrown then other bad - but not catchable things - could
occur as well. Even an expensive SELECT statement inside the function would
make this behavior undesirable - though I am guessing it would otherwise be
invisible since the SELECT is not a side-effect and thus the engine would
only return one set of results - though I haven't tested this theory either.

The fact that: SELECT createpkrecord('1') works - returning a "row" - leads
me to think that decomposing that row should be (but is not) independent of
the source of that "row".

The work around I described (converting the SELECT function() statement to a
sub-query and expanding the results in the parent) is fine but if that is
the only safe way to do it then the alternate method should fail since it is
unsafe. Now, back to my first question, are there other alternatives that
I've overlooked when you want to use the result of a SELECT statement as the
source of values for a function call?

That is, how would you re-write this to place "createpkrecord(sub)" in a
FROM clause instead of the SELECT list?

SELECT createpkrecord(sub)
FROM (SELECT sub FROM generate_series(1, 10) sub ) src;

David J.