Re: Implement for window functions
Hi, patch applies and compiles, all included and external tests and building of the docs pass. After the last run of the cfbot, there are no any building warnings. I am using last version in our testing environment with real data and I didn't find any bugs, so I'm marking this patch as ready for the committer in the commitfest app. На сб, 9.01.2021 г. в 13:30 ч. Krasiyan Andreev написа: > Hi, the building warning below is fixed now, no other changes. Also, I can > confirm that the corner case with offset=0 in lead and lag works correctly. > > gcc -Wall -Wmissing-prototypes -Wpointer-arith > -Wdeclaration-after-statement -Werror=vla -Wendif-labels > -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type > -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard > -Wno-format-truncation -Wno-stringop-truncation -O2 -I../../../src/include > -I/home/krasiyan/pgsql/postgresql/src/include -D_GNU_SOURCE > -I/usr/include/libxml2 -c -o nodeWindowAgg.o > /home/krasiyan/pgsql/postgresql/src/backend/executor/nodeWindowAgg.c > /home/krasiyan/pgsql/postgresql/src/backend/executor/nodeWindowAgg.c: In > function ‘WinGetFuncArgInPartition’: > /home/krasiyan/pgsql/postgresql/src/backend/executor/nodeWindowAgg.c:3274:10: > warning: ‘step’ may be used uninitialized in this function > [-Wmaybe-uninitialized] > 3274 | relpos += step; > | ~~~^~~ > /home/krasiyan/pgsql/postgresql/src/backend/executor/nodeWindowAgg.c: In > function ‘WinGetFuncArgInFrame’: > /home/krasiyan/pgsql/postgresql/src/backend/executor/nodeWindowAgg.c:3531:10: > warning: ‘step’ may be used uninitialized in this function > [-Wmaybe-uninitialized] > 3531 | relpos += step; > | ~~~^~~ > > > > На пт, 8.01.2021 г. в 2:02 ч. Vik Fearing > написа: > >> On 1/1/21 10:21 PM, Zhihong Yu wrote: >> > Krasiyan: >> > Happy New Year. >> > >> > For WinGetFuncArgInPartition(): >> > >> > + if (target > 0) >> > + step = 1; >> > + else if (target < 0) >> > + step = -1; >> > + else >> > + step = 0; >> > >> > When would the last else statement execute ? Since the above code is >> > for WINDOW_SEEK_CURRENT, I wonder why step should be 0. >> >> Hi. >> >> "lag(expr, 0) over w" is useless but valid. >> -- >> Vik Fearing >> >
Re: Implement for window functions
Hi, the building warning below is fixed now, no other changes. Also, I can
confirm that the corner case with offset=0 in lead and lag works correctly.
gcc -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Werror=vla -Wendif-labels
-Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type
-Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard
-Wno-format-truncation -Wno-stringop-truncation -O2 -I../../../src/include
-I/home/krasiyan/pgsql/postgresql/src/include -D_GNU_SOURCE
-I/usr/include/libxml2 -c -o nodeWindowAgg.o
/home/krasiyan/pgsql/postgresql/src/backend/executor/nodeWindowAgg.c
/home/krasiyan/pgsql/postgresql/src/backend/executor/nodeWindowAgg.c: In
function ‘WinGetFuncArgInPartition’:
/home/krasiyan/pgsql/postgresql/src/backend/executor/nodeWindowAgg.c:3274:10:
warning: ‘step’ may be used uninitialized in this function
[-Wmaybe-uninitialized]
3274 | relpos += step;
| ~~~^~~
/home/krasiyan/pgsql/postgresql/src/backend/executor/nodeWindowAgg.c: In
function ‘WinGetFuncArgInFrame’:
/home/krasiyan/pgsql/postgresql/src/backend/executor/nodeWindowAgg.c:3531:10:
warning: ‘step’ may be used uninitialized in this function
[-Wmaybe-uninitialized]
3531 | relpos += step;
| ~~~^~~
На пт, 8.01.2021 г. в 2:02 ч. Vik Fearing написа:
> On 1/1/21 10:21 PM, Zhihong Yu wrote:
> > Krasiyan:
> > Happy New Year.
> >
> > For WinGetFuncArgInPartition():
> >
> > + if (target > 0)
> > + step = 1;
> > + else if (target < 0)
> > + step = -1;
> > + else
> > + step = 0;
> >
> > When would the last else statement execute ? Since the above code is
> > for WINDOW_SEEK_CURRENT, I wonder why step should be 0.
>
> Hi.
>
> "lag(expr, 0) over w" is useless but valid.
> --
> Vik Fearing
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 02a37658ad..7a6f194138 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20402,6 +20402,14 @@ SELECT count(*) FROM sometable;
about frame specifications.
+
+ The functions lead, lag,
+ first_value, last_value, and
+ nth_value accept a null treatment option which is
+ RESPECT NULLS or IGNORE NULLS.
+ If this option is not specified, the default is RESPECT NULLS.
+
+
When an aggregate function is used as a window function, it aggregates
over the rows within the current row's window frame.
@@ -20415,14 +20423,9 @@ SELECT count(*) FROM sometable;
-The SQL standard defines a RESPECT NULLS or
-IGNORE NULLS option for lead, lag,
-first_value, last_value, and
-nth_value. This is not implemented in
-PostgreSQL: the behavior is always the
-same as the standard's default, namely RESPECT NULLS.
-Likewise, the standard's FROM FIRST or FROM LAST
-option for nth_value is not implemented: only the
+The SQL standard defines a FROM FIRST or FROM LAST
+option for nth_value. This is not implemented in
+PostgreSQL: only the
default FROM FIRST behavior is supported. (You can achieve
the result of FROM LAST by reversing the ORDER BY
ordering.)
diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml
index 3c1eaea651..31e08c26b4 100644
--- a/doc/src/sgml/ref/create_function.sgml
+++ b/doc/src/sgml/ref/create_function.sgml
@@ -28,6 +28,7 @@ CREATE [ OR REPLACE ] FUNCTION
{ LANGUAGE lang_name
| TRANSFORM { FOR TYPE type_name } [, ... ]
| WINDOW
+| TREAT NULLS
| IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
@@ -293,6 +294,17 @@ CREATE [ OR REPLACE ] FUNCTION
+
+ TREAT NULLS
+
+
+ TREAT NULLS indicates that the function is able
+ to handle the RESPECT NULLS and IGNORE
+ NULLS options. Only window functions may specify this.
+
+
+
+
IMMUTABLE
STABLE
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index d66560b587..103595d21b 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1766,6 +1766,8 @@ FROM generate_series(1,10) AS s(i);
The syntax of a window function call is one of the following:
+function_name (expression , expression ... ) [ RESPECT NULLS | IGNORE NULLS ] OVER window_name
+function_name (expression , expression ... ) [ RESPECT NULLS | IGNORE NULLS ] OVER ( window_definition )
function_name (expression , expression ... ) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name (expression , expression ... ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name
@@ -1779,6 +1781,18 @@ FROM generate_series(1,10) AS s(i);
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [,
Re: Implement for window functions
Hi, it looks like cfbot.cputube.org didn't recognize and can't apply a
patch, so I resend it now with a different format, no other changes.
На ср, 30.12.2020 г. в 22:16 ч. Krasiyan Andreev
написа:
> It works - now it compiles clean and all checks are passed, thank you. I
> will continue with more complex tests.
>
> На ср, 30.12.2020 г. в 21:50 ч. David Fetter написа:
>
>> On Wed, Dec 30, 2020 at 09:32:26PM +0200, Krasiyan Andreev wrote:
>> > Hi, after latest committed patches about multirange datatypes, I get a
>> > compilation error,
>>
>> Oh, right. I'd been meaning to send a patch to fix that. Here it is.
>>
>> Best,
>> David.
>> --
>> David Fetter http://fetter.org/
>> Phone: +1 415 235 3778
>>
>> Remember to vote!
>> Consider donating to Postgres: http://www.postgresql.org/about/donate
>>
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5021ac1ca9..0e877c48df 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20373,6 +20373,14 @@ SELECT count(*) FROM sometable;
about frame specifications.
+
+ The functions lead, lag,
+ first_value, last_value, and
+ nth_value accept a null treatment option which is
+ RESPECT NULLS or IGNORE NULLS.
+ If this option is not specified, the default is RESPECT NULLS.
+
+
When an aggregate function is used as a window function, it aggregates
over the rows within the current row's window frame.
@@ -20386,14 +20394,9 @@ SELECT count(*) FROM sometable;
-The SQL standard defines a RESPECT NULLS or
-IGNORE NULLS option for lead, lag,
-first_value, last_value, and
-nth_value. This is not implemented in
-PostgreSQL: the behavior is always the
-same as the standard's default, namely RESPECT NULLS.
-Likewise, the standard's FROM FIRST or FROM LAST
-option for nth_value is not implemented: only the
+The SQL standard defines a FROM FIRST or FROM LAST
+option for nth_value. This is not implemented in
+PostgreSQL: only the
default FROM FIRST behavior is supported. (You can achieve
the result of FROM LAST by reversing the ORDER BY
ordering.)
diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml
index 3c1eaea651..31e08c26b4 100644
--- a/doc/src/sgml/ref/create_function.sgml
+++ b/doc/src/sgml/ref/create_function.sgml
@@ -28,6 +28,7 @@ CREATE [ OR REPLACE ] FUNCTION
{ LANGUAGE lang_name
| TRANSFORM { FOR TYPE type_name } [, ... ]
| WINDOW
+| TREAT NULLS
| IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
@@ -293,6 +294,17 @@ CREATE [ OR REPLACE ] FUNCTION
+
+ TREAT NULLS
+
+
+ TREAT NULLS indicates that the function is able
+ to handle the RESPECT NULLS and IGNORE
+ NULLS options. Only window functions may specify this.
+
+
+
+
IMMUTABLE
STABLE
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index d66560b587..103595d21b 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1766,6 +1766,8 @@ FROM generate_series(1,10) AS s(i);
The syntax of a window function call is one of the following:
+function_name (expression , expression ... ) [ RESPECT NULLS | IGNORE NULLS ] OVER window_name
+function_name (expression , expression ... ) [ RESPECT NULLS | IGNORE NULLS ] OVER ( window_definition )
function_name (expression , expression ... ) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name (expression , expression ... ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name
@@ -1779,6 +1781,18 @@ FROM generate_series(1,10) AS s(i);
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
+
+
+
+
+ The versions with RESPECT NULLS or IGNORE
+ NULLS only apply to true window functions, whereas the versions
+ with FILTER only apply to aggregate functions used as
+ window functions.
+
+
+
+
The optional frame_clause
can be one of
diff --git a/src/backend/catalog/pg_aggregate.c b/src/backend/catalog/pg_aggregate.c
index 7664bb6285..fea8778ec8 100644
--- a/src/backend/catalog/pg_aggregate.c
+++ b/src/backend/catalog/pg_aggregate.c
@@ -627,6 +627,7 @@ AggregateCreate(const char *aggName,
* definable for agg) */
false, /* isLeakProof */
false, /* isStrict (not needed for agg) */
+ false, /* null_treatment (not needed for agg) */
PROVOLATILE_IMMUTABLE, /* volatility (not needed
* for agg) */
proparallel,
@@ -848,7 +849,7 @@ lookup_agg_function(List *fnNam
Re: Implement for window functions
It works - now it compiles clean and all checks are passed, thank you. I will continue with more complex tests. На ср, 30.12.2020 г. в 21:50 ч. David Fetter написа: > On Wed, Dec 30, 2020 at 09:32:26PM +0200, Krasiyan Andreev wrote: > > Hi, after latest committed patches about multirange datatypes, I get a > > compilation error, > > Oh, right. I'd been meaning to send a patch to fix that. Here it is. > > Best, > David. > -- > David Fetter http://fetter.org/ > Phone: +1 415 235 3778 > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate >
Re: Implement for window functions
: note:
expected ‘char’ but argument is of type ‘oidvector *’
206 | char parallel,
| ~^~~~
In file included from
/home/krasiyan/pgsql/postgresql/src/backend/commands/typecmds.c:32:
/home/krasiyan/pgsql/postgresql/src/include/postgres.h:556:29: warning:
passing argument 18 of ‘ProcedureCreate’ makes pointer from integer without
a cast [-Wint-conversion]
556 | #define PointerGetDatum(X) ((Datum) (X))
|~^~~~
| |
| long unsigned int
/home/krasiyan/pgsql/postgresql/src/backend/commands/typecmds.c:1933:9:
note: in expansion of macro ‘PointerGetDatum’
1933 | PointerGetDatum(allParameterTypes), /* allParameterTypes */
| ^~~
In file included from
/home/krasiyan/pgsql/postgresql/src/backend/commands/typecmds.c:52:
/home/krasiyan/pgsql/postgresql/src/include/catalog/pg_proc.h:207:22: note:
expected ‘oidvector *’ but argument is of type ‘long unsigned int’
207 | oidvector *parameterTypes,
| ~~~^~
In file included from
/home/krasiyan/pgsql/postgresql/src/include/access/tupdesc.h:19,
from
/home/krasiyan/pgsql/postgresql/src/include/utils/relcache.h:18,
from
/home/krasiyan/pgsql/postgresql/src/include/access/genam.h:21,
from
/home/krasiyan/pgsql/postgresql/src/backend/commands/typecmds.c:34:
/home/krasiyan/pgsql/postgresql/src/include/nodes/pg_list.h:65:19: warning:
passing argument 21 of ‘ProcedureCreate’ makes integer from pointer without
a cast [-Wint-conversion]
65 | #define NIL ((List *) NULL)
| ~^~
| |
| List *
/home/krasiyan/pgsql/postgresql/src/backend/commands/typecmds.c:1936:9:
note: in expansion of macro ‘NIL’
1936 | NIL, /* parameterDefaults */
| ^~~
In file included from
/home/krasiyan/pgsql/postgresql/src/backend/commands/typecmds.c:52:
/home/krasiyan/pgsql/postgresql/src/include/catalog/pg_proc.h:210:17: note:
expected ‘Datum’ {aka ‘long unsigned int’} but argument is of type ‘List *’
210 | Datum parameterNames,
| ~~^~
In file included from
/home/krasiyan/pgsql/postgresql/src/backend/commands/typecmds.c:32:
/home/krasiyan/pgsql/postgresql/src/include/postgres.h:556:29: warning:
passing argument 22 of ‘ProcedureCreate’ makes pointer from integer without
a cast [-Wint-conversion]
556 | #define PointerGetDatum(X) ((Datum) (X))
|~^~~~
| |
| long unsigned int
/home/krasiyan/pgsql/postgresql/src/backend/commands/typecmds.c:1937:9:
note: in expansion of macro ‘PointerGetDatum’
1937 | PointerGetDatum(NULL), /* trftypes */
| ^~~
In file included from
/home/krasiyan/pgsql/postgresql/src/backend/commands/typecmds.c:52:
/home/krasiyan/pgsql/postgresql/src/include/catalog/pg_proc.h:211:17: note:
expected ‘List *’ but argument is of type ‘long unsigned int’
211 | List *parameterDefaults,
| ~~^
/home/krasiyan/pgsql/postgresql/src/backend/commands/typecmds.c:1916:11:
error: too few arguments to function ‘ProcedureCreate’
1916 | myself = ProcedureCreate(name, /* name: same as multirange type */
| ^~~
In file included from
/home/krasiyan/pgsql/postgresql/src/backend/commands/typecmds.c:52:
/home/krasiyan/pgsql/postgresql/src/include/catalog/pg_proc.h:190:22: note:
declared here
190 | extern ObjectAddress ProcedureCreate(const char *procedureName,
| ^~~
make[3]: *** [: typecmds.o] Error 1
make[3]: Leaving directory '/home/krasiyan/pgsql/build/src/backend/commands'
make[2]: *** [/home/krasiyan/pgsql/postgresql/src/backend/common.mk:39:
commands-recursive] Error 2
make[2]: Leaving directory '/home/krasiyan/pgsql/build/src/backend'
make[1]: *** [Makefile:42: all-backend-recurse] Error 2
make[1]: Leaving directory '/home/krasiyan/pgsql/build/src'
make: *** [GNUmakefile:11: all-src-recurse] Error 2
[krasiyan@localhost build]$
На вт, 8.12.2020 г. в 16:27 ч. Vik Fearing написа:
> On 11/21/20 10:07 AM, Krasiyan Andreev wrote:
> > Fixed patch attached, after new introduced conflicts.
> > Vik, can you add it to the next commitfest, to be able to test it.
>
>
> I have done this now. Thanks!
> --
> Vik Fearing
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5021ac1ca9..0e877c48df 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -20373,6 +20373,14 @@ SELECT count(*) FROM sometable;
about frame specifications.
+
+ The functions lead, lag,
+ first_value, last_value, and
+ nth_value accept a null treatment option which is
+ RESPECT NULLS or
Re: Implement for window functions
Fixed patch attached, after new introduced conflicts.
Vik, can you add it to the next commitfest, to be able to test it.
Also, all tests from Oliver Ford's old patch also passed successfully.
На пт, 13.11.2020 г. в 0:44 ч. Vik Fearing написа:
> On 11/12/20 11:35 PM, Krasiyan Andreev wrote:
> > Hi, it looks like Vik Fearing's patch does not apply anymore, because
> there
> > are many conflicts with recent changes, fixed patch attached.
>
> Thanks! I've been away from this list for a while and I have some
> catching up to do.
> --
> Vik Fearing
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7d06b979eb..e69977de76 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19722,6 +19722,14 @@ SELECT count(*) FROM sometable;
about frame specifications.
+
+ The functions lead, lag,
+ first_value, last_value, and
+ nth_value accept a null treatment option which is
+ RESPECT NULLS or IGNORE NULLS.
+ If this option is not specified, the default is RESPECT NULLS.
+
+
When an aggregate function is used as a window function, it aggregates
over the rows within the current row's window frame.
@@ -19735,14 +19743,9 @@ SELECT count(*) FROM sometable;
-The SQL standard defines a RESPECT NULLS or
-IGNORE NULLS option for lead, lag,
-first_value, last_value, and
-nth_value. This is not implemented in
-PostgreSQL: the behavior is always the
-same as the standard's default, namely RESPECT NULLS.
-Likewise, the standard's FROM FIRST or FROM LAST
-option for nth_value is not implemented: only the
+The SQL standard defines a FROM FIRST or FROM LAST
+option for nth_value. This is not implemented in
+PostgreSQL: only the
default FROM FIRST behavior is supported. (You can achieve
the result of FROM LAST by reversing the ORDER BY
ordering.)
diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml
index 3c1eaea651..31e08c26b4 100644
--- a/doc/src/sgml/ref/create_function.sgml
+++ b/doc/src/sgml/ref/create_function.sgml
@@ -28,6 +28,7 @@ CREATE [ OR REPLACE ] FUNCTION
{ LANGUAGE lang_name
| TRANSFORM { FOR TYPE type_name } [, ... ]
| WINDOW
+| TREAT NULLS
| IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
@@ -293,6 +294,17 @@ CREATE [ OR REPLACE ] FUNCTION
+
+ TREAT NULLS
+
+
+ TREAT NULLS indicates that the function is able
+ to handle the RESPECT NULLS and IGNORE
+ NULLS options. Only window functions may specify this.
+
+
+
+
IMMUTABLE
STABLE
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 3fdd87823e..685454c1ec 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1770,6 +1770,8 @@ FROM generate_series(1,10) AS s(i);
The syntax of a window function call is one of the following:
+function_name (expression , expression ... ) [ RESPECT NULLS | IGNORE NULLS ] OVER window_name
+function_name (expression , expression ... ) [ RESPECT NULLS | IGNORE NULLS ] OVER ( window_definition )
function_name (expression , expression ... ) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name (expression , expression ... ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name
@@ -1783,6 +1785,18 @@ FROM generate_series(1,10) AS s(i);
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
+
+
+
+
+ The versions with RESPECT NULLS or IGNORE
+ NULLS only apply to true window functions, whereas the versions
+ with FILTER only apply to aggregate functions used as
+ window functions.
+
+
+
+
The optional frame_clause
can be one of
diff --git a/src/backend/catalog/pg_aggregate.c b/src/backend/catalog/pg_aggregate.c
index 7664bb6285..fea8778ec8 100644
--- a/src/backend/catalog/pg_aggregate.c
+++ b/src/backend/catalog/pg_aggregate.c
@@ -627,6 +627,7 @@ AggregateCreate(const char *aggName,
* definable for agg) */
false, /* isLeakProof */
false, /* isStrict (not needed for agg) */
+ false, /* null_treatment (not needed for agg) */
PROVOLATILE_IMMUTABLE, /* volatility (not needed
* for agg) */
proparallel,
@@ -848,7 +849,7 @@ lookup_agg_function(List *fnName,
nargs, input_types, false, false,
, rettype, ,
, ,
- _oid_array, NULL);
+ _oid_array, NULL, NULL);
/* only valid case is a normal function not returning a set */
if (fdresult != FUNCDETAIL_NORMAL || !OidIsValid(fnOid))
diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
Re: Implement for window functions
Hi, it looks like Vik Fearing's patch does not apply anymore, because there
are many conflicts with recent changes, fixed patch attached.
I am interested in reviewing and testing it for the next commitfest, if
it's design and implementation is found to be acceptable.
Additionally, if it is also acceptable, I can add support for handling
negative indexes for nth_value(), to be able to reverse order from
first/from last for the window frame.
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2783985b55..44c8d006d0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -19735,6 +19735,14 @@ SELECT count(*) FROM sometable;
about frame specifications.
+
+ The functions lead, lag,
+ first_value, last_value, and
+ nth_value accept a null treatment option which is
+ RESPECT NULLS or IGNORE NULLS.
+ If this option is not specified, the default is RESPECT NULLS.
+
+
When an aggregate function is used as a window function, it aggregates
over the rows within the current row's window frame.
@@ -19748,14 +19756,9 @@ SELECT count(*) FROM sometable;
-The SQL standard defines a RESPECT NULLS or
-IGNORE NULLS option for lead, lag,
-first_value, last_value, and
-nth_value. This is not implemented in
-PostgreSQL: the behavior is always the
-same as the standard's default, namely RESPECT NULLS.
-Likewise, the standard's FROM FIRST or FROM LAST
-option for nth_value is not implemented: only the
+The SQL standard defines a FROM FIRST or FROM LAST
+option for nth_value. This is not implemented in
+PostgreSQL: only the
default FROM FIRST behavior is supported. (You can achieve
the result of FROM LAST by reversing the ORDER BY
ordering.)
diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml
index 3c1eaea651..31e08c26b4 100644
--- a/doc/src/sgml/ref/create_function.sgml
+++ b/doc/src/sgml/ref/create_function.sgml
@@ -28,6 +28,7 @@ CREATE [ OR REPLACE ] FUNCTION
{ LANGUAGE lang_name
| TRANSFORM { FOR TYPE type_name } [, ... ]
| WINDOW
+| TREAT NULLS
| IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
@@ -293,6 +294,17 @@ CREATE [ OR REPLACE ] FUNCTION
+
+ TREAT NULLS
+
+
+ TREAT NULLS indicates that the function is able
+ to handle the RESPECT NULLS and IGNORE
+ NULLS options. Only window functions may specify this.
+
+
+
+
IMMUTABLE
STABLE
diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml
index 3fdd87823e..685454c1ec 100644
--- a/doc/src/sgml/syntax.sgml
+++ b/doc/src/sgml/syntax.sgml
@@ -1770,6 +1770,8 @@ FROM generate_series(1,10) AS s(i);
The syntax of a window function call is one of the following:
+function_name (expression , expression ... ) [ RESPECT NULLS | IGNORE NULLS ] OVER window_name
+function_name (expression , expression ... ) [ RESPECT NULLS | IGNORE NULLS ] OVER ( window_definition )
function_name (expression , expression ... ) [ FILTER ( WHERE filter_clause ) ] OVER window_name
function_name (expression , expression ... ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER window_name
@@ -1783,6 +1785,18 @@ FROM generate_series(1,10) AS s(i);
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
+
+
+
+
+ The versions with RESPECT NULLS or IGNORE
+ NULLS only apply to true window functions, whereas the versions
+ with FILTER only apply to aggregate functions used as
+ window functions.
+
+
+
+
The optional frame_clause
can be one of
diff --git a/src/backend/catalog/pg_aggregate.c b/src/backend/catalog/pg_aggregate.c
index 7664bb6285..fea8778ec8 100644
--- a/src/backend/catalog/pg_aggregate.c
+++ b/src/backend/catalog/pg_aggregate.c
@@ -627,6 +627,7 @@ AggregateCreate(const char *aggName,
* definable for agg) */
false, /* isLeakProof */
false, /* isStrict (not needed for agg) */
+ false, /* null_treatment (not needed for agg) */
PROVOLATILE_IMMUTABLE, /* volatility (not needed
* for agg) */
proparallel,
@@ -848,7 +849,7 @@ lookup_agg_function(List *fnName,
nargs, input_types, false, false,
, rettype, ,
, ,
- _oid_array, NULL);
+ _oid_array, NULL, NULL);
/* only valid case is a normal function not returning a set */
if (fdresult != FUNCDETAIL_NORMAL || !OidIsValid(fnOid))
diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index 1dd9ecc063..2783b0d630 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -80,6 +80,7 @@ ProcedureCreate(const
Re: [PATCH] distinct aggregates within a window function WIP
Thank you very much. I think that Vik Fearing's patch about "Implement for window functions" is much clear, better and has a chance to be committed. For me it's not important which patch will go into PostgreSQL, because it's a much needed feature. In mine patch, there is also a feature about using negative indexes, to be able to reverse order in exact same window frame for "FROM FIRST/FROM LAST", but I am not sure, is such non-standard usage is acceptable (it's the same as some array functions in programming language), if it's acceptable, it can be easy ported to Vik's patch. I am thinking also to concentrate on Vik's patch, if it has a clear design point of view, clear design, I can withdraw mine patch. На ср, 16.09.2020 г. в 11:19 Surafel Temesgen написа: > > On Thu, Mar 5, 2020 at 4:17 AM Krasiyan Andreev > wrote: > >> I have currently suspended development of this patch, based on it's >> review, >> but I will continue development of the other Oliver Ford's work about >> adding support of respect/ignore nulls >> for lag(),lead(),first_value(),last_value() and nth_value() and from >> first/last for nth_value() patch, >> but I am not sure how to proceed with it's implementation and any >> feedback will be very helpful. >> >> > * I applied your patch on top of 58c47ccfff20b8c125903 . It applied > cleanly , compiled, make check pass, but it have white space errors: > > *Added functions on windowfuncs.c have no comments so it's not easily > understandable. > > * Regression test addition seems huge to me. Can you reduce that? You can > use existing tables and fewer records. > > * I don’t understand why this patch has to change makeBoolAConst? It > already make “bool” constant node > > > regards > > Surafel > >
Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options
Thank you very much for feedback and yes, that is very useful SQL syntax. Maybe you miss my previous answer, but you are right, that patch is currently dead, because some important design questions must be discussed here, before patch rewriting. I have dropped support of from first/last for nth_value(), but also I reimplemented it in a different way, by using negative number for the position argument, to be able to get the same frame in exact reverse order. After that patch becomes much more simple and some concerns about precedence hack has gone. I have not renamed special bool type "ignorenulls" (I know that it is not acceptable way for calling extra version of window functions, but also it makes things very easy and it can reuse frames), but I removed the other special bool type "fromlast". Attached file was for PostgreSQL 13 (master git branch, last commit fest), everything was working and patch was at the time in very good shape, all tests was passed. I read previous review and suggestions from Tom about special bool type and unreserved keywords and also, that IGNORE NULLS could be implemented inside the WinGetFuncArgXXX functions, but I am not sure how exactly to proceed (some example will be very helpful). На чт, 30.04.2020 г. в 21:58 Stephen Frost написа: > Greetings, > > This seems to have died out, and that's pretty unfortunate because this > is awfully useful SQL standard syntax that people look for and wish we > had. > > * Andrew Gierth (and...@tao11.riddles.org.uk) wrote: > > So I've tried to rough out a decision tree for the various options on > > how this might be implemented (discarding the "use precedence hacks" > > option). Opinions? Additions? > > > > (formatted for emacs outline-mode) > > > > * 1. use lexical lookahead > > > > +: relatively straightforward parser changes > > +: no new reserved words > > +: has the option of working extensibly with all functions > > > > -: base_yylex needs extending to 3 lookahead tokens > > This sounds awful grotty and challenging to do and get right, and the > alternative (just reserving these, as the spec does) doesn't seem so > draconian as to be that much of an issue. > > > * 2. reserve nth_value etc. as functions > > > > +: follows the spec reasonably well > > +: less of a hack than extending base_yylex > > > > -: new reserved words > > -: more parser rules > > -: not extensible > > > > For my 2c, at least, reserving these strikes me as entirely reasonable. > Yes, it sucks that we have to partially-reserve some additional > keywords, but such is life. I get that we'll throw syntax errors > sometimes when we might have given a better error, but I think we can > accept that. > > > (now goto 1.2.1) > > Hmm, not sure this was right? but sure, I'll try... > > > *** 1.2.1. Check the function name in parse analysis against a fixed > list. > > > > +: simple > > -: not extensible > > Seems like this is more-or-less required since we'd be reserving them..? > > > *** 1.2.2. Provide some option in CREATE FUNCTION > > > > +: extensible > > -: fairly intrusive, adding stuff to create function and pg_proc > > How would this work though, if we reserve the functions as keywords..? > Maybe I'm not entirely following, but wouldn't attempts to use other > functions end up with syntax errors in at least some of the cases, > meaning that having other functions support this wouldn't really work? > I don't particularly like the idea that some built-in functions would > always work but others would work but only some of the time. > > > *** 1.2.3. Do something magical with function argument types > > > > +: doesn't need changes in create function / pg_proc > > -: it's an ugly hack > > Not really a fan of 'ugly hack'. > > > * 3. "just say no" to the spec > > > > e.g. add new functions like lead_ignore_nulls(), or add extra boolean > > args to lead() etc. telling them to skip nulls > > > > +: simple > > -: doesn't conform to spec > > -: using extra args isn't quite the right semantics > > Ugh, no thank you. > > Thanks! > > Stephen > diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 28035f1635..3d73c96891 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -15702,7 +15702,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; lag(value anyelement [, offset integer - [, default anyelement ]]) + [, default anyelement ]]) [null_treatment] @@ -15731,7 +15731,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; lead(value anyelement [, offset integer - [, default anyelement ]]) + [, default anyelement ]]) [null_treatment] @@ -15757,7 +15757,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; first_value - first_value(value any) +
Re: [PATCH] distinct aggregates within a window function WIP
I have currently suspended development of this patch, based on it's review, but I will continue development of the other Oliver Ford's work about adding support of respect/ignore nulls for lag(),lead(),first_value(),last_value() and nth_value() and from first/last for nth_value() patch, but I am not sure how to proceed with it's implementation and any feedback will be very helpful. I have dropped support of from first/last for nth_value(), but also I reimplemented it in a different way, by using negative number for the position argument, to be able to get the same frame in exact reverse order. After that patch becomes much more simple and major concerns about precedence hack has gone, but maybe it can be additionally simplified. I have not renamed special bool type "ignorenulls", because it is probably not acceptable way for calling extra version of window functions (but it makes things very easy and it can reuse frames), but I removed the other special bool type "fromlast". Attached file is for PostgreSQL 13 (master git branch) and I will add it now to a March commit fest, to be able to track changes. Everything works and patch is in very good shape, all tests are passed and also, I use it from some time for SQL analysis purposes (because ignore nulls is one of the most needed feature in OLAP/BI area and Oracle, Amazon Redshift, even Informix have it). After patch review and suggestions about what to do with special bool type and unreserved keywords, I will reimplement it, if needed. На пн, 13.01.2020 г. в 18:19 Vik Fearing написа: > On 13/01/2020 15:19, Tom Lane wrote: > > Krasiyan Andreev writes: > >> I want to propose to you an old patch for Postgres 11, off-site > developed > >> by Oliver Ford, > >> but I have permission from him to publish it and to continue it's > >> development, > >> that allow distinct aggregates, like select sum(distinct nums) within a > >> window function. > > I started to respond by asking whether that's well-defined, but > > reading down further I see that that's not actually what the feature > > is: what it is is attaching DISTINCT to a window function itself. > > I'd still ask whether it's well-defined though, or even minimally > > sensible. Window functions are generally supposed to produce one > > row per input row --- how does that square with the implicit row > > merging of DISTINCT? They're also typically row-order-sensitive > > --- how does that work with DISTINCT? > > > It's a little strange because the spec says: > > > > If the window ordering clause or the window framing clause of the window > structure descriptor that describes the > is present, then no simply contained in function> shall specify DISTINCT or . > > > > So it seems to be well defined if all you have is a partition. > > > But then it also says: > > > > DENSE_RANK() OVER WNS is equivalent to the : > COUNT (DISTINCT ROW ( VE 1 , ..., VE N ) ) > OVER (WNS1 RANGE UNBOUNDED PRECEDING) > > > > And that kind of looks like a framing clause there. > > > > Also, to the extent that > > this is sensible, can't you get the same results already today > > with appropriate use of window framing options? > > > I don't see how. > > > I have sometimes wanted this feature so I am +1 on us getting at least a > minimal form of it. > > -- > > Vik > > diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 28035f1635..3d73c96891 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -15702,7 +15702,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; lag(value anyelement [, offset integer - [, default anyelement ]]) + [, default anyelement ]]) [null_treatment] @@ -15731,7 +15731,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; lead(value anyelement [, offset integer - [, default anyelement ]]) + [, default anyelement ]]) [null_treatment] @@ -15757,7 +15757,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; first_value - first_value(value any) + first_value(value any) [null_treatment] same type as value @@ -15773,7 +15773,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; last_value - last_value(value any) + last_value(value any) [null_treatment] same type as value @@ -15790,7 +15790,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; nth_value - nth_value(value any, nth in
[PATCH] respect/ignore nulls for lag,lead,first_value,last_value and nth_value and from first/last for nth_value
Hi hackers, I want to continue development of Oliver Ford's respect/ignore nulls for lag,lead,first_value,last_value and nth_value and from first/last for nth_value patch, but I am not sure how to proceed with it and any feedback will be very useful. I have dropped support of from first/last for nth_value(), but also I reimplemented it in a different way, by using negative number for the position argument, to be able to get the same frame in exact reverse order. After that patch becomes much more simple and major concerns about precedence hack has gone, but maybe it can be additionally simplified. I have not renamed special bool type "ignorenulls", because it is probably not acceptable way for calling extra version of window functions (because it makes things very easy, and it can reuse frames), but I removed the other special bool type "fromlast". So, that is the major question, can someone give me an better idea or example that I can use, for something, that can be more acceptable as implementation and I will try to do it in a such way. Attached file is for PostgreSQL 13 (master git branch) and I will add it now to a March commit fest, to be able to track changes. Everything works and patch is in very good shape, make check is passed and also, I use it from some time for SQL analysis purposes (because ignore nulls is one of the most needed feature in OLAP/BI area and Oracle, Amazon Redshift and Informix have it). After patch review and suggestions about what to do with special bool type and unreserved keywords, I will reimplement it, if needed. diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 28035f1635..3d73c96891 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -15702,7 +15702,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; lag(value anyelement [, offset integer - [, default anyelement ]]) + [, default anyelement ]]) [null_treatment] @@ -15731,7 +15731,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; lead(value anyelement [, offset integer - [, default anyelement ]]) + [, default anyelement ]]) [null_treatment] @@ -15757,7 +15757,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; first_value - first_value(value any) + first_value(value any) [null_treatment] same type as value @@ -15773,7 +15773,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; last_value - last_value(value any) + last_value(value any) [null_treatment] same type as value @@ -15790,7 +15790,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; nth_value - nth_value(value any, nth integer) + nth_value(value any, nth integer) [null_treatment] @@ -15806,6 +15806,16 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; + + In , null_treatment is one of: + + RESPECT NULLS + IGNORE NULLS + + RESPECT NULLS specifies the default behavior to include nulls in the result. + IGNORE NULLS ignores any null values when determining a result. + + All of the functions listed in depend on the sort ordering @@ -15843,17 +15853,11 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; -The SQL standard defines a RESPECT NULLS or -IGNORE NULLS option for lead, lag, -first_value, last_value, and -nth_value. This is not implemented in -PostgreSQL: the behavior is always the -same as the standard's default, namely RESPECT NULLS. -Likewise, the standard's FROM FIRST or FROM LAST -option for nth_value is not implemented: only the -default FROM FIRST behavior is supported. (You can achieve -the result of FROM LAST by reversing the ORDER BY -ordering.) +The SQL standard defines a FROM FIRST or FROM LAST +option for nth_value. This is not implemented in PostgreSQL: +only the default FROM FIRST behavior is supported. +(You can achieve the result of FROM LAST by using negative number for the position argument, +as is done in many languages to indicate a FROM END index.) diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 9f840ddfd2..a355e379e7 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -508,9 +508,9 @@ T612 Advanced OLAP operations YES T613 Sampling YES T614 NTILE function YES T615 LEAD and LAG functions YES -T616 Null treatment option for LEAD and LAG functions NO +T616 Null treatment option for LEAD and LAG functions YES T617 FIRST_VALUE and LAST_VALUE function YES -T618 NTH_VALUE
Re: [PATCH] distinct aggregates within a window function WIP
I understand yours note about datatype-specific operations, so I need to think more generic about it. About yours additional note, I think that it is not possible to get easy the same result with appropriate use of window framing options, because "exclude ties" will not exclude "current row" itself, only peers of it. So, that is the only difference and reason of DISTINCT aggregate. Maybe, if we can specify at the same time to "exclude ties" and "exclude current row" itself, there will not be need of DISTINCT, but right now I think that nor "exclude ties" nor "exclude groups" or "exclude current row", can specify it, because they can't be nested or used at the same time. На пн, 13.01.2020 г. в 16:19 Tom Lane написа: > Krasiyan Andreev writes: > > I want to propose to you an old patch for Postgres 11, off-site developed > > by Oliver Ford, > > but I have permission from him to publish it and to continue it's > > development, > > that allow distinct aggregates, like select sum(distinct nums) within a > > window function. > > I started to respond by asking whether that's well-defined, but > reading down further I see that that's not actually what the feature > is: what it is is attaching DISTINCT to a window function itself. > I'd still ask whether it's well-defined though, or even minimally > sensible. Window functions are generally supposed to produce one > row per input row --- how does that square with the implicit row > merging of DISTINCT? They're also typically row-order-sensitive > --- how does that work with DISTINCT? Also, to the extent that > this is sensible, can't you get the same results already today > with appropriate use of window framing options? > > > It's a WIP, because it doesn't have tests yet (I will add them later) and > > also, it works for a int, float, and numeric types, > > As a rule of thumb, operations like this should not be coded to be > datatype-specific. We threw out some features in the original window > function patch until they could be rewritten to not be limited to a > hard-coded set of data types (cf commit 0a459cec9), and I don't see > why we'd apply a lesser standard here. Certainly DISTINCT for > aggregates has no such limitation. > > regards, tom lane >
[PATCH] distinct aggregates within a window function WIP
Hi hackers,
I want to propose to you an old patch for Postgres 11, off-site developed
by Oliver Ford,
but I have permission from him to publish it and to continue it's
development,
that allow distinct aggregates, like select sum(distinct nums) within a
window function.
I have rebased it for current git master branch and have made necessary
changes to it to work with Postgres 13devel.
It's a WIP, because it doesn't have tests yet (I will add them later) and
also, it works for a int, float, and numeric types,
but probably distinct check can be rewritten for possible performance
improvement,
with storing the distinct elements in a hash table which should give a
performance improvement.
If you find the implementation of patch acceptable from committers
perspective,
I will answer to all yours design and review notes and will try to go ahead
with it,
also, I will add this patch to the March commit fest.
For example usage of a patch, if you have time series data, with current
Postgres you will get an error:
postgres=# CREATE TABLE t_demo AS
postgres-# SELECT ordinality, day, date_part('week', day) AS week
postgres-# FROMgenerate_series('2020-01-02', '2020-01-15', '1
day'::interval)
postgres-# WITH ORDINALITY AS day;
SELECT 14
postgres=# SELECT * FROM t_demo;
ordinality | day | week
++--
1 | 2020-01-02 00:00:00+02 |1
2 | 2020-01-03 00:00:00+02 |1
3 | 2020-01-04 00:00:00+02 |1
4 | 2020-01-05 00:00:00+02 |1
5 | 2020-01-06 00:00:00+02 |2
6 | 2020-01-07 00:00:00+02 |2
7 | 2020-01-08 00:00:00+02 |2
8 | 2020-01-09 00:00:00+02 |2
9 | 2020-01-10 00:00:00+02 |2
10 | 2020-01-11 00:00:00+02 |2
11 | 2020-01-12 00:00:00+02 |2
12 | 2020-01-13 00:00:00+02 |3
13 | 2020-01-14 00:00:00+02 |3
14 | 2020-01-15 00:00:00+02 |3
(14 rows)
postgres=# SELECT *,
postgres-# array_agg(DISTINCT week) OVER (ORDER BY day ROWS
postgres(# BETWEEN 2 PRECEDING AND 2
FOLLOWING)
postgres-# FROMt_demo;
ERROR: DISTINCT is not implemented for window functions
LINE 2: array_agg(DISTINCT week) OVER (ORDER BY day ROWS
^
So you will need to write something like this:
postgres=# SELECT *, (SELECT array_agg(DISTINCT unnest) FROM unnest(x)) AS
b
postgres-# FROM
postgres-# (
postgres(# SELECT *,
postgres(# array_agg(week) OVER (ORDER BY day ROWS
postgres(# BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS x
postgres(# FROMt_demo
postgres(# ) AS a;
ordinality | day | week | x | b
++--+-+---
1 | 2020-01-02 00:00:00+02 |1 | {1,1,1} | {1}
2 | 2020-01-03 00:00:00+02 |1 | {1,1,1,1} | {1}
3 | 2020-01-04 00:00:00+02 |1 | {1,1,1,1,2} | {1,2}
4 | 2020-01-05 00:00:00+02 |1 | {1,1,1,2,2} | {1,2}
5 | 2020-01-06 00:00:00+02 |2 | {1,1,2,2,2} | {1,2}
6 | 2020-01-07 00:00:00+02 |2 | {1,2,2,2,2} | {1,2}
7 | 2020-01-08 00:00:00+02 |2 | {2,2,2,2,2} | {2}
8 | 2020-01-09 00:00:00+02 |2 | {2,2,2,2,2} | {2}
9 | 2020-01-10 00:00:00+02 |2 | {2,2,2,2,2} | {2}
10 | 2020-01-11 00:00:00+02 |2 | {2,2,2,2,3} | {2,3}
11 | 2020-01-12 00:00:00+02 |2 | {2,2,2,3,3} | {2,3}
12 | 2020-01-13 00:00:00+02 |3 | {2,2,3,3,3} | {2,3}
13 | 2020-01-14 00:00:00+02 |3 | {2,3,3,3} | {2,3}
14 | 2020-01-15 00:00:00+02 |3 | {3,3,3} | {3}
(14 rows)
With attached version, you will get the desired results:
postgres=# SELECT *,
postgres-# array_agg(DISTINCT week) OVER (ORDER BY day ROWS
postgres(# BETWEEN 2 PRECEDING AND 2
FOLLOWING)
postgres-# FROMt_demo;
ordinality | day | week | array_agg
++--+---
1 | 2020-01-02 00:00:00+02 |1 | {1}
2 | 2020-01-03 00:00:00+02 |1 | {1}
3 | 2020-01-04 00:00:00+02 |1 | {1,2}
4 | 2020-01-05 00:00:00+02 |1 | {1,2}
5 | 2020-01-06 00:00:00+02 |2 | {1,2}
6 | 2020-01-07 00:00:00+02 |2 | {1,2}
7 | 2020-01-08 00:00:00+02 |2 | {2}
8 | 2020-01-09 00:00:00+02 |2 | {2}
9 | 2020-01-10 00:00:00+02 |2 | {2}
10 | 2020-01-11 00:00:00+02 |2 | {2,3}
11 | 2020-01-12 00:00:00+02 |2 | {2,3}
12 | 2020-01-13 00:00:00+02 |3 | {2,3}
13 | 2020-01-14 00:00:00+02 |3 | {2,3}
14 | 2020-01-15 00:00:00+02 |3 | {3}
(14 rows)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index
Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options
Hi, Patch applies and compiles, all included tests and building of the docs pass. I am using last version from more than two months ago in production environment with real data and I didn't find any bugs, so I'm marking this patch as ready for committer in the commitfest app. На сб, 28.07.2018 г. в 22:00 ч. David Fetter написа: > On Fri, Jul 13, 2018 at 01:52:00PM +0100, Oliver Ford wrote: > > Adds the options RESPECT/IGNORE NULLS (null treatment clause) and FROM > > FIRST/LAST to the non-aggregate window functions. > > Please find attached an updated version for OID drift. > > Best, > David. > -- > David Fetter http://fetter.org/ > Phone: +1 415 235 3778 > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate >
