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
120
121
122
|
LOAD 'plpgsql';
CREATE EXTENSION IF NOT EXISTS plpgsql_check;
NOTICE: extension "plpgsql_check" already exists, skipping
set client_min_messages to notice;
create or replace function fxtest()
returns void as $$
declare
v_sqlstate text;
v_message text;
v_context text;
begin
get stacked diagnostics
v_sqlstate = returned_sqlstate,
v_message = message_text,
v_context = pg_exception_context;
end;
$$ language plpgsql;
select * from plpgsql_check_function('fxtest');
plpgsql_check_function
-----------------------------------------------------------------------------------------------------------
error:0Z002:7:GET STACKED DIAGNOSTICS:GET STACKED DIAGNOSTICS cannot be used outside an exception handler
(1 row)
drop function fxtest();
create or replace procedure prtest()
as $$
begin
commit;
end;
$$ language plpgsql;
select * from plpgsql_check_function('prtest'); --ok
plpgsql_check_function
------------------------
(0 rows)
create or replace procedure prtest()
as $$
begin
begin
begin
commit;
end;
end;
exception when others then
raise;
end;
$$ language plpgsql;
select * from plpgsql_check_function('prtest'); --error
plpgsql_check_function
---------------------------------------------------------------------
error:2D000:5:COMMIT:cannot commit while a subtransaction is active
(1 row)
create or replace procedure prtest()
as $$
begin
raise exception 'error';
exception when others then
begin
begin
commit;
end;
end;
end;
$$ language plpgsql;
select * from plpgsql_check_function('prtest'); --ok
plpgsql_check_function
------------------------
(0 rows)
drop procedure prtest();
create function return_constant_refcursor() returns refcursor as $$
declare
rc constant refcursor;
begin
open rc for select a from rc_test;
return rc;
end
$$ language plpgsql;
create table rc_test(a int);
select * from plpgsql_check_function('return_constant_refcursor');
plpgsql_check_function
-------------------------------------------------------
error:22005:5:OPEN:variable "rc" is declared CONSTANT
(1 row)
drop table rc_test;
drop function return_constant_refcursor();
create procedure p1(a int, out b int)
as $$
begin
b := a + 10;
end;
$$ language plpgsql;
create function f1()
returns void as $$
declare b constant int;
begin
call p1(10, b);
end;
$$ language plpgsql;
select * from plpgsql_check_function('f1');
plpgsql_check_function
------------------------------------------------------
error:22005:4:CALL:variable "b" is declared CONSTANT
(1 row)
drop function f1();
drop procedure p1(int, int);
create or replace function f1()
returns int as $$
declare c constant int default 100;
begin
return c;
end;
$$ language plpgsql;
-- should be ok
select * from plpgsql_check_function('f1');
plpgsql_check_function
------------------------
(0 rows)
|
