Re: First Aggregate Funtion? - Mailing list pgsql-hackers

From sudalai
Subject Re: First Aggregate Funtion?
Date
Msg-id 1436880205628-5857866.post@n5.nabble.com
Whole thread Raw
In response to Re: First Aggregate Funtion?  ("Mike Rylander" <mrylander@gmail.com>)
Responses Re: First Aggregate Funtion?
List pgsql-hackers
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.. 
--
Sudalai




-----
sudalai
--
View this message in context: http://postgresql.nabble.com/First-Aggregate-Funtion-tp1943031p5857866.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.



pgsql-hackers by date:

Previous
From: Robert Haas
Date:
Subject: Re: Improving log capture of TAP tests with IPC::Run
Next
From: Tomas Vondra
Date:
Subject: Re: multivariate statistics / patch v7