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

Hi All,

I'd like to throw here an enhancement proposal to discuss/consider.
The FIRST/LAST_value()
<https://www.postgresql.org/docs/devel/static/functions-window.html#id-1.5.8.26.6.2.2.9.1.1>
functions offer powerful lookup capabilities, eg.
here
1)
https://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=0f13c8541191c3018703d2a97aa90bf9

SELECT t.* ,FIRST_value(v1)OVER(PARTITION BY gid ORDER BY v2) fv
,LAST_value(v1)OVER(PARTITION BY gid ORDER BY v2 ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING) lv 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);

gidv1v2fvlv
1 d 1 d b
1 a 2 d b
1 b 3 d b
2 x 7 x v
2 z 8 x v
2 y 9 x v
2 v 9 x v

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…
Am I missing some workaround here?

Anyway, Oracle is an example where both contexts are possible
<https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions065.htm#SQLRF00641>
and I think this could provide great reference as to what I have in mind.
2) Demonstrating those functions in both contexts there:
https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=ed62d9af3fbe6a038e4433625a70540f

a) as Windowing Aggregate:

* WITH t(gid, v1, v2) AS(SELECT 1, 'b', 3 FROM dual UNION ALLSELECT 1,
'd', 1 FROM dual UNION ALLSELECT 1, 'a', 2 FROM dual UNION ALLSELECT 2,
'x', 7 FROM dual UNION ALLSELECT 2, 'y', 9 FROM dual UNION ALLSELECT 2,
'z', 8 FROM dual UNION ALLSELECT 2, 'v', 9 FROM dual --UNION ALL)SELECT t.*
,MIN(v1)KEEP(DENSE_RANK FIRST ORDER BY v2) OVER(PARTITION BY gid)
lkp_first ,MIN(v1)KEEP(DENSE_RANK LAST ORDER BY v2) OVER(PARTITION BY
gid) lkp_lastMin ,MAX(v1)KEEP(DENSE_RANK LAST ORDER BY v2)
OVER(PARTITION BY gid) lkp_lastMax FROM t*;
GIDV1V2LKP_FIRSTLKP_LASTMINLKP_LASTMAX
1 a 2 d b b
1 d 1 d b b
1 b 3 d b b
2 z 8 x v y
2 y 9 x v y
2 v 9 x v y
2 x 7 x v y

b) as a regular aggregate (cf. GROUP BY and two rows only in the result)

* WITH t(gid, v1, v2) AS(SELECT 1, 'b', 3 FROM dual UNION ALLSELECT 1,
'd', 1 FROM dual UNION ALLSELECT 1, 'a', 2 FROM dual UNION ALLSELECT 2,
'x', 7 FROM dual UNION ALLSELECT 2, 'y', 9 FROM dual UNION ALLSELECT 2,
'z', 8 FROM dual UNION ALLSELECT 2, 'v', 9 FROM dual --UNION ALL)SELECT
t.gid ,MIN(v1)KEEP(DENSE_RANK FIRST ORDER BY v2)lkp_first
,MIN(v1)KEEP(DENSE_RANK LAST ORDER BY v2)lkp_lastMin
,MAX(v1)KEEP(DENSE_RANK LAST ORDER BY v2)lkp_lastMax FROM t GROUP BY
t.gid*;
GIDLKP_FIRSTLKP_LASTMINLKP_LASTMAX
1 d b b
2 x v y

Any chances of implementing that?

Thank you,
Kind Regards
~Maciek