On Tue, Jul 14, 2015 at 9:23 AM, sudalai <sudalait2@gmail.com> wrote:
> The above implementation of "first" aggregate returns the first non-NULL item
> value.
>
> To get *first row item value* for a column use the below implementation.
>
> -- create a function that push at most two element on given array
> -- push the first row value at second index of the array
> CREATE OR REPLACE FUNCTION two_value_holder(anyarray, anyelement)
> returns anyarray as $$
> select case when array_length($1,1) < 2 then array_append($1,$2) else
> $1 end ;
> $$ language sql immutable;
>
> -- create a function that returns second element of an array
> CREATE OR replace FUNCTION second_element (ANYARRAY)
> RETURNS ANYELEMENT AS $$ SELECT $1[2]; $$ LANGUAGE SQL;
>
> -- create first aggregate function that return first_row item value
> CREATE AGGREGATE first(anyelement)(
> SFUNC=two_value_holder,
> STYPE=ANYARRAY,
> INITCOND='{NULL}',
> FINALFUNC=second_element
> );
>
> I hope this work..
I don't think so, because arrays can contain duplicates.
rhaas=# select coalesce(first(x.column1), 'wrong') from (values
(null), ('correct')) x;coalesce
----------wrong
(1 row)
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company