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

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: Creating a non-strict custom aggregate that initializes to the first value
Дата
Msg-id CAKFQuwbm26JTbCJaB7uzHxR1QyRDestRzfaJMCiFciEcfU3jsQ@mail.gmail.com
обсуждение исходный текст
Ответ на Creating a non-strict custom aggregate that initializes to the first value  (Timothy Garnett <tgarnett@panjiva.com>)
Ответы Re: Creating a non-strict custom aggregate that initializes to the first value  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
On Thu, Mar 26, 2015 at 1:49 PM, Timothy Garnett <tgarnett@panjiva.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.

В списке pgsql-general по дате отправления:

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: Strange behavior of insert CTE with trigger
Следующее
От: TonyS
Дата:
Сообщение: Re: Would like to know how analyze works technically