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

Thanks Robert!
I thought this could be nice functionality available out of the box
but yea, sure, that would work for me.
Can I make custom aggregate function that accepts WITHIN GROUP syntax?
Which language would that need to be implemented in? Would you have
examples (url?)

Thank you,
Kind Regards
~Maciek
On Thu, 26 Jul 2018 at 16:22, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

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