Thread: First aggregate with null support

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