Hi Timothy: On Thu, Mar 26, 2015 at 9:49 PM, Timothy Garnett <tgarnett@xxxxxxxxxxx> wrote: > I'm trying to create a custom aggregate function that returns the value from > the first row (possibly null). ... > The straightforward way would seem to be something like > > CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) > RETURNS anyelement LANGUAGE sql IMMUTABLE STRICT AS $$ > SELECT $1; > $$; > but if that is declared strict then it would take the first non-null value > and return A in my second example, if declared non-strict then the initial > state would be fed as null rather then the first value. Is there a way to > declare the function non-strict (so that null values are passed) but still > have it initialize to the first value like it would if it was strict? First, your pasted code is incomplete, you should post all relevant code ( I suspect a highly relevant create aggregate statement is missing ). Next, it's already been pointed you are trying to use null for two meanings, which is not gonna work. I think your problem is you are using the one function form of aggregates. Aggregates, in postgres and lots of other places, are done with two functions, an state transition function called for each row ( more or less, strictness asside ) and a final function. Think on average, you need an state consisting of sum, count, and state transition function adding to sum and incremementing count and a final function to do the division. Generally, you start with STATE_TYPE state=initial_state; foreach value state=transition(state, current_val); AGG_TYPE agg_value = final(state). STATE_TYPE and AGG_TYPE need not be the same. When STATE_TYPE and AGG_TYPE are the same many systems, postgres included, have a shortcut for using just an state transition function , using indentity as the final function, as this is adequate for things like count, min/max, sum, and this seems to be the variant you are using. In your case you should probably use the two form agregate, use a composite type for the state ( first_time, val ), an state transition function ( if ft true store val, else skip ) and a final function ( return val from state ) ( there are posibly other methods, like using an array for state, initializing to empty, storing val if empty or skipping, just play around ). Also, you may be having an http://xyproblem.info/ , do you want an aggregate to return the first value, or a function?. ( I suspect it is the first case, as you are pasting a create function for first_agg and a select for my_first, but not having posted a complete example makes it very hard ). Regards. Francisco Olarte. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
