ALTER_PROCEDURE(7)

1ALTER PROCEDURE(7)       PostgreSQL 14.3 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_ROLE | CURRENT_USER | SESSION_USER }
15       ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
16           SET SCHEMA new_schema
17       ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ]
18           [ NO ] 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, OUT, INOUT, or VARIADIC. If omitted,
48           the default is 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 used to determine the procedure's identity.
54
55       argtype
56           The data type(s) of the procedure's arguments (optionally
57           schema-qualified), if any. See DROP PROCEDURE (DROP_PROCEDURE(7))
58           for the details of how the procedure is looked up using the
59           argument data type(s).
60
61       new_name
62           The new name of the procedure.
63
64       new_owner
65           The new owner of the procedure. Note that if the procedure is
66           marked SECURITY DEFINER, it will subsequently execute as the new
67           owner.
68
69       new_schema
70           The new schema for the procedure.
71
72       extension_name
73           The name of the extension that the procedure is to depend on.
74
75       [ EXTERNAL ] SECURITY INVOKER
76       [ EXTERNAL ] SECURITY DEFINER
77           Change whether the procedure is a security definer or not. The key
78           word EXTERNAL is ignored for SQL conformance. See CREATE PROCEDURE
79           (CREATE_PROCEDURE(7)) for more information about this capability.
80
81       configuration_parameter
82       value
83           Add or change the assignment to be made to a configuration
84           parameter when the procedure is called. If value is DEFAULT or,
85           equivalently, RESET is used, the procedure-local setting is
86           removed, so that the procedure executes with the value present in
87           its environment. Use RESET ALL to clear all procedure-local
88           settings.  SET FROM CURRENT saves the value of the parameter that
89           is current when ALTER PROCEDURE is executed as the value to be
90           applied when the procedure is entered.
91
92           See SET(7) and Chapter 20 for more information about allowed
93           parameter names and values.
94
95       RESTRICT
96           Ignored for conformance with the SQL standard.
97

EXAMPLES

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

COMPATIBILITY

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

NAME

SYNOPSIS

DESCRIPTION

PARAMETERS

EXAMPLES

COMPATIBILITY

SEE ALSO