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

Commitf2c6573

Browse files
committed
Add CREATE OR REPLACE FUNCTION syntax to allow replacing a function
definition without changing the function's OID, thereby not breakingrules, views, triggers, etc that depend on it. From Gavin Sherry.
1 parentf24fe14 commitf2c6573

File tree

12 files changed

+130
-68
lines changed

12 files changed

+130
-68
lines changed

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

Lines changed: 28 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_function.sgml,v 1.26 2001/09/03 12:57:49 petere Exp $
2+
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_function.sgml,v 1.27 2001/10/02 21:39:35 tgl Exp $
33
-->
44

55
<refentry id="SQL-CREATEFUNCTION">
@@ -15,12 +15,12 @@ $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_function.sgml,v 1.26 2001/09/03
1515

1616
<refsynopsisdiv>
1717
<synopsis>
18-
CREATE FUNCTION <replaceable class="parameter">name</replaceable> ( [ <replaceable class="parameter">argtype</replaceable> [, ...] ] )
18+
CREATE[ OR REPLACE ]FUNCTION <replaceable class="parameter">name</replaceable> ( [ <replaceable class="parameter">argtype</replaceable> [, ...] ] )
1919
RETURNS <replaceable class="parameter">rettype</replaceable>
2020
AS '<replaceable class="parameter">definition</replaceable>'
2121
LANGUAGE <replaceable class="parameter">langname</replaceable>
2222
[ WITH ( <replaceable class="parameter">attribute</replaceable> [, ...] ) ]
23-
CREATE FUNCTION <replaceable class="parameter">name</replaceable> ( [ <replaceable class="parameter">argtype</replaceable> [, ...] ] )
23+
CREATE[ OR REPLACE ]FUNCTION <replaceable class="parameter">name</replaceable> ( [ <replaceable class="parameter">argtype</replaceable> [, ...] ] )
2424
RETURNS <replaceable class="parameter">rettype</replaceable>
2525
AS '<replaceable class="parameter">obj_file</replaceable>', '<replaceable class="parameter">link_symbol</replaceable>'
2626
LANGUAGE <replaceable class="parameter">langname</replaceable>
@@ -33,6 +33,8 @@ CREATE FUNCTION <replaceable class="parameter">name</replaceable> ( [ <replaceab
3333

3434
<para>
3535
<command>CREATE FUNCTION</command> defines a new function.
36+
<command>CREATE OR REPLACE FUNCTION</command> will either create
37+
a new function, or replace an existing definition.
3638

3739
<variablelist>
3840
<title>Parameters</title>
@@ -202,11 +204,6 @@ CREATE FUNCTION <replaceable class="parameter">name</replaceable> ( [ <replaceab
202204
for further information on writing external functions.
203205
</para>
204206

205-
<para>
206-
Use <command>DROP FUNCTION</command>
207-
to remove user-defined functions.
208-
</para>
209-
210207
<para>
211208
The full <acronym>SQL</acronym> type syntax is allowed for
212209
input arguments and return value. However, some details of the
@@ -250,6 +247,29 @@ CREATE FUNCTION <replaceable class="parameter">name</replaceable> ( [ <replaceab
250247
linkend="sql-load"> command.
251248
</para>
252249

250+
<para>
251+
Use <command>DROP FUNCTION</command>
252+
to remove user-defined functions.
253+
</para>
254+
255+
<para>
256+
To update the definition of an existing function, use
257+
<command>CREATE OR REPLACE FUNCTION</command>. Note that it is
258+
not possible to change the name or argument types of a function
259+
this way (if you tried, you'd just be creating a new, distinct
260+
function). Also, <command>CREATE OR REPLACE FUNCTION</command>
261+
will not let you change the return type of an existing function.
262+
To do that, you must drop and re-create the function.
263+
</para>
264+
265+
<para>
266+
If you drop and then re-create a function, the new function is not
267+
the same entity as the old; you will break existing rules, views,
268+
triggers, etc that referred to the old function. Use
269+
<command>CREATE OR REPLACE FUNCTION</command> to change a function
270+
definition without breaking objects that refer to the function.
271+
</para>
272+
253273
</refsect1>
254274

255275

‎src/backend/catalog/pg_proc.c‎

Lines changed: 50 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/catalog/pg_proc.c,v 1.59 2001/09/08 01:10:19 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/catalog/pg_proc.c,v 1.60 2001/10/02 21:39:35 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -41,6 +41,7 @@ static void checkretval(Oid rettype, List *queryTreeList);
4141
*/
4242
Oid
4343
ProcedureCreate(char*procedureName,
44+
boolreplace,
4445
boolreturnsSet,
4546
char*returnTypeName,
4647
char*languageName,
@@ -58,10 +59,12 @@ ProcedureCreate(char *procedureName,
5859
inti;
5960
Relationrel;
6061
HeapTupletup;
62+
HeapTupleoldtup;
6163
booldefined;
6264
uint16parameterCount;
6365
charnulls[Natts_pg_proc];
6466
Datumvalues[Natts_pg_proc];
67+
charreplaces[Natts_pg_proc];
6568
OidlanguageObjectId;
6669
OidtypeObjectId;
6770
List*x;
@@ -120,15 +123,6 @@ ProcedureCreate(char *procedureName,
120123
typev[parameterCount++]=toid;
121124
}
122125

123-
/* Check for duplicate definition */
124-
if (SearchSysCacheExists(PROCNAME,
125-
PointerGetDatum(procedureName),
126-
UInt16GetDatum(parameterCount),
127-
PointerGetDatum(typev),
128-
0))
129-
elog(ERROR,"function %s already exists with same argument types",
130-
procedureName);
131-
132126
if (languageObjectId==SQLlanguageId)
133127
{
134128

@@ -260,13 +254,14 @@ ProcedureCreate(char *procedureName,
260254
}
261255

262256
/*
263-
* All seems OK; prepare thetuple to be inserted into pg_proc.
257+
* All seems OK; prepare thedata to be inserted into pg_proc.
264258
*/
265259

266260
for (i=0;i<Natts_pg_proc;++i)
267261
{
268262
nulls[i]=' ';
269263
values[i]= (Datum)NULL;
264+
replaces[i]='r';
270265
}
271266

272267
i=0;
@@ -293,14 +288,49 @@ ProcedureCreate(char *procedureName,
293288
CStringGetDatum(probin));
294289

295290
rel=heap_openr(ProcedureRelationName,RowExclusiveLock);
296-
297291
tupDesc=rel->rd_att;
298-
tup=heap_formtuple(tupDesc,
299-
values,
300-
nulls);
301292

302-
heap_insert(rel,tup);
293+
/* Check for pre-existing definition */
294+
oldtup=SearchSysCache(PROCNAME,
295+
PointerGetDatum(procedureName),
296+
UInt16GetDatum(parameterCount),
297+
PointerGetDatum(typev),
298+
0);
303299

300+
if (HeapTupleIsValid(oldtup))
301+
{
302+
/* There is one; okay to replace it? */
303+
Form_pg_procoldproc= (Form_pg_proc)GETSTRUCT(oldtup);
304+
305+
if (!replace)
306+
elog(ERROR,"function %s already exists with same argument types",
307+
procedureName);
308+
if (GetUserId()!=oldproc->proowner&& !superuser())
309+
elog(ERROR,"ProcedureCreate: you do not have permission to replace function %s",
310+
procedureName);
311+
/*
312+
* Not okay to change the return type of the existing proc, since
313+
* existing rules, views, etc may depend on the return type.
314+
*/
315+
if (typeObjectId!=oldproc->prorettype||
316+
returnsSet!=oldproc->proretset)
317+
elog(ERROR,"ProcedureCreate: cannot change return type of existing function."
318+
"\n\tUse DROP FUNCTION first.");
319+
320+
/* Okay, do it... */
321+
tup=heap_modifytuple(oldtup,rel,values,nulls,replaces);
322+
simple_heap_update(rel,&tup->t_self,tup);
323+
324+
ReleaseSysCache(oldtup);
325+
}
326+
else
327+
{
328+
/* Creating a new procedure */
329+
tup=heap_formtuple(tupDesc,values,nulls);
330+
heap_insert(rel,tup);
331+
}
332+
333+
/* Need to update indices for either the insert or update case */
304334
if (RelationGetForm(rel)->relhasindex)
305335
{
306336
Relationidescs[Num_pg_proc_indices];
@@ -309,9 +339,12 @@ ProcedureCreate(char *procedureName,
309339
CatalogIndexInsert(idescs,Num_pg_proc_indices,rel,tup);
310340
CatalogCloseIndices(Num_pg_proc_indices,idescs);
311341
}
312-
heap_close(rel,RowExclusiveLock);
342+
313343
retval=tup->t_data->t_oid;
314344
heap_freetuple(tup);
345+
346+
heap_close(rel,RowExclusiveLock);
347+
315348
returnretval;
316349
}
317350

‎src/backend/commands/define.c‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $Header: /cvsroot/pgsql/src/backend/commands/define.c,v 1.60 2001/09/08 01:10:20 tgl Exp $
13+
* $Header: /cvsroot/pgsql/src/backend/commands/define.c,v 1.61 2001/10/02 21:39:35 tgl Exp $
1414
*
1515
* DESCRIPTION
1616
* The "DefineFoo" routines take the parse tree and pick out the
@@ -324,6 +324,7 @@ CreateFunction(ProcedureStmt *stmt)
324324
* to do so, go ahead and create the function.
325325
*/
326326
ProcedureCreate(stmt->funcname,
327+
stmt->replace,
327328
returnsSet,
328329
prorettype,
329330
languageName,

‎src/backend/nodes/copyfuncs.c‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -15,7 +15,7 @@
1515
* Portions Copyright (c) 1994, Regents of the University of California
1616
*
1717
* IDENTIFICATION
18-
* $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.156 2001/09/18 01:59:06 tgl Exp $
18+
* $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.157 2001/10/02 21:39:35 tgl Exp $
1919
*
2020
*-------------------------------------------------------------------------
2121
*/
@@ -2071,6 +2071,7 @@ _copyProcedureStmt(ProcedureStmt *from)
20712071
{
20722072
ProcedureStmt*newnode=makeNode(ProcedureStmt);
20732073

2074+
newnode->replace=from->replace;
20742075
newnode->funcname=pstrdup(from->funcname);
20752076
Node_Copy(from,newnode,argTypes);
20762077
Node_Copy(from,newnode,returnType);

‎src/backend/nodes/equalfuncs.c‎

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -20,7 +20,7 @@
2020
* Portions Copyright (c) 1994, Regents of the University of California
2121
*
2222
* IDENTIFICATION
23-
* $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.104 2001/09/18 01:59:06 tgl Exp $
23+
* $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.105 2001/10/02 21:39:35 tgl Exp $
2424
*
2525
*-------------------------------------------------------------------------
2626
*/
@@ -941,6 +941,8 @@ _equalIndexStmt(IndexStmt *a, IndexStmt *b)
941941
staticbool
942942
_equalProcedureStmt(ProcedureStmt*a,ProcedureStmt*b)
943943
{
944+
if (a->replace!=b->replace)
945+
return false;
944946
if (!equalstr(a->funcname,b->funcname))
945947
return false;
946948
if (!equal(a->argTypes,b->argTypes))

‎src/backend/parser/gram.y‎

Lines changed: 19 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -11,7 +11,7 @@
1111
*
1212
*
1313
* IDENTIFICATION
14-
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.255 2001/10/01 04:19:18 tgl Exp $
14+
* $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.256 2001/10/02 21:39:35 tgl Exp $
1515
*
1616
* HISTORY
1717
* AUTHORDATEMAJOR EVENT
@@ -151,7 +151,7 @@ static void doNegateFloat(Value *v);
151151
%type<list>createdb_opt_list,createdb_opt_item
152152

153153
%type<ival>opt_lock,lock_type
154-
%type<boolean>opt_force
154+
%type<boolean>opt_force,opt_or_replace
155155

156156
%type<list>user_list
157157

@@ -321,13 +321,13 @@ static void doNegateFloat(Value *v);
321321
VALUES,VARCHAR,VARYING,VIEW,
322322
WHEN,WHERE,WITH,WORK,YEAR_P,ZONE
323323

324-
/* Keywords (inSQL3 reserved words)*/
324+
/* Keywords (inSQL99 reserved words)*/
325325
%tokenCHAIN,CHARACTERISTICS,
326326
DEFERRABLE,DEFERRED,
327327
IMMEDIATE,INITIALLY,INOUT,
328328
OFF,OUT,
329329
PATH_P,PENDANT,
330-
RESTRICT,
330+
REPLACE,RESTRICT,
331331
TRIGGER,
332332
WITHOUT
333333

@@ -2497,33 +2497,32 @@ RecipeStmt: EXECUTE RECIPE recipe_name
24972497
/*****************************************************************************
24982498
*
24992499
*QUERY:
2500-
*define function <fname>
2500+
*create [or replace] function <fname>
25012501
*[(<type-1> { , <type-n>})]
25022502
*returns <type-r>
25032503
*as <filename or code in language as appropriate>
2504-
*language <lang> [with
2505-
*[ arch_pct = <percentage | pre-defined>]
2506-
*[, disk_pct = <percentage | pre-defined>]
2507-
*[, byte_pct = <percentage | pre-defined>]
2508-
*[, perbyte_cpu = <int | pre-defined>]
2509-
*[, percall_cpu = <int | pre-defined>]
2510-
*[, iscachable] ]
2504+
*language <lang> [with parameters]
25112505
*
25122506
*****************************************************************************/
25132507

2514-
ProcedureStmt:CREATEFUNCTIONfunc_namefunc_args
2508+
ProcedureStmt:CREATEopt_or_replaceFUNCTIONfunc_namefunc_args
25152509
RETURNSfunc_returnASfunc_asLANGUAGEColId_or_Sconstopt_with
25162510
{
25172511
ProcedureStmt *n = makeNode(ProcedureStmt);
2518-
n->funcname =$3;
2519-
n->argTypes =$4;
2520-
n->returnType = (Node *)$6;
2521-
n->withClause =$11;
2522-
n->as =$8;
2523-
n->language =$10;
2512+
n->replace =$2;
2513+
n->funcname =$4;
2514+
n->argTypes =$5;
2515+
n->returnType = (Node *)$7;
2516+
n->withClause =$12;
2517+
n->as =$9;
2518+
n->language =$11;
25242519
$$ = (Node *)n;
25252520
};
25262521

2522+
opt_or_replace:ORREPLACE{$$ =TRUE; }
2523+
|/*EMPTY*/{$$ =FALSE; }
2524+
;
2525+
25272526
opt_with:WITHdefinition{$$ =$2; }
25282527
|/*EMPTY*/{$$ = NIL; }
25292528
;
@@ -5682,6 +5681,7 @@ TokenId: ABSOLUTE{ $$ = "absolute"; }
56825681
|REINDEX{$$ ="reindex"; }
56835682
|RELATIVE{$$ ="relative"; }
56845683
|RENAME{$$ ="rename"; }
5684+
|REPLACE{$$ ="replace"; }
56855685
|RESTRICT{$$ ="restrict"; }
56865686
|RETURNS{$$ ="returns"; }
56875687
|REVOKE{$$ ="revoke"; }

‎src/backend/parser/keywords.c‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/parser/keywords.c,v 1.97 2001/08/26 16:56:00 tgl Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/parser/keywords.c,v 1.98 2001/10/02 21:39:35 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -216,6 +216,7 @@ static ScanKeyword ScanKeywords[] = {
216216
{"reindex",REINDEX},
217217
{"relative",RELATIVE},
218218
{"rename",RENAME},
219+
{"replace",REPLACE},
219220
{"reset",RESET},
220221
{"restrict",RESTRICT},
221222
{"returns",RETURNS},

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

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/Attic/sets.c,v 1.38 2001/09/08 01:10:20 tgl Exp $
13+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/Attic/sets.c,v 1.39 2001/10/02 21:39:35 tgl Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -51,6 +51,7 @@ SetDefine(char *querystr, char *typename)
5151
charrepl[Natts_pg_proc];
5252

5353
setoid=ProcedureCreate(procname,/* changed below, after oid known */
54+
false,/* don't replace */
5455
true,/* returnsSet */
5556
typename,/* returnTypeName */
5657
"sql",/* languageName */

‎src/include/catalog/pg_proc.h‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $Id: pg_proc.h,v 1.212 2001/09/28 08:09:13 thomas Exp $
10+
* $Id: pg_proc.h,v 1.213 2001/10/02 21:39:36 tgl Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -2854,6 +2854,7 @@ DESCR("time zone");
28542854
* prototypes for functions pg_proc.c
28552855
*/
28562856
externOidProcedureCreate(char*procedureName,
2857+
boolreplace,
28572858
boolreturnsSet,
28582859
char*returnTypeName,
28592860
char*languageName,

0 commit comments

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