| 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: | Whole Thread | Raw Message | 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
| 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? |