1LOAD 'plpgsql';
2CREATE EXTENSION  IF NOT EXISTS plpgsql_check;
3set client_min_messages to notice;
4
5create or replace procedure proc(a int)
6as $$
7begin
8end;
9$$ language plpgsql;
10
11call proc(10);
12
13select * from plpgsql_check_function('proc(int)');
14
15create or replace procedure testproc()
16as $$
17begin
18  call proc(10);
19end;
20$$ language plpgsql;
21
22call testproc();
23
24select * from plpgsql_check_function('testproc()');
25
26-- should to fail
27create or replace procedure testproc()
28as $$
29begin
30  call proc((select count(*) from pg_class));
31end;
32$$ language plpgsql;
33
34call testproc();
35
36select * from plpgsql_check_function('testproc()');
37
38drop procedure proc(int);
39
40create procedure proc(in a int, inout b int, in c int)
41as $$
42begin
43end;
44$$ language plpgsql;
45
46select * from plpgsql_check_function('proc(int,int, int)');
47
48create or replace procedure proc(in a int, inout b int, in c int)
49as $$
50begin
51  b := a + c;
52end;
53$$ language plpgsql;
54
55select * from plpgsql_check_function('proc(int,int, int)');
56
57create or replace procedure testproc()
58as $$
59declare r int;
60begin
61  call proc(10, r, 20);
62end;
63$$ language plpgsql;
64
65call testproc();
66
67select * from plpgsql_check_function('testproc()');
68
69-- should to fail
70create or replace procedure testproc()
71as $$
72declare r int;
73begin
74  call proc(10, r + 10, 20);
75end;
76$$ language plpgsql;
77
78call testproc();
79
80select * from plpgsql_check_function('testproc()');
81
82create or replace procedure testproc(inout r int)
83as $$
84begin
85  call proc(10, r, 20);
86end;
87$$ language plpgsql;
88
89call testproc(10);
90
91select * from plpgsql_check_function('testproc(int)');
92
93drop procedure testproc(int);
94
95-- should to raise warnings
96create or replace procedure testproc2(in p1 int, inout p2 int, in p3 int, inout p4 int)
97as $$
98begin
99  raise notice '% %', p1, p3;
100end;
101$$ language plpgsql;
102
103select * from plpgsql_check_function('testproc2');
104
105drop procedure testproc2;
106
107-- should be ok
108create or replace procedure testproc3(in p1 int, inout p2 int, in p3 int, inout p4 int)
109as $$
110begin
111  p2 := p1;
112  p4 := p3;
113end;
114$$ language plpgsql;
115
116select * from plpgsql_check_function('testproc3');
117
118drop procedure testproc3;
119