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
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 |