ALTER_PROCEDURE(7)

1ALTER PROCEDURE(7)       PostgreSQL 12.2 Documentation      ALTER PROCEDURE(7)
2
3
4

NAME

6       ALTER_PROCEDURE - change the definition of a procedure
7

SYNOPSIS

9       ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
10           action [ ... ] [ RESTRICT ]
11       ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
12           RENAME TO new_name
13       ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
14           OWNER TO { new_owner | CURRENT_USER | SESSION_USER }
15       ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
16           SET SCHEMA new_schema
17       ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
18           DEPENDS ON EXTENSION extension_name
19
20       where action is one of:
21
22           [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
23           SET configuration_parameter { TO | = } { value | DEFAULT }
24           SET configuration_parameter FROM CURRENT
25           RESET configuration_parameter
26           RESET ALL
27

DESCRIPTION

29       ALTER PROCEDURE changes the definition of a procedure.
30
31       You must own the procedure to use ALTER PROCEDURE. To change a
32       procedure's schema, you must also have CREATE privilege on the new
33       schema. To alter the owner, you must also be a direct or indirect
34       member of the new owning role, and that role must have CREATE privilege
35       on the procedure's schema. (These restrictions enforce that altering
36       the owner doesn't do anything you couldn't do by dropping and
37       recreating the procedure. However, a superuser can alter ownership of
38       any procedure anyway.)
39

PARAMETERS

41       name
42           The name (optionally schema-qualified) of an existing procedure. If
43           no argument list is specified, the name must be unique in its
44           schema.
45
46       argmode
47           The mode of an argument: IN or VARIADIC. If omitted, the default is
48           IN.
49
50       argname
51           The name of an argument. Note that ALTER PROCEDURE does not
52           actually pay any attention to argument names, since only the
53           argument data types are needed to determine the procedure's
54           identity.
55
56       argtype
57           The data type(s) of the procedure's arguments (optionally
58           schema-qualified), if any.
59
60       new_name
61           The new name of the procedure.
62
63       new_owner
64           The new owner of the procedure. Note that if the procedure is
65           marked SECURITY DEFINER, it will subsequently execute as the new
66           owner.
67
68       new_schema
69           The new schema for the procedure.
70
71       extension_name
72           The name of the extension that the procedure is to depend on.
73
74       [ EXTERNAL ] SECURITY INVOKER
75       [ EXTERNAL ] SECURITY DEFINER
76           Change whether the procedure is a security definer or not. The key
77           word EXTERNAL is ignored for SQL conformance. See CREATE PROCEDURE
78           (CREATE_PROCEDURE(7)) for more information about this capability.
79
80       configuration_parameter
81       value
82           Add or change the assignment to be made to a configuration
83           parameter when the procedure is called. If value is DEFAULT or,
84           equivalently, RESET is used, the procedure-local setting is
85           removed, so that the procedure executes with the value present in
86           its environment. Use RESET ALL to clear all procedure-local
87           settings.  SET FROM CURRENT saves the value of the parameter that
88           is current when ALTER PROCEDURE is executed as the value to be
89           applied when the procedure is entered.
90
91           See SET(7) and Chapter 19 for more information about allowed
92           parameter names and values.
93
94       RESTRICT
95           Ignored for conformance with the SQL standard.
96

EXAMPLES

98       To rename the procedure insert_data with two arguments of type integer
99       to insert_record:
100
101           ALTER PROCEDURE insert_data(integer, integer) RENAME TO insert_record;
102
103       To change the owner of the procedure insert_data with two arguments of
104       type integer to joe:
105
106           ALTER PROCEDURE insert_data(integer, integer) OWNER TO joe;
107
108       To change the schema of the procedure insert_data with two arguments of
109       type integer to accounting:
110
111           ALTER PROCEDURE insert_data(integer, integer) SET SCHEMA accounting;
112
113       To mark the procedure insert_data(integer, integer) as being dependent
114       on the extension myext:
115
116           ALTER PROCEDURE insert_data(integer, integer) DEPENDS ON EXTENSION myext;
117
118       To adjust the search path that is automatically set for a procedure:
119
120           ALTER PROCEDURE check_password(text) SET search_path = admin, pg_temp;
121
122       To disable automatic setting of search_path for a procedure:
123
124           ALTER PROCEDURE check_password(text) RESET search_path;
125
126       The procedure will now execute with whatever search path is used by its
127       caller.
128

COMPATIBILITY

130       This statement is partially compatible with the ALTER PROCEDURE
131       statement in the SQL standard. The standard allows more properties of a
132       procedure to be modified, but does not provide the ability to rename a
133       procedure, make a procedure a security definer, attach configuration
134       parameter values to a procedure, or change the owner, schema, or
135       volatility of a procedure. The standard also requires the RESTRICT key
136       word, which is optional in PostgreSQL.
137

NAME

SYNOPSIS

DESCRIPTION

PARAMETERS

EXAMPLES

COMPATIBILITY

SEE ALSO