blob: f09ba2ad30c4834c734e6a2412ed7ff93df6bfee (
plain)
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
|
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
\df ptest1
SELECT * FROM cp_test ORDER BY a;
CREATE PROCEDURE ptest2()
LANGUAGE SQL
AS $$
SELECT 5;
$$;
CALL ptest2();
-- various error cases
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;
|
