Skip to content

Commit 26ce4e8

Browse files
tglsfdctglsfdc
committed
Add a WINDOW attribute to CREATE FUNCTION, and teach pg_dump about it,
so that user-defined window functions are possible. For the moment you'll have to write them in C, for lack of any interface to the WindowObject API in the available PLs, but it's better than no support at all. There was some debate about the best syntax for this. I ended up choosing the "it's an attribute" position --- the other approach will inevitably be more work, and the likely market for user-defined window functions is probably too small to justify it.
1 parent 8e8854d commit 26ce4e8

File tree

9 files changed

+70
-18
lines changed

9 files changed

+70
-18
lines changed

‎doc/src/sgml/ref/create_function.sgml

Lines changed: 16 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.83 2008/12/18 18:20:33 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.84 2008/12/31 02:25:03 tgl Exp $
33
-->
44

55
<refentry id="SQL-CREATEFUNCTION">
@@ -25,6 +25,7 @@ CREATE [ OR REPLACE ] FUNCTION
2525
[ RETURNS <replaceable class="parameter">rettype</replaceable>
2626
| RETURNS TABLE ( <replaceable class="parameter">colname</replaceable> <replaceable class="parameter">coltype</replaceable> [, ...] ) ]
2727
{ LANGUAGE <replaceable class="parameter">langname</replaceable>
28+
| WINDOW
2829
| IMMUTABLE | STABLE | VOLATILE
2930
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
3031
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
@@ -241,6 +242,20 @@ CREATE [ OR REPLACE ] FUNCTION
241242
</listitem>
242243
</varlistentry>
243244

245+
<varlistentry>
246+
<term><literal>WINDOW</literal></term>
247+
248+
<listitem>
249+
<para>
250+
<literal>WINDOW</literal> indicates that the function is a
251+
<firstterm>window function</> rather than a plain function.
252+
This is currently only useful for functions written in C.
253+
The <literal>WINDOW</> attribute cannot be changed when
254+
replacing an existing function definition.
255+
</para>
256+
</listitem>
257+
</varlistentry>
258+
244259
<varlistentry>
245260
<term><literal>IMMUTABLE</literal></term>
246261
<term><literal>STABLE</literal></term>

‎src/backend/catalog/pg_aggregate.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/pg_aggregate.c,v 1.99 2008/12/18 18:20:33 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/pg_aggregate.c,v 1.100 2008/12/31 02:25:03 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -217,6 +217,7 @@ AggregateCreate(const char *aggName,
217217
"aggregate_dummy", /* placeholder proc */
218218
NULL, /* probin */
219219
true, /* isAgg */
220+
false, /* isWindowFunc */
220221
false, /* security invoker (currently not
221222
* definable for agg) */
222223
false, /* isStrict (not needed for agg) */

‎src/backend/catalog/pg_proc.c

Lines changed: 3 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/pg_proc.c,v 1.158 2008/12/28 18:53:54 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/pg_proc.c,v 1.159 2008/12/31 02:25:03 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -53,7 +53,7 @@ static bool match_prosrc_to_literal(const char *prosrc, const char *literal,
5353
*
5454
* Note: allParameterTypes, parameterModes, parameterNames, and proconfig
5555
* are either arrays of the proper types or NULL. We declare them Datum,
56-
* not "ArrayType *", to avoid importing array.h into pg_proc.h.
56+
* not "ArrayType *", to avoid importing array.h into pg_proc_fn.h.
5757
* ----------------------------------------------------------------
5858
*/
5959
Oid
@@ -67,6 +67,7 @@ ProcedureCreate(const char *procedureName,
6767
const char *prosrc,
6868
const char *probin,
6969
bool isAgg,
70+
bool isWindowFunc,
7071
bool security_definer,
7172
bool isStrict,
7273
char volatility,
@@ -80,8 +81,6 @@ ProcedureCreate(const char *procedureName,
8081
float4 prorows)
8182
{
8283
Oid retval;
83-
/* XXX we don't currently have a way to make new window functions */
84-
bool isWindowFunc = false;
8584
int parameterCount;
8685
int allParamCount;
8786
Oid *allParams;

‎src/backend/commands/functioncmds.c

Lines changed: 19 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/commands/functioncmds.c,v 1.104 2008/12/28 18:53:55 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/commands/functioncmds.c,v 1.105 2008/12/31 02:25:03 tgl Exp $
1414
*
1515
* DESCRIPTION
1616
* These routines take the parse tree and pick out the
@@ -503,6 +503,7 @@ static void
503503
compute_attributes_sql_style(List *options,
504504
List **as,
505505
char **language,
506+
bool *windowfunc_p,
506507
char *volatility_p,
507508
bool *strict_p,
508509
bool *security_definer,
@@ -513,6 +514,7 @@ compute_attributes_sql_style(List *options,
513514
ListCell *option;
514515
DefElem *as_item = NULL;
515516
DefElem *language_item = NULL;
517+
DefElem *windowfunc_item = NULL;
516518
DefElem *volatility_item = NULL;
517519
DefElem *strict_item = NULL;
518520
DefElem *security_item = NULL;
@@ -540,6 +542,14 @@ compute_attributes_sql_style(List *options,
540542
errmsg("conflicting or redundant options")));
541543
language_item = defel;
542544
}
545+
else if (strcmp(defel->defname, "window") == 0)
546+
{
547+
if (windowfunc_item)
548+
ereport(ERROR,
549+
(errcode(ERRCODE_SYNTAX_ERROR),
550+
errmsg("conflicting or redundant options")));
551+
windowfunc_item = defel;
552+
}
543553
else if (compute_common_attribute(defel,
544554
&volatility_item,
545555
&strict_item,
@@ -578,6 +588,8 @@ compute_attributes_sql_style(List *options,
578588
}
579589

580590
/* process optional items */
591+
if (windowfunc_item)
592+
*windowfunc_p = intVal(windowfunc_item->arg);
581593
if (volatility_item)
582594
*volatility_p = interpret_func_volatility(volatility_item);
583595
if (strict_item)
@@ -735,7 +747,8 @@ CreateFunction(CreateFunctionStmt *stmt, const char *queryString)
735747
ArrayType *parameterNames;
736748
List *parameterDefaults;
737749
Oid requiredResultType;
738-
bool isStrict,
750+
bool isWindowFunc,
751+
isStrict,
739752
security;
740753
char volatility;
741754
ArrayType *proconfig;
@@ -756,6 +769,7 @@ CreateFunction(CreateFunctionStmt *stmt, const char *queryString)
756769
get_namespace_name(namespaceId));
757770

758771
/* default attributes */
772+
isWindowFunc = false;
759773
isStrict = false;
760774
security = false;
761775
volatility = PROVOLATILE_VOLATILE;
@@ -766,7 +780,8 @@ CreateFunction(CreateFunctionStmt *stmt, const char *queryString)
766780
/* override attributes from explicit list */
767781
compute_attributes_sql_style(stmt->options,
768782
&as_clause, &language,
769-
&volatility, &isStrict, &security,
783+
&isWindowFunc, &volatility,
784+
&isStrict, &security,
770785
&proconfig, &procost, &prorows);
771786

772787
/* Convert language name to canonical case */
@@ -892,6 +907,7 @@ CreateFunction(CreateFunctionStmt *stmt, const char *queryString)
892907
prosrc_str, /* converted to text later */
893908
probin_str, /* converted to text later */
894909
false, /* not an aggregate */
910+
isWindowFunc,
895911
security,
896912
isStrict,
897913
volatility,

‎src/backend/commands/proclang.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1994, Regents of the University of California
88
*
99
* IDENTIFICATION
10-
* $PostgreSQL: pgsql/src/backend/commands/proclang.c,v 1.82 2008/12/18 18:20:33 tgl Exp $
10+
* $PostgreSQL: pgsql/src/backend/commands/proclang.c,v 1.83 2008/12/31 02:25:03 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -140,6 +140,7 @@ CreateProceduralLanguage(CreatePLangStmt *stmt)
140140
pltemplate->tmplhandler,
141141
pltemplate->tmpllibrary,
142142
false, /* isAgg */
143+
false, /* isWindowFunc */
143144
false, /* security_definer */
144145
false, /* isStrict */
145146
PROVOLATILE_VOLATILE,
@@ -174,6 +175,7 @@ CreateProceduralLanguage(CreatePLangStmt *stmt)
174175
pltemplate->tmplvalidator,
175176
pltemplate->tmpllibrary,
176177
false, /* isAgg */
178+
false, /* isWindowFunc */
177179
false, /* security_definer */
178180
false, /* isStrict */
179181
PROVOLATILE_VOLATILE,

‎src/backend/parser/gram.y

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.649 2008/12/31 00:08:36 tgl Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.650 2008/12/31 02:25:04 tgl Exp $
1515
*
1616
* HISTORY
1717
* AUTHOR DATE MAJOR EVENT
@@ -4801,6 +4801,10 @@ createfunc_opt_item:
48014801
{
48024802
$$ = makeDefElem("language", (Node *)makeString($2));
48034803
}
4804+
| WINDOW
4805+
{
4806+
$$ = makeDefElem("window", (Node *)makeInteger(TRUE));
4807+
}
48044808
| common_func_opt_item
48054809
{
48064810
$$ = $1;

‎src/backend/utils/adt/ruleutils.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.292 2008/12/31 00:08:37 tgl Exp $
12+
* $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.293 2008/12/31 02:25:04 tgl Exp $
1313
*
1414
*-------------------------------------------------------------------------
1515
*/
@@ -1466,6 +1466,8 @@ pg_get_functiondef(PG_FUNCTION_ARGS)
14661466
/* Emit some miscellaneous options on one line */
14671467
oldlen = buf.len;
14681468

1469+
if (proc->proiswindow)
1470+
appendStringInfoString(&buf, " WINDOW");
14691471
switch (proc->provolatile)
14701472
{
14711473
case PROVOLATILE_IMMUTABLE:

‎src/bin/pg_dump/pg_dump.c

Lines changed: 17 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@
1212
* by PostgreSQL
1313
*
1414
* IDENTIFICATION
15-
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.509 2008/12/19 16:25:18 petere Exp $
15+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.510 2008/12/31 02:25:05 tgl Exp $
1616
*
1717
*-------------------------------------------------------------------------
1818
*/
@@ -6916,6 +6916,7 @@ dumpFunc(Archive *fout, FuncInfo *finfo)
69166916
char *proallargtypes;
69176917
char *proargmodes;
69186918
char *proargnames;
6919+
char *proiswindow;
69196920
char *provolatile;
69206921
char *proisstrict;
69216922
char *prosecdef;
@@ -6956,7 +6957,7 @@ dumpFunc(Archive *fout, FuncInfo *finfo)
69566957
"pg_catalog.pg_get_function_arguments(oid) as funcargs, "
69576958
"pg_catalog.pg_get_function_identity_arguments(oid) as funciargs, "
69586959
"pg_catalog.pg_get_function_result(oid) as funcresult, "
6959-
"provolatile, proisstrict, prosecdef, "
6960+
"proiswindow, provolatile, proisstrict, prosecdef, "
69606961
"proconfig, procost, prorows, "
69616962
"(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
69626963
"FROM pg_catalog.pg_proc "
@@ -6968,6 +6969,7 @@ dumpFunc(Archive *fout, FuncInfo *finfo)
69686969
appendPQExpBuffer(query,
69696970
"SELECT proretset, prosrc, probin, "
69706971
"proallargtypes, proargmodes, proargnames, "
6972+
"false as proiswindow, "
69716973
"provolatile, proisstrict, prosecdef, "
69726974
"proconfig, procost, prorows, "
69736975
"(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
@@ -6980,6 +6982,7 @@ dumpFunc(Archive *fout, FuncInfo *finfo)
69806982
appendPQExpBuffer(query,
69816983
"SELECT proretset, prosrc, probin, "
69826984
"proallargtypes, proargmodes, proargnames, "
6985+
"false as proiswindow, "
69836986
"provolatile, proisstrict, prosecdef, "
69846987
"null as proconfig, 0 as procost, 0 as prorows, "
69856988
"(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
@@ -6994,6 +6997,7 @@ dumpFunc(Archive *fout, FuncInfo *finfo)
69946997
"null as proallargtypes, "
69956998
"null as proargmodes, "
69966999
"proargnames, "
7000+
"false as proiswindow, "
69977001
"provolatile, proisstrict, prosecdef, "
69987002
"null as proconfig, 0 as procost, 0 as prorows, "
69997003
"(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
@@ -7008,6 +7012,7 @@ dumpFunc(Archive *fout, FuncInfo *finfo)
70087012
"null as proallargtypes, "
70097013
"null as proargmodes, "
70107014
"null as proargnames, "
7015+
"false as proiswindow, "
70117016
"provolatile, proisstrict, prosecdef, "
70127017
"null as proconfig, 0 as procost, 0 as prorows, "
70137018
"(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) as lanname "
@@ -7022,9 +7027,10 @@ dumpFunc(Archive *fout, FuncInfo *finfo)
70227027
"null as proallargtypes, "
70237028
"null as proargmodes, "
70247029
"null as proargnames, "
7030+
"false as proiswindow, "
70257031
"case when proiscachable then 'i' else 'v' end as provolatile, "
70267032
"proisstrict, "
7027-
"'f'::boolean as prosecdef, "
7033+
"false as prosecdef, "
70287034
"null as proconfig, 0 as procost, 0 as prorows, "
70297035
"(SELECT lanname FROM pg_language WHERE oid = prolang) as lanname "
70307036
"FROM pg_proc "
@@ -7038,9 +7044,10 @@ dumpFunc(Archive *fout, FuncInfo *finfo)
70387044
"null as proallargtypes, "
70397045
"null as proargmodes, "
70407046
"null as proargnames, "
7047+
"false as proiswindow, "
70417048
"case when proiscachable then 'i' else 'v' end as provolatile, "
7042-
"'f'::boolean as proisstrict, "
7043-
"'f'::boolean as prosecdef, "
7049+
"false as proisstrict, "
7050+
"false as prosecdef, "
70447051
"null as proconfig, 0 as procost, 0 as prorows, "
70457052
"(SELECT lanname FROM pg_language WHERE oid = prolang) as lanname "
70467053
"FROM pg_proc "
@@ -7077,6 +7084,7 @@ dumpFunc(Archive *fout, FuncInfo *finfo)
70777084
proargnames = PQgetvalue(res, 0, PQfnumber(res, "proargnames"));
70787085
funcargs = funciargs = funcresult = NULL;
70797086
}
7087+
proiswindow = PQgetvalue(res, 0, PQfnumber(res, "proiswindow"));
70807088
provolatile = PQgetvalue(res, 0, PQfnumber(res, "provolatile"));
70817089
proisstrict = PQgetvalue(res, 0, PQfnumber(res, "proisstrict"));
70827090
prosecdef = PQgetvalue(res, 0, PQfnumber(res, "prosecdef"));
@@ -7217,6 +7225,10 @@ dumpFunc(Archive *fout, FuncInfo *finfo)
72177225
}
72187226

72197227
appendPQExpBuffer(q, "\n LANGUAGE %s", fmtId(lanname));
7228+
7229+
if (proiswindow[0] == 't')
7230+
appendPQExpBuffer(q, " WINDOW");
7231+
72207232
if (provolatile[0] != PROVOLATILE_VOLATILE)
72217233
{
72227234
if (provolatile[0] == PROVOLATILE_IMMUTABLE)

‎src/include/catalog/pg_proc_fn.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2008, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc_fn.h,v 1.4 2008/12/18 18:20:35 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc_fn.h,v 1.5 2008/12/31 02:25:06 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -26,6 +26,7 @@ extern Oid ProcedureCreate(const char *procedureName,
2626
const char *prosrc,
2727
const char *probin,
2828
bool isAgg,
29+
bool isWindowFunc,
2930
bool security_definer,
3031
bool isStrict,
3132
char volatility,

0 commit comments

Comments
 (0)