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

On Tue, Jul 24, 2018 at 4:16 PM, matshyeq <matshyeq(at)gmail(dot)com> wrote:

> 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?
>

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