1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
|
CALL nonexistent(); -- error
ERROR: procedure nonexistent() does not exist
LINE 1: CALL nonexistent();
^
HINT: No procedure matches the given name and argument types. You might need to add explicit type casts.
CALL random(); -- error
ERROR: random() is not a procedure
LINE 1: CALL random();
^
HINT: To call a function, use SELECT.
CREATE FUNCTION cp_testfunc1(a int) RETURNS int LANGUAGE SQL AS $$ SELECT a $$;
CREATE TABLE cp_test (a int, b text);
CREATE PROCEDURE ptest1(x text)
LANGUAGE SQL
AS $$
INSERT INTO cp_test VALUES (1, x);
$$;
\df ptest1
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------+------------------+---------------------+------
public | ptest1 | | x text | proc
(1 row)
SELECT pg_get_functiondef('ptest1'::regproc);
pg_get_functiondef
---------------------------------------------------
CREATE OR REPLACE PROCEDURE public.ptest1(x text)+
LANGUAGE sql +
AS $procedure$ +
INSERT INTO cp_test VALUES (1, x); +
$procedure$ +
(1 row)
-- show only normal functions
\dfn public.*test*1
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------------+------------------+---------------------+------
public | cp_testfunc1 | integer | a integer | func
(1 row)
-- show only procedures
\dfp public.*test*1
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------+------------------+---------------------+------
public | ptest1 | | x text | proc
(1 row)
SELECT ptest1('x'); -- error
ERROR: ptest1(unknown) is a procedure
LINE 1: SELECT ptest1('x');
^
HINT: To call a procedure, use CALL.
CALL ptest1('a'); -- ok
CALL ptest1('xy' || 'zzy'); -- ok, constant-folded arg
CALL ptest1(substring(random()::numeric(20,15)::text, 1, 1)); -- ok, volatile arg
SELECT * FROM cp_test ORDER BY b COLLATE "C";
a | b
---+-------
1 | 0
1 | a
1 | xyzzy
(3 rows)
CREATE PROCEDURE ptest2()
LANGUAGE SQL
AS $$
SELECT 5;
$$;
CALL ptest2();
-- nested CALL
TRUNCATE cp_test;
CREATE PROCEDURE ptest3(y text)
LANGUAGE SQL
AS $$
CALL ptest1(y);
CALL ptest1($1);
$$;
CALL ptest3('b');
SELECT * FROM cp_test;
a | b
---+---
1 | b
1 | b
(2 rows)
-- output arguments
CREATE PROCEDURE ptest4a(INOUT a int, INOUT b int)
LANGUAGE SQL
AS $$
SELECT 1, 2;
$$;
CALL ptest4a(NULL, NULL);
a | b
---+---
1 | 2
(1 row)
CREATE PROCEDURE ptest4b(INOUT b int, INOUT a int)
LANGUAGE SQL
AS $$
CALL ptest4a(a, b); -- error, not supported
$$;
ERROR: calling procedures with output arguments is not supported in SQL functions
CONTEXT: SQL function "ptest4b"
DROP PROCEDURE ptest4a;
-- named and default parameters
CREATE OR REPLACE PROCEDURE ptest5(a int, b text, c int default 100)
LANGUAGE SQL
AS $$
INSERT INTO cp_test VALUES(a, b);
INSERT INTO cp_test VALUES(c, b);
$$;
TRUNCATE cp_test;
CALL ptest5(10, 'Hello', 20);
CALL ptest5(10, 'Hello');
CALL ptest5(10, b => 'Hello');
CALL ptest5(b => 'Hello', a => 10);
SELECT * FROM cp_test;
a | b
-----+-------
10 | Hello
20 | Hello
10 | Hello
100 | Hello
10 | Hello
100 | Hello
10 | Hello
100 | Hello
(8 rows)
-- polymorphic types
CREATE PROCEDURE ptest6(a int, b anyelement)
LANGUAGE SQL
AS $$
SELECT NULL::int;
$$;
CALL ptest6(1, 2);
-- collation assignment
CREATE PROCEDURE ptest7(a text, b text)
LANGUAGE SQL
AS $$
SELECT a = b;
$$;
CALL ptest7(least('a', 'b'), 'a');
-- various error cases
CALL version(); -- error: not a procedure
ERROR: version() is not a procedure
LINE 1: CALL version();
^
HINT: To call a function, use SELECT.
CALL sum(1); -- error: not a procedure
ERROR: sum(integer) is not a procedure
LINE 1: CALL sum(1);
^
HINT: To call a function, use SELECT.
CREATE PROCEDURE ptestx() LANGUAGE SQL WINDOW AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
ERROR: invalid attribute in procedure definition
LINE 1: CREATE PROCEDURE ptestx() LANGUAGE SQL WINDOW AS $$ INSERT I...
^
CREATE PROCEDURE ptestx() LANGUAGE SQL STRICT AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
ERROR: invalid attribute in procedure definition
LINE 1: CREATE PROCEDURE ptestx() LANGUAGE SQL STRICT AS $$ INSERT I...
^
CREATE PROCEDURE ptestx(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
ERROR: procedures cannot have OUT arguments
HINT: INOUT arguments are permitted.
ALTER PROCEDURE ptest1(text) STRICT;
ERROR: invalid attribute in procedure definition
LINE 1: ALTER PROCEDURE ptest1(text) STRICT;
^
ALTER FUNCTION ptest1(text) VOLATILE; -- error: not a function
ERROR: ptest1(text) is not a function
ALTER PROCEDURE cp_testfunc1(int) VOLATILE; -- error: not a procedure
ERROR: cp_testfunc1(integer) is not a procedure
ALTER PROCEDURE nonexistent() VOLATILE;
ERROR: procedure nonexistent() does not exist
DROP FUNCTION ptest1(text); -- error: not a function
ERROR: ptest1(text) is not a function
DROP PROCEDURE cp_testfunc1(int); -- error: not a procedure
ERROR: cp_testfunc1(integer) is not a procedure
DROP PROCEDURE nonexistent();
ERROR: procedure nonexistent() does not exist
-- privileges
CREATE USER regress_cp_user1;
GRANT INSERT ON cp_test TO regress_cp_user1;
REVOKE EXECUTE ON PROCEDURE ptest1(text) FROM PUBLIC;
SET ROLE regress_cp_user1;
CALL ptest1('a'); -- error
ERROR: permission denied for procedure ptest1
RESET ROLE;
GRANT EXECUTE ON PROCEDURE ptest1(text) TO regress_cp_user1;
SET ROLE regress_cp_user1;
CALL ptest1('a'); -- ok
RESET ROLE;
-- ROUTINE syntax
ALTER ROUTINE cp_testfunc1(int) RENAME TO cp_testfunc1a;
ALTER ROUTINE cp_testfunc1a RENAME TO cp_testfunc1;
ALTER ROUTINE ptest1(text) RENAME TO ptest1a;
ALTER ROUTINE ptest1a RENAME TO ptest1;
DROP ROUTINE cp_testfunc1(int);
-- cleanup
DROP PROCEDURE ptest1;
DROP PROCEDURE ptest2;
DROP TABLE cp_test;
DROP USER regress_cp_user1;
|
