Creating a non-strict custom aggregate that initializes to the first value

From: Timothy Garnett <tgarnett(at)panjiva(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Creating a non-strict custom aggregate that initializes to the first value
Date: 2015-03-26 20:49:51
Message-ID: CAPcyiQ2BuAc0438jbh5OnZDEgF=z4g8sdFbhLEy+MUpbqaxKWA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi all,

I'm trying to create a custom aggregate function that returns the value
from the first row (possibly null).

For ex.

Table t
a | b
-----
1 | A
2 | NULL

SELECT my_first(b order by a) => A
SELECT my_first(b order by a DESC) => NULL

The straightforward way would seem to be something like

CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement )
RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$
SELECT $1;
$$;

but if that is declared strict then it would take the first non-null value
and return A in my second example, if declared non-strict then the initial
state would be fed as null rather then the first value. Is there a way to
declare the function non-strict (so that null values are passed) but still
have it initialize to the first value like it would if it was strict?

Thanks for the help,
Tim

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Erik Jones 2015-03-26 21:21:36 9.4's limited logical replication, anyone actually used it, yet?
Previous Message Francisco Olarte 2015-03-26 17:57:17 Re: Column does not exists?