2 -- Tests for procedures / CALL syntax
4 CREATE PROCEDURE test_proc1()
12 -- error: can't return non-NULL
13 CREATE PROCEDURE test_proc2()
20 ERROR: RETURN cannot have a parameter in a procedure
23 CREATE TABLE test1 (a int);
24 CREATE PROCEDURE test_proc3(x int)
28 INSERT INTO test1 VALUES (x);
40 CREATE PROCEDURE test_proc4(y int)
67 CREATE PROCEDURE test_proc5(INOUT a text)
74 CALL test_proc5('abc');
80 CREATE PROCEDURE test_proc6(a int, INOUT b int, INOUT c int)
88 CALL test_proc6(2, 3, 4);
101 CALL test_proc6(2, x, y);
102 RAISE INFO 'x = %, y = %', x, y;
113 CALL test_proc6(2, x + 1, y); -- error
114 RAISE INFO 'x = %, y = %', x, y;
117 ERROR: argument 2 is an output argument but is not writable
118 CONTEXT: PL/pgSQL function inline_code_block line 6 at CALL
127 CALL test_proc6(i, x, y);
128 RAISE INFO 'x = %, y = %', x, y;
136 INFO: x = 360, y = 480
137 -- recursive with output arguments
138 CREATE PROCEDURE test_proc7(x int, INOUT a int, INOUT b numeric)
145 CALL test_proc7(b::int, a, b);
149 CALL test_proc7(100, -1, -1);
155 -- transition variable assignment
157 CREATE FUNCTION triggerfunc1() RETURNS trigger
163 CALL test_proc6(2, NEW.a, NEW.a);
167 CREATE TRIGGER t1 BEFORE INSERT ON test1 EXECUTE PROCEDURE triggerfunc1();
168 INSERT INTO test1 VALUES (1), (2), (3);
169 UPDATE test1 SET a = 22 WHERE a = 2;
170 SELECT * FROM test1 ORDER BY a;
178 DROP PROCEDURE test_proc1;
179 DROP PROCEDURE test_proc3;
180 DROP PROCEDURE test_proc4;