Re: strange evaluation Window function and SRF functions?

Josh Berkus <josh(at)agliodbs(dot)com> writes:
>> it looks like row_number is evaluated before SRF - this behave is
>> absolutely undefined - for me - more native behave is different evaluation.

> SRFs which return multiple rows in the SELECT clause have ALWAYS behaved
> oddly when it comes to row evaluation (LIMIT, COUNT(), etc.). This
> isn't necessarily desireable, but it is consistent with past releases,
> and it's not in any way limited to Windowing functions. In general, if
> you care about rows when calling such an SRF, you need to subselect it.

> It would be nice to clean that up, but you'd have to start with a
> comprehensive definition of what the behavior *should* be in all common
> cases. And then you'd be in for a big code overhaul.

And a lot of application code breakage, if you change the semantics at all.

My feeling is that SRFs in targetlists are just fundamentally poorly
defined, and the answer is to avoid them not try to make them cleaner.
Most of the real use-cases for them could be handled in a
better-defined, more standard way with LATERAL ... so what we ought
to be spending time on is getting LATERAL done, not worrying about
putting lipstick on tlist SRFs.

regards, tom lane