Hi Timothy:
On Thu, Mar 26, 2015 at 9:49 PM, Timothy Garnett <tgarnett@panjiva.com> 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.