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

On Tue, Jul 24, 2018 at 4:16 PM, matshyeq <matshyeq@gmail.com> wrote:

I'd like to throw here an enhancement proposal to discuss/consider.

The FIRST/LAST_value() 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);

gid	v1	v2	fv	lv

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?

Why not just define a custom aggregate function that does whatever you need?  I don't think it would be too hard.  e.g. for something like LAST_VALUE() just make the transition type equal to the output type and save the last value you've seen thus far as the transition value.

--

Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company