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