Re: Enhancement request: enable FIRST/LAST_value() also as a regular aggregate (not only as windowing function)

I was hoping the proposal would be judged on its merits - primarily
usefulness, not necessarily conforming to the standards
I would expect postgres overlap with the SQL standard (eg. SQL:2016)
doesn't differ much from other big vendors and has lots of own
deviations/extensions itself.
Not a fan of Oracle myself but have to give it to them the mentioned
versatility is really nice.
In terms of performance it also carries a significant difference:
being forced to work on an unaggregated result and process it further (eg.
with extra subselect filtering on row_number)
vs. simple GROUP BY

Yes it would require particular ordering of the input
but please note PostgreSQL already supports a small set of such Ordered-Set
Aggregate Functions
<https://www.postgresql.org/docs/devel/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE>
(
https://www.postgresql.org/docs/devel/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE
)

Currently I can do workaround with something like:

SELECT gid
,RIGHT(MIN(lpad(v2::varchar,10,'0')||v1),-10) pseudo_lkp_first
,RIGHT(MAX(lpad(v2::varchar,10,'0')||v1),-10) pseudo_lkp_last
FROM(
VALUES (1, 'b', 3),(1, 'd', 1),(1, 'a', 2)
,(2, 'x', 7),(2, 'y', 9),(2, 'z', 8),(2, 'v', 9)) t (gid, v1, v2)
GROUP BY gid;

but it's neither universal nor readable.
It's fast though.

I was hoping those FIRST/LAST lookup functions could be made available as
some new Ordered-Set Aggregate Functions
<https://www.postgresql.org/docs/devel/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE>
too…

On Tue, 24 Jul 2018 at 23:16, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> matshyeq <matshyeq(at)gmail(dot)com> writes:
> > I'd like to throw here an enhancement proposal to discuss/consider.
> > The FIRST/LAST_value()
> > functions offer powerful lookup capabilities, ...
> > but, given those values are repeating - why can't I simply use this
> > functions as regular aggregates?
> > Or can I? It doesn't seem to be possible while I find this use case
> > actually more common than in windowing context…
>
> The reason the SQL standard doesn't allow that usage, or similar ones
> for other window functions, is that the function results are undefined
> unless you specify a particular ordering of the inputs ... which is
> exactly what the window syntax provides.
>
> Yeah, in most cases there are other ways where you could get the same
> result, but all of them require extra syntax too. So you might as
> well use window syntax and be standards-conforming.
>
> > Anyway, Oracle is an example where both contexts are possible
>
> Oracle is not exactly a shining example of either SQL spec compliance
> or well-thought-out features ...
>
> regards, tom lane
>