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
|
CALL nonexistent(); -- error
CALL random(); -- error
CREATE FUNCTION 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);
$$;
SELECT ptest1('x'); -- error
CALL ptest1('a'); -- ok
CALL ptest1('xy' || 'zzy'); -- ok, constant-folded arg
CALL ptest1(substring(random()::text, 1, 1)); -- ok, volatile arg
\df ptest1
SELECT pg_get_functiondef('ptest1'::regproc);
SELECT * FROM cp_test ORDER BY b COLLATE "C";
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;
-- output arguments
CREATE PROCEDURE ptest4a(INOUT a int, INOUT b int)
LANGUAGE SQL
AS $$
SELECT 1, 2;
$$;
CALL ptest4a(NULL, NULL);
CREATE PROCEDURE ptest4b(INOUT b int, INOUT a int)
LANGUAGE SQL
AS $$
CALL ptest4a(a, b); -- error, not supported
$$;
DROP PROCEDURE ptest4a;
-- various error cases
CALL version(); -- error: not a procedure
CALL sum(1); -- error: not a procedure
CREATE PROCEDURE ptestx() LANGUAGE SQL WINDOW AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
CREATE PROCEDURE ptestx() LANGUAGE SQL STRICT AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
CREATE PROCEDURE ptestx(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test VALUES (1, 'a') $$;
ALTER PROCEDURE ptest1(text) STRICT;
ALTER FUNCTION ptest1(text) VOLATILE; -- error: not a function
ALTER PROCEDURE testfunc1(int) VOLATILE; -- error: not a procedure
ALTER PROCEDURE nonexistent() VOLATILE;
DROP FUNCTION ptest1(text); -- error: not a function
DROP PROCEDURE testfunc1(int); -- error: not a procedure
DROP PROCEDURE nonexistent();
-- privileges
CREATE USER regress_user1;
GRANT INSERT ON cp_test TO regress_user1;
REVOKE EXECUTE ON PROCEDURE ptest1(text) FROM PUBLIC;
SET ROLE regress_user1;
CALL ptest1('a'); -- error
RESET ROLE;
GRANT EXECUTE ON PROCEDURE ptest1(text) TO regress_user1;
SET ROLE regress_user1;
CALL ptest1('a'); -- ok
RESET ROLE;
-- ROUTINE syntax
ALTER ROUTINE testfunc1(int) RENAME TO testfunc1a;
ALTER ROUTINE testfunc1a RENAME TO testfunc1;
ALTER ROUTINE ptest1(text) RENAME TO ptest1a;
ALTER ROUTINE ptest1a RENAME TO ptest1;
DROP ROUTINE testfunc1(int);
-- cleanup
DROP PROCEDURE ptest1;
DROP PROCEDURE ptest2;
DROP TABLE cp_test;
DROP USER regress_user1;
|
