Re: BUG #13798: Unexpected multiple exection of user defined function with out parameters - Mailing list pgsql-bugs

From Michael Lang
Subject Re: BUG #13798: Unexpected multiple exection of user defined function with out parameters
Date
Msg-id CADWw+NTO+YqGnm-h0ZidfBPfwe=kMUAhhoMx2V4==7faRq+szw@mail.gmail.com
Whole thread Raw
In response to Re: BUG #13798: Unexpected multiple exection of user defined function with out parameters  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-bugs
On Tue, Dec 8, 2015 at 1:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Kevin Grittner <kgrittn@gmail.com> writes:
> > On Tue, Dec 8, 2015 at 10:24 AM, David G. Johnston
> > <david.g.johnston@gmail.com> wrote:
> >> While you've explained how to see what is happening it doesn't remove
> >> the POLA violation that has occurred here.
>
> > I tend to think of x.* as being something that is resolved at parse
> > analysis time, before any consideration is given to how to execute;
> > thus, when x is a function I didn't find it at all astonishing that
> > it resolved as above.
>
> As far as table references go, it's effectively required by the SQL spec
> that "tab.*" in a SELECT list is expanded to "tab.c1, tab.c2, etc" at
> parse time.  (I draw this conclusion from the parts of the spec that say
> that tab.*'s meaning in a view referencing tab does not change if tab
> subsequently gains new columns.)
>
> It's definitely annoying that (foo()).* is expanded similarly; but to fix
> that we'd have to introduce an additional layer of evaluation into SELECT
> lists, and I think that might create some visible semantic oddities of its
> own.  Between backwards-compatibility worries and the existence of the
> LATERAL workaround, it's unlikely we'll ever change this.
>
> I'm not sure how well this point is documented, though.  Might be worth
> some effort in that direction.
>
>                         regards, tom lane
>

Thanks to everyone for the attention given, and the illuminating
responses.  I'm not sure how I passed over the SELECT * FROM udf(); form
suggested by Oleksandr Shulgin, but it produces the desired result -
executing the function once while returning the out parameters as a row
rather than as the text literal representation of the composite type.  It's
an odd quirk that (udf()).* may execute the function multiple times, but
the workarounds make it simple enough to avoid.

Thanks!
Mike Lang

pgsql-bugs by date:

Previous
From: Peter Geoghegan
Date:
Subject: Re: Incorrect UPDATE trigger invocation in the UPDATE clause of an UPSERT statement.
Next
From: Andres Freund
Date:
Subject: Re: Incorrect UPDATE trigger invocation in the UPDATE clause of an UPSERT statement.