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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
|
--
-- CREATE FUNCTION
--
-- sanity check of pg_proc catalog to the given parameters
--
CREATE SCHEMA temp_func_test;
SET search_path TO temp_func_test, public;
--
-- ARGUMENT and RETURN TYPES
--
CREATE FUNCTION functest_A_1(text, date) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 = ''abcd'' AND $2 > ''2001-01-01''';
CREATE FUNCTION functest_A_2(text[]) RETURNS int LANGUAGE 'sql'
AS 'SELECT $1[0]::int';
CREATE FUNCTION functest_A_3() RETURNS bool LANGUAGE 'sql'
AS 'SELECT false';
SELECT proname, prorettype::regtype, proargtypes::regtype[] FROM pg_proc
WHERE oid in ('functest_A_1'::regproc,
'functest_A_2'::regproc,
'functest_A_3'::regproc);
--
-- IMMUTABLE | STABLE | VOLATILE
--
CREATE FUNCTION functest_B_1(int) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 > 0';
CREATE FUNCTION functest_B_2(int) RETURNS bool LANGUAGE 'sql'
IMMUTABLE AS 'SELECT $1 > 0';
CREATE FUNCTION functest_B_3(int) RETURNS bool LANGUAGE 'sql'
STABLE AS 'SELECT $1 = 0';
CREATE FUNCTION functest_B_4(int) RETURNS bool LANGUAGE 'sql'
VOLATILE AS 'SELECT $1 < 0';
SELECT proname, provolatile FROM pg_proc
WHERE oid in ('functest_B_1'::regproc,
'functest_B_2'::regproc,
'functest_B_3'::regproc,
'functest_B_4'::regproc);
ALTER FUNCTION functest_B_2(int) VOLATILE;
ALTER FUNCTION functest_B_3(int) COST 100; -- unrelated change, no effect
SELECT proname, provolatile FROM pg_proc
WHERE oid in ('functest_B_1'::regproc,
'functest_B_2'::regproc,
'functest_B_3'::regproc,
'functest_B_4'::regproc);
--
-- SECURITY DEFINER | INVOKER
--
CREATE FUNCTION functext_C_1(int) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 > 0';
CREATE FUNCTION functext_C_2(int) RETURNS bool LANGUAGE 'sql'
SECURITY DEFINER AS 'SELECT $1 = 0';
CREATE FUNCTION functext_C_3(int) RETURNS bool LANGUAGE 'sql'
SECURITY INVOKER AS 'SELECT $1 < 0';
SELECT proname, prosecdef FROM pg_proc
WHERE oid in ('functext_C_1'::regproc,
'functext_C_2'::regproc,
'functext_C_3'::regproc);
ALTER FUNCTION functext_C_1(int) IMMUTABLE; -- unrelated change, no effect
ALTER FUNCTION functext_C_2(int) SECURITY INVOKER;
ALTER FUNCTION functext_C_3(int) SECURITY DEFINER;
SELECT proname, prosecdef FROM pg_proc
WHERE oid in ('functext_C_1'::regproc,
'functext_C_2'::regproc,
'functext_C_3'::regproc);
--
-- COST
--
CREATE FUNCTION functext_D_1(int,int) RETURNS int LANGUAGE 'sql'
AS 'SELECT $1 + $2';
CREATE FUNCTION functext_D_2(int,int) RETURNS int LANGUAGE 'internal'
AS 'int4pl';
CREATE FUNCTION functext_D_3(int,int) RETURNS int LANGUAGE 'sql'
COST 500 AS 'SELECT $1 * $2';
CREATE FUNCTION functext_D_4(int,int) RETURNS int LANGUAGE 'sql'
COST 0 AS 'SELECT $1 / $2'; -- Error
SELECT proname, procost FROM pg_proc
WHERE oid in ('functext_D_1'::regproc,
'functext_D_2'::regproc,
'functext_D_3'::regproc);
ALTER FUNCTION functext_D_1(int,int) STABLE; -- unrelated change, no effect
ALTER FUNCTION functext_D_2(int,int) COST 50;
ALTER FUNCTION functext_D_3(int,int) COST 0.0001;
SELECT proname, procost FROM pg_proc
WHERE oid in ('functext_D_1'::regproc,
'functext_D_2'::regproc,
'functext_D_3'::regproc);
--
-- LEAKPROOF
--
CREATE FUNCTION functext_E_1(int) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 > 100';
CREATE FUNCTION functext_E_2(int) RETURNS bool LANGUAGE 'sql'
LEAKPROOF AS 'SELECT $1 > 100';
SELECT proname, proleakproof FROM pg_proc
WHERE oid in ('functext_E_1'::regproc,
'functext_E_2'::regproc);
ALTER FUNCTION functext_E_1(int) LEAKPROOF;
ALTER FUNCTION functext_E_2(int) STABLE; -- unrelated change, no effect
SELECT proname, proleakproof FROM pg_proc
WHERE oid in ('functext_E_1'::regproc,
'functext_E_2'::regproc);
-- list of built-in leakproof functions
SELECT proname, prorettype::regtype, proargtypes::regtype[]
FROM pg_proc JOIN pg_namespace ON pronamespace = pg_namespace.oid
WHERE nspname = 'pg_catalog' AND proleakproof ORDER BY proname;
--
-- CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
--
CREATE FUNCTION functext_F_1(int) RETURNS bool LANGUAGE 'sql'
AS 'SELECT $1 > 50';
CREATE FUNCTION functext_F_2(int) RETURNS bool LANGUAGE 'sql'
CALLED ON NULL INPUT AS 'SELECT $1 = 50';
CREATE FUNCTION functext_F_3(int) RETURNS bool LANGUAGE 'sql'
RETURNS NULL ON NULL INPUT AS 'SELECT $1 < 50';
CREATE FUNCTION functext_F_4(int) RETURNS bool LANGUAGE 'sql'
STRICT AS 'SELECT $1 = 50';
SELECT proname, proisstrict FROM pg_proc
WHERE oid in ('functext_F_1'::regproc,
'functext_F_2'::regproc,
'functext_F_3'::regproc,
'functext_F_4'::regproc);
ALTER FUNCTION functext_F_1(int) IMMUTABLE; -- unrelated change, no effect
ALTER FUNCTION functext_F_2(int) STRICT;
ALTER FUNCTION functext_F_3(int) CALLED ON NULL INPUT;
SELECT proname, proisstrict FROM pg_proc
WHERE oid in ('functext_F_1'::regproc,
'functext_F_2'::regproc,
'functext_F_3'::regproc,
'functext_F_4'::regproc);
-- Cleanups
DROP SCHEMA temp_func_test CASCADE;
RESET search_path;
|
