Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit26ce4e8

Browse files
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'llhave to write them in C, for lack of any interface to the WindowObject APIin 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 choosingthe "it's an attribute" position --- the other approach will inevitably bemore work, and the likely market for user-defined window functions isprobably too small to justify it.
1 parent8e8854d commit26ce4e8

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 intopg_proc.h.
56+
* not "ArrayType *", to avoid importing array.h intopg_proc_fn.h.
5757
* ----------------------------------------------------------------
5858
*/
5959
Oid
@@ -67,6 +67,7 @@ ProcedureCreate(const char *procedureName,
6767
constchar*prosrc,
6868
constchar*probin,
6969
boolisAgg,
70+
boolisWindowFunc,
7071
boolsecurity_definer,
7172
boolisStrict,
7273
charvolatility,
@@ -80,8 +81,6 @@ ProcedureCreate(const char *procedureName,
8081
float4prorows)
8182
{
8283
Oidretval;
83-
/* XXX we don't currently have a way to make new window functions */
84-
boolisWindowFunc= false;
8584
intparameterCount;
8685
intallParamCount;
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+
elseif (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
elseif (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
OidrequiredResultType;
738-
boolisStrict,
750+
boolisWindowFunc,
751+
isStrict,
739752
security;
740753
charvolatility;
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/3100:08:36 tgl Exp $
14+
* $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.650 2008/12/3102:25:04 tgl Exp $
1515
*
1616
* HISTORY
1717
* AUTHORDATEMAJOR 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/3100:08:37 tgl Exp $
12+
* $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.293 2008/12/3102: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
casePROVOLATILE_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
constchar*prosrc,
2727
constchar*probin,
2828
boolisAgg,
29+
boolisWindowFunc,
2930
boolsecurity_definer,
3031
boolisStrict,
3132
charvolatility,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp