-
Notifications
You must be signed in to change notification settings - Fork 32
Expand file tree
/
Copy pathplpgsql_deparser_fixes.sql
More file actions
208 lines (184 loc) Β· 5.26 KB
/
plpgsql_deparser_fixes.sql
File metadata and controls
208 lines (184 loc) Β· 5.26 KB
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
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
-- Fixtures to test deparser fixes from constructive-db PR #229
-- These exercise: PERFORM, INTO clause placement, record field qualification, RETURN handling
-- Test 1: PERFORM statement (parser stores as SELECT, deparser must strip SELECT)
CREATE FUNCTION test_perform_basic() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
PERFORM pg_notify('test_channel', 'message');
RETURN NEW;
END$$;
-- Test 2: PERFORM with function call and arguments
CREATE FUNCTION test_perform_with_args() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN
PERFORM pg_notify(TG_ARGV[0], to_json(NEW)::text);
RETURN NEW;
END IF;
IF (TG_OP = 'DELETE') THEN
PERFORM pg_notify(TG_ARGV[0], to_json(OLD)::text);
RETURN OLD;
END IF;
RETURN NULL;
END$$;
-- Test 3: INTO clause with record field target (recfield qualification)
CREATE FUNCTION test_into_record_field() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
SELECT
NEW.is_approved IS TRUE
AND NEW.is_verified IS TRUE
AND NEW.is_disabled IS FALSE INTO NEW.is_active;
RETURN NEW;
END$$;
-- Test 4: INTO clause with subquery (depth-aware scanner must skip nested FROM)
CREATE FUNCTION test_into_with_subquery() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
result_value int;
BEGIN
SELECT count(*) INTO result_value
FROM (SELECT id FROM users WHERE id = NEW.user_id) sub;
RETURN NEW;
END$$;
-- Test 5: INTO clause with multiple record fields
CREATE FUNCTION test_into_multiple_fields() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
SELECT is_active, is_verified INTO NEW.is_active, NEW.is_verified
FROM users WHERE id = NEW.user_id;
RETURN NEW;
END$$;
-- Test 6: SETOF function with RETURN QUERY and bare RETURN
CREATE FUNCTION test_setof_return_query(p_limit int)
RETURNS SETOF int
LANGUAGE plpgsql AS $$
BEGIN
RETURN QUERY SELECT generate_series(1, p_limit);
RETURN;
END$$;
-- Test 7: SETOF function with RETURN NEXT
CREATE FUNCTION test_setof_return_next(p_count int)
RETURNS SETOF text
LANGUAGE plpgsql AS $$
DECLARE
i int;
BEGIN
FOR i IN 1..p_count LOOP
RETURN NEXT 'item_' || i::text;
END LOOP;
RETURN;
END$$;
-- Test 8: Void function with bare RETURN
CREATE FUNCTION test_void_function(p_value text)
RETURNS void
LANGUAGE plpgsql AS $$
BEGIN
RAISE NOTICE 'Value: %', p_value;
RETURN;
END$$;
-- Test 9: Scalar function with RETURN NULL
CREATE FUNCTION test_scalar_return_null()
RETURNS int
LANGUAGE plpgsql AS $$
BEGIN
RETURN NULL;
END$$;
-- Test 10: Scalar function with conditional RETURN
CREATE FUNCTION test_scalar_conditional(p_value int)
RETURNS int
LANGUAGE plpgsql AS $$
BEGIN
IF p_value > 0 THEN
RETURN p_value * 2;
END IF;
RETURN NULL;
END$$;
-- Test 11: OUT parameter function with bare RETURN
CREATE FUNCTION test_out_params(OUT ok boolean, OUT message text)
LANGUAGE plpgsql AS $$
BEGIN
ok := true;
message := 'success';
RETURN;
END$$;
-- Test 12: RETURNS TABLE function with RETURN QUERY
CREATE FUNCTION test_returns_table(p_prefix text)
RETURNS TABLE(id int, name text)
LANGUAGE plpgsql AS $$
BEGIN
RETURN QUERY SELECT 1, p_prefix || '_one';
RETURN QUERY SELECT 2, p_prefix || '_two';
RETURN;
END$$;
-- Test 13: Trigger function with complex logic
CREATE FUNCTION test_trigger_complex() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
defaults_record record;
bit_len int;
BEGIN
bit_len := bit_length(NEW.permissions);
SELECT * INTO defaults_record
FROM permission_defaults AS t
LIMIT 1;
IF found THEN
NEW.is_approved := defaults_record.is_approved;
NEW.is_verified := defaults_record.is_verified;
END IF;
IF NEW.is_owner IS TRUE THEN
NEW.is_admin := true;
NEW.is_approved := true;
NEW.is_verified := true;
END IF;
SELECT
NEW.is_approved IS TRUE
AND NEW.is_verified IS TRUE
AND NEW.is_disabled IS FALSE INTO NEW.is_active;
RETURN NEW;
END$$;
-- Test 14: Procedure (implicit void return)
CREATE PROCEDURE test_procedure(p_message text)
LANGUAGE plpgsql AS $$
BEGIN
RAISE NOTICE '%', p_message;
END$$;
-- Test 15: OUT parameters with SELECT INTO multiple variables
-- This pattern is used in auth functions (sign_in, sign_up) where we need to
-- populate multiple OUT parameters from a single SELECT statement
CREATE FUNCTION test_out_params_select_into(
p_user_id uuid,
OUT id uuid,
OUT user_id uuid,
OUT access_token text,
OUT access_token_expires_at timestamptz,
OUT is_verified boolean,
OUT totp_enabled boolean
)
LANGUAGE plpgsql AS $$
DECLARE
v_token_id uuid;
v_plaintext_token text;
BEGIN
v_plaintext_token := encode(gen_random_bytes(48), 'hex');
v_token_id := uuid_generate_v5(uuid_ns_url(), v_plaintext_token);
INSERT INTO tokens (id, user_id, access_token_hash)
VALUES (v_token_id, p_user_id, digest(v_plaintext_token, 'sha256'));
SELECT tkn.id, tkn.user_id, v_plaintext_token, tkn.access_token_expires_at, tkn.is_verified, tkn.totp_enabled
INTO id, user_id, access_token, access_token_expires_at, is_verified, totp_enabled
FROM tokens AS tkn
WHERE tkn.id = v_token_id;
RETURN;
END$$;
-- Test 16: OUT parameters with SELECT INTO and STRICT
CREATE FUNCTION test_out_params_strict(
p_id uuid,
OUT name text,
OUT email text
)
LANGUAGE plpgsql AS $$
BEGIN
SELECT u.name, u.email INTO STRICT name, email
FROM users u
WHERE u.id = p_id;
END$$;