Thread: ALTER FUNCTION / STRICT
This patch allows ALTER FUNCTION set change a function's strictness. In
and of itself this isn't very useful, but it is defined in SQL2003, so
it's probably worth implementing.
Notes:
- the optimizer considers strictness; for example, the optimizer will
pre-evaluate calls to a strict function that is passed NULL (i.e.
replacing the function call with a NULL). This means if you create a
function, create a plan that calls the function, change the function's
strictness, and then invoke the stored plan, the function's old
strictness will still apply. Considering we have rather more serious
problems involving out-of-date plans following DDL, I don't think this
is a big deal, but I thought I'd mention it.
- SQL *requires* a "CASCADE" keyword following the ALTER command. I made
it optional, but even optional it's a little ugly.
- I considered making it possible to change other attributes of a
function (e.g. volatility and security definer), and the patch is
implemented such that this should be easy to do. Does anyone think this
is worth doing? (The same kinds of not-flushing-dependent-objects on
ALTER issues apply as with strictness and saved plans.)
- SQL also specifies that the LANGUAGE clause of the function definition
should be modifiable, but that strikes me as quite bizarre. The other
things SQL defines for ALTER FUNCTION don't have equivalents in PG
(<parameter style clause>, <SQL-data access indication>, <dynamic result
sets characteristic>, and NAME <external routine name>).
Incidentally, is there a reason that DROP FUNCTION doesn't use the
FuncWithArgs node? Given that the latter exists, ISTM we may as well use
it where applicable.
-Neil
Index: doc/src/sgml/ref/alter_function.sgml
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/doc/src/sgml/ref/alter_function.sgml,v
retrieving revision 1.5
diff -c -r1.5 alter_function.sgml
*** doc/src/sgml/ref/alter_function.sgml 25 Jun 2004 21:55:50 -0000 1.5
--- doc/src/sgml/ref/alter_function.sgml 12 Mar 2005 13:42:28 -0000
***************
*** 20,27 ****
--- 20,34 ----
<refsynopsisdiv>
<synopsis>
+ ALTER FUNCTION <replaceable>name</replaceable> ( [ <replaceable class="parameter">type</replaceable> [, ...] ] )
<replaceableclass="PARAMETER">action</replaceable> [, ... ] [ RESTRICT ]
ALTER FUNCTION <replaceable>name</replaceable> ( [ <replaceable class="parameter">type</replaceable> [, ...] ] )
RENAMETO <replaceable>newname</replaceable>
ALTER FUNCTION <replaceable>name</replaceable> ( [ <replaceable class="parameter">type</replaceable> [, ...] ] )
OWNERTO <replaceable>newowner</replaceable>
+
+ where <replaceable class="PARAMETER">action</replaceable> is one of:
+
+ CALLED ON NULL INPUT
+ RETURNS NULL ON NULL INPUT
+ STRICT
</synopsis>
</refsynopsisdiv>
***************
*** 69,79 ****
<term><replaceable class="parameter">newowner</replaceable></term>
<listitem>
<para>
! The new owner of the function.
! To change the owner of a function, you must be a superuser.
! Note that if the function is marked
! <literal>SECURITY DEFINER</literal>,
! it will subsequently execute as the new owner.
</para>
</listitem>
</varlistentry>
--- 76,112 ----
<term><replaceable class="parameter">newowner</replaceable></term>
<listitem>
<para>
! The new owner of the function. To change the owner of a
! function, you must be a superuser. Note that if the function is
! marked <literal>SECURITY DEFINER</literal>, it will subsequently
! execute as the new owner.
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal>CALLED ON NULL INPUT</literal></term>
! <term><literal>RETURNS NULL ON NULL INPUT</literal></term>
! <term><literal>STRICT</literal></term>
!
! <listitem>
! <para>
! <literal>CALLED ON NULL INPUT</literal> changes the function so
! that it will be invoked when some or all of its arguments are
! null. <literal>RETURNS NULL ON NULL INPUT</literal> or
! <literal>STRICT</literal> changes the function so that it
! always returns null if any of its arguments are null. See <xref
! linkend="sql-createfunction"> for more information.
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal>RESTRICT</literal></term>
!
! <listitem>
! <para>
! Ignored for compatibility with the SQL standard.
</para>
</listitem>
</varlistentry>
***************
*** 104,112 ****
<title>Compatibility</title>
<para>
! There is an <command>ALTER FUNCTION</command> statement in the SQL
! standard, but it does not provide the option to rename the
! function or change the owner.
</para>
</refsect1>
--- 137,146 ----
<title>Compatibility</title>
<para>
! This statement is partially compatible with the <command>ALTER
! FUNCTION</command> statement in the SQL standard. The standard
! allows more properties of a function to be modified, but does not
! provide the ability to rename a function or changes its owner.
</para>
</refsect1>
Index: doc/src/sgml/ref/alter_index.sgml
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/doc/src/sgml/ref/alter_index.sgml,v
retrieving revision 1.4
diff -c -r1.4 alter_index.sgml
*** doc/src/sgml/ref/alter_index.sgml 24 Aug 2004 00:06:51 -0000 1.4
--- doc/src/sgml/ref/alter_index.sgml 12 Mar 2005 12:09:43 -0000
***************
*** 20,29 ****
<refsynopsisdiv>
<synopsis>
! ALTER INDEX <replaceable class="PARAMETER">name</replaceable>
! <replaceable class="PARAMETER">action</replaceable> [, ... ]
! ALTER INDEX <replaceable class="PARAMETER">name</replaceable>
! RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
where <replaceable class="PARAMETER">action</replaceable> is one of:
--- 20,27 ----
<refsynopsisdiv>
<synopsis>
! ALTER INDEX <replaceable class="PARAMETER">name</replaceable> <replaceable class="PARAMETER">action</replaceable> [,
...]
! ALTER INDEX <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable
class="PARAMETER">new_name</replaceable>
where <replaceable class="PARAMETER">action</replaceable> is one of:
Index: src/backend/commands/functioncmds.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/commands/functioncmds.c,v
retrieving revision 1.54
diff -c -r1.54 functioncmds.c
*** src/backend/commands/functioncmds.c 27 Jan 2005 23:23:55 -0000 1.54
--- src/backend/commands/functioncmds.c 12 Mar 2005 13:19:44 -0000
***************
*** 3,9 ****
* functioncmds.c
*
* Routines for CREATE and DROP FUNCTION commands and CREATE and DROP
! * CAST commands.
*
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
--- 3,9 ----
* functioncmds.c
*
* Routines for CREATE and DROP FUNCTION commands and CREATE and DROP
! * CAST commands.
*
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
***************
*** 805,811 ****
--- 805,877 ----
heap_close(rel, NoLock);
}
+ /*
+ * Implements the ALTER FUNCTION utility command (except for the
+ * RENAME and OWNER clauses, which are handled as part of the generic
+ * ALTER framework).
+ */
+ void
+ AlterFunction(AlterFunctionStmt *stmt)
+ {
+ HeapTuple tup;
+ Oid funcOid;
+ Form_pg_proc procForm;
+ Relation rel;
+ ListCell *l;
+
+ rel = heap_openr(ProcedureRelationName, RowExclusiveLock);
+
+ funcOid = LookupFuncNameTypeNames(stmt->func->funcname,
+ stmt->func->funcargs,
+ false);
+
+ tup = SearchSysCacheCopy(PROCOID,
+ ObjectIdGetDatum(funcOid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(tup)) /* should not happen */
+ elog(ERROR, "cache lookup failed for function %u", funcOid);
+
+ procForm = (Form_pg_proc) GETSTRUCT(tup);
+
+ /* Permission check: must own function */
+ if (!pg_proc_ownercheck(funcOid, GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_PROC,
+ NameListToString(stmt->func->funcname));
+
+ if (procForm->proisagg)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is an aggregate function",
+ NameListToString(stmt->func->funcname))));
+
+ /*
+ * Examine requested actions. Right now we only support changing
+ * a function's strictness, so there's not much point refactoring
+ * this to share code with CREATE FUNCTION. If the list of actions
+ * grows, reconsider this...
+ */
+ if (list_length(stmt->actions) != 1)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+
+ foreach (l, stmt->actions)
+ {
+ DefElem *defel = (DefElem *) lfirst(l);
+ if (strcmp(defel->defname, "strict") == 0)
+ procForm->proisstrict = intVal(defel->arg);
+ else
+ elog(ERROR, "option \"%s\" not recognized", defel->defname);
+ }
+
+ /* Do the update */
+ simple_heap_update(rel, &tup->t_self, tup);
+ CatalogUpdateIndexes(rel, tup);
+
+ heap_close(rel, NoLock);
+ heap_freetuple(tup);
+ }
/*
* SetFunctionReturnType - change declared return type of a function
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.297
diff -c -r1.297 copyfuncs.c
*** src/backend/nodes/copyfuncs.c 10 Mar 2005 23:21:21 -0000 1.297
--- src/backend/nodes/copyfuncs.c 12 Mar 2005 09:42:10 -0000
***************
*** 1892,1897 ****
--- 1892,1908 ----
return newnode;
}
+ static AlterFunctionStmt *
+ _copyAlterFunctionStmt(AlterFunctionStmt *from)
+ {
+ AlterFunctionStmt *newnode = makeNode(AlterFunctionStmt);
+
+ COPY_NODE_FIELD(func);
+ COPY_NODE_FIELD(actions);
+
+ return newnode;
+ }
+
static RemoveAggrStmt *
_copyRemoveAggrStmt(RemoveAggrStmt *from)
{
***************
*** 2882,2887 ****
--- 2893,2901 ----
case T_FunctionParameter:
retval = _copyFunctionParameter(from);
break;
+ case T_AlterFunctionStmt:
+ retval = _copyAlterFunctionStmt(from);
+ break;
case T_RemoveAggrStmt:
retval = _copyRemoveAggrStmt(from);
break;
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.236
diff -c -r1.236 equalfuncs.c
*** src/backend/nodes/equalfuncs.c 10 Mar 2005 23:21:21 -0000 1.236
--- src/backend/nodes/equalfuncs.c 12 Mar 2005 09:39:34 -0000
***************
*** 954,959 ****
--- 954,968 ----
}
static bool
+ _equalAlterFunctionStmt(AlterFunctionStmt *a, AlterFunctionStmt *b)
+ {
+ COMPARE_NODE_FIELD(func);
+ COMPARE_NODE_FIELD(actions);
+
+ return true;
+ }
+
+ static bool
_equalRemoveAggrStmt(RemoveAggrStmt *a, RemoveAggrStmt *b)
{
COMPARE_NODE_FIELD(aggname);
***************
*** 2014,2019 ****
--- 2023,2031 ----
case T_FunctionParameter:
retval = _equalFunctionParameter(a, b);
break;
+ case T_AlterFunctionStmt:
+ retval = _equalAlterFunctionStmt(a, b);
+ break;
case T_RemoveAggrStmt:
retval = _equalRemoveAggrStmt(a, b);
break;
Index: src/backend/parser/gram.y
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.483
diff -c -r2.483 gram.y
*** src/backend/parser/gram.y 2 Feb 2005 06:36:01 -0000 2.483
--- src/backend/parser/gram.y 12 Mar 2005 10:44:21 -0000
***************
*** 142,148 ****
DropUserStmt DropdbStmt DropTableSpaceStmt ExplainStmt FetchStmt
GrantStmt IndexStmt InsertStmt ListenStmt LoadStmt
LockStmt NotifyStmt ExplainableStmt PreparableStmt
! CreateFunctionStmt ReindexStmt RemoveAggrStmt
RemoveFuncStmt RemoveOperStmt RenameStmt RevokeStmt
RuleActionStmt RuleActionStmtOrEmpty RuleStmt
SelectStmt TransactionStmt TruncateStmt
--- 142,148 ----
DropUserStmt DropdbStmt DropTableSpaceStmt ExplainStmt FetchStmt
GrantStmt IndexStmt InsertStmt ListenStmt LoadStmt
LockStmt NotifyStmt ExplainableStmt PreparableStmt
! CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt
RemoveFuncStmt RemoveOperStmt RenameStmt RevokeStmt
RuleActionStmt RuleActionStmtOrEmpty RuleStmt
SelectStmt TransactionStmt TruncateStmt
***************
*** 213,219 ****
%type <list> stmtblock stmtmulti
OptTableElementList TableElementList OptInherit definition
opt_distinct opt_definition func_args
! func_args_list func_as createfunc_opt_list
oper_argtypes RuleActionList RuleActionMulti
opt_column_list columnList opt_name_list
sort_clause opt_sort_clause sortby_list index_params
--- 213,219 ----
%type <list> stmtblock stmtmulti
OptTableElementList TableElementList OptInherit definition
opt_distinct opt_definition func_args
! func_args_list func_as createfunc_opt_list alterfunc_opt_list
oper_argtypes RuleActionList RuleActionMulti
opt_column_list columnList opt_name_list
sort_clause opt_sort_clause sortby_list index_params
***************
*** 231,237 ****
%type <range> into_clause OptTempTableName
! %type <defelt> createfunc_opt_item
%type <fun_param> func_arg
%type <typnam> func_return func_type aggr_argtype
--- 231,237 ----
%type <range> into_clause OptTempTableName
! %type <defelt> createfunc_opt_item common_func_opt_item
%type <fun_param> func_arg
%type <typnam> func_return func_type aggr_argtype
***************
*** 486,491 ****
--- 486,492 ----
stmt :
AlterDatabaseSetStmt
| AlterDomainStmt
+ | AlterFunctionStmt
| AlterGroupStmt
| AlterOwnerStmt
| AlterSeqStmt
***************
*** 3371,3376 ****
--- 3372,3395 ----
| createfunc_opt_list createfunc_opt_item { $$ = lappend($1, $2); }
;
+ /*
+ * Options common to both CREATE FUNCTION and ALTER FUNCTION
+ */
+ common_func_opt_item:
+ CALLED ON NULL_P INPUT_P
+ {
+ $$ = makeDefElem("strict", (Node *)makeInteger(FALSE));
+ }
+ | RETURNS NULL_P ON NULL_P INPUT_P
+ {
+ $$ = makeDefElem("strict", (Node *)makeInteger(TRUE));
+ }
+ | STRICT_P
+ {
+ $$ = makeDefElem("strict", (Node *)makeInteger(TRUE));
+ }
+ ;
+
createfunc_opt_item:
AS func_as
{
***************
*** 3392,3409 ****
{
$$ = makeDefElem("volatility", (Node *)makeString("volatile"));
}
! | CALLED ON NULL_P INPUT_P
! {
! $$ = makeDefElem("strict", (Node *)makeInteger(FALSE));
! }
! | RETURNS NULL_P ON NULL_P INPUT_P
! {
! $$ = makeDefElem("strict", (Node *)makeInteger(TRUE));
! }
! | STRICT_P
! {
! $$ = makeDefElem("strict", (Node *)makeInteger(TRUE));
! }
| EXTERNAL SECURITY DEFINER
{
$$ = makeDefElem("security", (Node *)makeInteger(TRUE));
--- 3411,3417 ----
{
$$ = makeDefElem("volatility", (Node *)makeString("volatile"));
}
!
| EXTERNAL SECURITY DEFINER
{
$$ = makeDefElem("security", (Node *)makeInteger(TRUE));
***************
*** 3420,3425 ****
--- 3428,3437 ----
{
$$ = makeDefElem("security", (Node *)makeInteger(FALSE));
}
+ | common_func_opt_item
+ {
+ $$ = $1;
+ }
;
func_as: Sconst { $$ = list_make1(makeString($1)); }
***************
*** 3434,3439 ****
--- 3446,3481 ----
| /*EMPTY*/ { $$ = NIL; }
;
+ /*****************************************************************************
+ * ALTER FUNCTION
+ *
+ * RENAME and OWNER subcommands are already provided by the generic
+ * ALTER infrastructure, so here we just specify alterations that can
+ * only be applied to functions.
+ *
+ *****************************************************************************/
+ AlterFunctionStmt:
+ ALTER FUNCTION function_with_argtypes alterfunc_opt_list opt_restrict
+ {
+ AlterFunctionStmt *n = makeNode(AlterFunctionStmt);
+ n->func = (FuncWithArgs *) $3;
+ n->actions = $4;
+ $$ = (Node *) n;
+ }
+ ;
+
+ alterfunc_opt_list:
+ /* At least one option must be specified */
+ common_func_opt_item { $$ = list_make1($1); }
+ | alterfunc_opt_list common_func_opt_item { $$ = lappend($1, $2); }
+ ;
+
+ /* Ignored, merely for SQL compliance */
+ opt_restrict:
+ RESTRICT
+ | /* EMPTY */
+ ;
+
/*****************************************************************************
*
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.233
diff -c -r1.233 utility.c
*** src/backend/tcop/utility.c 27 Jan 2005 03:18:10 -0000 1.233
--- src/backend/tcop/utility.c 12 Mar 2005 12:52:18 -0000
***************
*** 277,282 ****
--- 277,283 ----
{
case T_AlterDatabaseSetStmt:
case T_AlterDomainStmt:
+ case T_AlterFunctionStmt:
case T_AlterGroupStmt:
case T_AlterOwnerStmt:
case T_AlterSeqStmt:
***************
*** 711,716 ****
--- 712,721 ----
CreateFunction((CreateFunctionStmt *) parsetree);
break;
+ case T_AlterFunctionStmt: /* ALTER FUNCTION */
+ AlterFunction((AlterFunctionStmt *) parsetree);
+ break;
+
case T_IndexStmt: /* CREATE INDEX */
{
IndexStmt *stmt = (IndexStmt *) parsetree;
***************
*** 1394,1403 ****
--- 1399,1413 ----
tag = "ALTER TABLE";
}
break;
+
case T_AlterDomainStmt:
tag = "ALTER DOMAIN";
break;
+ case T_AlterFunctionStmt:
+ tag = "ALTER FUNCTION";
+ break;
+
case T_GrantStmt:
{
GrantStmt *stmt = (GrantStmt *) parsetree;
Index: src/include/commands/defrem.h
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/include/commands/defrem.h,v
retrieving revision 1.62
diff -c -r1.62 defrem.h
*** src/include/commands/defrem.h 31 Dec 2004 22:03:28 -0000 1.62
--- src/include/commands/defrem.h 12 Mar 2005 10:42:45 -0000
***************
*** 49,54 ****
--- 49,55 ----
extern void SetFunctionArgType(Oid funcOid, int argIndex, Oid newArgType);
extern void RenameFunction(List *name, List *argtypes, const char *newname);
extern void AlterFunctionOwner(List *name, List *argtypes, AclId newOwnerSysId);
+ extern void AlterFunction(AlterFunctionStmt *stmt);
extern void CreateCast(CreateCastStmt *stmt);
extern void DropCast(DropCastStmt *stmt);
extern void DropCastById(Oid castOid);
Index: src/include/nodes/nodes.h
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/include/nodes/nodes.h,v
retrieving revision 1.163
diff -c -r1.163 nodes.h
*** src/include/nodes/nodes.h 31 Dec 2004 22:03:34 -0000 1.163
--- src/include/nodes/nodes.h 12 Mar 2005 09:37:33 -0000
***************
*** 223,228 ****
--- 223,229 ----
T_FetchStmt,
T_IndexStmt,
T_CreateFunctionStmt,
+ T_AlterFunctionStmt,
T_RemoveAggrStmt,
T_RemoveFuncStmt,
T_RemoveOperStmt,
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.273
diff -c -r1.273 parsenodes.h
*** src/include/nodes/parsenodes.h 10 Mar 2005 23:21:24 -0000 1.273
--- src/include/nodes/parsenodes.h 12 Mar 2005 09:37:00 -0000
***************
*** 1397,1402 ****
--- 1397,1409 ----
/* someday add IN/OUT/INOUT indicator here */
} FunctionParameter;
+ typedef struct AlterFunctionStmt
+ {
+ NodeTag type;
+ FuncWithArgs *func; /* name and args of function */
+ List *actions; /* list of DefElem */
+ } AlterFunctionStmt;
+
/* ----------------------
* Drop Aggregate Statement
* ----------------------
Index: src/test/regress/expected/alter_table.out
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/test/regress/expected/alter_table.out,v
retrieving revision 1.86
diff -c -r1.86 alter_table.out
*** src/test/regress/expected/alter_table.out 22 Jan 2005 05:12:23 -0000 1.86
--- src/test/regress/expected/alter_table.out 12 Mar 2005 12:45:28 -0000
***************
*** 1235,1237 ****
--- 1235,1272 ----
(3 rows)
drop table another;
+ --
+ -- alter function
+ --
+ create function test_strict(text) returns text as
+ 'select coalesce($1, ''got passed a null'');'
+ language sql returns null on null input;
+ select test_strict(NULL);
+ test_strict
+ -------------
+
+ (1 row)
+
+ alter function test_strict(text) called on null input;
+ select test_strict(NULL);
+ test_strict
+ -------------------
+ got passed a null
+ (1 row)
+
+ create function non_strict(text) returns text as
+ 'select coalesce($1, ''got passed a null'');'
+ language sql called on null input;
+ select non_strict(NULL);
+ non_strict
+ -------------------
+ got passed a null
+ (1 row)
+
+ alter function non_strict(text) returns null on null input;
+ select non_strict(NULL);
+ non_strict
+ ------------
+
+ (1 row)
+
Index: src/test/regress/sql/alter_table.sql
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/test/regress/sql/alter_table.sql,v
retrieving revision 1.48
diff -c -r1.48 alter_table.sql
*** src/test/regress/sql/alter_table.sql 22 Jan 2005 05:12:33 -0000 1.48
--- src/test/regress/sql/alter_table.sql 12 Mar 2005 12:32:09 -0000
***************
*** 975,977 ****
--- 975,994 ----
select * from another;
drop table another;
+
+ --
+ -- alter function
+ --
+ create function test_strict(text) returns text as
+ 'select coalesce($1, ''got passed a null'');'
+ language sql returns null on null input;
+ select test_strict(NULL);
+ alter function test_strict(text) called on null input;
+ select test_strict(NULL);
+
+ create function non_strict(text) returns text as
+ 'select coalesce($1, ''got passed a null'');'
+ language sql called on null input;
+ select non_strict(NULL);
+ alter function non_strict(text) returns null on null input;
+ select non_strict(NULL);
\ No newline at end of file
Neil Conway <neilc@samurai.com> writes:
> This patch allows ALTER FUNCTION set change a function's strictness. In
> and of itself this isn't very useful, but it is defined in SQL2003, so
> it's probably worth implementing.
You realize of course that that can already be done with CREATE OR
REPLACE FUNCTION. I think it's probably still somewhat useful to have
an ALTER, since the REPLACE way requires re-entering the whole function
body, which you very possibly don't want to change.
> - I considered making it possible to change other attributes of a
> function (e.g. volatility and security definer), and the patch is
> implemented such that this should be easy to do. Does anyone think this
> is worth doing?
Yes, on the same grounds as above.
> - SQL also specifies that the LANGUAGE clause of the function definition
> should be modifiable, but that strikes me as quite bizarre.
Indeed. It doesn't seem sensible to change LANGUAGE without supplying a
new function body, and so I would argue that this should be left to
CREATE OR REPLACE FUNCTION. But I can see wishing that I could change
the auxiliary properties of a function without retyping the body.
> Incidentally, is there a reason that DROP FUNCTION doesn't use the
> FuncWithArgs node?
Probably just historical, but why bother changing it? I don't think
that would let you accomplish anything useful, like consolidating
RemoveFuncStmt with something else. Nor would it make the code
measurably clearer IMHO.
regards, tom lane
Tom Lane wrote:
> You realize of course that that can already be done with CREATE OR
> REPLACE FUNCTION.
Good point; that makes me less wary of breaking dependencies on existing
functions via ALTER, since in any case that can already be done.
>>Incidentally, is there a reason that DROP FUNCTION doesn't use the
>>FuncWithArgs node?
>
> Probably just historical, but why bother changing it?
It's just a small cleanup, but it seems inconsistent to me to have an
abstraction "function name with args" that is only used in some of the
situations where it's applicable. I know, I'm ornery about these things :)
Attached is a revised patch that also allows security definer and
function volatility to be changed. Barring any objections, I'll apply
this tomorrow (I'm going to take a closer look at the patch before
applying it -- there might be a few details I want to polish up...)
-Neil
Index: doc/src/sgml/ref/alter_function.sgml
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/doc/src/sgml/ref/alter_function.sgml,v
retrieving revision 1.5
diff -c -r1.5 alter_function.sgml
*** doc/src/sgml/ref/alter_function.sgml 25 Jun 2004 21:55:50 -0000 1.5
--- doc/src/sgml/ref/alter_function.sgml 13 Mar 2005 06:37:42 -0000
***************
*** 20,27 ****
--- 20,34 ----
<refsynopsisdiv>
<synopsis>
+ ALTER FUNCTION <replaceable>name</replaceable> ( [ <replaceable class="parameter">type</replaceable> [, ...] ] )
<replaceableclass="PARAMETER">action</replaceable> [, ... ] [ RESTRICT ]
ALTER FUNCTION <replaceable>name</replaceable> ( [ <replaceable class="parameter">type</replaceable> [, ...] ] )
RENAMETO <replaceable>newname</replaceable>
ALTER FUNCTION <replaceable>name</replaceable> ( [ <replaceable class="parameter">type</replaceable> [, ...] ] )
OWNERTO <replaceable>newowner</replaceable>
+
+ where <replaceable class="PARAMETER">action</replaceable> is one of:
+
+ CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
+ IMMUTABLE | STRICT | VOLATILE
+ [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
</synopsis>
</refsynopsisdiv>
***************
*** 69,79 ****
<term><replaceable class="parameter">newowner</replaceable></term>
<listitem>
<para>
! The new owner of the function.
! To change the owner of a function, you must be a superuser.
! Note that if the function is marked
! <literal>SECURITY DEFINER</literal>,
! it will subsequently execute as the new owner.
</para>
</listitem>
</varlistentry>
--- 76,140 ----
<term><replaceable class="parameter">newowner</replaceable></term>
<listitem>
<para>
! The new owner of the function. To change the owner of a
! function, you must be a superuser. Note that if the function is
! marked <literal>SECURITY DEFINER</literal>, it will subsequently
! execute as the new owner.
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal>CALLED ON NULL INPUT</literal></term>
! <term><literal>RETURNS NULL ON NULL INPUT</literal></term>
! <term><literal>STRICT</literal></term>
!
! <listitem>
! <para>
! <literal>CALLED ON NULL INPUT</literal> changes the function so
! that it will be invoked when some or all of its arguments are
! null. <literal>RETURNS NULL ON NULL INPUT</literal> or
! <literal>STRICT</literal> changes the function so that it
! always returns null if any of its arguments are null. See <xref
! linkend="sql-createfunction"> for more information.
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal>IMMUTABLE</literal></term>
! <term><literal>STABLE</literal></term>
! <term><literal>VOLATILE</literal></term>
!
! <listitem>
! <para>
! Change the volatility of the function to the specified
! type. See <xref linkend="sql-createfunction"> for more
! information about function volatility.
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal><optional>EXTERNAL</optional> SECURITY INVOKER</literal></term>
! <term><literal><optional>EXTERNAL</optional> SECURITY DEFINER</literal></term>
!
! <listitem>
! <para>
! Change whether the function is a security definer or not. The
! key word <literal>EXTERNAL</literal> is ignored for SQL
! conformance. See <xref linkend="sql-createfunction"> for more
! information about this capability.
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal>RESTRICT</literal></term>
!
! <listitem>
! <para>
! Ignored for conformance with the SQL standard.
</para>
</listitem>
</varlistentry>
***************
*** 104,112 ****
<title>Compatibility</title>
<para>
! There is an <command>ALTER FUNCTION</command> statement in the SQL
! standard, but it does not provide the option to rename the
! function or change the owner.
</para>
</refsect1>
--- 165,177 ----
<title>Compatibility</title>
<para>
! This statement is partially compatible with the <command>ALTER
! FUNCTION</> statement in the SQL standard. The standard allows more
! properties of a function to be modified, but does not provide the
! ability to rename a function, make a function a security definer,
! or change the owner or volatility of a function. The standard also
! requires the <literal>RESTRICT</> key word; it is optional in
! <productname>PostgreSQL</>.
</para>
</refsect1>
Index: doc/src/sgml/ref/alter_index.sgml
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/doc/src/sgml/ref/alter_index.sgml,v
retrieving revision 1.4
diff -c -r1.4 alter_index.sgml
*** doc/src/sgml/ref/alter_index.sgml 24 Aug 2004 00:06:51 -0000 1.4
--- doc/src/sgml/ref/alter_index.sgml 13 Mar 2005 05:22:49 -0000
***************
*** 20,29 ****
<refsynopsisdiv>
<synopsis>
! ALTER INDEX <replaceable class="PARAMETER">name</replaceable>
! <replaceable class="PARAMETER">action</replaceable> [, ... ]
! ALTER INDEX <replaceable class="PARAMETER">name</replaceable>
! RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
where <replaceable class="PARAMETER">action</replaceable> is one of:
--- 20,27 ----
<refsynopsisdiv>
<synopsis>
! ALTER INDEX <replaceable class="PARAMETER">name</replaceable> <replaceable class="PARAMETER">action</replaceable> [,
...]
! ALTER INDEX <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable
class="PARAMETER">new_name</replaceable>
where <replaceable class="PARAMETER">action</replaceable> is one of:
Index: src/backend/commands/functioncmds.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/commands/functioncmds.c,v
retrieving revision 1.55
diff -c -r1.55 functioncmds.c
*** src/backend/commands/functioncmds.c 13 Mar 2005 05:19:26 -0000 1.55
--- src/backend/commands/functioncmds.c 13 Mar 2005 06:03:03 -0000
***************
*** 3,9 ****
* functioncmds.c
*
* Routines for CREATE and DROP FUNCTION commands and CREATE and DROP
! * CAST commands.
*
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
--- 3,9 ----
* functioncmds.c
*
* Routines for CREATE and DROP FUNCTION commands and CREATE and DROP
! * CAST commands.
*
* Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
***************
*** 195,206 ****
return parameterCount;
}
/*
* Dissect the list of options assembled in gram.y into function
* attributes.
*/
-
static void
compute_attributes_sql_style(List *options,
List **as,
--- 195,268 ----
return parameterCount;
}
+ /*
+ * Recognize one of the options that can be passed to both CREATE
+ * FUNCTION and ALTER FUNCTION. Returns true if the specified option
+ * was recognized. If the out parameter we were going to assign to
+ * pointers to non-NULL, raise a duplicate error.
+ */
+ static bool
+ compute_common_attribute(DefElem *defel,
+ DefElem **volatility_item,
+ DefElem **strict_item,
+ DefElem **security_item)
+ {
+ if (strcmp(defel->defname, "volatility") == 0)
+ {
+ if (*volatility_item)
+ goto duplicate_error;
+
+ *volatility_item = defel;
+ }
+ else if (strcmp(defel->defname, "strict") == 0)
+ {
+ if (*strict_item)
+ goto duplicate_error;
+
+ *strict_item = defel;
+ }
+ else if (strcmp(defel->defname, "security") == 0)
+ {
+ if (*security_item)
+ goto duplicate_error;
+
+ *security_item = defel;
+ }
+ else
+ return false;
+
+ /* Recognized an option */
+ return true;
+
+ duplicate_error:
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ return false; /* keep compiler quiet */
+ }
+
+ static char
+ interpret_func_volatility(DefElem *defel)
+ {
+ char *str = strVal(defel->arg);
+
+ if (strcmp(str, "immutable") == 0)
+ return PROVOLATILE_IMMUTABLE;
+ else if (strcmp(str, "stable") == 0)
+ return PROVOLATILE_STABLE;
+ else if (strcmp(str, "volatile") == 0)
+ return PROVOLATILE_VOLATILE;
+ else
+ {
+ elog(ERROR, "invalid volatility \"%s\"", str);
+ return 0; /* keep compiler quiet */
+ }
+ }
/*
* Dissect the list of options assembled in gram.y into function
* attributes.
*/
static void
compute_attributes_sql_style(List *options,
List **as,
***************
*** 236,264 ****
errmsg("conflicting or redundant options")));
language_item = defel;
}
! else if (strcmp(defel->defname, "volatility") == 0)
! {
! if (volatility_item)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("conflicting or redundant options")));
! volatility_item = defel;
! }
! else if (strcmp(defel->defname, "strict") == 0)
! {
! if (strict_item)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("conflicting or redundant options")));
! strict_item = defel;
! }
! else if (strcmp(defel->defname, "security") == 0)
{
! if (security_item)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
! errmsg("conflicting or redundant options")));
! security_item = defel;
}
else
elog(ERROR, "option \"%s\" not recognized",
--- 298,310 ----
errmsg("conflicting or redundant options")));
language_item = defel;
}
! else if (compute_common_attribute(defel,
! &volatility_item,
! &strict_item,
! &security_item))
{
! /* recognized common option */
! continue;
}
else
elog(ERROR, "option \"%s\" not recognized",
***************
*** 280,297 ****
errmsg("no language specified")));
if (volatility_item)
! {
! if (strcmp(strVal(volatility_item->arg), "immutable") == 0)
! *volatility_p = PROVOLATILE_IMMUTABLE;
! else if (strcmp(strVal(volatility_item->arg), "stable") == 0)
! *volatility_p = PROVOLATILE_STABLE;
! else if (strcmp(strVal(volatility_item->arg), "volatile") == 0)
! *volatility_p = PROVOLATILE_VOLATILE;
! else
! elog(ERROR, "invalid volatility \"%s\"",
! strVal(volatility_item->arg));
! }
!
if (strict_item)
*strict_p = intVal(strict_item->arg);
if (security_item)
--- 326,332 ----
errmsg("no language specified")));
if (volatility_item)
! *volatility_p = interpret_func_volatility(volatility_item);
if (strict_item)
*strict_p = intVal(strict_item->arg);
if (security_item)
***************
*** 301,307 ****
/*-------------
* Interpret the parameters *parameters and return their contents via
! * out parameters *isStrict_p and *volatility_p.
*
* These parameters supply optional information about a function.
* All have defaults if not specified. Parameters:
--- 336,342 ----
/*-------------
* Interpret the parameters *parameters and return their contents via
! * *isStrict_p and *volatility_p.
*
* These parameters supply optional information about a function.
* All have defaults if not specified. Parameters:
***************
*** 347,355 ****
* In all other cases
*
* AS <object reference, or sql code>
- *
*/
-
static void
interpret_AS_clause(Oid languageOid, const char *languageName, List *as,
char **prosrc_str_p, char **probin_str_p)
--- 382,388 ----
***************
*** 799,805 ****
--- 832,905 ----
heap_close(rel, NoLock);
}
+ /*
+ * Implements the ALTER FUNCTION utility command (except for the
+ * RENAME and OWNER clauses, which are handled as part of the generic
+ * ALTER framework).
+ */
+ void
+ AlterFunction(AlterFunctionStmt *stmt)
+ {
+ HeapTuple tup;
+ Oid funcOid;
+ Form_pg_proc procForm;
+ Relation rel;
+ ListCell *l;
+ DefElem *volatility_item = NULL;
+ DefElem *strict_item = NULL;
+ DefElem *security_def_item = NULL;
+
+ rel = heap_openr(ProcedureRelationName, RowExclusiveLock);
+
+ funcOid = LookupFuncNameTypeNames(stmt->func->funcname,
+ stmt->func->funcargs,
+ false);
+
+ tup = SearchSysCacheCopy(PROCOID,
+ ObjectIdGetDatum(funcOid),
+ 0, 0, 0);
+ if (!HeapTupleIsValid(tup)) /* should not happen */
+ elog(ERROR, "cache lookup failed for function %u", funcOid);
+
+ procForm = (Form_pg_proc) GETSTRUCT(tup);
+ /* Permission check: must own function */
+ if (!pg_proc_ownercheck(funcOid, GetUserId()))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_PROC,
+ NameListToString(stmt->func->funcname));
+
+ if (procForm->proisagg)
+ ereport(ERROR,
+ (errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("\"%s\" is an aggregate function",
+ NameListToString(stmt->func->funcname))));
+
+ /* Examine requested actions. */
+ foreach (l, stmt->actions)
+ {
+ DefElem *defel = (DefElem *) lfirst(l);
+
+ if (compute_common_attribute(defel,
+ &volatility_item,
+ &strict_item,
+ &security_def_item) == false)
+ elog(ERROR, "option \"%s\" not recognized", defel->defname);
+ }
+
+ if (volatility_item)
+ procForm->provolatile = interpret_func_volatility(volatility_item);
+ if (strict_item)
+ procForm->proisstrict = intVal(strict_item->arg);
+ if (security_def_item)
+ procForm->prosecdef = intVal(security_def_item->arg);
+
+ /* Do the update */
+ simple_heap_update(rel, &tup->t_self, tup);
+ CatalogUpdateIndexes(rel, tup);
+
+ heap_close(rel, NoLock);
+ heap_freetuple(tup);
+ }
/*
* SetFunctionReturnType - change declared return type of a function
Index: src/backend/nodes/copyfuncs.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.297
diff -c -r1.297 copyfuncs.c
*** src/backend/nodes/copyfuncs.c 10 Mar 2005 23:21:21 -0000 1.297
--- src/backend/nodes/copyfuncs.c 13 Mar 2005 05:22:49 -0000
***************
*** 1892,1897 ****
--- 1892,1908 ----
return newnode;
}
+ static AlterFunctionStmt *
+ _copyAlterFunctionStmt(AlterFunctionStmt *from)
+ {
+ AlterFunctionStmt *newnode = makeNode(AlterFunctionStmt);
+
+ COPY_NODE_FIELD(func);
+ COPY_NODE_FIELD(actions);
+
+ return newnode;
+ }
+
static RemoveAggrStmt *
_copyRemoveAggrStmt(RemoveAggrStmt *from)
{
***************
*** 2882,2887 ****
--- 2893,2901 ----
case T_FunctionParameter:
retval = _copyFunctionParameter(from);
break;
+ case T_AlterFunctionStmt:
+ retval = _copyAlterFunctionStmt(from);
+ break;
case T_RemoveAggrStmt:
retval = _copyRemoveAggrStmt(from);
break;
Index: src/backend/nodes/equalfuncs.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/nodes/equalfuncs.c,v
retrieving revision 1.236
diff -c -r1.236 equalfuncs.c
*** src/backend/nodes/equalfuncs.c 10 Mar 2005 23:21:21 -0000 1.236
--- src/backend/nodes/equalfuncs.c 13 Mar 2005 05:22:49 -0000
***************
*** 954,959 ****
--- 954,968 ----
}
static bool
+ _equalAlterFunctionStmt(AlterFunctionStmt *a, AlterFunctionStmt *b)
+ {
+ COMPARE_NODE_FIELD(func);
+ COMPARE_NODE_FIELD(actions);
+
+ return true;
+ }
+
+ static bool
_equalRemoveAggrStmt(RemoveAggrStmt *a, RemoveAggrStmt *b)
{
COMPARE_NODE_FIELD(aggname);
***************
*** 2014,2019 ****
--- 2023,2031 ----
case T_FunctionParameter:
retval = _equalFunctionParameter(a, b);
break;
+ case T_AlterFunctionStmt:
+ retval = _equalAlterFunctionStmt(a, b);
+ break;
case T_RemoveAggrStmt:
retval = _equalRemoveAggrStmt(a, b);
break;
Index: src/backend/parser/gram.y
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.483
diff -c -r2.483 gram.y
*** src/backend/parser/gram.y 2 Feb 2005 06:36:01 -0000 2.483
--- src/backend/parser/gram.y 13 Mar 2005 05:22:49 -0000
***************
*** 142,148 ****
DropUserStmt DropdbStmt DropTableSpaceStmt ExplainStmt FetchStmt
GrantStmt IndexStmt InsertStmt ListenStmt LoadStmt
LockStmt NotifyStmt ExplainableStmt PreparableStmt
! CreateFunctionStmt ReindexStmt RemoveAggrStmt
RemoveFuncStmt RemoveOperStmt RenameStmt RevokeStmt
RuleActionStmt RuleActionStmtOrEmpty RuleStmt
SelectStmt TransactionStmt TruncateStmt
--- 142,148 ----
DropUserStmt DropdbStmt DropTableSpaceStmt ExplainStmt FetchStmt
GrantStmt IndexStmt InsertStmt ListenStmt LoadStmt
LockStmt NotifyStmt ExplainableStmt PreparableStmt
! CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt
RemoveFuncStmt RemoveOperStmt RenameStmt RevokeStmt
RuleActionStmt RuleActionStmtOrEmpty RuleStmt
SelectStmt TransactionStmt TruncateStmt
***************
*** 213,219 ****
%type <list> stmtblock stmtmulti
OptTableElementList TableElementList OptInherit definition
opt_distinct opt_definition func_args
! func_args_list func_as createfunc_opt_list
oper_argtypes RuleActionList RuleActionMulti
opt_column_list columnList opt_name_list
sort_clause opt_sort_clause sortby_list index_params
--- 213,219 ----
%type <list> stmtblock stmtmulti
OptTableElementList TableElementList OptInherit definition
opt_distinct opt_definition func_args
! func_args_list func_as createfunc_opt_list alterfunc_opt_list
oper_argtypes RuleActionList RuleActionMulti
opt_column_list columnList opt_name_list
sort_clause opt_sort_clause sortby_list index_params
***************
*** 231,237 ****
%type <range> into_clause OptTempTableName
! %type <defelt> createfunc_opt_item
%type <fun_param> func_arg
%type <typnam> func_return func_type aggr_argtype
--- 231,237 ----
%type <range> into_clause OptTempTableName
! %type <defelt> createfunc_opt_item common_func_opt_item
%type <fun_param> func_arg
%type <typnam> func_return func_type aggr_argtype
***************
*** 486,491 ****
--- 486,492 ----
stmt :
AlterDatabaseSetStmt
| AlterDomainStmt
+ | AlterFunctionStmt
| AlterGroupStmt
| AlterOwnerStmt
| AlterSeqStmt
***************
*** 3371,3384 ****
| createfunc_opt_list createfunc_opt_item { $$ = lappend($1, $2); }
;
! createfunc_opt_item:
! AS func_as
{
! $$ = makeDefElem("as", (Node *)$2);
}
! | LANGUAGE ColId_or_Sconst
{
! $$ = makeDefElem("language", (Node *)makeString($2));
}
| IMMUTABLE
{
--- 3372,3392 ----
| createfunc_opt_list createfunc_opt_item { $$ = lappend($1, $2); }
;
! /*
! * Options common to both CREATE FUNCTION and ALTER FUNCTION
! */
! common_func_opt_item:
! CALLED ON NULL_P INPUT_P
{
! $$ = makeDefElem("strict", (Node *)makeInteger(FALSE));
}
! | RETURNS NULL_P ON NULL_P INPUT_P
{
! $$ = makeDefElem("strict", (Node *)makeInteger(TRUE));
! }
! | STRICT_P
! {
! $$ = makeDefElem("strict", (Node *)makeInteger(TRUE));
}
| IMMUTABLE
{
***************
*** 3392,3409 ****
{
$$ = makeDefElem("volatility", (Node *)makeString("volatile"));
}
! | CALLED ON NULL_P INPUT_P
! {
! $$ = makeDefElem("strict", (Node *)makeInteger(FALSE));
! }
! | RETURNS NULL_P ON NULL_P INPUT_P
! {
! $$ = makeDefElem("strict", (Node *)makeInteger(TRUE));
! }
! | STRICT_P
! {
! $$ = makeDefElem("strict", (Node *)makeInteger(TRUE));
! }
| EXTERNAL SECURITY DEFINER
{
$$ = makeDefElem("security", (Node *)makeInteger(TRUE));
--- 3400,3406 ----
{
$$ = makeDefElem("volatility", (Node *)makeString("volatile"));
}
!
| EXTERNAL SECURITY DEFINER
{
$$ = makeDefElem("security", (Node *)makeInteger(TRUE));
***************
*** 3422,3427 ****
--- 3419,3439 ----
}
;
+ createfunc_opt_item:
+ AS func_as
+ {
+ $$ = makeDefElem("as", (Node *)$2);
+ }
+ | LANGUAGE ColId_or_Sconst
+ {
+ $$ = makeDefElem("language", (Node *)makeString($2));
+ }
+ | common_func_opt_item
+ {
+ $$ = $1;
+ }
+ ;
+
func_as: Sconst { $$ = list_make1(makeString($1)); }
| Sconst ',' Sconst
{
***************
*** 3434,3439 ****
--- 3446,3481 ----
| /*EMPTY*/ { $$ = NIL; }
;
+ /*****************************************************************************
+ * ALTER FUNCTION
+ *
+ * RENAME and OWNER subcommands are already provided by the generic
+ * ALTER infrastructure, here we just specify alterations that can
+ * only be applied to functions.
+ *
+ *****************************************************************************/
+ AlterFunctionStmt:
+ ALTER FUNCTION function_with_argtypes alterfunc_opt_list opt_restrict
+ {
+ AlterFunctionStmt *n = makeNode(AlterFunctionStmt);
+ n->func = (FuncWithArgs *) $3;
+ n->actions = $4;
+ $$ = (Node *) n;
+ }
+ ;
+
+ alterfunc_opt_list:
+ /* At least one option must be specified */
+ common_func_opt_item { $$ = list_make1($1); }
+ | alterfunc_opt_list common_func_opt_item { $$ = lappend($1, $2); }
+ ;
+
+ /* Ignored, merely for SQL compliance */
+ opt_restrict:
+ RESTRICT
+ | /* EMPTY */
+ ;
+
/*****************************************************************************
*
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.233
diff -c -r1.233 utility.c
*** src/backend/tcop/utility.c 27 Jan 2005 03:18:10 -0000 1.233
--- src/backend/tcop/utility.c 13 Mar 2005 05:22:49 -0000
***************
*** 277,282 ****
--- 277,283 ----
{
case T_AlterDatabaseSetStmt:
case T_AlterDomainStmt:
+ case T_AlterFunctionStmt:
case T_AlterGroupStmt:
case T_AlterOwnerStmt:
case T_AlterSeqStmt:
***************
*** 711,716 ****
--- 712,721 ----
CreateFunction((CreateFunctionStmt *) parsetree);
break;
+ case T_AlterFunctionStmt: /* ALTER FUNCTION */
+ AlterFunction((AlterFunctionStmt *) parsetree);
+ break;
+
case T_IndexStmt: /* CREATE INDEX */
{
IndexStmt *stmt = (IndexStmt *) parsetree;
***************
*** 1394,1403 ****
--- 1399,1413 ----
tag = "ALTER TABLE";
}
break;
+
case T_AlterDomainStmt:
tag = "ALTER DOMAIN";
break;
+ case T_AlterFunctionStmt:
+ tag = "ALTER FUNCTION";
+ break;
+
case T_GrantStmt:
{
GrantStmt *stmt = (GrantStmt *) parsetree;
Index: src/include/commands/defrem.h
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/include/commands/defrem.h,v
retrieving revision 1.62
diff -c -r1.62 defrem.h
*** src/include/commands/defrem.h 31 Dec 2004 22:03:28 -0000 1.62
--- src/include/commands/defrem.h 13 Mar 2005 05:22:49 -0000
***************
*** 49,54 ****
--- 49,55 ----
extern void SetFunctionArgType(Oid funcOid, int argIndex, Oid newArgType);
extern void RenameFunction(List *name, List *argtypes, const char *newname);
extern void AlterFunctionOwner(List *name, List *argtypes, AclId newOwnerSysId);
+ extern void AlterFunction(AlterFunctionStmt *stmt);
extern void CreateCast(CreateCastStmt *stmt);
extern void DropCast(DropCastStmt *stmt);
extern void DropCastById(Oid castOid);
Index: src/include/nodes/nodes.h
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/include/nodes/nodes.h,v
retrieving revision 1.163
diff -c -r1.163 nodes.h
*** src/include/nodes/nodes.h 31 Dec 2004 22:03:34 -0000 1.163
--- src/include/nodes/nodes.h 13 Mar 2005 05:22:49 -0000
***************
*** 223,228 ****
--- 223,229 ----
T_FetchStmt,
T_IndexStmt,
T_CreateFunctionStmt,
+ T_AlterFunctionStmt,
T_RemoveAggrStmt,
T_RemoveFuncStmt,
T_RemoveOperStmt,
Index: src/include/nodes/parsenodes.h
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/include/nodes/parsenodes.h,v
retrieving revision 1.273
diff -c -r1.273 parsenodes.h
*** src/include/nodes/parsenodes.h 10 Mar 2005 23:21:24 -0000 1.273
--- src/include/nodes/parsenodes.h 13 Mar 2005 05:22:49 -0000
***************
*** 1397,1402 ****
--- 1397,1409 ----
/* someday add IN/OUT/INOUT indicator here */
} FunctionParameter;
+ typedef struct AlterFunctionStmt
+ {
+ NodeTag type;
+ FuncWithArgs *func; /* name and args of function */
+ List *actions; /* list of DefElem */
+ } AlterFunctionStmt;
+
/* ----------------------
* Drop Aggregate Statement
* ----------------------
Index: src/test/regress/expected/alter_table.out
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/test/regress/expected/alter_table.out,v
retrieving revision 1.86
diff -c -r1.86 alter_table.out
*** src/test/regress/expected/alter_table.out 22 Jan 2005 05:12:23 -0000 1.86
--- src/test/regress/expected/alter_table.out 13 Mar 2005 05:22:49 -0000
***************
*** 1235,1237 ****
--- 1235,1272 ----
(3 rows)
drop table another;
+ --
+ -- alter function
+ --
+ create function test_strict(text) returns text as
+ 'select coalesce($1, ''got passed a null'');'
+ language sql returns null on null input;
+ select test_strict(NULL);
+ test_strict
+ -------------
+
+ (1 row)
+
+ alter function test_strict(text) called on null input;
+ select test_strict(NULL);
+ test_strict
+ -------------------
+ got passed a null
+ (1 row)
+
+ create function non_strict(text) returns text as
+ 'select coalesce($1, ''got passed a null'');'
+ language sql called on null input;
+ select non_strict(NULL);
+ non_strict
+ -------------------
+ got passed a null
+ (1 row)
+
+ alter function non_strict(text) returns null on null input;
+ select non_strict(NULL);
+ non_strict
+ ------------
+
+ (1 row)
+
Index: src/test/regress/sql/alter_table.sql
===================================================================
RCS file: /Users/neilc/local/cvs/pgsql/src/test/regress/sql/alter_table.sql,v
retrieving revision 1.48
diff -c -r1.48 alter_table.sql
*** src/test/regress/sql/alter_table.sql 22 Jan 2005 05:12:33 -0000 1.48
--- src/test/regress/sql/alter_table.sql 13 Mar 2005 05:22:49 -0000
***************
*** 975,977 ****
--- 975,994 ----
select * from another;
drop table another;
+
+ --
+ -- alter function
+ --
+ create function test_strict(text) returns text as
+ 'select coalesce($1, ''got passed a null'');'
+ language sql returns null on null input;
+ select test_strict(NULL);
+ alter function test_strict(text) called on null input;
+ select test_strict(NULL);
+
+ create function non_strict(text) returns text as
+ 'select coalesce($1, ''got passed a null'');'
+ language sql called on null input;
+ select non_strict(NULL);
+ alter function non_strict(text) returns null on null input;
+ select non_strict(NULL);
\ No newline at end of file
Neil Conway wrote: > Attached is a revised patch that also allows security definer and > function volatility to be changed. Applied. -Neil