1CALL nonexistent();  -- error
2CALL random();  -- error
3
4CREATE FUNCTION cp_testfunc1(a int) RETURNS int LANGUAGE SQL AS $$ SELECT a $$;
5
6CREATE TABLE cp_test (a int, b text);
7
8CREATE PROCEDURE ptest1(x text)
9LANGUAGE SQL
10AS $$
11INSERT INTO cp_test VALUES (1, x);
12$$;
13
14\df ptest1
15SELECT pg_get_functiondef('ptest1'::regproc);
16
17-- show only normal functions
18\dfn public.*test*1
19
20-- show only procedures
21\dfp public.*test*1
22
23SELECT ptest1('x');  -- error
24CALL ptest1('a');  -- ok
25CALL ptest1('xy' || 'zzy');  -- ok, constant-folded arg
26CALL ptest1(substring(random()::numeric(20,15)::text, 1, 1));  -- ok, volatile arg
27
28SELECT * FROM cp_test ORDER BY b COLLATE "C";
29
30
31CREATE PROCEDURE ptest2()
32LANGUAGE SQL
33AS $$
34SELECT 5;
35$$;
36
37CALL ptest2();
38
39
40-- nested CALL
41TRUNCATE cp_test;
42
43CREATE PROCEDURE ptest3(y text)
44LANGUAGE SQL
45AS $$
46CALL ptest1(y);
47CALL ptest1($1);
48$$;
49
50CALL ptest3('b');
51
52SELECT * FROM cp_test;
53
54
55-- output arguments
56
57CREATE PROCEDURE ptest4a(INOUT a int, INOUT b int)
58LANGUAGE SQL
59AS $$
60SELECT 1, 2;
61$$;
62
63CALL ptest4a(NULL, NULL);
64
65CREATE PROCEDURE ptest4b(INOUT b int, INOUT a int)
66LANGUAGE SQL
67AS $$
68CALL ptest4a(a, b);  -- error, not supported
69$$;
70
71DROP PROCEDURE ptest4a;
72
73
74-- named and default parameters
75
76CREATE OR REPLACE PROCEDURE ptest5(a int, b text, c int default 100)
77LANGUAGE SQL
78AS $$
79INSERT INTO cp_test VALUES(a, b);
80INSERT INTO cp_test VALUES(c, b);
81$$;
82
83TRUNCATE cp_test;
84
85CALL ptest5(10, 'Hello', 20);
86CALL ptest5(10, 'Hello');
87CALL ptest5(10, b => 'Hello');
88CALL ptest5(b => 'Hello', a => 10);
89
90SELECT * FROM cp_test;
91
92
93-- polymorphic types
94
95CREATE PROCEDURE ptest6(a int, b anyelement)
96LANGUAGE SQL
97AS $$
98SELECT NULL::int;
99$$;
100
101CALL ptest6(1, 2);
102
103
104-- collation assignment
105
106CREATE PROCEDURE ptest7(a text, b text)
107LANGUAGE SQL
108AS $$
109SELECT a = b;
110$$;
111
112CALL ptest7(least('a', 'b'), 'a');
113
114
115-- various error cases
116
117CALL version();  -- error: not a procedure
118CALL sum(1);  -- error: not a procedure
119
120CREATE PROCEDURE ptestx() LANGUAGE SQL WINDOW AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
121CREATE PROCEDURE ptestx() LANGUAGE SQL STRICT AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
122CREATE PROCEDURE ptestx(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
123
124ALTER PROCEDURE ptest1(text) STRICT;
125ALTER FUNCTION ptest1(text) VOLATILE;  -- error: not a function
126ALTER PROCEDURE cp_testfunc1(int) VOLATILE;  -- error: not a procedure
127ALTER PROCEDURE nonexistent() VOLATILE;
128
129DROP FUNCTION ptest1(text);  -- error: not a function
130DROP PROCEDURE cp_testfunc1(int);  -- error: not a procedure
131DROP PROCEDURE nonexistent();
132
133
134-- privileges
135
136CREATE USER regress_cp_user1;
137GRANT INSERT ON cp_test TO regress_cp_user1;
138REVOKE EXECUTE ON PROCEDURE ptest1(text) FROM PUBLIC;
139SET ROLE regress_cp_user1;
140CALL ptest1('a');  -- error
141RESET ROLE;
142GRANT EXECUTE ON PROCEDURE ptest1(text) TO regress_cp_user1;
143SET ROLE regress_cp_user1;
144CALL ptest1('a');  -- ok
145RESET ROLE;
146
147
148-- ROUTINE syntax
149
150ALTER ROUTINE cp_testfunc1(int) RENAME TO cp_testfunc1a;
151ALTER ROUTINE cp_testfunc1a RENAME TO cp_testfunc1;
152
153ALTER ROUTINE ptest1(text) RENAME TO ptest1a;
154ALTER ROUTINE ptest1a RENAME TO ptest1;
155
156DROP ROUTINE cp_testfunc1(int);
157
158
159-- cleanup
160
161DROP PROCEDURE ptest1;
162DROP PROCEDURE ptest2;
163
164DROP TABLE cp_test;
165
166DROP USER regress_cp_user1;
167