From f2c657375d894ed197071c91090025d68a6057f7 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 2 Oct 2001 21:39:36 +0000 Subject: [PATCH] Add CREATE OR REPLACE FUNCTION syntax to allow replacing a function definition without changing the function's OID, thereby not breaking rules, views, triggers, etc that depend on it. From Gavin Sherry. --- doc/src/sgml/ref/create_function.sgml | 36 +++++++++++--- src/backend/catalog/pg_proc.c | 67 +++++++++++++++++++------- src/backend/commands/define.c | 3 +- src/backend/nodes/copyfuncs.c | 3 +- src/backend/nodes/equalfuncs.c | 4 +- src/backend/parser/gram.y | 38 +++++++-------- src/backend/parser/keywords.c | 3 +- src/backend/utils/adt/sets.c | 3 +- src/include/catalog/pg_proc.h | 3 +- src/include/nodes/parsenodes.h | 3 +- src/interfaces/ecpg/preproc/keywords.c | 6 ++- src/interfaces/ecpg/preproc/preproc.y | 29 ++++++----- 12 files changed, 130 insertions(+), 68 deletions(-) diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index a24a4252cf..b151f723a7 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -1,5 +1,5 @@ @@ -15,12 +15,12 @@ $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_function.sgml,v 1.26 2001/09/03 -CREATE FUNCTION name ( [ argtype [, ...] ] ) +CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] ) RETURNS rettype AS 'definition' LANGUAGE langname [ WITH ( attribute [, ...] ) ] -CREATE FUNCTION name ( [ argtype [, ...] ] ) +CREATE [ OR REPLACE ] FUNCTION name ( [ argtype [, ...] ] ) RETURNS rettype AS 'obj_file', 'link_symbol' LANGUAGE langname @@ -33,6 +33,8 @@ CREATE FUNCTION name ( [ CREATE FUNCTION defines a new function. + CREATE OR REPLACE FUNCTION will either create + a new function, or replace an existing definition. Parameters @@ -202,11 +204,6 @@ CREATE FUNCTION name ( [ - - Use DROP FUNCTION - to remove user-defined functions. - - The full SQL type syntax is allowed for input arguments and return value. However, some details of the @@ -250,6 +247,29 @@ CREATE FUNCTION name ( [ command. + + Use DROP FUNCTION + to remove user-defined functions. + + + + To update the definition of an existing function, use + CREATE OR REPLACE FUNCTION. Note that it is + not possible to change the name or argument types of a function + this way (if you tried, you'd just be creating a new, distinct + function). Also, CREATE OR REPLACE FUNCTION + will not let you change the return type of an existing function. + To do that, you must drop and re-create the function. + + + + If you drop and then re-create a function, the new function is not + the same entity as the old; you will break existing rules, views, + triggers, etc that referred to the old function. Use + CREATE OR REPLACE FUNCTION to change a function + definition without breaking objects that refer to the function. + + diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c index 68309a9af1..c154762175 100644 --- a/src/backend/catalog/pg_proc.c +++ b/src/backend/catalog/pg_proc.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/catalog/pg_proc.c,v 1.59 2001/09/08 01:10:19 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/catalog/pg_proc.c,v 1.60 2001/10/02 21:39:35 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -41,6 +41,7 @@ static void checkretval(Oid rettype, List *queryTreeList); */ Oid ProcedureCreate(char *procedureName, + bool replace, bool returnsSet, char *returnTypeName, char *languageName, @@ -58,10 +59,12 @@ ProcedureCreate(char *procedureName, int i; Relation rel; HeapTuple tup; + HeapTuple oldtup; bool defined; uint16 parameterCount; char nulls[Natts_pg_proc]; Datum values[Natts_pg_proc]; + char replaces[Natts_pg_proc]; Oid languageObjectId; Oid typeObjectId; List *x; @@ -120,15 +123,6 @@ ProcedureCreate(char *procedureName, typev[parameterCount++] = toid; } - /* Check for duplicate definition */ - if (SearchSysCacheExists(PROCNAME, - PointerGetDatum(procedureName), - UInt16GetDatum(parameterCount), - PointerGetDatum(typev), - 0)) - elog(ERROR, "function %s already exists with same argument types", - procedureName); - if (languageObjectId == SQLlanguageId) { @@ -260,13 +254,14 @@ ProcedureCreate(char *procedureName, } /* - * All seems OK; prepare the tuple to be inserted into pg_proc. + * All seems OK; prepare the data to be inserted into pg_proc. */ for (i = 0; i < Natts_pg_proc; ++i) { nulls[i] = ' '; values[i] = (Datum) NULL; + replaces[i] = 'r'; } i = 0; @@ -293,14 +288,49 @@ ProcedureCreate(char *procedureName, CStringGetDatum(probin)); rel = heap_openr(ProcedureRelationName, RowExclusiveLock); - tupDesc = rel->rd_att; - tup = heap_formtuple(tupDesc, - values, - nulls); - heap_insert(rel, tup); + /* Check for pre-existing definition */ + oldtup = SearchSysCache(PROCNAME, + PointerGetDatum(procedureName), + UInt16GetDatum(parameterCount), + PointerGetDatum(typev), + 0); + if (HeapTupleIsValid(oldtup)) + { + /* There is one; okay to replace it? */ + Form_pg_proc oldproc = (Form_pg_proc) GETSTRUCT(oldtup); + + if (!replace) + elog(ERROR, "function %s already exists with same argument types", + procedureName); + if (GetUserId() != oldproc->proowner && !superuser()) + elog(ERROR, "ProcedureCreate: you do not have permission to replace function %s", + procedureName); + /* + * Not okay to change the return type of the existing proc, since + * existing rules, views, etc may depend on the return type. + */ + if (typeObjectId != oldproc->prorettype || + returnsSet != oldproc->proretset) + elog(ERROR, "ProcedureCreate: cannot change return type of existing function." + "\n\tUse DROP FUNCTION first."); + + /* Okay, do it... */ + tup = heap_modifytuple(oldtup, rel, values, nulls, replaces); + simple_heap_update(rel, &tup->t_self, tup); + + ReleaseSysCache(oldtup); + } + else + { + /* Creating a new procedure */ + tup = heap_formtuple(tupDesc, values, nulls); + heap_insert(rel, tup); + } + + /* Need to update indices for either the insert or update case */ if (RelationGetForm(rel)->relhasindex) { Relation idescs[Num_pg_proc_indices]; @@ -309,9 +339,12 @@ ProcedureCreate(char *procedureName, CatalogIndexInsert(idescs, Num_pg_proc_indices, rel, tup); CatalogCloseIndices(Num_pg_proc_indices, idescs); } - heap_close(rel, RowExclusiveLock); + retval = tup->t_data->t_oid; heap_freetuple(tup); + + heap_close(rel, RowExclusiveLock); + return retval; } diff --git a/src/backend/commands/define.c b/src/backend/commands/define.c index 764cceff70..054046e417 100644 --- a/src/backend/commands/define.c +++ b/src/backend/commands/define.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/commands/define.c,v 1.60 2001/09/08 01:10:20 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/commands/define.c,v 1.61 2001/10/02 21:39:35 tgl Exp $ * * DESCRIPTION * The "DefineFoo" routines take the parse tree and pick out the @@ -324,6 +324,7 @@ CreateFunction(ProcedureStmt *stmt) * to do so, go ahead and create the function. */ ProcedureCreate(stmt->funcname, + stmt->replace, returnsSet, prorettype, languageName, diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 1003bcf35d..5cb918e555 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -15,7 +15,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.156 2001/09/18 01:59:06 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/nodes/copyfuncs.c,v 1.157 2001/10/02 21:39:35 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -2071,6 +2071,7 @@ _copyProcedureStmt(ProcedureStmt *from) { ProcedureStmt *newnode = makeNode(ProcedureStmt); + newnode->replace = from->replace; newnode->funcname = pstrdup(from->funcname); Node_Copy(from, newnode, argTypes); Node_Copy(from, newnode, returnType); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 437cd62ebd..dd2895f711 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -20,7 +20,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.104 2001/09/18 01:59:06 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/nodes/equalfuncs.c,v 1.105 2001/10/02 21:39:35 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -941,6 +941,8 @@ _equalIndexStmt(IndexStmt *a, IndexStmt *b) static bool _equalProcedureStmt(ProcedureStmt *a, ProcedureStmt *b) { + if (a->replace != b->replace) + return false; if (!equalstr(a->funcname, b->funcname)) return false; if (!equal(a->argTypes, b->argTypes)) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 68c28f1d9b..cf99cc5d0a 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -11,7 +11,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.255 2001/10/01 04:19:18 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/gram.y,v 2.256 2001/10/02 21:39:35 tgl Exp $ * * HISTORY * AUTHOR DATE MAJOR EVENT @@ -151,7 +151,7 @@ static void doNegateFloat(Value *v); %type createdb_opt_list, createdb_opt_item %type opt_lock, lock_type -%type opt_force +%type opt_force, opt_or_replace %type user_list @@ -321,13 +321,13 @@ static void doNegateFloat(Value *v); VALUES, VARCHAR, VARYING, VIEW, WHEN, WHERE, WITH, WORK, YEAR_P, ZONE -/* Keywords (in SQL3 reserved words) */ +/* Keywords (in SQL99 reserved words) */ %token CHAIN, CHARACTERISTICS, DEFERRABLE, DEFERRED, IMMEDIATE, INITIALLY, INOUT, OFF, OUT, PATH_P, PENDANT, - RESTRICT, + REPLACE, RESTRICT, TRIGGER, WITHOUT @@ -2497,33 +2497,32 @@ RecipeStmt: EXECUTE RECIPE recipe_name /***************************************************************************** * * QUERY: - * define function + * create [or replace] function * [( { , })] * returns * as - * language [with - * [ arch_pct = ] - * [, disk_pct = ] - * [, byte_pct = ] - * [, perbyte_cpu = ] - * [, percall_cpu = ] - * [, iscachable] ] + * language [with parameters] * *****************************************************************************/ -ProcedureStmt: CREATE FUNCTION func_name func_args +ProcedureStmt: CREATE opt_or_replace FUNCTION func_name func_args RETURNS func_return AS func_as LANGUAGE ColId_or_Sconst opt_with { ProcedureStmt *n = makeNode(ProcedureStmt); - n->funcname = $3; - n->argTypes = $4; - n->returnType = (Node *)$6; - n->withClause = $11; - n->as = $8; - n->language = $10; + n->replace = $2; + n->funcname = $4; + n->argTypes = $5; + n->returnType = (Node *) $7; + n->withClause = $12; + n->as = $9; + n->language = $11; $$ = (Node *)n; }; +opt_or_replace: OR REPLACE { $$ = TRUE; } + | /*EMPTY*/ { $$ = FALSE; } + ; + opt_with: WITH definition { $$ = $2; } | /*EMPTY*/ { $$ = NIL; } ; @@ -5682,6 +5681,7 @@ TokenId: ABSOLUTE { $$ = "absolute"; } | REINDEX { $$ = "reindex"; } | RELATIVE { $$ = "relative"; } | RENAME { $$ = "rename"; } + | REPLACE { $$ = "replace"; } | RESTRICT { $$ = "restrict"; } | RETURNS { $$ = "returns"; } | REVOKE { $$ = "revoke"; } diff --git a/src/backend/parser/keywords.c b/src/backend/parser/keywords.c index bff257a545..3beac5a6f4 100644 --- a/src/backend/parser/keywords.c +++ b/src/backend/parser/keywords.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/parser/keywords.c,v 1.97 2001/08/26 16:56:00 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/parser/keywords.c,v 1.98 2001/10/02 21:39:35 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -216,6 +216,7 @@ static ScanKeyword ScanKeywords[] = { {"reindex", REINDEX}, {"relative", RELATIVE}, {"rename", RENAME}, + {"replace", REPLACE}, {"reset", RESET}, {"restrict", RESTRICT}, {"returns", RETURNS}, diff --git a/src/backend/utils/adt/sets.c b/src/backend/utils/adt/sets.c index 922dc5a2b1..0cd3b76647 100644 --- a/src/backend/utils/adt/sets.c +++ b/src/backend/utils/adt/sets.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/utils/adt/Attic/sets.c,v 1.38 2001/09/08 01:10:20 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/utils/adt/Attic/sets.c,v 1.39 2001/10/02 21:39:35 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -51,6 +51,7 @@ SetDefine(char *querystr, char *typename) char repl[Natts_pg_proc]; setoid = ProcedureCreate(procname, /* changed below, after oid known */ + false, /* don't replace */ true, /* returnsSet */ typename, /* returnTypeName */ "sql", /* languageName */ diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index e4c8faa2b0..cd64d8d2c2 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: pg_proc.h,v 1.212 2001/09/28 08:09:13 thomas Exp $ + * $Id: pg_proc.h,v 1.213 2001/10/02 21:39:36 tgl Exp $ * * NOTES * The script catalog/genbki.sh reads this file and generates .bki @@ -2854,6 +2854,7 @@ DESCR("time zone"); * prototypes for functions pg_proc.c */ extern Oid ProcedureCreate(char *procedureName, + bool replace, bool returnsSet, char *returnTypeName, char *languageName, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index c5a68ef7c9..e190fb20bb 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2001, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $Id: parsenodes.h,v 1.144 2001/09/18 01:59:06 tgl Exp $ + * $Id: parsenodes.h,v 1.145 2001/10/02 21:39:36 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -514,6 +514,7 @@ typedef struct IndexStmt typedef struct ProcedureStmt { NodeTag type; + bool replace; /* T => replace if already exists */ char *funcname; /* name of function to create */ List *argTypes; /* list of argument types (TypeName nodes) */ Node *returnType; /* the return type (a TypeName node) */ diff --git a/src/interfaces/ecpg/preproc/keywords.c b/src/interfaces/ecpg/preproc/keywords.c index 2c6f29165d..1092cb2473 100644 --- a/src/interfaces/ecpg/preproc/keywords.c +++ b/src/interfaces/ecpg/preproc/keywords.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/interfaces/ecpg/preproc/keywords.c,v 1.44 2001/09/19 14:09:32 meskes Exp $ + * $Header: /cvsroot/pgsql/src/interfaces/ecpg/preproc/keywords.c,v 1.45 2001/10/02 21:39:36 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -118,6 +118,7 @@ static ScanKeyword ScanKeywords[] = { {"force", FORCE}, {"foreign", FOREIGN}, {"forward", FORWARD}, + {"freeze", FREEZE}, {"from", FROM}, {"full", FULL}, {"function", FUNCTION}, @@ -215,6 +216,7 @@ static ScanKeyword ScanKeywords[] = { {"reindex", REINDEX}, {"relative", RELATIVE}, {"rename", RENAME}, + {"replace", REPLACE}, {"reset", RESET}, {"restrict", RESTRICT}, {"returns", RETURNS}, @@ -262,7 +264,7 @@ static ScanKeyword ScanKeywords[] = { {"truncate", TRUNCATE}, {"trusted", TRUSTED}, {"type", TYPE_P}, - {"unencrypted", UNENCRYPTED}, + {"unencrypted", UNENCRYPTED}, {"union", UNION}, {"unique", UNIQUE}, {"unknown", UNKNOWN}, diff --git a/src/interfaces/ecpg/preproc/preproc.y b/src/interfaces/ecpg/preproc/preproc.y index e348f773c7..f6ef974922 100644 --- a/src/interfaces/ecpg/preproc/preproc.y +++ b/src/interfaces/ecpg/preproc/preproc.y @@ -202,7 +202,7 @@ make_name(void) %token DEFERRABLE, DEFERRED, IMMEDIATE, INITIALLY, PENDANT, - RESTRICT, + REPLACE, RESTRICT, TRIGGER /* Keywords (in SQL92 non-reserved words) */ @@ -338,7 +338,7 @@ make_name(void) %type constraints_set_mode comment_type comment_cl comment_ag %type CreateGroupStmt AlterGroupStmt DropGroupStmt key_delete %type opt_force key_update CreateSchemaStmt PosIntStringConst -%type IntConst PosIntConst grantee_list func_type +%type IntConst PosIntConst grantee_list func_type opt_or_replace %type select_limit opt_for_update_clause CheckPointStmt %type ECPGWhenever ECPGConnect connection_target ECPGOpen @@ -1881,26 +1881,24 @@ RecipeStmt: EXECUTE RECIPE recipe_name /***************************************************************************** * * QUERY: - * define function - * [( { , })] - * returns - * as - * language [with - * [ arch_pct = ] - * [, disk_pct = ] - * [, byte_pct = ] - * [, perbyte_cpu = ] - * [, percall_cpu = ] - * [, iscachable] + * create [or replace] function + * [( { , })] + * returns + * as + * language [with parameters] * *****************************************************************************/ -ProcedureStmt: CREATE FUNCTION func_name func_args +ProcedureStmt: CREATE opt_or_replace FUNCTION func_name func_args RETURNS func_return AS func_as LANGUAGE ColId_or_Sconst opt_with { - $$ = cat_str(10, make_str("create function"), $3, $4, make_str("returns"), $6, make_str("as"), $8, make_str("language"), $10, $11); + $$ = cat_str(12, make_str("create"), $2, make_str("function"), $4, $5, make_str("returns"), $7, make_str("as"), $9, make_str("language"), $11, $12); } +opt_or_replace: OR REPLACE { $$ = make_str("or replace"); } + | /*EMPTY*/ { $$ = EMPTY; } + ; + opt_with: WITH definition { $$ = cat2_str(make_str("with"), $2); } | /*EMPTY*/ { $$ = EMPTY; } ; @@ -5043,6 +5041,7 @@ TokenId: ABSOLUTE { $$ = make_str("absolute"); } | REINDEX { $$ = make_str("reindex"); } | RELATIVE { $$ = make_str("relative"); } | RENAME { $$ = make_str("rename"); } + | REPLACE { $$ = make_str("replace"); } | RESTRICT { $$ = make_str("restrict"); } | RETURNS { $$ = make_str("returns"); } | REVOKE { $$ = make_str("revoke"); } -- 2.40.0