Re: [PATCHES] ALTER FUNCTION / STRICT
Neil Conway wrote: Attached is a revised patch that also allows security definer and function volatility to be changed. Applied. -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] ALTER FUNCTION / STRICT
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 - 1.5 --- doc/src/sgml/ref/alter_function.sgml 13 Mar 2005 06:37:42 - *** *** 20,27 --- 20,34 + ALTER FUNCTION name ( [ type [, ...] ] ) action [, ... ] [ RESTRICT ] ALTER FUNCTION name ( [ type [, ...] ] ) RENAME TO newname ALTER FUNCTION name ( [ type [, ...] ] ) OWNER TO newowner + + where action is one of: + + CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT + IMMUTABLE | STRICT | VOLATILE + [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER *** *** 69,79 newowner ! 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 ! SECURITY DEFINER, ! it will subsequently execute as the new owner. --- 76,140 newowner ! 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 SECURITY DEFINER, it will subsequently ! execute as the new owner. ! ! ! ! ! ! CALLED ON NULL INPUT ! RETURNS NULL ON NULL INPUT ! STRICT ! ! ! !CALLED ON NULL INPUT changes the function so !that it will be invoked when some or all of its arguments are !null. RETURNS NULL ON NULL INPUT or !STRICT changes the function so that it !always returns null if any of its arguments are null. See for more information. ! ! ! ! ! ! IMMUTABLE ! STABLE ! VOLATILE ! ! ! !Change the volatility of the function to the specified !type. See for more !information about function volatility. ! ! ! ! ! ! EXTERNAL SECURITY INVOKER ! EXTERNAL SECURITY DEFINER ! ! ! ! Change whether the function is a security definer or not. The ! key word EXTERNAL is ignored for SQL ! conformance. See for more ! information about this capability. ! ! ! ! ! ! RESTRICT ! ! ! ! Ignored for conformance with the SQL standard. *** *** 104,112 Compatibility !There is an ALTER FUNCTION statement in the SQL !standard, but it does not provide the option to rename the !function or change the owner. --- 165,177 Compatibility !This statement is partially compatible with the 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 RESTRICT key word; it is optional in !PostgreSQL. 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 - 1.4 --- doc/src/sgml/ref/alter_index.sgml 13 Mar 2005 05:22:49 - *** *** 20,29 ! ALTER INDEX name ! action [, ... ] ! ALTER INDEX name ! RENAME TO new_name where action is one of: --- 20,27 ! ALTER INDEX name action [, ... ] ! ALTER INDEX name RENAME TO new_name where action is one of: Index: src/backend/commands/functioncmds.c === RCS file: /Users/neilc/local/cvs/pgsql/src/backend/commands/functioncmds.c,v retrieving revis
Re: [PATCHES] ALTER FUNCTION / STRICT
Neil Conway <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PATCHES] 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 (, , , and 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 - 1.5 --- doc/src/sgml/ref/alter_function.sgml 12 Mar 2005 13:42:28 - *** *** 20,27 --- 20,34 + ALTER FUNCTION name ( [ type [, ...] ] ) action [, ... ] [ RESTRICT ] ALTER FUNCTION name ( [ type [, ...] ] ) RENAME TO newname ALTER FUNCTION name ( [ type [, ...] ] ) OWNER TO newowner + + where action is one of: + + CALLED ON NULL INPUT + RETURNS NULL ON NULL INPUT + STRICT *** *** 69,79 newowner ! 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 ! SECURITY DEFINER, ! it will subsequently execute as the new owner. --- 76,112 newowner ! 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 SECURITY DEFINER, it will subsequently ! execute as the new owner. ! ! ! ! ! ! CALLED ON NULL INPUT ! RETURNS NULL ON NULL INPUT ! STRICT ! ! ! !CALLED ON NULL INPUT changes the function so !that it will be invoked when some or all of its arguments are !null. RETURNS NULL ON NULL INPUT or !STRICT changes the function so that it !always returns null if any of its arguments are null. See for more information. ! ! ! ! ! ! RESTRICT ! ! ! ! Ignored for compatibility with the SQL standard. *** *** 104,112 Compatibility !There is an ALTER FUNCTION statement in the SQL !standard, but it does not provide the option to rename the !function or change the owner. --- 137,146 Compatibility !This statement is partially compatible with the 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 or changes its owner. 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 - 1.4 --- doc/src/sgml/ref/alter_index.sgml 12 Mar 2005 12:09:43 - *** *** 20,29 ! ALTER INDEX name ! action [, ... ] ! ALTER INDEX name ! RENAME TO new_name where action is one of: --- 20,27 ! ALTER INDEX name action [, ... ] ! ALTER INDEX name RENAME TO new_name where action 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:
