In a partition why 1st time encounter NULL then call minvfunc

Поиск
Список
Период
Сортировка
От jian he
Тема In a partition why 1st time encounter NULL then call minvfunc
Дата
Msg-id CACJufxGw1hvDyaOcU8k7yuO-D+qRK20bgKR2y4LNdpzV0JZ7bw@mail.gmail.com
обсуждение исходный текст
Ответы Re: In a partition why 1st time encounter NULL then call minvfunc  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список pgsql-general
dbfilddle

source

create or replace function logging_msfunc_strict(text,anyelement)
returns text as
$$
select $1 || '+' || quote_nullable($2)
$$
LANGUAGE sql strict IMMUTABLE;

create or replace function logging_minvfunc_strict(text, anyelement)
returns text as
$$
select $1 || '-' || quote_nullable($2)
$$
LANGUAGE sql strict IMMUTABLE;

create aggregate logging_agg_strict(text)
(    stype = text,    sfunc = logging_sfunc_strict,    mstype =  text,    msfunc = logging_msfunc_strict,    minvfunc = logging_minvfunc_strict
);


create aggregate logging_agg_strict_initcond(anyelement)
(    stype = text,    sfunc = logging_sfunc_strict,    mstype = text,     msfunc = logging_msfunc_strict,    minvfunc = logging_minvfunc_strict,    initcond = 'I',    minitcond = 'MI'
);

execute following query: 

SELECT
p::text || ',' || i::text || ':' || COALESCE(v::text, 'NULL') AS _row, logging_agg_strict (v) OVER w AS nstrict, logging_agg_strict_initcond (v) OVER w AS nstrict FROM ( VALUES (1, 1, NULL), (1, 2, 'a'), (1, 3, 'b'), (1, 4, NULL), (1, 5, NULL), (1, 6, 'c'), (2, 1, NULL), (2, 2, 'x'), (3, 1, 'z')) AS t (p, i, v) WINDOW w AS (PARTITION BY p ORDER BY i ROWS BETWEEN 1 PRECEDING AND CURRENT ROW);

return following result: 

_row | nstrict | nstrict ----------+-----------+---------------- 1,1:NULL | [[null]] | MI 1,2:a | a | MI+'a' 1,3:b | a+'b' | MI+'a'+'b' 1,4:NULL | a+'b'-'a' | MI+'a'+'b'-'a' 1,5:NULL | [[null]] | MI 1,6:c | c | MI+'c' 2,1:NULL | [[null]] | MI 2,2:x | x | MI+'x' 3,1:z | z | MI+'z' (9 rows)


For now I don't understand row 1,4:NULL | a+'b'-'a' | MI+'a'+'b'-'a'. I am not sure why the 1st time you encounter NULL then it will call inverse transition function Overall, not sure about the idea of inverse transition function.



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

Предыдущее
От: Bryn Llewellyn
Дата:
Сообщение: Re: Two questions about "pg_constraint"
Следующее
От: Christophe Pettus
Дата:
Сообщение: Re: Two questions about "pg_constraint"