First aggregate with null support

From: "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: First aggregate with null support
Date: 2010-12-06 10:59:58
Message-ID: C4DAC901169B624F933534A26ED7DF31034BB971@JENMAIL01.ad.intershop.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello,

I'm looking for a First aggregate which may return null.

From the example at
http://wiki.postgresql.org/index.php?title=First_%28aggregate%29,
I have just defined a non strict function that returns the first value:

CREATE OR REPLACE FUNCTION public.first_agg_wnull ( anyelement,
anyelement )
RETURNS anyelement AS $$
SELECT $1;
$$ LANGUAGE SQL IMMUTABLE
COST 1;

And an aggregate:

CREATE AGGREGATE public.first_wnull (
sfunc = first_agg,
basetype = anyelement,
stype = anyelement
);


But this always return null which is the default init condition of the
aggregate :-(

I also have a working one using an array function (first_wnull_a, below)
, but I wonder if there is a simpler solution ...

best regards,

Marc Mamin

CREATE OR REPLACE FUNCTION first_element_state(anyarray, anyelement)
RETURNS anyarray AS
$$
SELECT CASE WHEN array_upper($1,1) IS NULL THEN array_append($1,$2) ELSE
$1 END;
$$
LANGUAGE 'sql' IMMUTABLE
COST 2;

CREATE OR REPLACE FUNCTION first_element(anyarray)
RETURNS anyelement AS
$$
SELECT ($1)[1] ;
$$
LANGUAGE 'sql' IMMUTABLE
COST 2;

CREATE AGGREGATE first_wnull_a(anyelement) (
SFUNC=first_element_state,
STYPE=anyarray,
FINALFUNC=first_element
);

select first_wnull(s) from generate_series (1,10) s
=>
NULL

select first_wnull_a(s) from generate_series (1,10) s
=>
1

Browse pgsql-sql by date

  From Date Subject
Next Message - 2010-12-06 14:59:28 The best option to insert data with primary id
Previous Message Jayadevan M 2010-12-06 02:55:21 Re: Get the max viewd product_id for user_id