From: | jian he <jian(dot)universality(at)gmail(dot)com> |
---|---|
To: | pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | In a partition why 1st time encounter NULL then call minvfunc |
Date: | 2022-08-26 05:40:49 |
Message-ID: | CACJufxGw1hvDyaOcU8k7yuO-D+qRK20bgKR2y4LNdpzV0JZ7bw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
same question:
https://stackoverflow.com/questions/73476732/postgresql-in-a-partition-1st-time-null-then-call-minvfunc
dbfilddle
<https://dbfiddle.uk/?rdbms=postgres_14&fiddle=307e1fa8d83af57aeb7698d9d58056cf>
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 nstrictFROM (
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Christophe Pettus | 2022-08-26 05:49:57 | Re: Two questions about "pg_constraint" |
Previous Message | Bryn Llewellyn | 2022-08-26 04:43:05 | Re: Two questions about "pg_constraint" |