Re: Creating a non-strict custom aggregate that initializes to the first value

On Thu, Mar 26, 2015 at 1:49 PM, Timothy Garnett <tgarnett(at)panjiva(dot)com>
wrote:

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

​Late night pondering here but...

Because of the way SQL null works, and your desired to handle "anyelement",
you are stuck determining whether you are currently evaluating the first
row of your input - or not. For the first row you always take the "new"
value while for all subsequent rows you take the "state" value. So, your
state needs to encompass both "prior row number" and "active value", which
suggests you need to create a custom type for your state variable.

You want NULL to both mean "not initialized" and "unknown value" which is
impossible and SQL does not provide any other universal literal that means
one or the other.

I'm not sure how you deal with "anyelement" in a custom type that could be
used as a state variable...

David J.