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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
