Re: Function Column Expansion Causes Inserts To Fail

On Tue, May 31, 2011 at 3:28 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> There have been multiple complaints about this in the archives.  In
>> the old days, you would have to rewrite your query to use the 'select
>> * from func()' form (which isn't always so easy) or use a subquery and
>> the 'offset 0' hack.  Running in to this problem has actually become
>> more common as our type system has gotten fancier and plpgsql got the
>> ability to be called with the column list, aka select func(), syntax.
>
>> The community has had to endure multiple sanctimonious rants about
>> this by yours truly.  Unfortunately complaints are cheap relative to
>> the hard work and consensus building it would require to fix this
>> problem.
>
> FWIW, the SQL-standard LATERAL construct would fix the problem
> reasonably well, and that is on the roadmap already.

right -- it looks like you could write the OP's query:
SELECT createpkrecord(sub) FROM (SELECT sub FROM generate_series(1,
10) sub ) src;

like this:
SELECT s.* from generate_series(1,10) sub, lateral(createpkrecord(sub)) AS s;

That doesn't really speak though to the OP's point, which I obviously
agree with, that the current behavior is pretty awful and that the
dangers of relying on it should be advertised more loudly. Maybe a
warning plus a hint to use lateral might be helpful if/when that
feature comes in, or a documentation fix.

I've never taken the time to really get my head around 'lateral'
enough to say for sure if it provides clean workarounds for all the
cases that get people into hot water. The case that used to get me a
lot is (the unfortunately generally under utilized) custom aggregates.

problem:
select bar_id, (some_agg(foo)).* from foo join bar ... group by bar_id;

solution with lateral?

merlin