'\" t .\" Title: ALTER FUNCTION .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets v1.75.2 .\" Date: 2016 .\" Manual: PostgreSQL 9.5.2 Documentation .\" Source: PostgreSQL 9.5.2 .\" Language: English .\" .TH "ALTER FUNCTION" "5sql" "2016" "PostgreSQL 9.5.2" "PostgreSQL 9.5.2 Documentation" .\" ----------------------------------------------------------------- .\" * set default formatting .\" ----------------------------------------------------------------- .\" disable hyphenation .nh .\" disable justification (adjust text to left margin only) .ad l .\" ----------------------------------------------------------------- .\" * MAIN CONTENT STARTS HERE * .\" ----------------------------------------------------------------- .SH "NAME" ALTER_FUNCTION \- change the definition of a function .SH "SYNOPSIS" .sp .nf ALTER FUNCTION \fIname\fR ( [ [ \fIargmode\fR ] [ \fIargname\fR ] \fIargtype\fR [, \&.\&.\&.] ] ) \fIaction\fR [ \&.\&.\&. ] [ RESTRICT ] ALTER FUNCTION \fIname\fR ( [ [ \fIargmode\fR ] [ \fIargname\fR ] \fIargtype\fR [, \&.\&.\&.] ] ) RENAME TO \fInew_name\fR ALTER FUNCTION \fIname\fR ( [ [ \fIargmode\fR ] [ \fIargname\fR ] \fIargtype\fR [, \&.\&.\&.] ] ) OWNER TO { \fInew_owner\fR | CURRENT_USER | SESSION_USER } ALTER FUNCTION \fIname\fR ( [ [ \fIargmode\fR ] [ \fIargname\fR ] \fIargtype\fR [, \&.\&.\&.] ] ) SET SCHEMA \fInew_schema\fR where \fIaction\fR is one of: CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT IMMUTABLE | STABLE | VOLATILE | [ NOT ] LEAKPROOF [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER COST \fIexecution_cost\fR ROWS \fIresult_rows\fR SET \fIconfiguration_parameter\fR { TO | = } { \fIvalue\fR | DEFAULT } SET \fIconfiguration_parameter\fR FROM CURRENT RESET \fIconfiguration_parameter\fR RESET ALL .fi .SH "DESCRIPTION" .PP \fBALTER FUNCTION\fR changes the definition of a function\&. .PP You must own the function to use \fBALTER FUNCTION\fR\&. To change a function\(aqs schema, you must also have CREATE privilege on the new schema\&. To alter the owner, you must also be a direct or indirect member of the new owning role, and that role must have CREATE privilege on the function\(aqs schema\&. (These restrictions enforce that altering the owner doesn\(aqt do anything you couldn\(aqt do by dropping and recreating the function\&. However, a superuser can alter ownership of any function anyway\&.) .SH "PARAMETERS" .PP \fIname\fR .RS 4 The name (optionally schema\-qualified) of an existing function\&. .RE .PP \fIargmode\fR .RS 4 The mode of an argument: IN, OUT, INOUT, or VARIADIC\&. If omitted, the default is IN\&. Note that \fBALTER FUNCTION\fR does not actually pay any attention to OUT arguments, since only the input arguments are needed to determine the function\(aqs identity\&. So it is sufficient to list the IN, INOUT, and VARIADIC arguments\&. .RE .PP \fIargname\fR .RS 4 The name of an argument\&. Note that \fBALTER FUNCTION\fR does not actually pay any attention to argument names, since only the argument data types are needed to determine the function\(aqs identity\&. .RE .PP \fIargtype\fR .RS 4 The data type(s) of the function\(aqs arguments (optionally schema\-qualified), if any\&. .RE .PP \fInew_name\fR .RS 4 The new name of the function\&. .RE .PP \fInew_owner\fR .RS 4 The new owner of the function\&. Note that if the function is marked SECURITY DEFINER, it will subsequently execute as the new owner\&. .RE .PP \fInew_schema\fR .RS 4 The new schema for the function\&. .RE .PP CALLED ON NULL INPUT .br RETURNS NULL ON NULL INPUT .br STRICT .RS 4 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 is not invoked if any of its arguments are null; instead, a null result is assumed automatically\&. See CREATE FUNCTION (\fBCREATE_FUNCTION\fR(5)) for more information\&. .RE .PP IMMUTABLE .br STABLE .br VOLATILE .RS 4 Change the volatility of the function to the specified setting\&. See CREATE FUNCTION (\fBCREATE_FUNCTION\fR(5)) for details\&. .RE .PP [ EXTERNAL ] SECURITY INVOKER .br [ EXTERNAL ] SECURITY DEFINER .RS 4 Change whether the function is a security definer or not\&. The key word EXTERNAL is ignored for SQL conformance\&. See CREATE FUNCTION (\fBCREATE_FUNCTION\fR(5)) for more information about this capability\&. .RE .PP LEAKPROOF .RS 4 Change whether the function is considered leakproof or not\&. See CREATE FUNCTION (\fBCREATE_FUNCTION\fR(5)) for more information about this capability\&. .RE .PP COST \fIexecution_cost\fR .RS 4 Change the estimated execution cost of the function\&. See CREATE FUNCTION (\fBCREATE_FUNCTION\fR(5)) for more information\&. .RE .PP ROWS \fIresult_rows\fR .RS 4 Change the estimated number of rows returned by a set\-returning function\&. See CREATE FUNCTION (\fBCREATE_FUNCTION\fR(5)) for more information\&. .RE .PP \fIconfiguration_parameter\fR .br \fIvalue\fR .RS 4 Add or change the assignment to be made to a configuration parameter when the function is called\&. If \fIvalue\fR is DEFAULT or, equivalently, RESET is used, the function\-local setting is removed, so that the function executes with the value present in its environment\&. Use RESET ALL to clear all function\-local settings\&. SET FROM CURRENT saves the session\(aqs current value of the parameter as the value to be applied when the function is entered\&. .sp See \fBSET\fR(5) and Chapter 18, Server Configuration, in the documentation for more information about allowed parameter names and values\&. .RE .PP RESTRICT .RS 4 Ignored for conformance with the SQL standard\&. .RE .SH "EXAMPLES" .PP To rename the function sqrt for type integer to square_root: .sp .if n \{\ .RS 4 .\} .nf ALTER FUNCTION sqrt(integer) RENAME TO square_root; .fi .if n \{\ .RE .\} .PP To change the owner of the function sqrt for type integer to joe: .sp .if n \{\ .RS 4 .\} .nf ALTER FUNCTION sqrt(integer) OWNER TO joe; .fi .if n \{\ .RE .\} .PP To change the schema of the function sqrt for type integer to maths: .sp .if n \{\ .RS 4 .\} .nf ALTER FUNCTION sqrt(integer) SET SCHEMA maths; .fi .if n \{\ .RE .\} .PP To adjust the search path that is automatically set for a function: .sp .if n \{\ .RS 4 .\} .nf ALTER FUNCTION check_password(text) SET search_path = admin, pg_temp; .fi .if n \{\ .RE .\} .PP To disable automatic setting of \fIsearch_path\fR for a function: .sp .if n \{\ .RS 4 .\} .nf ALTER FUNCTION check_password(text) RESET search_path; .fi .if n \{\ .RE .\} .sp The function will now execute with whatever search path is used by its caller\&. .SH "COMPATIBILITY" .PP This statement is partially compatible with the \fBALTER FUNCTION\fR 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, attach configuration parameter values to a function, or change the owner, schema, or volatility of a function\&. The standard also requires the RESTRICT key word, which is optional in PostgreSQL\&. .SH "SEE ALSO" CREATE FUNCTION (\fBCREATE_FUNCTION\fR(5)), DROP FUNCTION (\fBDROP_FUNCTION\fR(5))