Skip to content

Commit 767982e

Browse files
tglsfdctglsfdc
committed
Convert built-in SQL-language functions to SQL-standard-body style.
Adopt the new pre-parsed representation for all built-in and information_schema SQL-language functions, except for a small number that can't presently be converted because they have polymorphic arguments. This eliminates residual hazards around search-path safety of these functions, and might provide some small performance benefits by reducing parsing costs. It seems useful also to provide more test coverage for the SQL-standard-body feature. Discussion: https://postgr.es/m/3956760.1618529139@sss.pgh.pa.us
1 parent e809493 commit 767982e

File tree

4 files changed

+402
-113
lines changed

4 files changed

+402
-113
lines changed

‎src/backend/catalog/information_schema.sql

+20-28
Original file line numberDiff line numberDiff line change
@@ -43,7 +43,8 @@ SET search_path TO information_schema;
4343
CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int)
4444
RETURNS SETOF RECORD
4545
LANGUAGE sql STRICT IMMUTABLE PARALLEL SAFE
46-
AS 'select $1[s], s - pg_catalog.array_lower($1,1) + 1
46+
AS 'select $1[s],
47+
s operator(pg_catalog.-) pg_catalog.array_lower($1,1) operator(pg_catalog.+) 1
4748
from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
4849
pg_catalog.array_upper($1,1),
4950
1) as g(s)';
@@ -52,28 +53,26 @@ CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int)
5253
* column's position in the index (NULL if not there) */
5354
CREATE FUNCTION _pg_index_position(oid, smallint) RETURNS int
5455
LANGUAGE sql STRICT STABLE
55-
AS $$
56+
BEGIN ATOMIC
5657
SELECT (ss.a).n FROM
5758
(SELECT information_schema._pg_expandarray(indkey) AS a
5859
FROM pg_catalog.pg_index WHERE indexrelid = $1) ss
5960
WHERE (ss.a).x = $2;
60-
$$;
61+
END;
6162

6263
CREATE FUNCTION _pg_truetypid(pg_attribute, pg_type) RETURNS oid
6364
LANGUAGE sql
6465
IMMUTABLE
6566
PARALLEL SAFE
6667
RETURNS NULL ON NULL INPUT
67-
AS
68-
$$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typbasetype ELSE $1.atttypid END$$;
68+
RETURN CASE WHEN $2.typtype = 'd' THEN $2.typbasetype ELSE $1.atttypid END;
6969

7070
CREATE FUNCTION _pg_truetypmod(pg_attribute, pg_type) RETURNS int4
7171
LANGUAGE sql
7272
IMMUTABLE
7373
PARALLEL SAFE
7474
RETURNS NULL ON NULL INPUT
75-
AS
76-
$$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typtypmod ELSE $1.atttypmod END$$;
75+
RETURN CASE WHEN $2.typtype = 'd' THEN $2.typtypmod ELSE $1.atttypmod END;
7776

7877
-- these functions encapsulate knowledge about the encoding of typmod:
7978

@@ -82,40 +81,37 @@ CREATE FUNCTION _pg_char_max_length(typid oid, typmod int4) RETURNS integer
8281
IMMUTABLE
8382
PARALLEL SAFE
8483
RETURNS NULL ON NULL INPUT
85-
AS
86-
$$SELECT
84+
RETURN
8785
CASE WHEN $2 = -1 /* default typmod */
8886
THEN null
8987
WHEN $1 IN (1042, 1043) /* char, varchar */
9088
THEN $2 - 4
9189
WHEN $1 IN (1560, 1562) /* bit, varbit */
9290
THEN $2
9391
ELSE null
94-
END$$;
92+
END;
9593

9694
CREATE FUNCTION _pg_char_octet_length(typid oid, typmod int4) RETURNS integer
9795
LANGUAGE sql
9896
IMMUTABLE
9997
PARALLEL SAFE
10098
RETURNS NULL ON NULL INPUT
101-
AS
102-
$$SELECT
99+
RETURN
103100
CASE WHEN $1 IN (25, 1042, 1043) /* text, char, varchar */
104101
THEN CASE WHEN $2 = -1 /* default typmod */
105102
THEN CAST(2^30 AS integer)
106103
ELSE information_schema._pg_char_max_length($1, $2) *
107104
pg_catalog.pg_encoding_max_length((SELECT encoding FROM pg_catalog.pg_database WHERE datname = pg_catalog.current_database()))
108105
END
109106
ELSE null
110-
END$$;
107+
END;
111108

112109
CREATE FUNCTION _pg_numeric_precision(typid oid, typmod int4) RETURNS integer
113110
LANGUAGE sql
114111
IMMUTABLE
115112
PARALLEL SAFE
116113
RETURNS NULL ON NULL INPUT
117-
AS
118-
$$SELECT
114+
RETURN
119115
CASE $1
120116
WHEN 21 /*int2*/ THEN 16
121117
WHEN 23 /*int4*/ THEN 32
@@ -128,63 +124,59 @@ $$SELECT
128124
WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
129125
WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
130126
ELSE null
131-
END$$;
127+
END;
132128

133129
CREATE FUNCTION _pg_numeric_precision_radix(typid oid, typmod int4) RETURNS integer
134130
LANGUAGE sql
135131
IMMUTABLE
136132
PARALLEL SAFE
137133
RETURNS NULL ON NULL INPUT
138-
AS
139-
$$SELECT
134+
RETURN
140135
CASE WHEN $1 IN (21, 23, 20, 700, 701) THEN 2
141136
WHEN $1 IN (1700) THEN 10
142137
ELSE null
143-
END$$;
138+
END;
144139

145140
CREATE FUNCTION _pg_numeric_scale(typid oid, typmod int4) RETURNS integer
146141
LANGUAGE sql
147142
IMMUTABLE
148143
PARALLEL SAFE
149144
RETURNS NULL ON NULL INPUT
150-
AS
151-
$$SELECT
145+
RETURN
152146
CASE WHEN $1 IN (21, 23, 20) THEN 0
153147
WHEN $1 IN (1700) THEN
154148
CASE WHEN $2 = -1
155149
THEN null
156150
ELSE ($2 - 4) & 65535
157151
END
158152
ELSE null
159-
END$$;
153+
END;
160154

161155
CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer
162156
LANGUAGE sql
163157
IMMUTABLE
164158
PARALLEL SAFE
165159
RETURNS NULL ON NULL INPUT
166-
AS
167-
$$SELECT
160+
RETURN
168161
CASE WHEN $1 IN (1082) /* date */
169162
THEN 0
170163
WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */
171164
THEN CASE WHEN $2 < 0 THEN 6 ELSE $2 END
172165
WHEN $1 IN (1186) /* interval */
173166
THEN CASE WHEN $2 < 0 OR $2 & 65535 = 65535 THEN 6 ELSE $2 & 65535 END
174167
ELSE null
175-
END$$;
168+
END;
176169

177170
CREATE FUNCTION _pg_interval_type(typid oid, mod int4) RETURNS text
178171
LANGUAGE sql
179172
IMMUTABLE
180173
PARALLEL SAFE
181174
RETURNS NULL ON NULL INPUT
182-
AS
183-
$$SELECT
175+
RETURN
184176
CASE WHEN $1 IN (1186) /* interval */
185177
THEN pg_catalog.upper(substring(pg_catalog.format_type($1, $2) similar 'interval[()0-9]* #"%#"' escape '#'))
186178
ELSE null
187-
END$$;
179+
END;
188180

189181

190182
-- 5.2 INFORMATION_SCHEMA_CATALOG_NAME view appears later.

0 commit comments

Comments
 (0)