1 |
2094
|
aaronmk
|
--
|
2 |
|
|
-- PostgreSQL database dump
|
3 |
|
|
--
|
4 |
|
|
|
5 |
|
|
SET statement_timeout = 0;
|
6 |
11667
|
aaronmk
|
SET lock_timeout = 0;
|
7 |
2094
|
aaronmk
|
SET client_encoding = 'UTF8';
|
8 |
6213
|
aaronmk
|
SET standard_conforming_strings = on;
|
9 |
2094
|
aaronmk
|
SET check_function_bodies = false;
|
10 |
|
|
SET client_min_messages = warning;
|
11 |
|
|
|
12 |
|
|
--
|
13 |
8183
|
aaronmk
|
-- Name: util; Type: SCHEMA; Schema: -; Owner: -
|
14 |
2094
|
aaronmk
|
--
|
15 |
|
|
|
16 |
8183
|
aaronmk
|
CREATE SCHEMA util;
|
17 |
2094
|
aaronmk
|
|
18 |
|
|
|
19 |
4982
|
aaronmk
|
--
|
20 |
8183
|
aaronmk
|
-- Name: SCHEMA util; Type: COMMENT; Schema: -; Owner: -
|
21 |
4982
|
aaronmk
|
--
|
22 |
|
|
|
23 |
12235
|
aaronmk
|
COMMENT ON SCHEMA util IS '
|
24 |
|
|
IMPORTANT: Functions must always return NULL in place of '''' (the empty string). This ensures that empty strings do not find their way into VegBIEN.
|
25 |
4982
|
aaronmk
|
|
26 |
12447
|
aaronmk
|
NOTE: SQL-language functions should never be declared STRICT, because this prevents them from being inlined. inlining can create a significant speed improvement (7x+), by avoiding function calls and enabling additional constant folding. avoiding use of STRICT also makes functions *much* easier to troubleshoot, because they won''t mysteriously do nothing if called with only NULL arguments, even when you have added debug-print statements.
|
27 |
12235
|
aaronmk
|
';
|
28 |
4982
|
aaronmk
|
|
29 |
10378
|
aaronmk
|
|
30 |
8183
|
aaronmk
|
SET search_path = util, pg_catalog;
|
31 |
2107
|
aaronmk
|
|
32 |
2094
|
aaronmk
|
--
|
33 |
8183
|
aaronmk
|
-- Name: col_cast; Type: TYPE; Schema: util; Owner: -
|
34 |
8107
|
aaronmk
|
--
|
35 |
|
|
|
36 |
|
|
CREATE TYPE col_cast AS (
|
37 |
|
|
col_name text,
|
38 |
|
|
type regtype
|
39 |
|
|
);
|
40 |
|
|
|
41 |
|
|
|
42 |
|
|
--
|
43 |
8183
|
aaronmk
|
-- Name: col_ref; Type: TYPE; Schema: util; Owner: -
|
44 |
8106
|
aaronmk
|
--
|
45 |
|
|
|
46 |
|
|
CREATE TYPE col_ref AS (
|
47 |
|
|
table_ regclass,
|
48 |
|
|
name text
|
49 |
|
|
);
|
50 |
|
|
|
51 |
|
|
|
52 |
|
|
--
|
53 |
8183
|
aaronmk
|
-- Name: compass_dir; Type: TYPE; Schema: util; Owner: -
|
54 |
7673
|
aaronmk
|
--
|
55 |
|
|
|
56 |
|
|
CREATE TYPE compass_dir AS ENUM (
|
57 |
|
|
'N',
|
58 |
|
|
'E',
|
59 |
|
|
'S',
|
60 |
|
|
'W'
|
61 |
|
|
);
|
62 |
|
|
|
63 |
|
|
|
64 |
|
|
--
|
65 |
8183
|
aaronmk
|
-- Name: datatype; Type: TYPE; Schema: util; Owner: -
|
66 |
2610
|
aaronmk
|
--
|
67 |
|
|
|
68 |
|
|
CREATE TYPE datatype AS ENUM (
|
69 |
|
|
'str',
|
70 |
|
|
'float'
|
71 |
|
|
);
|
72 |
|
|
|
73 |
|
|
|
74 |
|
|
--
|
75 |
13491
|
aaronmk
|
-- Name: db_item; Type: TYPE; Schema: util; Owner: -
|
76 |
|
|
--
|
77 |
|
|
|
78 |
|
|
CREATE TYPE db_item AS (
|
79 |
|
|
path text,
|
80 |
|
|
def text
|
81 |
|
|
);
|
82 |
|
|
|
83 |
|
|
|
84 |
|
|
--
|
85 |
13488
|
aaronmk
|
-- Name: restore_views_info; Type: TYPE; Schema: util; Owner: -
|
86 |
|
|
--
|
87 |
|
|
|
88 |
|
|
CREATE TYPE restore_views_info AS (
|
89 |
13491
|
aaronmk
|
views db_item[]
|
90 |
13488
|
aaronmk
|
);
|
91 |
|
|
|
92 |
|
|
|
93 |
|
|
--
|
94 |
12423
|
aaronmk
|
-- Name: %==(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
95 |
|
|
--
|
96 |
|
|
|
97 |
|
|
CREATE FUNCTION "%=="(left_ anyelement, right_ anyelement) RETURNS boolean
|
98 |
12443
|
aaronmk
|
LANGUAGE sql STABLE
|
99 |
12423
|
aaronmk
|
AS $_$
|
100 |
12436
|
aaronmk
|
SELECT keys($1) = keys($2)
|
101 |
12423
|
aaronmk
|
$_$;
|
102 |
|
|
|
103 |
|
|
|
104 |
|
|
--
|
105 |
12443
|
aaronmk
|
-- Name: FUNCTION "%=="(left_ anyelement, right_ anyelement); Type: COMMENT; Schema: util; Owner: -
|
106 |
|
|
--
|
107 |
|
|
|
108 |
|
|
COMMENT ON FUNCTION "%=="(left_ anyelement, right_ anyelement) IS '
|
109 |
|
|
needs to be declared STABLE instead of IMMUTABLE because it depends on the search_path (as described at http://vegpath.org/links/#PostgreSQL:%20Documentation:%209.3:%20Function%20Volatility%20Categories%20**)
|
110 |
|
|
';
|
111 |
|
|
|
112 |
|
|
|
113 |
|
|
--
|
114 |
8183
|
aaronmk
|
-- Name: _alt(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
115 |
2596
|
aaronmk
|
--
|
116 |
|
|
|
117 |
4501
|
aaronmk
|
CREATE FUNCTION _alt("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown, "10" anyelement DEFAULT NULL::unknown, "11" anyelement DEFAULT NULL::unknown, "12" anyelement DEFAULT NULL::unknown) RETURNS anyelement
|
118 |
3422
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
119 |
|
|
AS $_$
|
120 |
4501
|
aaronmk
|
SELECT coalesce($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13)
|
121 |
3422
|
aaronmk
|
$_$;
|
122 |
|
|
|
123 |
|
|
|
124 |
|
|
--
|
125 |
8183
|
aaronmk
|
-- Name: _and(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
|
126 |
5937
|
aaronmk
|
--
|
127 |
|
|
|
128 |
5956
|
aaronmk
|
CREATE FUNCTION _and("0" boolean DEFAULT NULL::boolean, "1" boolean DEFAULT NULL::boolean, "2" boolean DEFAULT NULL::boolean, "3" boolean DEFAULT NULL::boolean, "4" boolean DEFAULT NULL::boolean) RETURNS boolean
|
129 |
5937
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
130 |
|
|
AS $_$
|
131 |
|
|
SELECT bool_and(value)
|
132 |
|
|
FROM
|
133 |
|
|
(VALUES
|
134 |
|
|
($1)
|
135 |
|
|
, ($2)
|
136 |
5956
|
aaronmk
|
, ($3)
|
137 |
|
|
, ($4)
|
138 |
|
|
, ($5)
|
139 |
5937
|
aaronmk
|
)
|
140 |
|
|
AS v (value)
|
141 |
|
|
$_$;
|
142 |
|
|
|
143 |
|
|
|
144 |
|
|
--
|
145 |
8183
|
aaronmk
|
-- Name: FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
|
146 |
5937
|
aaronmk
|
--
|
147 |
|
|
|
148 |
12235
|
aaronmk
|
COMMENT ON FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '
|
149 |
|
|
_and() ignores NULL values, while AND combines them with the other values to potentially convert true to NULL. AND should be used with required fields, and _and() with optional fields.
|
150 |
|
|
';
|
151 |
5937
|
aaronmk
|
|
152 |
|
|
|
153 |
|
|
--
|
154 |
8183
|
aaronmk
|
-- Name: _avg(double precision, double precision, double precision, double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
|
155 |
7704
|
aaronmk
|
--
|
156 |
|
|
|
157 |
|
|
CREATE FUNCTION _avg("0" double precision DEFAULT NULL::double precision, "1" double precision DEFAULT NULL::double precision, "2" double precision DEFAULT NULL::double precision, "3" double precision DEFAULT NULL::double precision, "4" double precision DEFAULT NULL::double precision) RETURNS double precision
|
158 |
|
|
LANGUAGE sql IMMUTABLE
|
159 |
|
|
AS $_$
|
160 |
|
|
SELECT avg(value)
|
161 |
|
|
FROM
|
162 |
|
|
(VALUES
|
163 |
|
|
($1)
|
164 |
|
|
, ($2)
|
165 |
|
|
, ($3)
|
166 |
|
|
, ($4)
|
167 |
|
|
, ($5)
|
168 |
|
|
)
|
169 |
|
|
AS v (value)
|
170 |
|
|
$_$;
|
171 |
|
|
|
172 |
|
|
|
173 |
|
|
--
|
174 |
8183
|
aaronmk
|
-- Name: _dms_to_dd(text); Type: FUNCTION; Schema: util; Owner: -
|
175 |
7679
|
aaronmk
|
--
|
176 |
|
|
|
177 |
|
|
CREATE FUNCTION _dms_to_dd(value text DEFAULT NULL::text) RETURNS double precision
|
178 |
12441
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
179 |
7679
|
aaronmk
|
AS $_$
|
180 |
8183
|
aaronmk
|
SELECT (g[1]||'1')::integer*util._dms_to_dd(deg := g[2]::double precision, min := g[3]::double precision, sec := g[4]::double precision, dir := g[5]::util.compass_dir)
|
181 |
7698
|
aaronmk
|
FROM
|
182 |
|
|
(
|
183 |
|
|
SELECT regexp_matches($1, '^ *(-?)(\d{1,3}(?:\.\d*)?)(?:(?:deg|[°º])(?: *([\d.]+)(?:min|[''’]))?(?: *([\d.]+)(?:sec|["”]))?)? *([NESW])? *$')
|
184 |
|
|
UNION ALL
|
185 |
7702
|
aaronmk
|
SELECT ARRAY[g[1], g[2], g[3]||'.'||g[4], NULL, g[5]]
|
186 |
|
|
FROM regexp_matches($1, '^ *(-?)(\d{2,3})(\d{2})(\d{3}) *([NESW])? *$') matches (g) -- [D]DDMMmmm, where MMmmm = MM.mmm
|
187 |
7698
|
aaronmk
|
)
|
188 |
|
|
matches (g)
|
189 |
7679
|
aaronmk
|
$_$;
|
190 |
|
|
|
191 |
|
|
|
192 |
|
|
--
|
193 |
8183
|
aaronmk
|
-- Name: _dms_to_dd(double precision, double precision, double precision, compass_dir); Type: FUNCTION; Schema: util; Owner: -
|
194 |
7672
|
aaronmk
|
--
|
195 |
|
|
|
196 |
7674
|
aaronmk
|
CREATE FUNCTION _dms_to_dd(deg double precision DEFAULT NULL::double precision, min double precision DEFAULT NULL::double precision, sec double precision DEFAULT NULL::double precision, dir compass_dir DEFAULT NULL::compass_dir) RETURNS double precision
|
197 |
7672
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
198 |
|
|
AS $_$
|
199 |
8183
|
aaronmk
|
SELECT sum(value)*COALESCE(util._map('N=>1,E=>1,S=>-1,W=>-1', $4::text)::integer, 1)
|
200 |
7672
|
aaronmk
|
FROM
|
201 |
|
|
(VALUES
|
202 |
7677
|
aaronmk
|
($1)
|
203 |
7672
|
aaronmk
|
, ($2/60)
|
204 |
|
|
, ($3/60/60)
|
205 |
|
|
)
|
206 |
|
|
AS v (value)
|
207 |
|
|
$_$;
|
208 |
|
|
|
209 |
|
|
|
210 |
|
|
--
|
211 |
8183
|
aaronmk
|
-- Name: _dms_to_dd(text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
|
212 |
7723
|
aaronmk
|
--
|
213 |
|
|
|
214 |
|
|
CREATE FUNCTION _dms_to_dd(deg text DEFAULT NULL::text, min text DEFAULT NULL::text, sec text DEFAULT NULL::text, dir text DEFAULT NULL::text) RETURNS double precision
|
215 |
|
|
LANGUAGE sql IMMUTABLE
|
216 |
|
|
AS $_$
|
217 |
8183
|
aaronmk
|
SELECT util._dms_to_dd($1::double precision, $2::double precision, $3::double precision, $4::util.compass_dir)
|
218 |
7723
|
aaronmk
|
$_$;
|
219 |
|
|
|
220 |
|
|
|
221 |
|
|
--
|
222 |
8183
|
aaronmk
|
-- Name: _eq(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
223 |
4142
|
aaronmk
|
--
|
224 |
|
|
|
225 |
|
|
CREATE FUNCTION _eq("left" anyelement DEFAULT NULL::unknown, "right" anyelement DEFAULT NULL::unknown) RETURNS boolean
|
226 |
|
|
LANGUAGE sql IMMUTABLE
|
227 |
|
|
AS $_$
|
228 |
|
|
SELECT $1 = $2
|
229 |
|
|
$_$;
|
230 |
|
|
|
231 |
|
|
|
232 |
|
|
--
|
233 |
8183
|
aaronmk
|
-- Name: _fix_date(date, date); Type: FUNCTION; Schema: util; Owner: -
|
234 |
7396
|
aaronmk
|
--
|
235 |
|
|
|
236 |
|
|
CREATE FUNCTION _fix_date(value date DEFAULT NULL::date, threshold date DEFAULT NULL::date) RETURNS date
|
237 |
|
|
LANGUAGE sql IMMUTABLE
|
238 |
|
|
AS $_$
|
239 |
|
|
-- Fix dates after threshold date
|
240 |
|
|
-- This fixes e.g. 2-digit years before 1970 misinterpreted as 20xx
|
241 |
|
|
SELECT (CASE WHEN $1 > COALESCE($2, now()) THEN ($1 - '100 years'::interval)::date ELSE $1 END)
|
242 |
|
|
$_$;
|
243 |
|
|
|
244 |
|
|
|
245 |
|
|
--
|
246 |
8183
|
aaronmk
|
-- Name: _if(boolean, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
247 |
4147
|
aaronmk
|
--
|
248 |
|
|
|
249 |
|
|
CREATE FUNCTION _if(cond boolean DEFAULT NULL::boolean, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
|
250 |
|
|
LANGUAGE sql IMMUTABLE
|
251 |
|
|
AS $_$
|
252 |
|
|
SELECT (CASE WHEN $1 THEN $2 ELSE $3 END)
|
253 |
|
|
$_$;
|
254 |
|
|
|
255 |
|
|
|
256 |
|
|
--
|
257 |
8183
|
aaronmk
|
-- Name: _if(text, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
258 |
4147
|
aaronmk
|
--
|
259 |
|
|
|
260 |
|
|
CREATE FUNCTION _if(cond text DEFAULT NULL::text, "then" anyelement DEFAULT NULL::unknown, "else" anyelement DEFAULT NULL::unknown) RETURNS anyelement
|
261 |
|
|
LANGUAGE sql IMMUTABLE
|
262 |
|
|
AS $_$
|
263 |
8183
|
aaronmk
|
SELECT util._if($1 != '', $2, $3)
|
264 |
4147
|
aaronmk
|
$_$;
|
265 |
|
|
|
266 |
|
|
|
267 |
|
|
--
|
268 |
10699
|
aaronmk
|
-- Name: _join(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
|
269 |
4325
|
aaronmk
|
--
|
270 |
|
|
|
271 |
10699
|
aaronmk
|
CREATE FUNCTION _join("0" text DEFAULT NULL::text, "1" text DEFAULT NULL::text, "2" text DEFAULT NULL::text, "3" text DEFAULT NULL::text, "4" text DEFAULT NULL::text, "5" text DEFAULT NULL::text, "6" text DEFAULT NULL::text, "7" text DEFAULT NULL::text, "8" text DEFAULT NULL::text, "9" text DEFAULT NULL::text) RETURNS text
|
272 |
4325
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
273 |
|
|
AS $_$
|
274 |
7848
|
aaronmk
|
SELECT NULLIF(concat_ws('; ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
|
275 |
4325
|
aaronmk
|
$_$;
|
276 |
|
|
|
277 |
|
|
|
278 |
|
|
--
|
279 |
10699
|
aaronmk
|
-- Name: _join_words(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
|
280 |
5009
|
aaronmk
|
--
|
281 |
|
|
|
282 |
10699
|
aaronmk
|
CREATE FUNCTION _join_words("0" text DEFAULT NULL::text, "1" text DEFAULT NULL::text, "2" text DEFAULT NULL::text, "3" text DEFAULT NULL::text, "4" text DEFAULT NULL::text, "5" text DEFAULT NULL::text, "6" text DEFAULT NULL::text, "7" text DEFAULT NULL::text, "8" text DEFAULT NULL::text, "9" text DEFAULT NULL::text) RETURNS text
|
283 |
5009
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
284 |
|
|
AS $_$
|
285 |
7848
|
aaronmk
|
SELECT NULLIF(concat_ws(' ', $1, $2, $3, $4, $5, $6, $7, $8, $9, $10), '')
|
286 |
5009
|
aaronmk
|
$_$;
|
287 |
|
|
|
288 |
|
|
|
289 |
|
|
--
|
290 |
13355
|
aaronmk
|
-- Name: _km_to_m(double precision); Type: FUNCTION; Schema: util; Owner: -
|
291 |
|
|
--
|
292 |
|
|
|
293 |
|
|
CREATE FUNCTION _km_to_m(value double precision) RETURNS double precision
|
294 |
|
|
LANGUAGE sql IMMUTABLE
|
295 |
|
|
AS $_$
|
296 |
|
|
SELECT $1*1000.
|
297 |
|
|
$_$;
|
298 |
|
|
|
299 |
|
|
|
300 |
|
|
--
|
301 |
10699
|
aaronmk
|
-- Name: _label(text, text); Type: FUNCTION; Schema: util; Owner: -
|
302 |
3422
|
aaronmk
|
--
|
303 |
|
|
|
304 |
10699
|
aaronmk
|
CREATE FUNCTION _label(label text, value text) RETURNS text
|
305 |
4682
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
306 |
|
|
AS $_$
|
307 |
|
|
SELECT coalesce($1 || ': ', '') || $2
|
308 |
|
|
$_$;
|
309 |
2596
|
aaronmk
|
|
310 |
|
|
|
311 |
|
|
--
|
312 |
8825
|
aaronmk
|
-- Name: _lowercase(text); Type: FUNCTION; Schema: util; Owner: -
|
313 |
|
|
--
|
314 |
|
|
|
315 |
|
|
CREATE FUNCTION _lowercase(value text) RETURNS text
|
316 |
10388
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
317 |
8825
|
aaronmk
|
AS $_$
|
318 |
|
|
SELECT lower($1)
|
319 |
|
|
$_$;
|
320 |
|
|
|
321 |
|
|
|
322 |
|
|
--
|
323 |
11667
|
aaronmk
|
-- Name: _map(hstore, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
324 |
|
|
--
|
325 |
|
|
|
326 |
|
|
CREATE FUNCTION _map(map hstore, value anyelement) RETURNS anyelement
|
327 |
|
|
LANGUAGE plpgsql IMMUTABLE STRICT
|
328 |
|
|
AS $$
|
329 |
|
|
DECLARE
|
330 |
|
|
result value%TYPE := util._map(map, value::text)::unknown;
|
331 |
|
|
BEGIN
|
332 |
|
|
RETURN result;
|
333 |
|
|
END;
|
334 |
|
|
$$;
|
335 |
|
|
|
336 |
|
|
|
337 |
|
|
--
|
338 |
8183
|
aaronmk
|
-- Name: _map(hstore, text); Type: FUNCTION; Schema: util; Owner: -
|
339 |
6222
|
aaronmk
|
--
|
340 |
|
|
|
341 |
|
|
CREATE FUNCTION _map(map hstore, value text) RETURNS text
|
342 |
7820
|
aaronmk
|
LANGUAGE plpgsql IMMUTABLE STRICT
|
343 |
6222
|
aaronmk
|
AS $$
|
344 |
|
|
DECLARE
|
345 |
6271
|
aaronmk
|
match text := map -> value;
|
346 |
6222
|
aaronmk
|
BEGIN
|
347 |
6271
|
aaronmk
|
IF match IS NULL AND NOT map ? value THEN -- no match rather than NULL match
|
348 |
|
|
match := map -> '*'; -- use default entry
|
349 |
|
|
IF match IS NULL AND NOT map ? '*' THEN match := '!'; -- no default
|
350 |
|
|
END IF;
|
351 |
|
|
END IF;
|
352 |
|
|
|
353 |
|
|
-- Interpret result
|
354 |
6243
|
aaronmk
|
IF match = '!' THEN RAISE 'Value not in map: %', value USING ERRCODE = 'data_exception';
|
355 |
|
|
ELSIF match = '*' THEN RETURN value;
|
356 |
|
|
ELSE RETURN match;
|
357 |
6222
|
aaronmk
|
END IF;
|
358 |
|
|
END;
|
359 |
|
|
$$;
|
360 |
|
|
|
361 |
|
|
|
362 |
|
|
--
|
363 |
8183
|
aaronmk
|
-- Name: _max(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
364 |
5408
|
aaronmk
|
--
|
365 |
|
|
|
366 |
|
|
CREATE FUNCTION _max("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
|
367 |
|
|
LANGUAGE sql IMMUTABLE
|
368 |
|
|
AS $_$
|
369 |
7289
|
aaronmk
|
SELECT GREATEST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
|
370 |
5408
|
aaronmk
|
$_$;
|
371 |
|
|
|
372 |
|
|
|
373 |
|
|
--
|
374 |
8183
|
aaronmk
|
-- Name: _merge(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
375 |
2940
|
aaronmk
|
--
|
376 |
|
|
|
377 |
4150
|
aaronmk
|
CREATE FUNCTION _merge("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
|
378 |
2940
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
379 |
|
|
AS $_$
|
380 |
8183
|
aaronmk
|
SELECT util.join_strs(value, '; ')
|
381 |
2940
|
aaronmk
|
FROM
|
382 |
|
|
(
|
383 |
|
|
SELECT *
|
384 |
|
|
FROM
|
385 |
|
|
(
|
386 |
|
|
SELECT
|
387 |
|
|
DISTINCT ON (value)
|
388 |
|
|
*
|
389 |
|
|
FROM
|
390 |
|
|
(VALUES
|
391 |
4012
|
aaronmk
|
(1, $1)
|
392 |
|
|
, (2, $2)
|
393 |
|
|
, (3, $3)
|
394 |
|
|
, (4, $4)
|
395 |
|
|
, (5, $5)
|
396 |
|
|
, (6, $6)
|
397 |
|
|
, (7, $7)
|
398 |
|
|
, (8, $8)
|
399 |
|
|
, (9, $9)
|
400 |
|
|
, (10, $10)
|
401 |
2940
|
aaronmk
|
)
|
402 |
|
|
AS v (sort_order, value)
|
403 |
4011
|
aaronmk
|
WHERE value IS NOT NULL
|
404 |
2940
|
aaronmk
|
)
|
405 |
|
|
AS v
|
406 |
|
|
ORDER BY sort_order
|
407 |
|
|
)
|
408 |
|
|
AS v
|
409 |
|
|
$_$;
|
410 |
|
|
|
411 |
|
|
|
412 |
|
|
--
|
413 |
8183
|
aaronmk
|
-- Name: _merge_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
|
414 |
7140
|
aaronmk
|
--
|
415 |
|
|
|
416 |
|
|
CREATE FUNCTION _merge_prefix(prefix text DEFAULT NULL::text, value text DEFAULT NULL::text) RETURNS text
|
417 |
|
|
LANGUAGE sql IMMUTABLE
|
418 |
|
|
AS $_$
|
419 |
|
|
SELECT _join_words((CASE WHEN $2 ~ ('^'||$1||E'\\y') THEN NULL ELSE $1 END), $2)
|
420 |
|
|
$_$;
|
421 |
|
|
|
422 |
|
|
|
423 |
|
|
--
|
424 |
8183
|
aaronmk
|
-- Name: _merge_words(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
425 |
6354
|
aaronmk
|
--
|
426 |
|
|
|
427 |
|
|
CREATE FUNCTION _merge_words("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
|
428 |
|
|
LANGUAGE sql IMMUTABLE
|
429 |
|
|
AS $_$
|
430 |
8183
|
aaronmk
|
SELECT util.join_strs(value, ' ')
|
431 |
6354
|
aaronmk
|
FROM
|
432 |
|
|
(
|
433 |
|
|
SELECT *
|
434 |
|
|
FROM
|
435 |
|
|
(
|
436 |
|
|
SELECT
|
437 |
|
|
DISTINCT ON (value)
|
438 |
|
|
*
|
439 |
|
|
FROM
|
440 |
|
|
(VALUES
|
441 |
|
|
(1, $1)
|
442 |
|
|
, (2, $2)
|
443 |
|
|
, (3, $3)
|
444 |
|
|
, (4, $4)
|
445 |
|
|
, (5, $5)
|
446 |
|
|
, (6, $6)
|
447 |
|
|
, (7, $7)
|
448 |
|
|
, (8, $8)
|
449 |
|
|
, (9, $9)
|
450 |
|
|
, (10, $10)
|
451 |
|
|
)
|
452 |
|
|
AS v (sort_order, value)
|
453 |
|
|
WHERE value IS NOT NULL
|
454 |
|
|
)
|
455 |
|
|
AS v
|
456 |
|
|
ORDER BY sort_order
|
457 |
|
|
)
|
458 |
|
|
AS v
|
459 |
|
|
$_$;
|
460 |
|
|
|
461 |
|
|
|
462 |
|
|
--
|
463 |
8183
|
aaronmk
|
-- Name: _min(anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
464 |
5408
|
aaronmk
|
--
|
465 |
|
|
|
466 |
|
|
CREATE FUNCTION _min("0" anyelement DEFAULT NULL::unknown, "1" anyelement DEFAULT NULL::unknown, "2" anyelement DEFAULT NULL::unknown, "3" anyelement DEFAULT NULL::unknown, "4" anyelement DEFAULT NULL::unknown, "5" anyelement DEFAULT NULL::unknown, "6" anyelement DEFAULT NULL::unknown, "7" anyelement DEFAULT NULL::unknown, "8" anyelement DEFAULT NULL::unknown, "9" anyelement DEFAULT NULL::unknown) RETURNS anyelement
|
467 |
|
|
LANGUAGE sql IMMUTABLE
|
468 |
|
|
AS $_$
|
469 |
7289
|
aaronmk
|
SELECT LEAST($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
|
470 |
5408
|
aaronmk
|
$_$;
|
471 |
|
|
|
472 |
|
|
|
473 |
|
|
--
|
474 |
8183
|
aaronmk
|
-- Name: _not(boolean); Type: FUNCTION; Schema: util; Owner: -
|
475 |
6316
|
aaronmk
|
--
|
476 |
|
|
|
477 |
|
|
CREATE FUNCTION _not(value boolean) RETURNS boolean
|
478 |
10388
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
479 |
6316
|
aaronmk
|
AS $_$
|
480 |
|
|
SELECT NOT $1
|
481 |
|
|
$_$;
|
482 |
|
|
|
483 |
|
|
|
484 |
|
|
--
|
485 |
8183
|
aaronmk
|
-- Name: _now(); Type: FUNCTION; Schema: util; Owner: -
|
486 |
7104
|
aaronmk
|
--
|
487 |
|
|
|
488 |
|
|
CREATE FUNCTION _now() RETURNS timestamp with time zone
|
489 |
|
|
LANGUAGE sql STABLE
|
490 |
|
|
AS $$
|
491 |
|
|
SELECT now()
|
492 |
|
|
$$;
|
493 |
|
|
|
494 |
|
|
|
495 |
|
|
--
|
496 |
11667
|
aaronmk
|
-- Name: _nullIf(anyelement, text, text); Type: FUNCTION; Schema: util; Owner: -
|
497 |
|
|
--
|
498 |
|
|
|
499 |
|
|
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type text) RETURNS anyelement
|
500 |
|
|
LANGUAGE sql IMMUTABLE
|
501 |
|
|
AS $_$
|
502 |
|
|
SELECT util."_nullIf"($1, $2, $3::util.datatype)
|
503 |
|
|
$_$;
|
504 |
|
|
|
505 |
|
|
|
506 |
|
|
--
|
507 |
8183
|
aaronmk
|
-- Name: _nullIf(anyelement, text, datatype); Type: FUNCTION; Schema: util; Owner: -
|
508 |
2949
|
aaronmk
|
--
|
509 |
|
|
|
510 |
4475
|
aaronmk
|
CREATE FUNCTION "_nullIf"(value anyelement, "null" text, type datatype DEFAULT 'str'::datatype) RETURNS anyelement
|
511 |
2949
|
aaronmk
|
LANGUAGE plpgsql IMMUTABLE
|
512 |
|
|
AS $$
|
513 |
|
|
DECLARE
|
514 |
8183
|
aaronmk
|
type util.datatype NOT NULL := type; -- add NOT NULL
|
515 |
2949
|
aaronmk
|
BEGIN
|
516 |
4475
|
aaronmk
|
IF type = 'str' THEN RETURN nullif(value::text, "null");
|
517 |
2722
|
aaronmk
|
-- Invalid value is ignored, but invalid null value generates error
|
518 |
2949
|
aaronmk
|
ELSIF type = 'float' THEN
|
519 |
2722
|
aaronmk
|
DECLARE
|
520 |
|
|
-- Outside the try block so that invalid null value generates error
|
521 |
2949
|
aaronmk
|
"null" double precision := "null"::double precision;
|
522 |
2722
|
aaronmk
|
BEGIN
|
523 |
2949
|
aaronmk
|
RETURN nullif(value::double precision, "null");
|
524 |
2722
|
aaronmk
|
EXCEPTION
|
525 |
2949
|
aaronmk
|
WHEN data_exception THEN RETURN value; -- ignore invalid value
|
526 |
2722
|
aaronmk
|
END;
|
527 |
2610
|
aaronmk
|
END IF;
|
528 |
|
|
END;
|
529 |
|
|
$$;
|
530 |
|
|
|
531 |
|
|
|
532 |
|
|
--
|
533 |
8183
|
aaronmk
|
-- Name: _or(boolean, boolean, boolean, boolean, boolean); Type: FUNCTION; Schema: util; Owner: -
|
534 |
6355
|
aaronmk
|
--
|
535 |
|
|
|
536 |
|
|
CREATE FUNCTION _or("0" boolean DEFAULT NULL::boolean, "1" boolean DEFAULT NULL::boolean, "2" boolean DEFAULT NULL::boolean, "3" boolean DEFAULT NULL::boolean, "4" boolean DEFAULT NULL::boolean) RETURNS boolean
|
537 |
|
|
LANGUAGE sql IMMUTABLE
|
538 |
|
|
AS $_$
|
539 |
|
|
SELECT bool_or(value)
|
540 |
|
|
FROM
|
541 |
|
|
(VALUES
|
542 |
|
|
($1)
|
543 |
|
|
, ($2)
|
544 |
|
|
, ($3)
|
545 |
|
|
, ($4)
|
546 |
|
|
, ($5)
|
547 |
|
|
)
|
548 |
|
|
AS v (value)
|
549 |
|
|
$_$;
|
550 |
|
|
|
551 |
|
|
|
552 |
|
|
--
|
553 |
8183
|
aaronmk
|
-- Name: FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean); Type: COMMENT; Schema: util; Owner: -
|
554 |
6437
|
aaronmk
|
--
|
555 |
|
|
|
556 |
12235
|
aaronmk
|
COMMENT ON FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '
|
557 |
|
|
_or() ignores NULL values, while OR combines them with the other values to potentially convert false to NULL. OR should be used with required fields, and _or() with optional fields.
|
558 |
|
|
';
|
559 |
6437
|
aaronmk
|
|
560 |
|
|
|
561 |
|
|
--
|
562 |
8183
|
aaronmk
|
-- Name: _range(double precision, double precision); Type: FUNCTION; Schema: util; Owner: -
|
563 |
7706
|
aaronmk
|
--
|
564 |
|
|
|
565 |
|
|
CREATE FUNCTION _range("from" double precision DEFAULT NULL::double precision, "to" double precision DEFAULT NULL::double precision) RETURNS double precision
|
566 |
|
|
LANGUAGE sql IMMUTABLE
|
567 |
|
|
AS $_$
|
568 |
|
|
SELECT $2 - $1
|
569 |
|
|
$_$;
|
570 |
|
|
|
571 |
|
|
|
572 |
|
|
--
|
573 |
8183
|
aaronmk
|
-- Name: _split(text, text); Type: FUNCTION; Schema: util; Owner: -
|
574 |
6793
|
aaronmk
|
--
|
575 |
|
|
|
576 |
|
|
CREATE FUNCTION _split(value text DEFAULT NULL::text, separator text DEFAULT '; '::text) RETURNS SETOF text
|
577 |
10388
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
578 |
6793
|
aaronmk
|
AS $_$
|
579 |
|
|
SELECT regexp_split_to_table($1, $2)
|
580 |
|
|
$_$;
|
581 |
|
|
|
582 |
|
|
|
583 |
|
|
--
|
584 |
10594
|
aaronmk
|
-- Name: added_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
|
585 |
|
|
--
|
586 |
|
|
|
587 |
|
|
CREATE FUNCTION added_cols(table_ regclass, names regclass) RETURNS SETOF text
|
588 |
12446
|
aaronmk
|
LANGUAGE sql STABLE
|
589 |
10594
|
aaronmk
|
AS $_$
|
590 |
|
|
SELECT util.derived_cols($1, $2)
|
591 |
|
|
UNION
|
592 |
|
|
SELECT util.eval2set($$
|
593 |
|
|
SELECT col
|
594 |
|
|
FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
|
595 |
|
|
JOIN $$||$2||$$ ON "to" = col
|
596 |
|
|
WHERE "from" LIKE ':%'
|
597 |
|
|
$$, NULL::text)
|
598 |
|
|
$_$;
|
599 |
|
|
|
600 |
|
|
|
601 |
|
|
--
|
602 |
|
|
-- Name: FUNCTION added_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
|
603 |
|
|
--
|
604 |
|
|
|
605 |
12235
|
aaronmk
|
COMMENT ON FUNCTION added_cols(table_ regclass, names regclass) IS '
|
606 |
|
|
gets table_''s added columns (all the columns not in the original data)
|
607 |
|
|
';
|
608 |
10594
|
aaronmk
|
|
609 |
|
|
|
610 |
|
|
--
|
611 |
9959
|
aaronmk
|
-- Name: all_same_final(anyarray); Type: FUNCTION; Schema: util; Owner: -
|
612 |
|
|
--
|
613 |
|
|
|
614 |
|
|
CREATE FUNCTION all_same_final(state anyarray) RETURNS boolean
|
615 |
|
|
LANGUAGE sql IMMUTABLE
|
616 |
|
|
AS $_$
|
617 |
|
|
SELECT $1 IS NULL/*no rows*/ OR util.not_empty($1)/*not no_match_sentinel*/
|
618 |
|
|
$_$;
|
619 |
|
|
|
620 |
|
|
|
621 |
|
|
--
|
622 |
|
|
-- Name: all_same_transform(anyarray, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
623 |
|
|
--
|
624 |
|
|
|
625 |
|
|
CREATE FUNCTION all_same_transform(state anyarray, value anyelement) RETURNS anyarray
|
626 |
|
|
LANGUAGE plpgsql IMMUTABLE
|
627 |
|
|
AS $$
|
628 |
|
|
DECLARE
|
629 |
|
|
value_cmp state%TYPE = ARRAY[value];
|
630 |
|
|
state state%TYPE = COALESCE(state, value_cmp);
|
631 |
|
|
no_match_sentinel state%TYPE = value_cmp[1:0]/*=ARRAY[]::state%TYPE*/;
|
632 |
|
|
BEGIN
|
633 |
|
|
RETURN (CASE WHEN value_cmp IS NOT DISTINCT FROM state THEN state ELSE no_match_sentinel END);
|
634 |
|
|
END;
|
635 |
|
|
$$;
|
636 |
|
|
|
637 |
|
|
|
638 |
|
|
--
|
639 |
12320
|
aaronmk
|
-- Name: analyze_(regclass); Type: FUNCTION; Schema: util; Owner: -
|
640 |
|
|
--
|
641 |
|
|
|
642 |
|
|
CREATE FUNCTION analyze_(table_ regclass) RETURNS void
|
643 |
|
|
LANGUAGE sql
|
644 |
|
|
AS $_$
|
645 |
|
|
SELECT util.eval($$ANALYZE VERBOSE $$||$1)
|
646 |
|
|
$_$;
|
647 |
|
|
|
648 |
|
|
|
649 |
|
|
--
|
650 |
12369
|
aaronmk
|
-- Name: append_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
|
651 |
|
|
--
|
652 |
|
|
|
653 |
|
|
CREATE FUNCTION append_comment(table_ regclass, comment text) RETURNS void
|
654 |
12446
|
aaronmk
|
LANGUAGE sql
|
655 |
12369
|
aaronmk
|
AS $_$
|
656 |
|
|
SELECT util.set_comment($1, concat(util.comment($1), $2))
|
657 |
|
|
$_$;
|
658 |
|
|
|
659 |
|
|
|
660 |
|
|
--
|
661 |
|
|
-- Name: FUNCTION append_comment(table_ regclass, comment text); Type: COMMENT; Schema: util; Owner: -
|
662 |
|
|
--
|
663 |
|
|
|
664 |
|
|
COMMENT ON FUNCTION append_comment(table_ regclass, comment text) IS '
|
665 |
|
|
comment: must start and end with a newline
|
666 |
|
|
';
|
667 |
|
|
|
668 |
|
|
|
669 |
|
|
--
|
670 |
10305
|
aaronmk
|
-- Name: array_fill(anyelement, integer); Type: FUNCTION; Schema: util; Owner: -
|
671 |
|
|
--
|
672 |
|
|
|
673 |
|
|
CREATE FUNCTION array_fill(value anyelement, length integer) RETURNS anyarray
|
674 |
|
|
LANGUAGE sql IMMUTABLE
|
675 |
|
|
AS $_$
|
676 |
|
|
SELECT pg_catalog.array_fill($1, ARRAY[$2])
|
677 |
|
|
$_$;
|
678 |
|
|
|
679 |
|
|
|
680 |
|
|
--
|
681 |
10303
|
aaronmk
|
-- Name: array_length(anyarray); Type: FUNCTION; Schema: util; Owner: -
|
682 |
|
|
--
|
683 |
|
|
|
684 |
|
|
CREATE FUNCTION array_length("array" anyarray) RETURNS integer
|
685 |
10354
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
686 |
10303
|
aaronmk
|
AS $_$
|
687 |
10321
|
aaronmk
|
SELECT util.array_length($1, 1)
|
688 |
10303
|
aaronmk
|
$_$;
|
689 |
|
|
|
690 |
|
|
|
691 |
|
|
--
|
692 |
10304
|
aaronmk
|
-- Name: array_length(anyarray, integer); Type: FUNCTION; Schema: util; Owner: -
|
693 |
|
|
--
|
694 |
|
|
|
695 |
|
|
CREATE FUNCTION array_length("array" anyarray, dimension integer) RETURNS integer
|
696 |
10354
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
697 |
10304
|
aaronmk
|
AS $_$
|
698 |
10354
|
aaronmk
|
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE COALESCE(pg_catalog.array_length($1, $2), 0) END
|
699 |
10304
|
aaronmk
|
$_$;
|
700 |
|
|
|
701 |
|
|
|
702 |
|
|
--
|
703 |
|
|
-- Name: FUNCTION array_length("array" anyarray, dimension integer); Type: COMMENT; Schema: util; Owner: -
|
704 |
|
|
--
|
705 |
|
|
|
706 |
12235
|
aaronmk
|
COMMENT ON FUNCTION array_length("array" anyarray, dimension integer) IS '
|
707 |
|
|
returns 0 instead of NULL for empty arrays
|
708 |
|
|
';
|
709 |
10304
|
aaronmk
|
|
710 |
|
|
|
711 |
|
|
--
|
712 |
13453
|
aaronmk
|
-- Name: array_reverse(anyarray); Type: FUNCTION; Schema: util; Owner: -
|
713 |
|
|
--
|
714 |
|
|
|
715 |
|
|
CREATE FUNCTION array_reverse("array" anyarray) RETURNS anyarray
|
716 |
|
|
LANGUAGE sql IMMUTABLE
|
717 |
|
|
AS $_$
|
718 |
13489
|
aaronmk
|
SELECT array(SELECT elem FROM util.in_reverse($1) elem)
|
719 |
13453
|
aaronmk
|
$_$;
|
720 |
|
|
|
721 |
|
|
|
722 |
|
|
--
|
723 |
12662
|
aaronmk
|
-- Name: auto_rm_freq(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
|
724 |
12655
|
aaronmk
|
--
|
725 |
|
|
|
726 |
12662
|
aaronmk
|
CREATE FUNCTION auto_rm_freq(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS void
|
727 |
12655
|
aaronmk
|
LANGUAGE sql
|
728 |
|
|
AS $_$
|
729 |
12662
|
aaronmk
|
SELECT CASE WHEN util.freq_always_1($1, $2)
|
730 |
13097
|
aaronmk
|
THEN util.rm_freq($1, $2)
|
731 |
12685
|
aaronmk
|
ELSE util.try_create($$ALTER TABLE $$||util.parent($1[1])||$$ ADD COLUMN $$||quote_ident($2)||$$ bigint NOT NULL$$)
|
732 |
12662
|
aaronmk
|
END
|
733 |
12655
|
aaronmk
|
$_$;
|
734 |
|
|
|
735 |
|
|
|
736 |
|
|
--
|
737 |
13134
|
aaronmk
|
-- Name: cast(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
738 |
|
|
--
|
739 |
|
|
|
740 |
|
|
CREATE FUNCTION "cast"(value text, ret_type_null anyelement) RETURNS anyelement
|
741 |
|
|
LANGUAGE plpgsql IMMUTABLE
|
742 |
|
|
AS $$
|
743 |
|
|
/* must use LANGUAGE plpgsql because LANGUAGE sql does not assignment-cast the
|
744 |
|
|
return value, causing a type mismatch */
|
745 |
|
|
BEGIN
|
746 |
|
|
-- will then be assignment-cast to return type via INOUT
|
747 |
|
|
RETURN value::cstring;
|
748 |
|
|
END;
|
749 |
|
|
$$;
|
750 |
|
|
|
751 |
|
|
|
752 |
|
|
--
|
753 |
|
|
-- Name: FUNCTION "cast"(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
|
754 |
|
|
--
|
755 |
|
|
|
756 |
|
|
COMMENT ON FUNCTION "cast"(value text, ret_type_null anyelement) IS '
|
757 |
|
|
allows casting to an arbitrary type without eval()
|
758 |
|
|
|
759 |
|
|
usage:
|
760 |
|
|
SELECT util.cast(''value'', NULL::integer);
|
761 |
|
|
|
762 |
|
|
note that there does *not* need to be a cast from text to the output type,
|
763 |
|
|
because an INOUT cast is used instead
|
764 |
|
|
(http://www.postgresql.org/docs/9.3/static/sql-createcast.html#AEN69507)
|
765 |
|
|
|
766 |
|
|
ret_type_null: NULL::ret_type
|
767 |
|
|
';
|
768 |
|
|
|
769 |
|
|
|
770 |
|
|
--
|
771 |
8183
|
aaronmk
|
-- Name: cluster_index(regclass); Type: FUNCTION; Schema: util; Owner: -
|
772 |
8104
|
aaronmk
|
--
|
773 |
|
|
|
774 |
|
|
CREATE FUNCTION cluster_index(table_ regclass) RETURNS regclass
|
775 |
12446
|
aaronmk
|
LANGUAGE sql STABLE
|
776 |
8104
|
aaronmk
|
AS $_$
|
777 |
|
|
SELECT indexrelid FROM pg_index WHERE indrelid = $1 AND indisclustered
|
778 |
|
|
$_$;
|
779 |
|
|
|
780 |
|
|
|
781 |
|
|
--
|
782 |
8183
|
aaronmk
|
-- Name: cluster_once(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
|
783 |
8105
|
aaronmk
|
--
|
784 |
|
|
|
785 |
|
|
CREATE FUNCTION cluster_once(table_ regclass, index regclass) RETURNS void
|
786 |
|
|
LANGUAGE plpgsql STRICT
|
787 |
|
|
AS $_$
|
788 |
|
|
BEGIN
|
789 |
|
|
-- not yet clustered (ARRAY[] compares NULLs literally)
|
790 |
8183
|
aaronmk
|
IF ARRAY[util.cluster_index(table_)] != ARRAY[index] THEN
|
791 |
8105
|
aaronmk
|
EXECUTE $$CLUSTER $$||table_||$$ USING $$||index;
|
792 |
|
|
END IF;
|
793 |
|
|
END;
|
794 |
|
|
$_$;
|
795 |
|
|
|
796 |
|
|
|
797 |
|
|
--
|
798 |
8183
|
aaronmk
|
-- Name: FUNCTION cluster_once(table_ regclass, index regclass); Type: COMMENT; Schema: util; Owner: -
|
799 |
8105
|
aaronmk
|
--
|
800 |
|
|
|
801 |
12235
|
aaronmk
|
COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS '
|
802 |
|
|
idempotent
|
803 |
|
|
';
|
804 |
8105
|
aaronmk
|
|
805 |
|
|
|
806 |
|
|
--
|
807 |
12683
|
aaronmk
|
-- Name: coalesce(anyarray); Type: FUNCTION; Schema: util; Owner: -
|
808 |
|
|
--
|
809 |
|
|
|
810 |
|
|
CREATE FUNCTION "coalesce"(VARIADIC values_ anyarray) RETURNS anyelement
|
811 |
|
|
LANGUAGE sql IMMUTABLE
|
812 |
|
|
AS $_$
|
813 |
|
|
SELECT value
|
814 |
|
|
FROM unnest($1) value
|
815 |
|
|
WHERE value IS NOT NULL
|
816 |
|
|
LIMIT 1
|
817 |
|
|
$_$;
|
818 |
|
|
|
819 |
|
|
|
820 |
|
|
--
|
821 |
|
|
-- Name: FUNCTION "coalesce"(VARIADIC values_ anyarray); Type: COMMENT; Schema: util; Owner: -
|
822 |
|
|
--
|
823 |
|
|
|
824 |
|
|
COMMENT ON FUNCTION "coalesce"(VARIADIC values_ anyarray) IS '
|
825 |
|
|
uses:
|
826 |
|
|
* coalescing array elements or rows together
|
827 |
|
|
* forcing evaluation of all values of a COALESCE()
|
828 |
|
|
';
|
829 |
|
|
|
830 |
|
|
|
831 |
|
|
--
|
832 |
10986
|
aaronmk
|
-- Name: col__min(col_ref); Type: FUNCTION; Schema: util; Owner: -
|
833 |
|
|
--
|
834 |
|
|
|
835 |
|
|
CREATE FUNCTION col__min(col col_ref) RETURNS integer
|
836 |
|
|
LANGUAGE sql STABLE
|
837 |
|
|
AS $_$
|
838 |
|
|
SELECT util.eval2val($$
|
839 |
|
|
SELECT $$||quote_ident($1.name)||$$
|
840 |
|
|
FROM $$||$1.table_||$$
|
841 |
|
|
ORDER BY $$||quote_ident($1.name)||$$ ASC
|
842 |
|
|
LIMIT 1
|
843 |
|
|
$$, NULL::integer)
|
844 |
|
|
$_$;
|
845 |
|
|
|
846 |
|
|
|
847 |
|
|
--
|
848 |
10136
|
aaronmk
|
-- Name: col_comment(col_ref); Type: FUNCTION; Schema: util; Owner: -
|
849 |
|
|
--
|
850 |
|
|
|
851 |
|
|
CREATE FUNCTION col_comment(col col_ref) RETURNS text
|
852 |
|
|
LANGUAGE plpgsql STABLE STRICT
|
853 |
|
|
AS $$
|
854 |
|
|
DECLARE
|
855 |
|
|
comment text;
|
856 |
|
|
BEGIN
|
857 |
|
|
SELECT description
|
858 |
|
|
FROM pg_attribute
|
859 |
|
|
LEFT JOIN pg_description ON objoid = attrelid
|
860 |
|
|
AND classoid = 'pg_class'::regclass AND objsubid = attnum
|
861 |
|
|
WHERE attrelid = col.table_ AND attname = col.name
|
862 |
|
|
INTO STRICT comment
|
863 |
|
|
;
|
864 |
|
|
RETURN comment;
|
865 |
|
|
EXCEPTION
|
866 |
|
|
WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
|
867 |
|
|
END;
|
868 |
|
|
$$;
|
869 |
|
|
|
870 |
|
|
|
871 |
|
|
--
|
872 |
10130
|
aaronmk
|
-- Name: col_default_sql(col_ref); Type: FUNCTION; Schema: util; Owner: -
|
873 |
|
|
--
|
874 |
|
|
|
875 |
|
|
CREATE FUNCTION col_default_sql(col col_ref) RETURNS text
|
876 |
|
|
LANGUAGE plpgsql STABLE STRICT
|
877 |
|
|
AS $$
|
878 |
|
|
DECLARE
|
879 |
|
|
default_sql text;
|
880 |
|
|
BEGIN
|
881 |
|
|
SELECT adsrc
|
882 |
|
|
FROM pg_attribute
|
883 |
|
|
LEFT JOIN pg_attrdef ON adrelid = attrelid AND adnum = attnum
|
884 |
|
|
WHERE attrelid = col.table_ AND attname = col.name
|
885 |
|
|
INTO STRICT default_sql
|
886 |
|
|
;
|
887 |
|
|
RETURN default_sql;
|
888 |
|
|
EXCEPTION
|
889 |
|
|
WHEN no_data_found THEN PERFORM util.raise_undefined_column(col);
|
890 |
|
|
END;
|
891 |
|
|
$$;
|
892 |
|
|
|
893 |
|
|
|
894 |
|
|
--
|
895 |
10134
|
aaronmk
|
-- Name: col_default_value(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
896 |
|
|
--
|
897 |
|
|
|
898 |
|
|
CREATE FUNCTION col_default_value(col col_ref, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
|
899 |
|
|
LANGUAGE sql STABLE
|
900 |
|
|
AS $_$
|
901 |
|
|
SELECT util.eval_expr_passthru(util.col_default_sql($1), $2)
|
902 |
|
|
$_$;
|
903 |
|
|
|
904 |
|
|
|
905 |
|
|
--
|
906 |
|
|
-- Name: FUNCTION col_default_value(col col_ref, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
|
907 |
|
|
--
|
908 |
|
|
|
909 |
12235
|
aaronmk
|
COMMENT ON FUNCTION col_default_value(col col_ref, ret_type_null anyelement) IS '
|
910 |
|
|
ret_type_null: NULL::ret_type
|
911 |
|
|
';
|
912 |
10134
|
aaronmk
|
|
913 |
|
|
|
914 |
|
|
--
|
915 |
8183
|
aaronmk
|
-- Name: col_exists(col_ref); Type: FUNCTION; Schema: util; Owner: -
|
916 |
8180
|
aaronmk
|
--
|
917 |
|
|
|
918 |
|
|
CREATE FUNCTION col_exists(col col_ref) RETURNS boolean
|
919 |
|
|
LANGUAGE plpgsql STRICT
|
920 |
|
|
AS $$
|
921 |
|
|
BEGIN
|
922 |
8183
|
aaronmk
|
PERFORM util.col_type(col);
|
923 |
8180
|
aaronmk
|
RETURN true;
|
924 |
|
|
EXCEPTION
|
925 |
|
|
WHEN undefined_column THEN RETURN false;
|
926 |
|
|
END;
|
927 |
|
|
$$;
|
928 |
|
|
|
929 |
|
|
|
930 |
|
|
--
|
931 |
8183
|
aaronmk
|
-- Name: col_global_names(regtype); Type: FUNCTION; Schema: util; Owner: -
|
932 |
8084
|
aaronmk
|
--
|
933 |
|
|
|
934 |
|
|
CREATE FUNCTION col_global_names(type regtype, OUT name text, OUT global_name text) RETURNS SETOF record
|
935 |
8097
|
aaronmk
|
LANGUAGE plpgsql STABLE STRICT
|
936 |
8084
|
aaronmk
|
AS $$
|
937 |
|
|
DECLARE
|
938 |
8183
|
aaronmk
|
prefix text := util.name(type)||'.';
|
939 |
8084
|
aaronmk
|
BEGIN
|
940 |
|
|
RETURN QUERY
|
941 |
8183
|
aaronmk
|
SELECT name_, (CASE WHEN util.contains(search_for:='.', in_str:=name_) THEN '' ELSE prefix END)||name_
|
942 |
|
|
FROM util.col_names(type) f (name_);
|
943 |
8084
|
aaronmk
|
END;
|
944 |
|
|
$$;
|
945 |
|
|
|
946 |
|
|
|
947 |
|
|
--
|
948 |
8183
|
aaronmk
|
-- Name: col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
|
949 |
8151
|
aaronmk
|
--
|
950 |
|
|
|
951 |
|
|
CREATE FUNCTION col_names(table_ regclass) RETURNS SETOF text
|
952 |
12446
|
aaronmk
|
LANGUAGE sql STABLE
|
953 |
8151
|
aaronmk
|
AS $_$
|
954 |
|
|
SELECT attname::text
|
955 |
|
|
FROM pg_attribute
|
956 |
10158
|
aaronmk
|
WHERE attrelid = $1 AND attnum >= 1 AND NOT attisdropped
|
957 |
8151
|
aaronmk
|
ORDER BY attnum
|
958 |
|
|
$_$;
|
959 |
|
|
|
960 |
|
|
|
961 |
|
|
--
|
962 |
11667
|
aaronmk
|
-- Name: col_names(regtype); Type: FUNCTION; Schema: util; Owner: -
|
963 |
|
|
--
|
964 |
|
|
|
965 |
|
|
CREATE FUNCTION col_names(type regtype) RETURNS SETOF text
|
966 |
|
|
LANGUAGE plpgsql STABLE STRICT
|
967 |
|
|
AS $_$
|
968 |
|
|
BEGIN
|
969 |
|
|
RETURN QUERY EXECUTE $$SELECT skeys(hstore(NULL::$$||type||$$))$$;
|
970 |
|
|
END;
|
971 |
|
|
$_$;
|
972 |
|
|
|
973 |
|
|
|
974 |
|
|
--
|
975 |
8183
|
aaronmk
|
-- Name: col_type(col_ref); Type: FUNCTION; Schema: util; Owner: -
|
976 |
8106
|
aaronmk
|
--
|
977 |
|
|
|
978 |
|
|
CREATE FUNCTION col_type(col col_ref) RETURNS regtype
|
979 |
8169
|
aaronmk
|
LANGUAGE plpgsql STABLE STRICT
|
980 |
|
|
AS $$
|
981 |
|
|
DECLARE
|
982 |
|
|
type regtype;
|
983 |
|
|
BEGIN
|
984 |
|
|
SELECT atttypid FROM pg_attribute
|
985 |
|
|
WHERE attrelid = col.table_ AND attname = col.name
|
986 |
|
|
INTO STRICT type
|
987 |
|
|
;
|
988 |
|
|
RETURN type;
|
989 |
|
|
EXCEPTION
|
990 |
8171
|
aaronmk
|
WHEN no_data_found THEN
|
991 |
8181
|
aaronmk
|
RAISE undefined_column USING MESSAGE =
|
992 |
|
|
concat('undefined column: ', col.name);
|
993 |
8169
|
aaronmk
|
END;
|
994 |
|
|
$$;
|
995 |
8106
|
aaronmk
|
|
996 |
|
|
|
997 |
|
|
--
|
998 |
12368
|
aaronmk
|
-- Name: comment(oid); Type: FUNCTION; Schema: util; Owner: -
|
999 |
|
|
--
|
1000 |
|
|
|
1001 |
|
|
CREATE FUNCTION comment(element oid) RETURNS text
|
1002 |
12446
|
aaronmk
|
LANGUAGE sql STABLE
|
1003 |
12368
|
aaronmk
|
AS $_$
|
1004 |
|
|
SELECT description FROM pg_description WHERE objoid = $1
|
1005 |
|
|
$_$;
|
1006 |
|
|
|
1007 |
|
|
|
1008 |
|
|
--
|
1009 |
11005
|
aaronmk
|
-- Name: concat_esc(text, text); Type: FUNCTION; Schema: util; Owner: -
|
1010 |
|
|
--
|
1011 |
|
|
|
1012 |
|
|
CREATE FUNCTION concat_esc("left" text, "right" text) RETURNS text
|
1013 |
|
|
LANGUAGE sql IMMUTABLE
|
1014 |
|
|
AS $_$
|
1015 |
|
|
SELECT util.esc_name__append($2, $1)
|
1016 |
|
|
$_$;
|
1017 |
|
|
|
1018 |
|
|
|
1019 |
|
|
--
|
1020 |
8183
|
aaronmk
|
-- Name: contains(text, text); Type: FUNCTION; Schema: util; Owner: -
|
1021 |
8095
|
aaronmk
|
--
|
1022 |
|
|
|
1023 |
|
|
CREATE FUNCTION contains(search_for text, in_str text) RETURNS boolean
|
1024 |
10388
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
1025 |
8095
|
aaronmk
|
AS $_$
|
1026 |
|
|
SELECT position($1 in $2) > 0 /*1-based offset*/
|
1027 |
|
|
$_$;
|
1028 |
|
|
|
1029 |
|
|
|
1030 |
|
|
--
|
1031 |
12288
|
aaronmk
|
-- Name: copy_struct(regclass, text); Type: FUNCTION; Schema: util; Owner: -
|
1032 |
|
|
--
|
1033 |
|
|
|
1034 |
|
|
CREATE FUNCTION copy_struct(from_ regclass, to_ text) RETURNS void
|
1035 |
|
|
LANGUAGE sql
|
1036 |
|
|
AS $_$
|
1037 |
|
|
SELECT util.eval($$CREATE TABLE $$||$2||$$ (LIKE $$||$1||$$ INCLUDING ALL)$$)
|
1038 |
|
|
$_$;
|
1039 |
|
|
|
1040 |
|
|
|
1041 |
|
|
--
|
1042 |
12649
|
aaronmk
|
-- Name: copy_types_and_data(regclass, text); Type: FUNCTION; Schema: util; Owner: -
|
1043 |
|
|
--
|
1044 |
|
|
|
1045 |
|
|
CREATE FUNCTION copy_types_and_data(from_ regclass, to_ text) RETURNS void
|
1046 |
|
|
LANGUAGE sql
|
1047 |
|
|
AS $_$
|
1048 |
|
|
SELECT util.materialize_view($2, $1)
|
1049 |
|
|
$_$;
|
1050 |
|
|
|
1051 |
|
|
|
1052 |
|
|
--
|
1053 |
13495
|
aaronmk
|
-- Name: create_if_not_exists(text, text); Type: FUNCTION; Schema: util; Owner: -
|
1054 |
8094
|
aaronmk
|
--
|
1055 |
|
|
|
1056 |
13495
|
aaronmk
|
CREATE FUNCTION create_if_not_exists(sql text, relation text DEFAULT NULL::text) RETURNS void
|
1057 |
|
|
LANGUAGE plpgsql
|
1058 |
8094
|
aaronmk
|
AS $$
|
1059 |
|
|
BEGIN
|
1060 |
13495
|
aaronmk
|
/* always generate standard exception if exists, even if table definition
|
1061 |
|
|
would be invalid (which generates a variety of exceptions) */
|
1062 |
|
|
IF util.relation_exists(relation) THEN RAISE duplicate_table; END IF;
|
1063 |
12595
|
aaronmk
|
PERFORM util.eval(sql);
|
1064 |
8094
|
aaronmk
|
EXCEPTION
|
1065 |
12676
|
aaronmk
|
WHEN duplicate_table
|
1066 |
|
|
OR duplicate_object -- eg. constraint
|
1067 |
|
|
OR duplicate_column
|
1068 |
|
|
OR duplicate_function
|
1069 |
|
|
THEN NULL;
|
1070 |
12595
|
aaronmk
|
WHEN invalid_table_definition THEN
|
1071 |
|
|
IF SQLERRM LIKE 'multiple primary keys for table % are not allowed' THEN NULL;
|
1072 |
|
|
ELSE RAISE;
|
1073 |
|
|
END IF;
|
1074 |
8094
|
aaronmk
|
END;
|
1075 |
|
|
$$;
|
1076 |
|
|
|
1077 |
|
|
|
1078 |
|
|
--
|
1079 |
13495
|
aaronmk
|
-- Name: FUNCTION create_if_not_exists(sql text, relation text); Type: COMMENT; Schema: util; Owner: -
|
1080 |
8094
|
aaronmk
|
--
|
1081 |
|
|
|
1082 |
13495
|
aaronmk
|
COMMENT ON FUNCTION create_if_not_exists(sql text, relation text) IS '
|
1083 |
12235
|
aaronmk
|
idempotent
|
1084 |
|
|
';
|
1085 |
8094
|
aaronmk
|
|
1086 |
|
|
|
1087 |
|
|
--
|
1088 |
12378
|
aaronmk
|
-- Name: curr_func(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
1089 |
|
|
--
|
1090 |
|
|
|
1091 |
|
|
CREATE FUNCTION curr_func(func text, schema_anchor anyelement) RETURNS text
|
1092 |
12590
|
aaronmk
|
LANGUAGE sql STABLE
|
1093 |
12378
|
aaronmk
|
AS $$
|
1094 |
|
|
SELECT util.schema_esc(schema_anchor)||'.'||quote_ident(func)
|
1095 |
|
|
$$;
|
1096 |
|
|
|
1097 |
|
|
|
1098 |
|
|
--
|
1099 |
12668
|
aaronmk
|
-- Name: debug_print_func_call(text); Type: FUNCTION; Schema: util; Owner: -
|
1100 |
|
|
--
|
1101 |
|
|
|
1102 |
|
|
CREATE FUNCTION debug_print_func_call(func_call text) RETURNS void
|
1103 |
|
|
LANGUAGE sql IMMUTABLE
|
1104 |
|
|
AS $_$
|
1105 |
|
|
SELECT util.raise('NOTICE', $$SELECT $$||$1)
|
1106 |
|
|
$_$;
|
1107 |
|
|
|
1108 |
|
|
|
1109 |
|
|
--
|
1110 |
12433
|
aaronmk
|
-- Name: debug_print_return_value(anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
|
1111 |
12430
|
aaronmk
|
--
|
1112 |
|
|
|
1113 |
12433
|
aaronmk
|
CREATE FUNCTION debug_print_return_value(value anyelement, encode boolean DEFAULT false) RETURNS anyelement
|
1114 |
12430
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
1115 |
|
|
AS $_$
|
1116 |
13447
|
aaronmk
|
SELECT util.debug_print_value('returns: ', $1, $2);
|
1117 |
12430
|
aaronmk
|
SELECT $1;
|
1118 |
|
|
$_$;
|
1119 |
|
|
|
1120 |
|
|
|
1121 |
|
|
--
|
1122 |
12250
|
aaronmk
|
-- Name: debug_print_sql(text); Type: FUNCTION; Schema: util; Owner: -
|
1123 |
|
|
--
|
1124 |
|
|
|
1125 |
|
|
CREATE FUNCTION debug_print_sql(sql text) RETURNS void
|
1126 |
|
|
LANGUAGE sql IMMUTABLE
|
1127 |
|
|
AS $_$
|
1128 |
|
|
/* newline before so the query starts at the beginning of the line.
|
1129 |
|
|
newline after to visually separate queries from one another. */
|
1130 |
12534
|
aaronmk
|
SELECT util.raise('NOTICE', $$
|
1131 |
12474
|
aaronmk
|
$$||util.runnable_sql($1)||$$
|
1132 |
12464
|
aaronmk
|
$$||COALESCE(util.explain2notice_msg_if_can($1), ''))
|
1133 |
12250
|
aaronmk
|
$_$;
|
1134 |
|
|
|
1135 |
|
|
|
1136 |
|
|
--
|
1137 |
13446
|
aaronmk
|
-- Name: debug_print_value(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
|
1138 |
|
|
--
|
1139 |
|
|
|
1140 |
|
|
CREATE FUNCTION debug_print_value(label text, value anyelement, encode boolean DEFAULT false) RETURNS void
|
1141 |
|
|
LANGUAGE sql IMMUTABLE
|
1142 |
|
|
AS $_$
|
1143 |
|
|
SELECT util.raise('NOTICE', concat($1,
|
1144 |
13449
|
aaronmk
|
(CASE WHEN $3 THEN util.quote_typed($2) ELSE $2::text END))||$$
|
1145 |
|
|
$$)
|
1146 |
13446
|
aaronmk
|
$_$;
|
1147 |
|
|
|
1148 |
|
|
|
1149 |
|
|
--
|
1150 |
13448
|
aaronmk
|
-- Name: debug_print_var(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
|
1151 |
|
|
--
|
1152 |
|
|
|
1153 |
|
|
CREATE FUNCTION debug_print_var(var text, value anyelement, encode boolean DEFAULT false) RETURNS void
|
1154 |
|
|
LANGUAGE sql IMMUTABLE
|
1155 |
|
|
AS $_$
|
1156 |
|
|
/* can't use EXECUTE in the caller because "No substitution of PL/pgSQL
|
1157 |
|
|
variables is done on the computed command string"
|
1158 |
|
|
(http://www.postgresql.org/docs/9.3/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) */
|
1159 |
|
|
SELECT util.debug_print_value($1||' = ', $2, $3)
|
1160 |
|
|
$_$;
|
1161 |
|
|
|
1162 |
|
|
|
1163 |
|
|
--
|
1164 |
10364
|
aaronmk
|
-- Name: derived_cols(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
|
1165 |
|
|
--
|
1166 |
|
|
|
1167 |
|
|
CREATE FUNCTION derived_cols(table_ regclass, names regclass) RETURNS SETOF text
|
1168 |
12446
|
aaronmk
|
LANGUAGE sql STABLE
|
1169 |
10364
|
aaronmk
|
AS $_$
|
1170 |
|
|
SELECT util.eval2set($$
|
1171 |
|
|
SELECT col
|
1172 |
|
|
FROM util.col_names($$||quote_nullable($1)||$$::regclass) f (col)
|
1173 |
|
|
LEFT JOIN $$||$2||$$ ON "to" = col
|
1174 |
|
|
WHERE "from" IS NULL
|
1175 |
|
|
$$, NULL::text)
|
1176 |
|
|
$_$;
|
1177 |
|
|
|
1178 |
|
|
|
1179 |
|
|
--
|
1180 |
|
|
-- Name: FUNCTION derived_cols(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
|
1181 |
|
|
--
|
1182 |
|
|
|
1183 |
12235
|
aaronmk
|
COMMENT ON FUNCTION derived_cols(table_ regclass, names regclass) IS '
|
1184 |
|
|
gets table_''s derived columns (all the columns not in the names table)
|
1185 |
|
|
';
|
1186 |
10364
|
aaronmk
|
|
1187 |
|
|
|
1188 |
|
|
--
|
1189 |
12298
|
aaronmk
|
-- Name: diff(regclass, regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
1190 |
12044
|
aaronmk
|
--
|
1191 |
|
|
|
1192 |
12298
|
aaronmk
|
CREATE FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
|
1193 |
12427
|
aaronmk
|
LANGUAGE sql
|
1194 |
12044
|
aaronmk
|
AS $_$
|
1195 |
12653
|
aaronmk
|
-- create a diff when the # of copies of a row differs between the tables
|
1196 |
|
|
SELECT util.to_freq($1);
|
1197 |
|
|
SELECT util.to_freq($2);
|
1198 |
12663
|
aaronmk
|
SELECT util.auto_rm_freq(ARRAY[$1, $2]);
|
1199 |
12653
|
aaronmk
|
|
1200 |
|
|
SELECT * FROM util.diff($1, $2, $3, has_freq := true)
|
1201 |
12298
|
aaronmk
|
$_$;
|
1202 |
|
|
|
1203 |
|
|
|
1204 |
|
|
--
|
1205 |
|
|
-- Name: FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
|
1206 |
|
|
--
|
1207 |
|
|
|
1208 |
|
|
COMMENT ON FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
|
1209 |
|
|
usage:
|
1210 |
|
|
SELECT * FROM util.diff(''"left_table"''::regclass, ''"right_table"''::regclass, NULL::shared_base_type)
|
1211 |
12653
|
aaronmk
|
|
1212 |
|
|
col_type_null (*required*): NULL::shared_base_type
|
1213 |
12298
|
aaronmk
|
';
|
1214 |
|
|
|
1215 |
|
|
|
1216 |
|
|
--
|
1217 |
12491
|
aaronmk
|
-- Name: diff(text, text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
|
1218 |
12298
|
aaronmk
|
--
|
1219 |
|
|
|
1220 |
12491
|
aaronmk
|
CREATE FUNCTION diff(left__ text, right__ text, col_type_null anyelement, single_row boolean DEFAULT false, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
|
1221 |
12429
|
aaronmk
|
LANGUAGE plpgsql
|
1222 |
|
|
SET search_path TO pg_temp
|
1223 |
12298
|
aaronmk
|
AS $_$
|
1224 |
12429
|
aaronmk
|
/* must use LANGUAGE plpgsql because LANGUAGE sql does not support runtime
|
1225 |
|
|
changes of search_path (schema elements are bound at inline time rather than
|
1226 |
|
|
runtime) */
|
1227 |
|
|
/* function option search_path is needed to limit the effects of
|
1228 |
12492
|
aaronmk
|
`SET LOCAL search_path` to the current function */
|
1229 |
12429
|
aaronmk
|
BEGIN
|
1230 |
12491
|
aaronmk
|
PERFORM util.use_schema($3); -- includes util.%== as default/fallback
|
1231 |
12429
|
aaronmk
|
|
1232 |
12565
|
aaronmk
|
PERFORM util.mk_keys_func(pg_typeof($3));
|
1233 |
12429
|
aaronmk
|
RETURN QUERY
|
1234 |
12554
|
aaronmk
|
SELECT * FROM util.eval2col_pair(util.mk_diff_query($1, $2,
|
1235 |
12479
|
aaronmk
|
$$/* need to explicitly cast each side to the return type because this does not
|
1236 |
12284
|
aaronmk
|
happen automatically even when an implicit cast is available */
|
1237 |
12479
|
aaronmk
|
left_::$$||util.typeof($3)||$$
|
1238 |
|
|
, right_::$$||util.typeof($3)
|
1239 |
12496
|
aaronmk
|
/* when using the util.%==(anyelement, anyelement) operator, you must cast to
|
1240 |
|
|
the *same* base type, *even though* this is optional when using a custom %== */
|
1241 |
12553
|
aaronmk
|
, util._if($4, $$true/*= CROSS JOIN*/$$,
|
1242 |
|
|
$$ left_::$$||util.typeof($3)||$$
|
1243 |
12496
|
aaronmk
|
%== right_::$$||util.typeof($3)||$$
|
1244 |
|
|
-- refer to EXPLAIN output for expansion of %==$$
|
1245 |
12657
|
aaronmk
|
)
|
1246 |
|
|
, $$ left_::$$||util.typeof($3)||$$
|
1247 |
|
|
IS DISTINCT FROM right_::$$||util.typeof($3)
|
1248 |
|
|
), $3)
|
1249 |
12429
|
aaronmk
|
;
|
1250 |
|
|
END;
|
1251 |
12044
|
aaronmk
|
$_$;
|
1252 |
|
|
|
1253 |
|
|
|
1254 |
|
|
--
|
1255 |
12491
|
aaronmk
|
-- Name: FUNCTION diff(left__ text, right__ text, col_type_null anyelement, single_row boolean, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
|
1256 |
12280
|
aaronmk
|
--
|
1257 |
|
|
|
1258 |
12491
|
aaronmk
|
COMMENT ON FUNCTION diff(left__ text, right__ text, col_type_null anyelement, single_row boolean, OUT left_ anyelement, OUT right_ anyelement) IS '
|
1259 |
12280
|
aaronmk
|
col_type_null (*required*): NULL::col_type
|
1260 |
12299
|
aaronmk
|
single_row: whether the tables consist of a single row, which should be
|
1261 |
|
|
displayed side-by-side
|
1262 |
12282
|
aaronmk
|
|
1263 |
12498
|
aaronmk
|
to match up rows using a subset of the columns, create a custom keys() function
|
1264 |
|
|
which returns this subset as a record:
|
1265 |
|
|
-- note that OUT parameters for the returned fields are *not* needed
|
1266 |
|
|
CREATE OR REPLACE FUNCTION [schema].keys(value [schema].[base_type])
|
1267 |
|
|
RETURNS record AS
|
1268 |
|
|
$BODY$
|
1269 |
|
|
SELECT ($1.key_field_0, $1.key_field_1)
|
1270 |
|
|
$BODY$
|
1271 |
|
|
LANGUAGE sql IMMUTABLE
|
1272 |
|
|
COST 100;
|
1273 |
|
|
|
1274 |
|
|
|
1275 |
12282
|
aaronmk
|
to run EXPLAIN on the FULL JOIN query:
|
1276 |
|
|
# run this function
|
1277 |
|
|
# look for a NOTICE containing the expanded query that it ran
|
1278 |
|
|
# run EXPLAIN on this expanded query
|
1279 |
12280
|
aaronmk
|
';
|
1280 |
|
|
|
1281 |
|
|
|
1282 |
|
|
--
|
1283 |
12653
|
aaronmk
|
-- Name: diff(regclass, regclass, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
|
1284 |
|
|
--
|
1285 |
|
|
|
1286 |
|
|
CREATE FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, has_freq boolean, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
|
1287 |
|
|
LANGUAGE sql
|
1288 |
|
|
AS $_$
|
1289 |
|
|
SELECT * FROM util.diff($1::text, $2::text, $3,
|
1290 |
|
|
single_row := util.has_single_row($1) AND util.has_single_row($2))
|
1291 |
|
|
$_$;
|
1292 |
|
|
|
1293 |
|
|
|
1294 |
|
|
--
|
1295 |
|
|
-- Name: FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, has_freq boolean, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
|
1296 |
|
|
--
|
1297 |
|
|
|
1298 |
|
|
COMMENT ON FUNCTION diff(left_table regclass, right_table regclass, col_type_null anyelement, has_freq boolean, OUT left_ anyelement, OUT right_ anyelement) IS '
|
1299 |
|
|
helper function used by diff(regclass, regclass)
|
1300 |
|
|
|
1301 |
|
|
usage:
|
1302 |
|
|
SELECT * FROM util.diff(''"left_freq_table"''::regclass, ''"right_freq_table"''::regclass, NULL::shared_base_type, has_freq := true)
|
1303 |
|
|
|
1304 |
|
|
col_type_null (*required*): NULL::shared_base_type
|
1305 |
|
|
';
|
1306 |
|
|
|
1307 |
|
|
|
1308 |
|
|
--
|
1309 |
8200
|
aaronmk
|
-- Name: do_optionally_ignore(text, boolean); Type: FUNCTION; Schema: util; Owner: -
|
1310 |
|
|
--
|
1311 |
|
|
|
1312 |
|
|
CREATE FUNCTION do_optionally_ignore(sql text, ignore boolean) RETURNS void
|
1313 |
12446
|
aaronmk
|
LANGUAGE sql
|
1314 |
8200
|
aaronmk
|
AS $_$
|
1315 |
|
|
SELECT CASE WHEN $2 THEN util.try_create($1) ELSE util.create_if_not_exists($1) END
|
1316 |
|
|
$_$;
|
1317 |
|
|
|
1318 |
|
|
|
1319 |
|
|
--
|
1320 |
|
|
-- Name: FUNCTION do_optionally_ignore(sql text, ignore boolean); Type: COMMENT; Schema: util; Owner: -
|
1321 |
|
|
--
|
1322 |
|
|
|
1323 |
12235
|
aaronmk
|
COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS '
|
1324 |
|
|
idempotent
|
1325 |
|
|
';
|
1326 |
8200
|
aaronmk
|
|
1327 |
|
|
|
1328 |
|
|
--
|
1329 |
12292
|
aaronmk
|
-- Name: drop_column(col_ref, boolean); Type: FUNCTION; Schema: util; Owner: -
|
1330 |
10362
|
aaronmk
|
--
|
1331 |
|
|
|
1332 |
12292
|
aaronmk
|
CREATE FUNCTION drop_column(col col_ref, force boolean DEFAULT false) RETURNS void
|
1333 |
12446
|
aaronmk
|
LANGUAGE sql
|
1334 |
10362
|
aaronmk
|
AS $_$
|
1335 |
|
|
SELECT util.eval($$ALTER TABLE $$||$1.table_||$$ DROP COLUMN IF EXISTS $$||
|
1336 |
12292
|
aaronmk
|
quote_ident($1.name)||util._if($2, $$ CASCADE$$, ''::text))
|
1337 |
10362
|
aaronmk
|
$_$;
|
1338 |
|
|
|
1339 |
|
|
|
1340 |
|
|
--
|
1341 |
12292
|
aaronmk
|
-- Name: FUNCTION drop_column(col col_ref, force boolean); Type: COMMENT; Schema: util; Owner: -
|
1342 |
10362
|
aaronmk
|
--
|
1343 |
|
|
|
1344 |
12292
|
aaronmk
|
COMMENT ON FUNCTION drop_column(col col_ref, force boolean) IS '
|
1345 |
12235
|
aaronmk
|
idempotent
|
1346 |
|
|
';
|
1347 |
10362
|
aaronmk
|
|
1348 |
|
|
|
1349 |
|
|
--
|
1350 |
12660
|
aaronmk
|
-- Name: drop_column(regclass[], text, boolean); Type: FUNCTION; Schema: util; Owner: -
|
1351 |
|
|
--
|
1352 |
|
|
|
1353 |
|
|
CREATE FUNCTION drop_column(tables regclass[], col text, force boolean DEFAULT false) RETURNS void
|
1354 |
|
|
LANGUAGE sql
|
1355 |
|
|
AS $_$
|
1356 |
12686
|
aaronmk
|
SELECT util.drop_column((table_, $2), $3) FROM unnest($1) table_;
|
1357 |
|
|
SELECT NULL::void; -- don't fold away functions called in previous query
|
1358 |
12660
|
aaronmk
|
$_$;
|
1359 |
|
|
|
1360 |
|
|
|
1361 |
|
|
--
|
1362 |
|
|
-- Name: FUNCTION drop_column(tables regclass[], col text, force boolean); Type: COMMENT; Schema: util; Owner: -
|
1363 |
|
|
--
|
1364 |
|
|
|
1365 |
|
|
COMMENT ON FUNCTION drop_column(tables regclass[], col text, force boolean) IS '
|
1366 |
|
|
idempotent
|
1367 |
|
|
';
|
1368 |
|
|
|
1369 |
|
|
|
1370 |
|
|
--
|
1371 |
12587
|
aaronmk
|
-- Name: drop_relation(anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
|
1372 |
12331
|
aaronmk
|
--
|
1373 |
|
|
|
1374 |
12587
|
aaronmk
|
CREATE FUNCTION drop_relation(relation anyelement, force boolean DEFAULT false) RETURNS void
|
1375 |
12331
|
aaronmk
|
LANGUAGE sql
|
1376 |
|
|
AS $_$
|
1377 |
12353
|
aaronmk
|
/* use util.qual_name() instead of ::text so that the schema qualifier is always
|
1378 |
|
|
included in the debug SQL */
|
1379 |
|
|
SELECT util.drop_relation(util.relation_type($1), util.qual_name($1), $2)
|
1380 |
12331
|
aaronmk
|
$_$;
|
1381 |
|
|
|
1382 |
|
|
|
1383 |
|
|
--
|
1384 |
12343
|
aaronmk
|
-- Name: drop_relation(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
|
1385 |
|
|
--
|
1386 |
|
|
|
1387 |
12364
|
aaronmk
|
CREATE FUNCTION drop_relation(type text, relation_esc text, force boolean DEFAULT false) RETURNS void
|
1388 |
12343
|
aaronmk
|
LANGUAGE sql
|
1389 |
|
|
AS $_$
|
1390 |
12347
|
aaronmk
|
SELECT util.eval($$DROP $$||$1||$$ IF EXISTS $$||$2
|
1391 |
12343
|
aaronmk
|
||util._if($3, $$ CASCADE$$, ''::text))
|
1392 |
|
|
$_$;
|
1393 |
|
|
|
1394 |
|
|
|
1395 |
|
|
--
|
1396 |
12364
|
aaronmk
|
-- Name: FUNCTION drop_relation(type text, relation_esc text, force boolean); Type: COMMENT; Schema: util; Owner: -
|
1397 |
12343
|
aaronmk
|
--
|
1398 |
|
|
|
1399 |
12364
|
aaronmk
|
COMMENT ON FUNCTION drop_relation(type text, relation_esc text, force boolean) IS '
|
1400 |
12343
|
aaronmk
|
idempotent
|
1401 |
|
|
';
|
1402 |
|
|
|
1403 |
|
|
|
1404 |
|
|
--
|
1405 |
12413
|
aaronmk
|
-- Name: drop_relations_like(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
|
1406 |
|
|
--
|
1407 |
|
|
|
1408 |
|
|
CREATE FUNCTION drop_relations_like(name_regexp text, schema_anchor anyelement, force boolean DEFAULT false) RETURNS void
|
1409 |
|
|
LANGUAGE sql
|
1410 |
|
|
AS $_$
|
1411 |
12502
|
aaronmk
|
SELECT util.drop_relations_like($1, util.schema_regexp($2), $3)
|
1412 |
12413
|
aaronmk
|
$_$;
|
1413 |
|
|
|
1414 |
|
|
|
1415 |
|
|
--
|
1416 |
|
|
-- Name: drop_relations_like(text, text, boolean); Type: FUNCTION; Schema: util; Owner: -
|
1417 |
|
|
--
|
1418 |
|
|
|
1419 |
|
|
CREATE FUNCTION drop_relations_like(name_regexp text, schema_regexp text DEFAULT ''::text, force boolean DEFAULT false) RETURNS void
|
1420 |
|
|
LANGUAGE sql
|
1421 |
|
|
AS $_$
|
1422 |
12668
|
aaronmk
|
SELECT util.debug_print_func_call(util.quote_func_call(
|
1423 |
|
|
'util.drop_relations_like', util.quote_typed($1), util.quote_typed($2),
|
1424 |
|
|
util.quote_typed($3)))
|
1425 |
12667
|
aaronmk
|
;
|
1426 |
12413
|
aaronmk
|
SELECT util.drop_relation(relation, $3)
|
1427 |
|
|
FROM util.show_relations_like($1, $2) relation
|
1428 |
|
|
;
|
1429 |
|
|
SELECT NULL::void; -- don't fold away functions called in previous query
|
1430 |
|
|
$_$;
|
1431 |
|
|
|
1432 |
|
|
|
1433 |
|
|
--
|
1434 |
12292
|
aaronmk
|
-- Name: drop_table(text, boolean); Type: FUNCTION; Schema: util; Owner: -
|
1435 |
10150
|
aaronmk
|
--
|
1436 |
|
|
|
1437 |
12292
|
aaronmk
|
CREATE FUNCTION drop_table(table_ text, force boolean DEFAULT false) RETURNS void
|
1438 |
12446
|
aaronmk
|
LANGUAGE sql
|
1439 |
10150
|
aaronmk
|
AS $_$
|
1440 |
12347
|
aaronmk
|
SELECT util.drop_relation('TABLE', $1, $2)
|
1441 |
10150
|
aaronmk
|
$_$;
|
1442 |
|
|
|
1443 |
|
|
|
1444 |
|
|
--
|
1445 |
12292
|
aaronmk
|
-- Name: FUNCTION drop_table(table_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
|
1446 |
10150
|
aaronmk
|
--
|
1447 |
|
|
|
1448 |
12292
|
aaronmk
|
COMMENT ON FUNCTION drop_table(table_ text, force boolean) IS '
|
1449 |
12235
|
aaronmk
|
idempotent
|
1450 |
|
|
';
|
1451 |
10150
|
aaronmk
|
|
1452 |
|
|
|
1453 |
|
|
--
|
1454 |
12292
|
aaronmk
|
-- Name: drop_view(text, boolean); Type: FUNCTION; Schema: util; Owner: -
|
1455 |
12229
|
aaronmk
|
--
|
1456 |
|
|
|
1457 |
12292
|
aaronmk
|
CREATE FUNCTION drop_view(view_ text, force boolean DEFAULT false) RETURNS void
|
1458 |
12446
|
aaronmk
|
LANGUAGE sql
|
1459 |
12229
|
aaronmk
|
AS $_$
|
1460 |
12347
|
aaronmk
|
SELECT util.drop_relation('VIEW', $1, $2)
|
1461 |
12229
|
aaronmk
|
$_$;
|
1462 |
|
|
|
1463 |
|
|
|
1464 |
|
|
--
|
1465 |
12292
|
aaronmk
|
-- Name: FUNCTION drop_view(view_ text, force boolean); Type: COMMENT; Schema: util; Owner: -
|
1466 |
12229
|
aaronmk
|
--
|
1467 |
|
|
|
1468 |
12292
|
aaronmk
|
COMMENT ON FUNCTION drop_view(view_ text, force boolean) IS '
|
1469 |
12235
|
aaronmk
|
idempotent
|
1470 |
|
|
';
|
1471 |
12229
|
aaronmk
|
|
1472 |
|
|
|
1473 |
|
|
--
|
1474 |
10322
|
aaronmk
|
-- Name: empty_array(anyelement); Type: FUNCTION; Schema: util; Owner: -
|
1475 |
|
|
--
|
1476 |
|
|
|
1477 |
|
|
CREATE FUNCTION empty_array(elem_type_null anyelement DEFAULT NULL::text) RETURNS anyarray
|
1478 |
|
|
LANGUAGE sql IMMUTABLE
|
1479 |
|
|
AS $_$
|
1480 |
|
|
SELECT util.array_fill($1, 0)
|
1481 |
|
|
$_$;
|
1482 |
|
|
|
1483 |
|
|
|
1484 |
|
|
--
|
1485 |
|
|
-- Name: FUNCTION empty_array(elem_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
|
1486 |
|
|
--
|
1487 |
|
|
|
1488 |
12235
|
aaronmk
|
COMMENT ON FUNCTION empty_array(elem_type_null anyelement) IS '
|
1489 |
|
|
constructs proper empty 1-dimensional array whose dimensions are not NULL ( ''{}''::text[] does not do this)
|
1490 |
|
|
';
|
1491 |
10322
|
aaronmk
|
|
1492 |
|
|
|
1493 |
|
|
--
|
1494 |
8183
|
aaronmk
|
-- Name: ensure_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
|
1495 |
8086
|
aaronmk
|
--
|
1496 |
|
|
|
1497 |
|
|
CREATE FUNCTION ensure_prefix(prefix text, str text) RETURNS text
|
1498 |
10388
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
1499 |
8086
|
aaronmk
|
AS $_$
|
1500 |
8183
|
aaronmk
|
SELECT (CASE WHEN util.has_prefix($1, $2) THEN $2 ELSE $1||$2 END)
|
1501 |
8086
|
aaronmk
|
$_$;
|
1502 |
|
|
|
1503 |
|
|
|
1504 |
|
|
--
|
1505 |
10987
|
aaronmk
|
-- Name: esc_name__append(text, text); Type: FUNCTION; Schema: util; Owner: -
|
1506 |
|
|
--
|
1507 |
|
|
|
1508 |
|
|
CREATE FUNCTION esc_name__append(suffix text, esc_name text) RETURNS text
|
1509 |
|
|
LANGUAGE sql IMMUTABLE
|
1510 |
|
|
AS $_$
|
1511 |
|
|
SELECT regexp_replace($2, '("?)$', $1||'\1')
|
1512 |
|
|
$_$;
|
1513 |
|
|
|
1514 |
|
|
|
1515 |
|
|
--
|
1516 |
13455
|
aaronmk
|
-- Name: eval(text[]); Type: FUNCTION; Schema: util; Owner: -
|
1517 |
|
|
--
|
1518 |
|
|
|
1519 |
|
|
CREATE FUNCTION eval(queries text[]) RETURNS void
|
1520 |
|
|
LANGUAGE sql
|
1521 |
|
|
AS $_$
|
1522 |
|
|
SELECT util.eval(query) FROM unnest($1) query;
|
1523 |
|
|
SELECT NULL::void; -- don't fold away functions called in previous query
|
1524 |
|
|
$_$;
|
1525 |
|
|
|
1526 |
|
|
|
1527 |
|
|
--
|
1528 |
12531
|
aaronmk
|
-- Name: eval(text, boolean); Type: FUNCTION; Schema: util; Owner: -
|
1529 |
9824
|
aaronmk
|
--
|
1530 |
|
|
|
1531 |
12531
|
aaronmk
|
CREATE FUNCTION eval(sql text, verbose_ boolean DEFAULT true) RETURNS void
|
1532 |
12558
|
aaronmk
|
LANGUAGE plpgsql
|
1533 |
9824
|
aaronmk
|
AS $$
|
1534 |
|
|
BEGIN
|
1535 |
12531
|
aaronmk
|
IF verbose_ THEN PERFORM util.debug_print_sql(sql); END IF;
|
1536 |
12251
|
aaronmk
|
EXECUTE sql;
|
1537 |
9824
|
aaronmk
|
END;
|
1538 |
|
|
$$;
|
1539 |
|
|
|
1540 |
|
|
|
1541 |
|
|
--
|
1542 |
12181
|
aaronmk
|
-- Name: eval2col_pair(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
1543 |
|
|
--
|
1544 |
|
|
|
1545 |
|
|
CREATE FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) RETURNS SETOF record
|
1546 |
|
|
LANGUAGE plpgsql
|
1547 |
|
|
AS $$
|
1548 |
|
|
BEGIN
|
1549 |
12251
|
aaronmk
|
PERFORM util.debug_print_sql(sql);
|
1550 |
12181
|
aaronmk
|
RETURN QUERY EXECUTE sql;
|
1551 |
|
|
END;
|
1552 |
|
|
$$;
|
1553 |
|
|
|
1554 |
|
|
|
1555 |
|
|
--
|
1556 |
|
|
-- Name: FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement); Type: COMMENT; Schema: util; Owner: -
|
1557 |
|
|
--
|
1558 |
|
|
|
1559 |
12235
|
aaronmk
|
COMMENT ON FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '
|
1560 |
|
|
col_type_null (*required*): NULL::col_type
|
1561 |
|
|
';
|
1562 |
12181
|
aaronmk
|
|
1563 |
|
|
|
1564 |
|
|
--
|
1565 |
12301
|
aaronmk
|
-- Name: eval2records(text); Type: FUNCTION; Schema: util; Owner: -
|
1566 |
|
|
--
|
1567 |
|
|
|
1568 |
|
|
CREATE FUNCTION eval2records(sql text) RETURNS SETOF record
|
1569 |
|
|
LANGUAGE plpgsql
|
1570 |
|
|
AS $$
|
1571 |
|
|
BEGIN
|
1572 |
|
|
PERFORM util.debug_print_sql(sql);
|
1573 |
|
|
RETURN QUERY EXECUTE sql;
|
1574 |
|
|
END;
|
1575 |
|
|
$$;
|
1576 |
|
|
|
1577 |
|
|
|
1578 |
|
|
--
|
1579 |
12458
|
aaronmk
|
-- Name: eval2set(text, anyelement, boolean); Type: FUNCTION; Schema: util; Owner: -
|
1580 |
10363
|
aaronmk
|
--
|
1581 |
|
|
|
1582 |
12458
|
aaronmk
|
CREATE FUNCTION eval2set(sql text, ret_type_null anyelement DEFAULT NULL::text, verbose_ boolean DEFAULT true) RETURNS SETOF anyelement
|
1583 |
10363
|
aaronmk
|
LANGUAGE plpgsql
|
1584 |
|
|
AS $$
|
1585 |
|
|
BEGIN
|
1586 |
12458
|
aaronmk
|
IF verbose_ THEN PERFORM util.debug_print_sql(sql); END IF;
|
1587 |
10363
|
aaronmk
|
RETURN QUERY EXECUTE sql;
|
1588 |
|
|
END;
|
1589 |
|
|
$$;
|
1590 |
|
|
|
1591 |
|
|
|
1592 |
|
|
--
|
1593 |
10129
|
aaronmk
|
-- Name: eval2val(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
1594 |
10128
|
aaronmk
|
--
|
1595 |
|
|
|
1596 |
10129
|
aaronmk
|
CREATE FUNCTION eval2val(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
|
1597 |
12514
|
aaronmk
|
LANGUAGE plpgsql STABLE
|
1598 |
10128
|
aaronmk
|
AS $$
|
1599 |
|
|
DECLARE
|
1600 |
|
|
ret_val ret_type_null%TYPE;
|
1601 |
|
|
BEGIN
|
1602 |
12251
|
aaronmk
|
PERFORM util.debug_print_sql(sql);
|
1603 |
10128
|
aaronmk
|
EXECUTE sql INTO STRICT ret_val;
|
1604 |
|
|
RETURN ret_val;
|
1605 |
|
|
END;
|
1606 |
|
|
$$;
|
1607 |
|
|
|
1608 |
|
|
|
1609 |
|
|
--
|
1610 |
10129
|
aaronmk
|
-- Name: FUNCTION eval2val(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
|
1611 |
10128
|
aaronmk
|
--
|
1612 |
|
|
|
1613 |
12235
|
aaronmk
|
COMMENT ON FUNCTION eval2val(sql text, ret_type_null anyelement) IS '
|
1614 |
|
|
ret_type_null: NULL::ret_type
|
1615 |
|
|
';
|
1616 |
10128
|
aaronmk
|
|
1617 |
|
|
|
1618 |
|
|
--
|
1619 |
10131
|
aaronmk
|
-- Name: eval_expr(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
1620 |
|
|
--
|
1621 |
|
|
|
1622 |
|
|
CREATE FUNCTION eval_expr(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
|
1623 |
|
|
LANGUAGE sql
|
1624 |
|
|
AS $_$
|
1625 |
10132
|
aaronmk
|
SELECT util.eval2val($$SELECT $$||$1, $2)
|
1626 |
10131
|
aaronmk
|
$_$;
|
1627 |
|
|
|
1628 |
|
|
|
1629 |
|
|
--
|
1630 |
|
|
-- Name: FUNCTION eval_expr(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
|
1631 |
|
|
--
|
1632 |
|
|
|
1633 |
12235
|
aaronmk
|
COMMENT ON FUNCTION eval_expr(sql text, ret_type_null anyelement) IS '
|
1634 |
|
|
ret_type_null: NULL::ret_type
|
1635 |
|
|
';
|
1636 |
10131
|
aaronmk
|
|
1637 |
|
|
|
1638 |
|
|
--
|
1639 |
10133
|
aaronmk
|
-- Name: eval_expr_passthru(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
1640 |
|
|
--
|
1641 |
|
|
|
1642 |
|
|
CREATE FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement DEFAULT NULL::text) RETURNS anyelement
|
1643 |
|
|
LANGUAGE sql
|
1644 |
|
|
AS $_$
|
1645 |
|
|
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE util.eval_expr($1, $2) END
|
1646 |
|
|
$_$;
|
1647 |
|
|
|
1648 |
|
|
|
1649 |
|
|
--
|
1650 |
|
|
-- Name: FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
|
1651 |
|
|
--
|
1652 |
|
|
|
1653 |
12235
|
aaronmk
|
COMMENT ON FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement) IS '
|
1654 |
|
|
sql: can be NULL, which will be passed through
|
1655 |
|
|
ret_type_null: NULL::ret_type
|
1656 |
|
|
';
|
1657 |
10133
|
aaronmk
|
|
1658 |
|
|
|
1659 |
|
|
--
|
1660 |
8183
|
aaronmk
|
-- Name: existing_cols(regclass, text[]); Type: FUNCTION; Schema: util; Owner: -
|
1661 |
8182
|
aaronmk
|
--
|
1662 |
|
|
|
1663 |
|
|
CREATE FUNCTION existing_cols(table_ regclass, VARIADIC col_names text[]) RETURNS SETOF text
|
1664 |
12446
|
aaronmk
|
LANGUAGE sql STABLE
|
1665 |
8182
|
aaronmk
|
AS $_$
|
1666 |
|
|
SELECT col_name
|
1667 |
|
|
FROM unnest($2) s (col_name)
|
1668 |
8183
|
aaronmk
|
WHERE util.col_exists(($1, col_name))
|
1669 |
8182
|
aaronmk
|
$_$;
|
1670 |
|
|
|
1671 |
|
|
|
1672 |
|
|
--
|
1673 |
11830
|
aaronmk
|
-- Name: explain(text); Type: FUNCTION; Schema: util; Owner: -
|
1674 |
|
|
--
|
1675 |
|
|
|
1676 |
|
|
CREATE FUNCTION explain(sql text) RETURNS SETOF text
|
1677 |
|
|
LANGUAGE sql
|
1678 |
13496
|
aaronmk
|
SET client_min_messages TO 'error'
|
1679 |
11830
|
aaronmk
|
AS $_$
|
1680 |
13496
|
aaronmk
|
/* `client_min_messages = ERROR`: EXPLAIN apparently runs IMMUTABLE functions in
|
1681 |
|
|
the query, so this prevents displaying any log messages printed by them */
|
1682 |
12459
|
aaronmk
|
SELECT util.eval2set($$EXPLAIN $$||$1, verbose_ := false)
|
1683 |
11830
|
aaronmk
|
$_$;
|
1684 |
|
|
|
1685 |
|
|
|
1686 |
|
|
--
|
1687 |
11833
|
aaronmk
|
-- Name: explain2notice(text); Type: FUNCTION; Schema: util; Owner: -
|
1688 |
|
|
--
|
1689 |
|
|
|
1690 |
|
|
CREATE FUNCTION explain2notice(sql text) RETURNS void
|
1691 |
12449
|
aaronmk
|
LANGUAGE sql
|
1692 |
|
|
AS $_$
|
1693 |
12534
|
aaronmk
|
SELECT util.raise('NOTICE', util.explain2notice_msg($1))
|
1694 |
12449
|
aaronmk
|
$_$;
|
1695 |
12448
|
aaronmk
|
|
1696 |
|
|
|
1697 |
|
|
--
|
1698 |
|
|
-- Name: explain2notice_msg(text); Type: FUNCTION; Schema: util; Owner: -
|
1699 |
|
|
--
|
1700 |
|
|
|
1701 |
|
|
CREATE FUNCTION explain2notice_msg(sql text) RETURNS text
|
1702 |
|
|
LANGUAGE sql
|
1703 |
|
|
AS $_$
|
1704 |
12464
|
aaronmk
|
-- newline before and after to visually separate it from other debug info
|
1705 |
12756
|
aaronmk
|
SELECT COALESCE($$
|
1706 |
12464
|
aaronmk
|
EXPLAIN:
|
1707 |
12756
|
aaronmk
|
$$||util.fold_explain_msg(util.explain2str($1))||$$
|
1708 |
|
|
$$, '')
|
1709 |
11833
|
aaronmk
|
$_$;
|
1710 |
|
|
|
1711 |
|
|
|
1712 |
|
|
--
|
1713 |
12452
|
aaronmk
|
-- Name: explain2notice_msg_if_can(text); Type: FUNCTION; Schema: util; Owner: -
|
1714 |
|
|
--
|
1715 |
|
|
|
1716 |
|
|
CREATE FUNCTION explain2notice_msg_if_can(sql text) RETURNS text
|
1717 |
13403
|
aaronmk
|
LANGUAGE plpgsql
|
1718 |
|
|
AS $$
|
1719 |
|
|
BEGIN
|
1720 |
|
|
RETURN util.explain2notice_msg(sql);
|
1721 |
|
|
EXCEPTION
|
1722 |
|
|
WHEN syntax_error THEN RETURN NULL; -- non-explainable query
|
1723 |
|
|
/* don't use util.is_explainable() because the list provided by Postgres
|
1724 |
|
|
(http://www.postgresql.org/docs/9.3/static/sql-explain.html#AEN77691)
|
1725 |
|
|
excludes some query types that are in fact EXPLAIN-able */
|
1726 |
|
|
END;
|
1727 |
|
|
$$;
|
1728 |
12452
|
aaronmk
|
|
1729 |
|
|
|
1730 |
|
|
--
|
1731 |
11832
|
aaronmk
|
-- Name: explain2str(text); Type: FUNCTION; Schema: util; Owner: -
|
1732 |
|
|
--
|
1733 |
|
|
|
1734 |
|
|
CREATE FUNCTION explain2str(sql text) RETURNS text
|
1735 |
|
|
LANGUAGE sql
|
1736 |
|
|
AS $_$
|
1737 |
|
|
SELECT util.join_strs(explain, $$
|
1738 |
|
|
$$) FROM util.explain($1)
|
1739 |
|
|
$_$;
|
1740 |
|
|
|
1741 |
|
|
|
1742 |
11835
|
aaronmk
|
SET default_tablespace = '';
|
1743 |
|
|
|
1744 |
|
|
SET default_with_oids = false;
|
1745 |
|
|
|
1746 |
11832
|
aaronmk
|
--
|
1747 |
11835
|
aaronmk
|
-- Name: explain; Type: TABLE; Schema: util; Owner: -; Tablespace:
|
1748 |
11831
|
aaronmk
|
--
|
1749 |
|
|
|
1750 |
11835
|
aaronmk
|
CREATE TABLE explain (
|
1751 |
|
|
line text NOT NULL
|
1752 |
|
|
);
|
1753 |
|
|
|
1754 |
|
|
|
1755 |
|
|
--
|
1756 |
|
|
-- Name: explain2table(text, regclass); Type: FUNCTION; Schema: util; Owner: -
|
1757 |
|
|
--
|
1758 |
|
|
|
1759 |
|
|
CREATE FUNCTION explain2table(sql text, table_ regclass DEFAULT 'explain'::regclass) RETURNS void
|
1760 |
11831
|
aaronmk
|
LANGUAGE sql
|
1761 |
|
|
AS $_$
|
1762 |
11835
|
aaronmk
|
SELECT util.eval($$INSERT INTO $$||$2||$$ SELECT util.explain(
|
1763 |
|
|
$$||quote_nullable($1)||$$
|
1764 |
11831
|
aaronmk
|
)$$)
|
1765 |
|
|
$_$;
|
1766 |
|
|
|
1767 |
|
|
|
1768 |
|
|
--
|
1769 |
11836
|
aaronmk
|
-- Name: FUNCTION explain2table(sql text, table_ regclass); Type: COMMENT; Schema: util; Owner: -
|
1770 |
|
|
--
|
1771 |
|
|
|
1772 |
12235
|
aaronmk
|
COMMENT ON FUNCTION explain2table(sql text, table_ regclass) IS '
|
1773 |
|
|
usage:
|
1774 |
11836
|
aaronmk
|
PERFORM util.explain2table($$
|
1775 |
|
|
query
|
1776 |
12235
|
aaronmk
|
$$);
|
1777 |
|
|
';
|
1778 |
11836
|
aaronmk
|
|
1779 |
|
|
|
1780 |
|
|
--
|
1781 |
12450
|
aaronmk
|
-- Name: first_word(text); Type: FUNCTION; Schema: util; Owner: -
|
1782 |
|
|
--
|
1783 |
|
|
|
1784 |
|
|
CREATE FUNCTION first_word(str text) RETURNS text
|
1785 |
|
|
LANGUAGE sql IMMUTABLE
|
1786 |
|
|
AS $_$
|
1787 |
12461
|
aaronmk
|
SELECT match[1] FROM regexp_matches(util.ltrim_nl($1), '^(\S*)') match
|
1788 |
12450
|
aaronmk
|
$_$;
|
1789 |
|
|
|
1790 |
|
|
|
1791 |
|
|
--
|
1792 |
10323
|
aaronmk
|
-- Name: fix_array(anyarray); Type: FUNCTION; Schema: util; Owner: -
|
1793 |
|
|
--
|
1794 |
|
|
|
1795 |
|
|
CREATE FUNCTION fix_array("array" anyarray) RETURNS anyarray
|
1796 |
10355
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
1797 |
10323
|
aaronmk
|
AS $_$
|
1798 |
10355
|
aaronmk
|
SELECT CASE WHEN $1 IS NULL THEN NULL ELSE (
|
1799 |
|
|
CASE WHEN pg_catalog.array_ndims($1) IS NULL THEN util.empty_array($1[1]) ELSE $1 END
|
1800 |
|
|
) END
|
1801 |
10323
|
aaronmk
|
$_$;
|
1802 |
|
|
|
1803 |
|
|
|
1804 |
|
|
--
|
1805 |
|
|
-- Name: FUNCTION fix_array("array" anyarray); Type: COMMENT; Schema: util; Owner: -
|
1806 |
|
|
--
|
1807 |
|
|
|
1808 |
12235
|
aaronmk
|
COMMENT ON FUNCTION fix_array("array" anyarray) IS '
|
1809 |
|
|
ensures that an array will always have proper non-NULL dimensions
|
1810 |
|
|
';
|
1811 |
10323
|
aaronmk
|
|
1812 |
|
|
|
1813 |
|
|
--
|
1814 |
12755
|
aaronmk
|
-- Name: fold_explain_msg(text); Type: FUNCTION; Schema: util; Owner: -
|
1815 |
|
|
--
|
1816 |
|
|
|
1817 |
|
|
CREATE FUNCTION fold_explain_msg(explain text) RETURNS text
|
1818 |
|
|
LANGUAGE sql IMMUTABLE
|
1819 |
|
|
AS $_$
|
1820 |
|
|
SELECT (CASE WHEN util.first_word($1) = 'Result' THEN NULL ELSE $1 END)
|
1821 |
|
|
$_$;
|
1822 |
|
|
|
1823 |
|
|
|
1824 |
|
|
--
|
1825 |
11695
|
aaronmk
|
-- Name: force_recreate(text, text[]); Type: FUNCTION; Schema: util; Owner: -
|
1826 |
|
|
--
|
1827 |
|
|
|
1828 |
|
|
CREATE FUNCTION force_recreate(cmd text, users text[] DEFAULT NULL::text[]) RETURNS void
|
1829 |
|
|
LANGUAGE plpgsql
|
1830 |
|
|
AS $_$
|
1831 |
|
|
DECLARE
|
1832 |
|
|
PG_EXCEPTION_DETAIL text;
|
1833 |
13488
|
aaronmk
|
restore_views_info util.restore_views_info;
|
1834 |
11695
|
aaronmk
|
BEGIN
|
1835 |
13488
|
aaronmk
|
restore_views_info = util.save_drop_views(users);
|
1836 |
11695
|
aaronmk
|
PERFORM util.eval(cmd);
|
1837 |
13487
|
aaronmk
|
PERFORM util.restore_views(restore_views_info);
|
1838 |
11695
|
aaronmk
|
EXCEPTION
|
1839 |
|
|
WHEN dependent_objects_still_exist THEN
|
1840 |
|
|
IF users IS NOT NULL THEN RAISE; END IF; -- save_drop_views() didn't fix it
|
1841 |
|
|
GET STACKED DIAGNOSTICS PG_EXCEPTION_DETAIL = PG_EXCEPTION_DETAIL;
|
1842 |
13484
|
aaronmk
|
users = array(SELECT * FROM util.regexp_matches_group(
|
1843 |
|
|
PG_EXCEPTION_DETAIL, '(?m)^view (.*) depends on [[:lower:]]+ .*$'));
|
1844 |
|
|
-- will be in forward dependency order
|
1845 |
13450
|
aaronmk
|
PERFORM util.debug_print_var('PG_EXCEPTION_DETAIL', PG_EXCEPTION_DETAIL);
|
1846 |
|
|
PERFORM util.debug_print_var('users', users);
|
1847 |
11695
|
aaronmk
|
IF util.is_empty(users) THEN RAISE; END IF;
|
1848 |
|
|
PERFORM util.force_recreate(cmd, users);
|
1849 |
|
|
END;
|
1850 |
|
|
$_$;
|
1851 |
|
|
|
1852 |
|
|
|
1853 |
|
|
--
|
1854 |
|
|
-- Name: FUNCTION force_recreate(cmd text, users text[]); Type: COMMENT; Schema: util; Owner: -
|
1855 |
|
|
--
|
1856 |
|
|
|
1857 |
12235
|
aaronmk
|
COMMENT ON FUNCTION force_recreate(cmd text, users text[]) IS '
|
1858 |
13499
|
aaronmk
|
usage:
|
1859 |
|
|
SELECT util.force_recreate($$
|
1860 |
|
|
-- trigger the dependent_objects_still_exist exception
|
1861 |
|
|
DROP VIEW schemas.main_view; -- *not* CASCADE; it must trigger an exception
|
1862 |
|
|
|
1863 |
|
|
CREATE VIEW schemas.main_view AS _;
|
1864 |
|
|
|
1865 |
|
|
-- manually restore views that need to be updated for the changes
|
1866 |
|
|
CREATE VIEW schemas.dependent_view AS _;
|
1867 |
|
|
$$);
|
1868 |
|
|
|
1869 |
12235
|
aaronmk
|
idempotent
|
1870 |
11695
|
aaronmk
|
|
1871 |
12235
|
aaronmk
|
users: not necessary to provide this because it will be autopopulated
|
1872 |
|
|
';
|
1873 |
11695
|
aaronmk
|
|
1874 |
|
|
|
1875 |
|
|
--
|
1876 |
8321
|
aaronmk
|
-- Name: force_update_view(text, text); Type: FUNCTION; Schema: util; Owner: -
|
1877 |
|
|
--
|
1878 |
|
|
|
1879 |
|
|
CREATE FUNCTION force_update_view(view_ text, query text) RETURNS void
|
1880 |
|
|
LANGUAGE plpgsql STRICT
|
1881 |
|
|
AS $_$
|
1882 |
|
|
DECLARE
|
1883 |
|
|
mk_view text = $$CREATE OR REPLACE VIEW $$||view_||$$ AS
|
1884 |
|
|
$$||query;
|
1885 |
|
|
BEGIN
|
1886 |
|
|
EXECUTE mk_view;
|
1887 |
|
|
EXCEPTION
|
1888 |
|
|
WHEN invalid_table_definition THEN
|
1889 |
8323
|
aaronmk
|
IF SQLERRM = 'cannot drop columns from view'
|
1890 |
|
|
OR SQLERRM LIKE 'cannot change name of view column "%" to "%"'
|
1891 |
|
|
THEN
|
1892 |
8321
|
aaronmk
|
EXECUTE $$DROP VIEW $$||view_||$$ CASCADE$$;
|
1893 |
|
|
EXECUTE mk_view;
|
1894 |
|
|
ELSE RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
|
1895 |
|
|
END IF;
|
1896 |
|
|
END;
|
1897 |
|
|
$_$;
|
1898 |
|
|
|
1899 |
|
|
|
1900 |
|
|
--
|
1901 |
|
|
-- Name: FUNCTION force_update_view(view_ text, query text); Type: COMMENT; Schema: util; Owner: -
|
1902 |
|
|
--
|
1903 |
|
|
|
1904 |
12235
|
aaronmk
|
COMMENT ON FUNCTION force_update_view(view_ text, query text) IS '
|
1905 |
|
|
idempotent
|
1906 |
|
|
';
|
1907 |
8321
|
aaronmk
|
|
1908 |
|
|
|
1909 |
|
|
--
|
1910 |
12654
|
aaronmk
|
-- Name: freq_always_1(regclass, text); Type: FUNCTION; Schema: util; Owner: -
|
1911 |
|
|
--
|
1912 |
|
|
|
1913 |
|
|
CREATE FUNCTION freq_always_1(table_ regclass, freq_col text DEFAULT 'copies'::text) RETURNS boolean
|
1914 |
|
|
LANGUAGE sql STABLE
|
1915 |
|
|
AS $_$
|
1916 |
|
|
SELECT util.eval2val(
|
1917 |
|
|
$$SELECT NOT EXISTS( -- there is no row that is != 1
|
1918 |
|
|
SELECT NULL
|
1919 |
|
|
FROM $$||$1||$$
|
1920 |
|
|
WHERE $$||quote_ident(freq_col)||$$ IS DISTINCT FROM 1
|
1921 |
|
|
LIMIT 1
|
1922 |
|
|
)
|
1923 |
|
|
$$, NULL::boolean)
|
1924 |
|
|
$_$;
|
1925 |
|
|
|
1926 |
|
|
|
1927 |
|
|
--
|
1928 |
12661
|
aaronmk
|
-- Name: freq_always_1(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
|
1929 |
|
|
--
|
1930 |
|
|
|
1931 |
|
|
CREATE FUNCTION freq_always_1(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS boolean
|
1932 |
|
|
LANGUAGE sql STABLE
|
1933 |
|
|
AS $_$
|
1934 |
|
|
SELECT bool_and(util.freq_always_1(table_, $2)) FROM unnest($1) table_
|
1935 |
|
|
$_$;
|
1936 |
|
|
|
1937 |
|
|
|
1938 |
|
|
--
|
1939 |
11655
|
aaronmk
|
-- Name: grants_users(); Type: FUNCTION; Schema: util; Owner: -
|
1940 |
|
|
--
|
1941 |
|
|
|
1942 |
|
|
CREATE FUNCTION grants_users() RETURNS SETOF text
|
1943 |
|
|
LANGUAGE sql IMMUTABLE
|
1944 |
|
|
AS $$
|
1945 |
|
|
VALUES ('bien_read'), ('public_')
|
1946 |
|
|
$$;
|
1947 |
|
|
|
1948 |
|
|
|
1949 |
|
|
--
|
1950 |
8183
|
aaronmk
|
-- Name: has_prefix(text, text); Type: FUNCTION; Schema: util; Owner: -
|
1951 |
8085
|
aaronmk
|
--
|
1952 |
|
|
|
1953 |
|
|
CREATE FUNCTION has_prefix(prefix text, str text) RETURNS boolean
|
1954 |
10388
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
1955 |
8085
|
aaronmk
|
AS $_$
|
1956 |
|
|
SELECT substring($2 for length($1)) = $1
|
1957 |
|
|
$_$;
|
1958 |
|
|
|
1959 |
|
|
|
1960 |
|
|
--
|
1961 |
12296
|
aaronmk
|
-- Name: has_single_row(regclass); Type: FUNCTION; Schema: util; Owner: -
|
1962 |
|
|
--
|
1963 |
|
|
|
1964 |
|
|
CREATE FUNCTION has_single_row(table_ regclass) RETURNS boolean
|
1965 |
|
|
LANGUAGE sql STABLE
|
1966 |
|
|
AS $_$
|
1967 |
|
|
SELECT util.eval2val($$SELECT COUNT(*) = 1 FROM $$||$1, NULL::boolean)
|
1968 |
|
|
$_$;
|
1969 |
|
|
|
1970 |
|
|
|
1971 |
|
|
--
|
1972 |
10307
|
aaronmk
|
-- Name: hstore(text[], text); Type: FUNCTION; Schema: util; Owner: -
|
1973 |
|
|
--
|
1974 |
|
|
|
1975 |
|
|
CREATE FUNCTION hstore(keys text[], value text) RETURNS hstore
|
1976 |
|
|
LANGUAGE sql IMMUTABLE
|
1977 |
|
|
AS $_$
|
1978 |
10324
|
aaronmk
|
SELECT hstore(util.fix_array($1), util.array_fill($2, util.array_length($1)))
|
1979 |
10307
|
aaronmk
|
$_$;
|
1980 |
|
|
|
1981 |
|
|
|
1982 |
|
|
--
|
1983 |
|
|
-- Name: FUNCTION hstore(keys text[], value text); Type: COMMENT; Schema: util; Owner: -
|
1984 |
|
|
--
|
1985 |
|
|
|
1986 |
12235
|
aaronmk
|
COMMENT ON FUNCTION hstore(keys text[], value text) IS '
|
1987 |
|
|
avoids repeating the same value for each key
|
1988 |
|
|
';
|
1989 |
10307
|
aaronmk
|
|
1990 |
|
|
|
1991 |
|
|
--
|
1992 |
12218
|
aaronmk
|
-- Name: ifnull(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
1993 |
|
|
--
|
1994 |
|
|
|
1995 |
|
|
CREATE FUNCTION ifnull(value anyelement, null_ anyelement) RETURNS anyelement
|
1996 |
|
|
LANGUAGE sql IMMUTABLE
|
1997 |
|
|
AS $_$
|
1998 |
12222
|
aaronmk
|
SELECT COALESCE($1, $2)
|
1999 |
12218
|
aaronmk
|
$_$;
|
2000 |
|
|
|
2001 |
|
|
|
2002 |
|
|
--
|
2003 |
|
|
-- Name: FUNCTION ifnull(value anyelement, null_ anyelement); Type: COMMENT; Schema: util; Owner: -
|
2004 |
|
|
--
|
2005 |
|
|
|
2006 |
12235
|
aaronmk
|
COMMENT ON FUNCTION ifnull(value anyelement, null_ anyelement) IS '
|
2007 |
|
|
equivalent to MySQL''s IFNULL() (Postgres auto-lowercases the name)
|
2008 |
|
|
';
|
2009 |
12218
|
aaronmk
|
|
2010 |
|
|
|
2011 |
|
|
--
|
2012 |
13452
|
aaronmk
|
-- Name: in_reverse(anyarray); Type: FUNCTION; Schema: util; Owner: -
|
2013 |
|
|
--
|
2014 |
|
|
|
2015 |
|
|
CREATE FUNCTION in_reverse("array" anyarray) RETURNS SETOF anyelement
|
2016 |
|
|
LANGUAGE sql IMMUTABLE
|
2017 |
|
|
AS $_$
|
2018 |
13490
|
aaronmk
|
SELECT elem FROM unnest($1) elem ORDER BY row_number() OVER () DESC
|
2019 |
13452
|
aaronmk
|
$_$;
|
2020 |
|
|
|
2021 |
|
|
|
2022 |
|
|
--
|
2023 |
12285
|
aaronmk
|
-- Name: inherit(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
|
2024 |
|
|
--
|
2025 |
|
|
|
2026 |
|
|
CREATE FUNCTION inherit(derived regclass, base regclass) RETURNS void
|
2027 |
|
|
LANGUAGE sql
|
2028 |
|
|
AS $_$
|
2029 |
|
|
SELECT util.eval($$ALTER TABLE $$||$1||$$ INHERIT $$||$2)
|
2030 |
|
|
$_$;
|
2031 |
|
|
|
2032 |
|
|
|
2033 |
|
|
--
|
2034 |
13136
|
aaronmk
|
-- Name: is_castable(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
2035 |
|
|
--
|
2036 |
|
|
|
2037 |
|
|
CREATE FUNCTION is_castable(value text, ret_type_null anyelement) RETURNS boolean
|
2038 |
|
|
LANGUAGE plpgsql IMMUTABLE
|
2039 |
|
|
AS $$
|
2040 |
|
|
BEGIN
|
2041 |
|
|
PERFORM util.cast(value, ret_type_null);
|
2042 |
13139
|
aaronmk
|
-- must happen *after* cast check, because NULL is not valid for some types
|
2043 |
|
|
IF value IS NULL THEN RETURN NULL; END IF; -- pass NULL through
|
2044 |
13136
|
aaronmk
|
RETURN true;
|
2045 |
|
|
EXCEPTION
|
2046 |
13493
|
aaronmk
|
WHEN data_exception
|
2047 |
|
|
OR syntax_error_or_access_rule_violation -- eg. ::regclass
|
2048 |
|
|
THEN
|
2049 |
|
|
RETURN false;
|
2050 |
13136
|
aaronmk
|
END;
|
2051 |
|
|
$$;
|
2052 |
|
|
|
2053 |
|
|
|
2054 |
|
|
--
|
2055 |
|
|
-- Name: FUNCTION is_castable(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
|
2056 |
|
|
--
|
2057 |
|
|
|
2058 |
|
|
COMMENT ON FUNCTION is_castable(value text, ret_type_null anyelement) IS '
|
2059 |
13139
|
aaronmk
|
passes NULL through. however, if NULL is not valid for the type, false will be
|
2060 |
|
|
returned instead.
|
2061 |
|
|
|
2062 |
13136
|
aaronmk
|
ret_type_null: NULL::ret_type
|
2063 |
|
|
';
|
2064 |
|
|
|
2065 |
|
|
|
2066 |
|
|
--
|
2067 |
10137
|
aaronmk
|
-- Name: is_constant(col_ref); Type: FUNCTION; Schema: util; Owner: -
|
2068 |
|
|
--
|
2069 |
|
|
|
2070 |
|
|
CREATE FUNCTION is_constant(col col_ref) RETURNS boolean
|
2071 |
12446
|
aaronmk
|
LANGUAGE sql STABLE
|
2072 |
10137
|
aaronmk
|
AS $_$
|
2073 |
12789
|
aaronmk
|
SELECT COALESCE(util.col_comment($1) LIKE '
|
2074 |
|
|
constant
|
2075 |
|
|
%', false)
|
2076 |
10137
|
aaronmk
|
$_$;
|
2077 |
|
|
|
2078 |
|
|
|
2079 |
|
|
--
|
2080 |
11659
|
aaronmk
|
-- Name: is_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
|
2081 |
|
|
--
|
2082 |
|
|
|
2083 |
|
|
CREATE FUNCTION is_empty("array" anyarray) RETURNS boolean
|
2084 |
|
|
LANGUAGE sql IMMUTABLE
|
2085 |
|
|
AS $_$
|
2086 |
|
|
SELECT util.array_length($1) = 0
|
2087 |
|
|
$_$;
|
2088 |
|
|
|
2089 |
|
|
|
2090 |
|
|
--
|
2091 |
12457
|
aaronmk
|
-- Name: is_explain(text); Type: FUNCTION; Schema: util; Owner: -
|
2092 |
|
|
--
|
2093 |
|
|
|
2094 |
|
|
CREATE FUNCTION is_explain(sql text) RETURNS boolean
|
2095 |
|
|
LANGUAGE sql IMMUTABLE
|
2096 |
|
|
AS $_$
|
2097 |
|
|
SELECT upper(util.first_word($1)) = 'EXPLAIN'
|
2098 |
|
|
$_$;
|
2099 |
|
|
|
2100 |
|
|
|
2101 |
|
|
--
|
2102 |
12451
|
aaronmk
|
-- Name: is_explainable(text); Type: FUNCTION; Schema: util; Owner: -
|
2103 |
|
|
--
|
2104 |
|
|
|
2105 |
|
|
CREATE FUNCTION is_explainable(sql text) RETURNS boolean
|
2106 |
|
|
LANGUAGE sql IMMUTABLE
|
2107 |
|
|
AS $_$
|
2108 |
|
|
SELECT upper(util.first_word($1)) = ANY(
|
2109 |
|
|
'{SELECT,INSERT,UPDATE,DELETE,VALUES,EXECUTE,DECLARE}'::text[]
|
2110 |
|
|
/*from http://www.postgresql.org/docs/9.3/static/sql-explain.html#AEN77691*/
|
2111 |
|
|
)
|
2112 |
|
|
$_$;
|
2113 |
|
|
|
2114 |
|
|
|
2115 |
|
|
--
|
2116 |
10391
|
aaronmk
|
-- Name: is_more_complete_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
2117 |
|
|
--
|
2118 |
|
|
|
2119 |
|
|
CREATE FUNCTION is_more_complete_than("left" anyelement, "right" anyelement) RETURNS boolean
|
2120 |
|
|
LANGUAGE sql IMMUTABLE
|
2121 |
|
|
AS $_$
|
2122 |
|
|
SELECT $1 IS NOT DISTINCT FROM $2 OR ($1 IS NOT NULL AND $2 IS NULL)
|
2123 |
|
|
$_$;
|
2124 |
|
|
|
2125 |
|
|
|
2126 |
|
|
--
|
2127 |
10613
|
aaronmk
|
-- Name: is_populated_more_often_than(anyelement, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
2128 |
|
|
--
|
2129 |
|
|
|
2130 |
|
|
CREATE FUNCTION is_populated_more_often_than("left" anyelement, "right" anyelement) RETURNS boolean
|
2131 |
|
|
LANGUAGE sql IMMUTABLE
|
2132 |
|
|
AS $_$
|
2133 |
|
|
SELECT $1 IS NOT NULL >= $2 IS NOT NULL -- true > false
|
2134 |
|
|
$_$;
|
2135 |
|
|
|
2136 |
|
|
|
2137 |
|
|
--
|
2138 |
12480
|
aaronmk
|
-- Name: is_set_stmt(text); Type: FUNCTION; Schema: util; Owner: -
|
2139 |
|
|
--
|
2140 |
|
|
|
2141 |
|
|
CREATE FUNCTION is_set_stmt(sql text) RETURNS boolean
|
2142 |
|
|
LANGUAGE sql IMMUTABLE
|
2143 |
|
|
AS $_$
|
2144 |
|
|
SELECT upper(util.first_word($1)) = 'SET'
|
2145 |
|
|
$_$;
|
2146 |
|
|
|
2147 |
|
|
|
2148 |
|
|
--
|
2149 |
12330
|
aaronmk
|
-- Name: is_table(regclass); Type: FUNCTION; Schema: util; Owner: -
|
2150 |
|
|
--
|
2151 |
|
|
|
2152 |
|
|
CREATE FUNCTION is_table(relation regclass) RETURNS boolean
|
2153 |
12332
|
aaronmk
|
LANGUAGE sql STABLE
|
2154 |
12330
|
aaronmk
|
AS $_$
|
2155 |
|
|
SELECT relkind = 'r' FROM pg_class WHERE oid = $1
|
2156 |
|
|
$_$;
|
2157 |
|
|
|
2158 |
|
|
|
2159 |
|
|
--
|
2160 |
12329
|
aaronmk
|
-- Name: is_view(regclass); Type: FUNCTION; Schema: util; Owner: -
|
2161 |
|
|
--
|
2162 |
|
|
|
2163 |
|
|
CREATE FUNCTION is_view(relation regclass) RETURNS boolean
|
2164 |
12332
|
aaronmk
|
LANGUAGE sql STABLE
|
2165 |
12329
|
aaronmk
|
AS $_$
|
2166 |
|
|
SELECT relkind = 'v' FROM pg_class WHERE oid = $1
|
2167 |
|
|
$_$;
|
2168 |
|
|
|
2169 |
|
|
|
2170 |
|
|
--
|
2171 |
8183
|
aaronmk
|
-- Name: join_strs_transform(text, text, text); Type: FUNCTION; Schema: util; Owner: -
|
2172 |
4009
|
aaronmk
|
--
|
2173 |
|
|
|
2174 |
4053
|
aaronmk
|
CREATE FUNCTION join_strs_transform(state text, value text, delim text) RETURNS text
|
2175 |
12444
|
aaronmk
|
LANGUAGE sql IMMUTABLE STRICT
|
2176 |
4009
|
aaronmk
|
AS $_$
|
2177 |
4054
|
aaronmk
|
SELECT $1 || $3 || $2
|
2178 |
2595
|
aaronmk
|
$_$;
|
2179 |
|
|
|
2180 |
|
|
|
2181 |
|
|
--
|
2182 |
12444
|
aaronmk
|
-- Name: FUNCTION join_strs_transform(state text, value text, delim text); Type: COMMENT; Schema: util; Owner: -
|
2183 |
|
|
--
|
2184 |
|
|
|
2185 |
|
|
COMMENT ON FUNCTION join_strs_transform(state text, value text, delim text) IS '
|
2186 |
|
|
must be declared STRICT to use the special handling of STRICT aggregating functions
|
2187 |
|
|
';
|
2188 |
|
|
|
2189 |
|
|
|
2190 |
|
|
--
|
2191 |
12436
|
aaronmk
|
-- Name: keys(anyelement); Type: FUNCTION; Schema: util; Owner: -
|
2192 |
|
|
--
|
2193 |
|
|
|
2194 |
|
|
CREATE FUNCTION keys(value anyelement) RETURNS anyelement
|
2195 |
|
|
LANGUAGE sql IMMUTABLE
|
2196 |
|
|
AS $_$
|
2197 |
|
|
SELECT $1 -- compare on the entire value
|
2198 |
|
|
$_$;
|
2199 |
|
|
|
2200 |
|
|
|
2201 |
|
|
--
|
2202 |
10989
|
aaronmk
|
-- Name: limit2row_num(integer, integer, integer); Type: FUNCTION; Schema: util; Owner: -
|
2203 |
10985
|
aaronmk
|
--
|
2204 |
|
|
|
2205 |
10989
|
aaronmk
|
CREATE FUNCTION limit2row_num(limit_ integer, offset_ integer DEFAULT NULL::integer, min_row_num integer DEFAULT 1) RETURNS integer
|
2206 |
10985
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
2207 |
|
|
AS $_$
|
2208 |
10989
|
aaronmk
|
SELECT COALESCE(util.offset2row_num($2, $3) + $1 - 1, 2147483647)
|
2209 |
10985
|
aaronmk
|
$_$;
|
2210 |
|
|
|
2211 |
|
|
|
2212 |
|
|
--
|
2213 |
13384
|
aaronmk
|
-- Name: loop_ignore_errors(text, text, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
2214 |
|
|
--
|
2215 |
|
|
|
2216 |
|
|
CREATE FUNCTION loop_ignore_errors(iter_sql text, loop_body_sql text, loop_type_null anyelement DEFAULT NULL::text) RETURNS void
|
2217 |
|
|
LANGUAGE plpgsql
|
2218 |
|
|
AS $$
|
2219 |
|
|
DECLARE
|
2220 |
|
|
errors_ct integer = 0;
|
2221 |
|
|
loop_var loop_type_null%TYPE;
|
2222 |
|
|
BEGIN
|
2223 |
|
|
FOR loop_var IN SELECT * FROM util.eval2set(iter_sql, loop_type_null)
|
2224 |
|
|
LOOP
|
2225 |
|
|
BEGIN
|
2226 |
|
|
EXECUTE loop_body_sql USING loop_var;
|
2227 |
|
|
EXCEPTION
|
2228 |
|
|
WHEN OTHERS THEN
|
2229 |
|
|
errors_ct = errors_ct+1;
|
2230 |
|
|
PERFORM util.raise_error_warning(SQLERRM);
|
2231 |
|
|
END;
|
2232 |
|
|
END LOOP;
|
2233 |
|
|
IF errors_ct > 0 THEN
|
2234 |
|
|
-- can't raise exception because this would roll back the transaction
|
2235 |
|
|
PERFORM util.raise_error_warning('there were '||errors_ct
|
2236 |
|
|
||' errors: see the WARNINGs for details');
|
2237 |
|
|
END IF;
|
2238 |
|
|
END;
|
2239 |
|
|
$$;
|
2240 |
|
|
|
2241 |
|
|
|
2242 |
|
|
--
|
2243 |
12275
|
aaronmk
|
-- Name: ltrim_nl(text); Type: FUNCTION; Schema: util; Owner: -
|
2244 |
|
|
--
|
2245 |
|
|
|
2246 |
|
|
CREATE FUNCTION ltrim_nl(str text) RETURNS text
|
2247 |
|
|
LANGUAGE sql IMMUTABLE
|
2248 |
|
|
AS $_$
|
2249 |
|
|
SELECT ltrim($1, $$
|
2250 |
|
|
$$)
|
2251 |
|
|
$_$;
|
2252 |
|
|
|
2253 |
|
|
|
2254 |
|
|
--
|
2255 |
10110
|
aaronmk
|
-- Name: map_filter_insert(); Type: FUNCTION; Schema: util; Owner: -
|
2256 |
|
|
--
|
2257 |
|
|
|
2258 |
|
|
CREATE FUNCTION map_filter_insert() RETURNS trigger
|
2259 |
|
|
LANGUAGE plpgsql
|
2260 |
|
|
AS $$
|
2261 |
|
|
BEGIN
|
2262 |
|
|
IF new."from" LIKE ':%' THEN RETURN NULL; END IF; -- exclude metadata values
|
2263 |
|
|
RETURN new;
|
2264 |
|
|
END;
|
2265 |
|
|
$$;
|
2266 |
|
|
|
2267 |
|
|
|
2268 |
|
|
--
|
2269 |
8183
|
aaronmk
|
-- Name: map_get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
|
2270 |
8146
|
aaronmk
|
--
|
2271 |
|
|
|
2272 |
|
|
CREATE FUNCTION map_get(map regclass, key text) RETURNS text
|
2273 |
|
|
LANGUAGE plpgsql STABLE STRICT
|
2274 |
|
|
AS $_$
|
2275 |
|
|
DECLARE
|
2276 |
|
|
value text;
|
2277 |
|
|
BEGIN
|
2278 |
|
|
EXECUTE $$SELECT "to" FROM $$||map||$$ WHERE "from" = $1$$
|
2279 |
8149
|
aaronmk
|
INTO value USING key;
|
2280 |
8146
|
aaronmk
|
RETURN value;
|
2281 |
|
|
END;
|
2282 |
|
|
$_$;
|
2283 |
|
|
|
2284 |
|
|
|
2285 |
|
|
--
|
2286 |
10358
|
aaronmk
|
-- Name: map_nulls(text[], anyelement); Type: FUNCTION; Schema: util; Owner: -
|
2287 |
10325
|
aaronmk
|
--
|
2288 |
|
|
|
2289 |
10358
|
aaronmk
|
CREATE FUNCTION map_nulls(nulls text[], value anyelement) RETURNS anyelement
|
2290 |
10353
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
2291 |
10325
|
aaronmk
|
AS $_$
|
2292 |
10374
|
aaronmk
|
SELECT util._map(util.nulls_map($1), $2)
|
2293 |
10325
|
aaronmk
|
$_$;
|
2294 |
|
|
|
2295 |
|
|
|
2296 |
|
|
--
|
2297 |
10359
|
aaronmk
|
-- Name: FUNCTION map_nulls(nulls text[], value anyelement); Type: COMMENT; Schema: util; Owner: -
|
2298 |
|
|
--
|
2299 |
|
|
|
2300 |
12235
|
aaronmk
|
COMMENT ON FUNCTION map_nulls(nulls text[], value anyelement) IS '
|
2301 |
|
|
due to dynamic inlining[1], this is just as fast as util._map() which it wraps[2].
|
2302 |
10359
|
aaronmk
|
|
2303 |
|
|
[1] inlining of function calls, which is different from constant folding
|
2304 |
|
|
[2] _map()''s profiling query
|
2305 |
|
|
SELECT util._map(''"1"=>NULL, "2"=>NULL, "3"=>NULL, *=>*'', v) FROM unnest(array_fill(1, array[100000])) f (v)
|
2306 |
|
|
and map_nulls()''s profiling query
|
2307 |
|
|
SELECT util.map_nulls(array[1, 2, 3]::text[], v) FROM unnest(array_fill(1, array[100000])) f (v)
|
2308 |
10375
|
aaronmk
|
both take ~920 ms.
|
2309 |
12235
|
aaronmk
|
also, /inputs/REMIB/Specimen/postprocess.sql > country takes the same amount of time (56000 ms) to build with map_nulls() as with a literal hstore.
|
2310 |
|
|
';
|
2311 |
10359
|
aaronmk
|
|
2312 |
|
|
|
2313 |
|
|
--
|
2314 |
8183
|
aaronmk
|
-- Name: map_values(regclass); Type: FUNCTION; Schema: util; Owner: -
|
2315 |
8150
|
aaronmk
|
--
|
2316 |
|
|
|
2317 |
|
|
CREATE FUNCTION map_values(map regclass) RETURNS SETOF text
|
2318 |
|
|
LANGUAGE plpgsql STABLE STRICT
|
2319 |
|
|
AS $_$
|
2320 |
|
|
BEGIN
|
2321 |
|
|
RETURN QUERY EXECUTE $$SELECT "to" FROM $$||map;
|
2322 |
|
|
END;
|
2323 |
|
|
$_$;
|
2324 |
|
|
|
2325 |
|
|
|
2326 |
|
|
--
|
2327 |
12228
|
aaronmk
|
-- Name: materialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
|
2328 |
|
|
--
|
2329 |
|
|
|
2330 |
12262
|
aaronmk
|
CREATE FUNCTION materialize_query(table_esc text, sql text) RETURNS void
|
2331 |
12228
|
aaronmk
|
LANGUAGE sql
|
2332 |
|
|
AS $_$
|
2333 |
12262
|
aaronmk
|
SELECT util.create_if_not_exists($$CREATE TABLE $$||$1||$$ AS
|
2334 |
12321
|
aaronmk
|
$$||util.ltrim_nl($2));
|
2335 |
|
|
-- make sure the created table has the correct estimated row count
|
2336 |
|
|
SELECT util.analyze_($1);
|
2337 |
12470
|
aaronmk
|
|
2338 |
|
|
SELECT util.append_comment($1, '
|
2339 |
|
|
contents generated from:
|
2340 |
13397
|
aaronmk
|
'||util.ltrim_nl(util.runnable_sql($2))||';
|
2341 |
12470
|
aaronmk
|
');
|
2342 |
12228
|
aaronmk
|
$_$;
|
2343 |
|
|
|
2344 |
|
|
|
2345 |
|
|
--
|
2346 |
12262
|
aaronmk
|
-- Name: FUNCTION materialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
|
2347 |
12228
|
aaronmk
|
--
|
2348 |
|
|
|
2349 |
12262
|
aaronmk
|
COMMENT ON FUNCTION materialize_query(table_esc text, sql text) IS '
|
2350 |
12235
|
aaronmk
|
idempotent
|
2351 |
|
|
';
|
2352 |
12228
|
aaronmk
|
|
2353 |
|
|
|
2354 |
|
|
--
|
2355 |
12234
|
aaronmk
|
-- Name: materialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
|
2356 |
|
|
--
|
2357 |
|
|
|
2358 |
12262
|
aaronmk
|
CREATE FUNCTION materialize_view(table_esc text, view_ regclass) RETURNS void
|
2359 |
12234
|
aaronmk
|
LANGUAGE sql
|
2360 |
|
|
AS $_$
|
2361 |
|
|
SELECT util.materialize_query($1, $$SELECT * FROM $$||$2)
|
2362 |
|
|
$_$;
|
2363 |
|
|
|
2364 |
|
|
|
2365 |
|
|
--
|
2366 |
12262
|
aaronmk
|
-- Name: FUNCTION materialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
|
2367 |
12234
|
aaronmk
|
--
|
2368 |
|
|
|
2369 |
12262
|
aaronmk
|
COMMENT ON FUNCTION materialize_view(table_esc text, view_ regclass) IS '
|
2370 |
12235
|
aaronmk
|
idempotent
|
2371 |
|
|
';
|
2372 |
12234
|
aaronmk
|
|
2373 |
|
|
|
2374 |
|
|
--
|
2375 |
8190
|
aaronmk
|
-- Name: mk_const_col(col_ref, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
2376 |
|
|
--
|
2377 |
|
|
|
2378 |
|
|
CREATE FUNCTION mk_const_col(col col_ref, value anyelement) RETURNS void
|
2379 |
12446
|
aaronmk
|
LANGUAGE sql
|
2380 |
8190
|
aaronmk
|
AS $_$
|
2381 |
10135
|
aaronmk
|
SELECT util.create_if_not_exists($$
|
2382 |
|
|
ALTER TABLE $$||$1.table_||$$ ADD COLUMN $$
|
2383 |
8190
|
aaronmk
|
||quote_ident($1.name)||$$ $$||pg_typeof($2)||util.type_qual($2)||$$ DEFAULT $$
|
2384 |
10135
|
aaronmk
|
||quote_literal($2)||$$;
|
2385 |
12235
|
aaronmk
|
COMMENT ON COLUMN $$||$1.table_||$$.$$||quote_ident($1.name)||$$ IS '
|
2386 |
|
|
constant
|
2387 |
|
|
';
|
2388 |
10135
|
aaronmk
|
$$)
|
2389 |
8190
|
aaronmk
|
$_$;
|
2390 |
|
|
|
2391 |
|
|
|
2392 |
|
|
--
|
2393 |
|
|
-- Name: FUNCTION mk_const_col(col col_ref, value anyelement); Type: COMMENT; Schema: util; Owner: -
|
2394 |
|
|
--
|
2395 |
|
|
|
2396 |
12235
|
aaronmk
|
COMMENT ON FUNCTION mk_const_col(col col_ref, value anyelement) IS '
|
2397 |
|
|
idempotent
|
2398 |
|
|
';
|
2399 |
8190
|
aaronmk
|
|
2400 |
|
|
|
2401 |
|
|
--
|
2402 |
10296
|
aaronmk
|
-- Name: mk_derived_col(col_ref, text, boolean); Type: FUNCTION; Schema: util; Owner: -
|
2403 |
8187
|
aaronmk
|
--
|
2404 |
|
|
|
2405 |
10296
|
aaronmk
|
CREATE FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean DEFAULT false) RETURNS void
|
2406 |
8187
|
aaronmk
|
LANGUAGE plpgsql STRICT
|
2407 |
|
|
AS $_$
|
2408 |
|
|
DECLARE
|
2409 |
|
|
type regtype = util.typeof(expr, col.table_::text::regtype);
|
2410 |
|
|
col_name_sql text = quote_ident(col.name);
|
2411 |
|
|
BEGIN
|
2412 |
10296
|
aaronmk
|
PERFORM util.create_if_not_exists((CASE WHEN overwrite THEN '' ELSE $$
|
2413 |
|
|
ALTER TABLE $$||col.table_||$$ ADD COLUMN $$||col_name_sql||$$ $$||type||$$;$$ END)||$$
|
2414 |
8187
|
aaronmk
|
ALTER TABLE $$||col.table_||$$ ALTER COLUMN $$||col_name_sql||$$ TYPE $$||type||$$ USING
|
2415 |
|
|
$$||expr||$$;
|
2416 |
|
|
$$);
|
2417 |
|
|
END;
|
2418 |
|
|
$_$;
|
2419 |
|
|
|
2420 |
|
|
|
2421 |
|
|
--
|
2422 |
10296
|
aaronmk
|
-- Name: FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean); Type: COMMENT; Schema: util; Owner: -
|
2423 |
8188
|
aaronmk
|
--
|
2424 |
|
|
|
2425 |
12235
|
aaronmk
|
COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean) IS '
|
2426 |
|
|
idempotent
|
2427 |
|
|
';
|
2428 |
8188
|
aaronmk
|
|
2429 |
|
|
|
2430 |
|
|
--
|
2431 |
12554
|
aaronmk
|
-- Name: mk_diff_query(text, text, text, text, text); Type: FUNCTION; Schema: util; Owner: -
|
2432 |
12475
|
aaronmk
|
--
|
2433 |
|
|
|
2434 |
12554
|
aaronmk
|
CREATE FUNCTION mk_diff_query(left_ text, right_ text, cols text DEFAULT 'left_, right_'::text, join_cond text DEFAULT 'left_ %== right_ -- refer to EXPLAIN output for expansion of %=='::text, filter text DEFAULT 'left_ IS DISTINCT FROM right_'::text) RETURNS text
|
2435 |
12475
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
2436 |
|
|
AS $_$
|
2437 |
|
|
SELECT
|
2438 |
12478
|
aaronmk
|
$$SELECT
|
2439 |
12554
|
aaronmk
|
$$||$3||$$
|
2440 |
12555
|
aaronmk
|
FROM $$||$1||$$ left_
|
2441 |
12554
|
aaronmk
|
FULL JOIN $$||$2||$$ right_
|
2442 |
|
|
ON $$||$4||$$
|
2443 |
|
|
WHERE $$||$5||$$
|
2444 |
12475
|
aaronmk
|
ORDER BY left_, right_
|
2445 |
|
|
$$
|
2446 |
|
|
$_$;
|
2447 |
|
|
|
2448 |
|
|
|
2449 |
|
|
--
|
2450 |
12564
|
aaronmk
|
-- Name: mk_keys_func(regtype); Type: FUNCTION; Schema: util; Owner: -
|
2451 |
|
|
--
|
2452 |
|
|
|
2453 |
|
|
CREATE FUNCTION mk_keys_func(type regtype) RETURNS void
|
2454 |
12591
|
aaronmk
|
LANGUAGE sql
|
2455 |
12564
|
aaronmk
|
AS $_$
|
2456 |
12570
|
aaronmk
|
-- keys()
|
2457 |
12564
|
aaronmk
|
SELECT util.mk_keys_func($1, ARRAY(
|
2458 |
|
|
SELECT col FROM util.typed_cols($1) col
|
2459 |
|
|
WHERE (col).type != ANY('{bigint}'::regtype[]) -- not a count col
|
2460 |
12570
|
aaronmk
|
));
|
2461 |
|
|
|
2462 |
12571
|
aaronmk
|
-- values_()
|
2463 |
12570
|
aaronmk
|
SELECT util.mk_keys_func($1, COALESCE(
|
2464 |
|
|
NULLIF(ARRAY(
|
2465 |
|
|
SELECT col FROM util.typed_cols($1) col
|
2466 |
|
|
WHERE (col).type = ANY('{bigint}'::regtype[]) -- is a count col
|
2467 |
|
|
), ARRAY[]::util.col_cast[])
|
2468 |
|
|
, ARRAY(SELECT util.typed_cols($1))) -- no count cols, so use all cols
|
2469 |
12571
|
aaronmk
|
, 'values_');
|
2470 |
12564
|
aaronmk
|
$_$;
|
2471 |
|
|
|
2472 |
|
|
|
2473 |
|
|
--
|
2474 |
12569
|
aaronmk
|
-- Name: mk_keys_func(regtype, col_cast[], text); Type: FUNCTION; Schema: util; Owner: -
|
2475 |
12561
|
aaronmk
|
--
|
2476 |
|
|
|
2477 |
12569
|
aaronmk
|
CREATE FUNCTION mk_keys_func(type regtype, cols col_cast[], name text DEFAULT 'keys'::text) RETURNS void
|
2478 |
12591
|
aaronmk
|
LANGUAGE sql
|
2479 |
12561
|
aaronmk
|
AS $_$
|
2480 |
12567
|
aaronmk
|
SELECT util.create_if_not_exists($$
|
2481 |
12577
|
aaronmk
|
CREATE TYPE $$||util.prefixed_name($3||'_', $1)||$$ AS
|
2482 |
|
|
($$||util.mk_typed_cols_list($2)||$$);
|
2483 |
12671
|
aaronmk
|
COMMENT ON TYPE $$||util.prefixed_name($3||'_', $1)||$$ IS '
|
2484 |
|
|
autogenerated
|
2485 |
|
|
';
|
2486 |
12594
|
aaronmk
|
$$);
|
2487 |
12577
|
aaronmk
|
|
2488 |
12594
|
aaronmk
|
SELECT util.mk_keys_func($1, util.prefixed_name($3||'_', $1)::regtype, $3);
|
2489 |
|
|
$_$;
|
2490 |
|
|
|
2491 |
|
|
|
2492 |
|
|
--
|
2493 |
|
|
-- Name: mk_keys_func(regtype, regtype, text); Type: FUNCTION; Schema: util; Owner: -
|
2494 |
|
|
--
|
2495 |
|
|
|
2496 |
|
|
CREATE FUNCTION mk_keys_func(type regtype, return_type regtype, name text DEFAULT 'keys'::text) RETURNS void
|
2497 |
|
|
LANGUAGE sql
|
2498 |
|
|
AS $_$
|
2499 |
|
|
SELECT util.create_if_not_exists($$
|
2500 |
12581
|
aaronmk
|
CREATE FUNCTION $$||util.qual_name(util.schema($1), $3)||$$(value $$
|
2501 |
12577
|
aaronmk
|
||util.qual_name($1)||$$)
|
2502 |
12594
|
aaronmk
|
RETURNS $$||util.qual_name($2)||$$ AS
|
2503 |
12561
|
aaronmk
|
$BODY1$
|
2504 |
12577
|
aaronmk
|
SELECT ROW($$||
|
2505 |
12594
|
aaronmk
|
(SELECT COALESCE(string_agg($$$1.$$||quote_ident((col).col_name), ', '), '')
|
2506 |
|
|
FROM util.typed_cols($2) col) ||$$)::$$||util.qual_name($2)||$$
|
2507 |
12561
|
aaronmk
|
$BODY1$
|
2508 |
|
|
LANGUAGE sql IMMUTABLE
|
2509 |
|
|
COST 100;
|
2510 |
12594
|
aaronmk
|
$$);
|
2511 |
12561
|
aaronmk
|
$_$;
|
2512 |
|
|
|
2513 |
|
|
|
2514 |
|
|
--
|
2515 |
8183
|
aaronmk
|
-- Name: mk_map_table(text); Type: FUNCTION; Schema: util; Owner: -
|
2516 |
8139
|
aaronmk
|
--
|
2517 |
|
|
|
2518 |
|
|
CREATE FUNCTION mk_map_table(table_ text) RETURNS void
|
2519 |
12446
|
aaronmk
|
LANGUAGE sql
|
2520 |
8139
|
aaronmk
|
AS $_$
|
2521 |
8183
|
aaronmk
|
SELECT util.create_if_not_exists($$
|
2522 |
8141
|
aaronmk
|
CREATE TABLE $$||$1||$$
|
2523 |
8139
|
aaronmk
|
(
|
2524 |
8183
|
aaronmk
|
LIKE util.map INCLUDING ALL
|
2525 |
10110
|
aaronmk
|
);
|
2526 |
|
|
|
2527 |
|
|
CREATE TRIGGER map_filter_insert
|
2528 |
|
|
BEFORE INSERT
|
2529 |
|
|
ON $$||$1||$$
|
2530 |
|
|
FOR EACH ROW
|
2531 |
|
|
EXECUTE PROCEDURE util.map_filter_insert();
|
2532 |
8141
|
aaronmk
|
$$)
|
2533 |
8139
|
aaronmk
|
$_$;
|
2534 |
|
|
|
2535 |
|
|
|
2536 |
|
|
--
|
2537 |
12725
|
aaronmk
|
-- Name: mk_not_null(text); Type: FUNCTION; Schema: util; Owner: -
|
2538 |
|
|
--
|
2539 |
|
|
|
2540 |
|
|
CREATE FUNCTION mk_not_null(text) RETURNS text
|
2541 |
|
|
LANGUAGE sql IMMUTABLE
|
2542 |
|
|
AS $_$
|
2543 |
|
|
SELECT COALESCE($1, '<NULL>')
|
2544 |
|
|
$_$;
|
2545 |
|
|
|
2546 |
|
|
|
2547 |
|
|
--
|
2548 |
12556
|
aaronmk
|
-- Name: mk_out_params(col_cast[]); Type: FUNCTION; Schema: util; Owner: -
|
2549 |
|
|
--
|
2550 |
|
|
|
2551 |
|
|
CREATE FUNCTION mk_out_params(cols col_cast[]) RETURNS text
|
2552 |
|
|
LANGUAGE sql IMMUTABLE
|
2553 |
|
|
AS $_$
|
2554 |
12559
|
aaronmk
|
SELECT COALESCE(string_agg($$, OUT $$||(unnest).col_name||$$ $$||
|
2555 |
|
|
util.qual_name((unnest).type), ''), '')
|
2556 |
12556
|
aaronmk
|
FROM unnest($1)
|
2557 |
|
|
$_$;
|
2558 |
|
|
|
2559 |
|
|
|
2560 |
|
|
--
|
2561 |
12236
|
aaronmk
|
-- Name: mk_search_path(text[]); Type: FUNCTION; Schema: util; Owner: -
|
2562 |
|
|
--
|
2563 |
|
|
|
2564 |
|
|
CREATE FUNCTION mk_search_path(VARIADIC schemas text[]) RETURNS text
|
2565 |
|
|
LANGUAGE sql IMMUTABLE
|
2566 |
|
|
AS $_$
|
2567 |
12486
|
aaronmk
|
SELECT string_agg(quote_ident(unnest), ', ') FROM unnest($1||'util'::text)
|
2568 |
12236
|
aaronmk
|
$_$;
|
2569 |
|
|
|
2570 |
|
|
|
2571 |
|
|
--
|
2572 |
12486
|
aaronmk
|
-- Name: FUNCTION mk_search_path(VARIADIC schemas text[]); Type: COMMENT; Schema: util; Owner: -
|
2573 |
|
|
--
|
2574 |
|
|
|
2575 |
|
|
COMMENT ON FUNCTION mk_search_path(VARIADIC schemas text[]) IS '
|
2576 |
|
|
auto-appends util to the search_path to enable use of util operators
|
2577 |
|
|
';
|
2578 |
|
|
|
2579 |
|
|
|
2580 |
|
|
--
|
2581 |
13474
|
aaronmk
|
-- Name: mk_set_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
|
2582 |
|
|
--
|
2583 |
|
|
|
2584 |
|
|
CREATE FUNCTION mk_set_comment(table_ regclass, comment text) RETURNS text
|
2585 |
|
|
LANGUAGE sql STABLE
|
2586 |
|
|
AS $_$
|
2587 |
13483
|
aaronmk
|
SELECT COALESCE($$COMMENT ON $$||util.relation_type($1)||$$ $$||$1||$$ IS $$
|
2588 |
13481
|
aaronmk
|
||quote_literal($2)/*pass NULL through*/||$$;$$, ''/*no comment*/)
|
2589 |
13474
|
aaronmk
|
$_$;
|
2590 |
|
|
|
2591 |
|
|
|
2592 |
|
|
--
|
2593 |
12467
|
aaronmk
|
-- Name: mk_set_search_path(boolean); Type: FUNCTION; Schema: util; Owner: -
|
2594 |
|
|
--
|
2595 |
|
|
|
2596 |
|
|
CREATE FUNCTION mk_set_search_path(for_printing boolean DEFAULT false) RETURNS text
|
2597 |
|
|
LANGUAGE sql IMMUTABLE
|
2598 |
|
|
AS $_$
|
2599 |
|
|
SELECT util.mk_set_search_path(current_setting('search_path'), $1)
|
2600 |
|
|
$_$;
|
2601 |
|
|
|
2602 |
|
|
|
2603 |
|
|
--
|
2604 |
12466
|
aaronmk
|
-- Name: mk_set_search_path(text, boolean); Type: FUNCTION; Schema: util; Owner: -
|
2605 |
12270
|
aaronmk
|
--
|
2606 |
|
|
|
2607 |
12466
|
aaronmk
|
CREATE FUNCTION mk_set_search_path(search_path text, for_printing boolean DEFAULT false) RETURNS text
|
2608 |
12270
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
2609 |
|
|
AS $_$
|
2610 |
12432
|
aaronmk
|
/* debug_print_return_value() needed because this function is used with EXECUTE
|
2611 |
|
|
rather than util.eval() (in order to affect the calling function), so the
|
2612 |
|
|
search_path would not otherwise be printed */
|
2613 |
12487
|
aaronmk
|
SELECT $$SET$$||util._if($2, $$ /*LOCAL*/$$::text, $$ LOCAL$$)
|
2614 |
|
|
||$$ search_path TO $$||$1
|
2615 |
12270
|
aaronmk
|
$_$;
|
2616 |
|
|
|
2617 |
|
|
|
2618 |
|
|
--
|
2619 |
10113
|
aaronmk
|
-- Name: mk_source_col(regclass); Type: FUNCTION; Schema: util; Owner: -
|
2620 |
|
|
--
|
2621 |
|
|
|
2622 |
|
|
CREATE FUNCTION mk_source_col(table_ regclass) RETURNS void
|
2623 |
12446
|
aaronmk
|
LANGUAGE sql
|
2624 |
10113
|
aaronmk
|
AS $_$
|
2625 |
12240
|
aaronmk
|
SELECT util.mk_const_col(($1, 'source'), util.schema($1))
|
2626 |
10113
|
aaronmk
|
$_$;
|
2627 |
|
|
|
2628 |
|
|
|
2629 |
|
|
--
|
2630 |
|
|
-- Name: FUNCTION mk_source_col(table_ regclass); Type: COMMENT; Schema: util; Owner: -
|
2631 |
|
|
--
|
2632 |
|
|
|
2633 |
12235
|
aaronmk
|
COMMENT ON FUNCTION mk_source_col(table_ regclass) IS '
|
2634 |
|
|
idempotent
|
2635 |
|
|
';
|
2636 |
10113
|
aaronmk
|
|
2637 |
|
|
|
2638 |
|
|
--
|
2639 |
11011
|
aaronmk
|
-- Name: mk_subset_by_row_num_func(regclass); Type: FUNCTION; Schema: util; Owner: -
|
2640 |
|
|
--
|
2641 |
|
|
|
2642 |
|
|
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass) RETURNS void
|
2643 |
|
|
LANGUAGE plpgsql STRICT
|
2644 |
|
|
AS $_$
|
2645 |
|
|
DECLARE
|
2646 |
|
|
view_qual_name text = util.qual_name(view_);
|
2647 |
|
|
BEGIN
|
2648 |
|
|
EXECUTE $$
|
2649 |
|
|
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
|
2650 |
|
|
RETURNS SETOF $$||view_||$$ AS
|
2651 |
|
|
$BODY1$
|
2652 |
|
|
SELECT * FROM $$||view_qual_name||$$
|
2653 |
|
|
ORDER BY sort_col
|
2654 |
|
|
LIMIT $1 OFFSET $2
|
2655 |
|
|
$BODY1$
|
2656 |
|
|
LANGUAGE sql STABLE
|
2657 |
|
|
COST 100
|
2658 |
|
|
ROWS 1000
|
2659 |
|
|
$$;
|
2660 |
|
|
|
2661 |
|
|
PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
|
2662 |
|
|
END;
|
2663 |
|
|
$_$;
|
2664 |
|
|
|
2665 |
|
|
|
2666 |
|
|
--
|
2667 |
8325
|
aaronmk
|
-- Name: mk_subset_by_row_num_func(regclass, text); Type: FUNCTION; Schema: util; Owner: -
|
2668 |
|
|
--
|
2669 |
|
|
|
2670 |
|
|
CREATE FUNCTION mk_subset_by_row_num_func(view_ regclass, row_num_col text) RETURNS void
|
2671 |
|
|
LANGUAGE plpgsql STRICT
|
2672 |
|
|
AS $_$
|
2673 |
10990
|
aaronmk
|
DECLARE
|
2674 |
|
|
view_qual_name text = util.qual_name(view_);
|
2675 |
|
|
row_num__min__fn text = util.esc_name__append('__row_num__min', view_qual_name);
|
2676 |
8325
|
aaronmk
|
BEGIN
|
2677 |
|
|
EXECUTE $$
|
2678 |
10990
|
aaronmk
|
CREATE OR REPLACE FUNCTION $$||row_num__min__fn||$$()
|
2679 |
|
|
RETURNS integer AS
|
2680 |
|
|
$BODY1$
|
2681 |
|
|
SELECT $$||quote_ident(row_num_col)||$$
|
2682 |
|
|
FROM $$||view_qual_name||$$
|
2683 |
|
|
ORDER BY $$||quote_ident(row_num_col)||$$ ASC
|
2684 |
|
|
LIMIT 1
|
2685 |
|
|
$BODY1$
|
2686 |
|
|
LANGUAGE sql STABLE
|
2687 |
|
|
COST 100;
|
2688 |
|
|
$$;
|
2689 |
|
|
|
2690 |
|
|
EXECUTE $$
|
2691 |
8325
|
aaronmk
|
CREATE OR REPLACE FUNCTION $$||view_||$$(limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
|
2692 |
|
|
RETURNS SETOF $$||view_||$$ AS
|
2693 |
|
|
$BODY1$
|
2694 |
10990
|
aaronmk
|
SELECT * FROM $$||view_qual_name||$$
|
2695 |
|
|
WHERE $$||quote_ident(row_num_col)||$$ BETWEEN
|
2696 |
|
|
util.offset2row_num( $2, $$||row_num__min__fn||$$())
|
2697 |
|
|
AND util.limit2row_num ($1, $2, $$||row_num__min__fn||$$())
|
2698 |
10991
|
aaronmk
|
ORDER BY $$||quote_ident(row_num_col)||$$
|
2699 |
8325
|
aaronmk
|
$BODY1$
|
2700 |
|
|
LANGUAGE sql STABLE
|
2701 |
|
|
COST 100
|
2702 |
|
|
ROWS 1000
|
2703 |
|
|
$$;
|
2704 |
11010
|
aaronmk
|
|
2705 |
|
|
PERFORM util.mk_subset_by_row_num_no_sort_func(view_);
|
2706 |
|
|
END;
|
2707 |
|
|
$_$;
|
2708 |
|
|
|
2709 |
|
|
|
2710 |
|
|
--
|
2711 |
|
|
-- Name: mk_subset_by_row_num_no_sort_func(regclass); Type: FUNCTION; Schema: util; Owner: -
|
2712 |
|
|
--
|
2713 |
|
|
|
2714 |
|
|
CREATE FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) RETURNS void
|
2715 |
|
|
LANGUAGE plpgsql STRICT
|
2716 |
|
|
AS $_$
|
2717 |
|
|
DECLARE
|
2718 |
|
|
view_qual_name text = util.qual_name(view_);
|
2719 |
|
|
BEGIN
|
2720 |
8326
|
aaronmk
|
EXECUTE $$
|
2721 |
|
|
CREATE OR REPLACE FUNCTION $$||view_||$$(no_sort boolean, limit_ integer DEFAULT NULL::integer, offset_ integer DEFAULT NULL)
|
2722 |
|
|
RETURNS SETOF $$||view_||$$
|
2723 |
|
|
SET enable_sort TO 'off'
|
2724 |
|
|
AS
|
2725 |
|
|
$BODY1$
|
2726 |
10990
|
aaronmk
|
SELECT * FROM $$||view_qual_name||$$($2, $3)
|
2727 |
8326
|
aaronmk
|
$BODY1$
|
2728 |
|
|
LANGUAGE sql STABLE
|
2729 |
|
|
COST 100
|
2730 |
|
|
ROWS 1000
|
2731 |
|
|
;
|
2732 |
|
|
COMMENT ON FUNCTION $$||view_||$$(no_sort boolean, limit_ integer, offset_ integer) IS '
|
2733 |
|
|
Use this for limit values greater than ~100,000 to avoid unwanted slow sorts.
|
2734 |
|
|
If you want to run EXPLAIN and get expanded output, use the regular subset
|
2735 |
|
|
function instead. (When a config param is set on a function, EXPLAIN produces
|
2736 |
|
|
just a function scan.)
|
2737 |
|
|
';
|
2738 |
|
|
$$;
|
2739 |
8325
|
aaronmk
|
END;
|
2740 |
|
|
$_$;
|
2741 |
|
|
|
2742 |
|
|
|
2743 |
|
|
--
|
2744 |
11010
|
aaronmk
|
-- Name: FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass); Type: COMMENT; Schema: util; Owner: -
|
2745 |
|
|
--
|
2746 |
|
|
|
2747 |
12235
|
aaronmk
|
COMMENT ON FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) IS '
|
2748 |
|
|
creates subset function which turns off enable_sort
|
2749 |
|
|
';
|
2750 |
11010
|
aaronmk
|
|
2751 |
|
|
|
2752 |
|
|
--
|
2753 |
12576
|
aaronmk
|
-- Name: mk_typed_cols_list(col_cast[]); Type: FUNCTION; Schema: util; Owner: -
|
2754 |
|
|
--
|
2755 |
|
|
|
2756 |
|
|
CREATE FUNCTION mk_typed_cols_list(cols col_cast[]) RETURNS text
|
2757 |
|
|
LANGUAGE sql IMMUTABLE
|
2758 |
|
|
AS $_$
|
2759 |
12579
|
aaronmk
|
SELECT COALESCE(string_agg(quote_ident((unnest).col_name)||$$ $$||
|
2760 |
12576
|
aaronmk
|
util.qual_name((unnest).type), ', '), '')
|
2761 |
|
|
FROM unnest($1)
|
2762 |
|
|
$_$;
|
2763 |
|
|
|
2764 |
|
|
|
2765 |
|
|
--
|
2766 |
12242
|
aaronmk
|
-- Name: name(regclass); Type: FUNCTION; Schema: util; Owner: -
|
2767 |
|
|
--
|
2768 |
|
|
|
2769 |
|
|
CREATE FUNCTION name(table_ regclass) RETURNS text
|
2770 |
|
|
LANGUAGE sql STABLE
|
2771 |
|
|
AS $_$
|
2772 |
|
|
SELECT relname::text FROM pg_class WHERE oid = $1
|
2773 |
|
|
$_$;
|
2774 |
|
|
|
2775 |
|
|
|
2776 |
|
|
--
|
2777 |
8183
|
aaronmk
|
-- Name: name(regtype); Type: FUNCTION; Schema: util; Owner: -
|
2778 |
8083
|
aaronmk
|
--
|
2779 |
|
|
|
2780 |
|
|
CREATE FUNCTION name(type regtype) RETURNS text
|
2781 |
12446
|
aaronmk
|
LANGUAGE sql STABLE
|
2782 |
8083
|
aaronmk
|
AS $_$
|
2783 |
|
|
SELECT typname::text FROM pg_type WHERE oid = $1
|
2784 |
|
|
$_$;
|
2785 |
|
|
|
2786 |
|
|
|
2787 |
|
|
--
|
2788 |
12360
|
aaronmk
|
-- Name: name_was_truncated(text, integer); Type: FUNCTION; Schema: util; Owner: -
|
2789 |
12355
|
aaronmk
|
--
|
2790 |
|
|
|
2791 |
12360
|
aaronmk
|
CREATE FUNCTION name_was_truncated(name_ text, max_prefix_len integer DEFAULT 0) RETURNS boolean
|
2792 |
12355
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
2793 |
|
|
AS $_$
|
2794 |
12360
|
aaronmk
|
SELECT octet_length($1) >= util.namedatalen() - $2
|
2795 |
12355
|
aaronmk
|
$_$;
|
2796 |
|
|
|
2797 |
|
|
|
2798 |
|
|
--
|
2799 |
12354
|
aaronmk
|
-- Name: namedatalen(); Type: FUNCTION; Schema: util; Owner: -
|
2800 |
|
|
--
|
2801 |
|
|
|
2802 |
|
|
CREATE FUNCTION namedatalen() RETURNS integer
|
2803 |
|
|
LANGUAGE sql IMMUTABLE
|
2804 |
|
|
AS $$
|
2805 |
|
|
SELECT octet_length(repeat('_', 1024/*>63*/)::name::text)
|
2806 |
|
|
$$;
|
2807 |
|
|
|
2808 |
|
|
|
2809 |
|
|
--
|
2810 |
9958
|
aaronmk
|
-- Name: not_empty(anyarray); Type: FUNCTION; Schema: util; Owner: -
|
2811 |
|
|
--
|
2812 |
|
|
|
2813 |
|
|
CREATE FUNCTION not_empty(value anyarray) RETURNS boolean
|
2814 |
|
|
LANGUAGE sql IMMUTABLE
|
2815 |
|
|
AS $_$
|
2816 |
10329
|
aaronmk
|
SELECT $1 IS NOT NULL AND util.array_length($1) > 0
|
2817 |
9958
|
aaronmk
|
$_$;
|
2818 |
|
|
|
2819 |
|
|
|
2820 |
|
|
--
|
2821 |
9956
|
aaronmk
|
-- Name: not_null(anyelement); Type: FUNCTION; Schema: util; Owner: -
|
2822 |
|
|
--
|
2823 |
|
|
|
2824 |
|
|
CREATE FUNCTION not_null(value anyelement) RETURNS boolean
|
2825 |
9957
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
2826 |
9956
|
aaronmk
|
AS $_$
|
2827 |
|
|
SELECT $1 IS NOT NULL
|
2828 |
|
|
$_$;
|
2829 |
|
|
|
2830 |
|
|
|
2831 |
|
|
--
|
2832 |
10373
|
aaronmk
|
-- Name: nulls_map(text[]); Type: FUNCTION; Schema: util; Owner: -
|
2833 |
|
|
--
|
2834 |
|
|
|
2835 |
|
|
CREATE FUNCTION nulls_map(nulls text[]) RETURNS hstore
|
2836 |
|
|
LANGUAGE sql IMMUTABLE
|
2837 |
|
|
AS $_$
|
2838 |
|
|
SELECT util.hstore($1, NULL) || '*=>*'
|
2839 |
|
|
$_$;
|
2840 |
|
|
|
2841 |
|
|
|
2842 |
|
|
--
|
2843 |
|
|
-- Name: FUNCTION nulls_map(nulls text[]); Type: COMMENT; Schema: util; Owner: -
|
2844 |
|
|
--
|
2845 |
|
|
|
2846 |
12235
|
aaronmk
|
COMMENT ON FUNCTION nulls_map(nulls text[]) IS '
|
2847 |
|
|
for use with _map()
|
2848 |
|
|
';
|
2849 |
10373
|
aaronmk
|
|
2850 |
|
|
|
2851 |
|
|
--
|
2852 |
10989
|
aaronmk
|
-- Name: offset2row_num(integer, integer); Type: FUNCTION; Schema: util; Owner: -
|
2853 |
10984
|
aaronmk
|
--
|
2854 |
|
|
|
2855 |
10989
|
aaronmk
|
CREATE FUNCTION offset2row_num(offset_ integer, min_row_num integer DEFAULT 1) RETURNS integer
|
2856 |
10984
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
2857 |
|
|
AS $_$
|
2858 |
10989
|
aaronmk
|
SELECT $2 + COALESCE($1, 0)
|
2859 |
10984
|
aaronmk
|
$_$;
|
2860 |
|
|
|
2861 |
|
|
|
2862 |
|
|
--
|
2863 |
12659
|
aaronmk
|
-- Name: parent(regclass); Type: FUNCTION; Schema: util; Owner: -
|
2864 |
|
|
--
|
2865 |
|
|
|
2866 |
|
|
CREATE FUNCTION parent(table_ regclass) RETURNS regclass
|
2867 |
|
|
LANGUAGE sql STABLE
|
2868 |
|
|
AS $_$
|
2869 |
|
|
SELECT inhparent FROM pg_inherits WHERE inhrelid = $1
|
2870 |
|
|
$_$;
|
2871 |
|
|
|
2872 |
|
|
|
2873 |
|
|
--
|
2874 |
12651
|
aaronmk
|
-- Name: populate_table(regclass, text); Type: FUNCTION; Schema: util; Owner: -
|
2875 |
|
|
--
|
2876 |
|
|
|
2877 |
|
|
CREATE FUNCTION populate_table(table_ regclass, sql text) RETURNS void
|
2878 |
|
|
LANGUAGE sql
|
2879 |
|
|
AS $_$
|
2880 |
|
|
SELECT util.eval($$INSERT INTO $$||$1||$$
|
2881 |
|
|
$$||util.ltrim_nl($2));
|
2882 |
|
|
-- make sure the created table has the correct estimated row count
|
2883 |
|
|
SELECT util.analyze_($1);
|
2884 |
|
|
$_$;
|
2885 |
|
|
|
2886 |
|
|
|
2887 |
|
|
--
|
2888 |
12575
|
aaronmk
|
-- Name: prefixed_name(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
2889 |
|
|
--
|
2890 |
|
|
|
2891 |
|
|
CREATE FUNCTION prefixed_name(prefix text, type anyelement) RETURNS text
|
2892 |
|
|
LANGUAGE sql IMMUTABLE
|
2893 |
|
|
AS $_$
|
2894 |
|
|
SELECT util.qual_name(util.schema($2), $1||util.name($2))
|
2895 |
|
|
$_$;
|
2896 |
|
|
|
2897 |
|
|
|
2898 |
|
|
--
|
2899 |
12494
|
aaronmk
|
-- Name: prepend_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
|
2900 |
|
|
--
|
2901 |
|
|
|
2902 |
|
|
CREATE FUNCTION prepend_comment(table_ regclass, comment text) RETURNS void
|
2903 |
|
|
LANGUAGE sql
|
2904 |
|
|
AS $_$
|
2905 |
|
|
SELECT util.set_comment($1, concat($2, util.comment($1)))
|
2906 |
|
|
$_$;
|
2907 |
|
|
|
2908 |
|
|
|
2909 |
|
|
--
|
2910 |
|
|
-- Name: FUNCTION prepend_comment(table_ regclass, comment text); Type: COMMENT; Schema: util; Owner: -
|
2911 |
|
|
--
|
2912 |
|
|
|
2913 |
|
|
COMMENT ON FUNCTION prepend_comment(table_ regclass, comment text) IS '
|
2914 |
|
|
comment: must start and end with a newline
|
2915 |
|
|
';
|
2916 |
|
|
|
2917 |
|
|
|
2918 |
|
|
--
|
2919 |
12260
|
aaronmk
|
-- Name: qual_name(text[]); Type: FUNCTION; Schema: util; Owner: -
|
2920 |
|
|
--
|
2921 |
|
|
|
2922 |
|
|
CREATE FUNCTION qual_name(VARIADIC elems text[]) RETURNS text
|
2923 |
|
|
LANGUAGE sql IMMUTABLE
|
2924 |
|
|
AS $_$
|
2925 |
|
|
SELECT string_agg(quote_ident(unnest), '.') FROM unnest($1)
|
2926 |
|
|
$_$;
|
2927 |
|
|
|
2928 |
|
|
|
2929 |
|
|
--
|
2930 |
10988
|
aaronmk
|
-- Name: qual_name(regclass); Type: FUNCTION; Schema: util; Owner: -
|
2931 |
|
|
--
|
2932 |
|
|
|
2933 |
|
|
CREATE FUNCTION qual_name(table_ regclass) RETURNS text
|
2934 |
12446
|
aaronmk
|
LANGUAGE sql STABLE
|
2935 |
12267
|
aaronmk
|
SET search_path TO pg_temp
|
2936 |
10988
|
aaronmk
|
AS $_$
|
2937 |
12267
|
aaronmk
|
SELECT $1::text
|
2938 |
10988
|
aaronmk
|
$_$;
|
2939 |
|
|
|
2940 |
|
|
|
2941 |
|
|
--
|
2942 |
12267
|
aaronmk
|
-- Name: qual_name(regtype); Type: FUNCTION; Schema: util; Owner: -
|
2943 |
|
|
--
|
2944 |
|
|
|
2945 |
|
|
CREATE FUNCTION qual_name(type regtype) RETURNS text
|
2946 |
12446
|
aaronmk
|
LANGUAGE sql STABLE
|
2947 |
12267
|
aaronmk
|
SET search_path TO pg_temp
|
2948 |
|
|
AS $_$
|
2949 |
|
|
SELECT $1::text
|
2950 |
|
|
$_$;
|
2951 |
|
|
|
2952 |
|
|
|
2953 |
|
|
--
|
2954 |
|
|
-- Name: FUNCTION qual_name(type regtype); Type: COMMENT; Schema: util; Owner: -
|
2955 |
|
|
--
|
2956 |
|
|
|
2957 |
|
|
COMMENT ON FUNCTION qual_name(type regtype) IS '
|
2958 |
|
|
a type''s schema-qualified name
|
2959 |
|
|
';
|
2960 |
|
|
|
2961 |
|
|
|
2962 |
|
|
--
|
2963 |
12268
|
aaronmk
|
-- Name: qual_name(unknown); Type: FUNCTION; Schema: util; Owner: -
|
2964 |
|
|
--
|
2965 |
|
|
|
2966 |
|
|
CREATE FUNCTION qual_name(type unknown) RETURNS text
|
2967 |
12446
|
aaronmk
|
LANGUAGE sql STABLE
|
2968 |
12268
|
aaronmk
|
AS $_$
|
2969 |
|
|
SELECT util.qual_name($1::text::regtype)
|
2970 |
|
|
$_$;
|
2971 |
|
|
|
2972 |
|
|
|
2973 |
|
|
--
|
2974 |
12376
|
aaronmk
|
-- Name: quote_func_call(regprocedure, text[]); Type: FUNCTION; Schema: util; Owner: -
|
2975 |
|
|
--
|
2976 |
|
|
|
2977 |
|
|
CREATE FUNCTION quote_func_call(func regprocedure, VARIADIC args_esc text[]) RETURNS text
|
2978 |
|
|
LANGUAGE sql IMMUTABLE
|
2979 |
|
|
AS $_$
|
2980 |
|
|
SELECT util.quote_func_call($1::regproc::text, VARIADIC $2)
|
2981 |
|
|
$_$;
|
2982 |
|
|
|
2983 |
|
|
|
2984 |
|
|
--
|
2985 |
|
|
-- Name: quote_func_call(text, text[]); Type: FUNCTION; Schema: util; Owner: -
|
2986 |
|
|
--
|
2987 |
|
|
|
2988 |
|
|
CREATE FUNCTION quote_func_call(func_esc text, VARIADIC args_esc text[]) RETURNS text
|
2989 |
|
|
LANGUAGE sql IMMUTABLE
|
2990 |
|
|
AS $_$
|
2991 |
|
|
SELECT $1||'('||concat_ws(', ', VARIADIC $2)||')'
|
2992 |
|
|
$_$;
|
2993 |
|
|
|
2994 |
|
|
|
2995 |
|
|
--
|
2996 |
12371
|
aaronmk
|
-- Name: quote_typed(anyelement); Type: FUNCTION; Schema: util; Owner: -
|
2997 |
|
|
--
|
2998 |
|
|
|
2999 |
|
|
CREATE FUNCTION quote_typed(value anyelement) RETURNS text
|
3000 |
|
|
LANGUAGE sql IMMUTABLE
|
3001 |
|
|
AS $_$
|
3002 |
12437
|
aaronmk
|
SELECT quote_nullable($1)||$$::$$||util.qual_name(pg_typeof($1))
|
3003 |
12371
|
aaronmk
|
$_$;
|
3004 |
|
|
|
3005 |
|
|
|
3006 |
|
|
--
|
3007 |
12530
|
aaronmk
|
-- Name: raise(text, text); Type: FUNCTION; Schema: util; Owner: -
|
3008 |
|
|
--
|
3009 |
|
|
|
3010 |
|
|
CREATE FUNCTION raise(type text, msg text) RETURNS void
|
3011 |
|
|
LANGUAGE sql IMMUTABLE
|
3012 |
12560
|
aaronmk
|
AS $_X$
|
3013 |
12530
|
aaronmk
|
SELECT util.eval($$
|
3014 |
|
|
CREATE OR REPLACE FUNCTION pg_temp.__raise()
|
3015 |
|
|
RETURNS void AS
|
3016 |
12560
|
aaronmk
|
-- $__BODY1$ in case msg contains $BODY1$ (in SQL)
|
3017 |
|
|
$__BODY1$
|
3018 |
12530
|
aaronmk
|
BEGIN
|
3019 |
|
|
RAISE $$||$1||$$ USING MESSAGE = $$||quote_nullable($2)||$$;
|
3020 |
|
|
END;
|
3021 |
12560
|
aaronmk
|
$__BODY1$
|
3022 |
12530
|
aaronmk
|
LANGUAGE plpgsql IMMUTABLE
|
3023 |
|
|
COST 100;
|
3024 |
12532
|
aaronmk
|
$$, verbose_ := false);
|
3025 |
12530
|
aaronmk
|
|
3026 |
12532
|
aaronmk
|
SELECT util.eval($$SELECT pg_temp.__raise()$$, verbose_ := false);
|
3027 |
12560
|
aaronmk
|
$_X$;
|
3028 |
12530
|
aaronmk
|
|
3029 |
|
|
|
3030 |
|
|
--
|
3031 |
12533
|
aaronmk
|
-- Name: FUNCTION raise(type text, msg text); Type: COMMENT; Schema: util; Owner: -
|
3032 |
|
|
--
|
3033 |
|
|
|
3034 |
|
|
COMMENT ON FUNCTION raise(type text, msg text) IS '
|
3035 |
|
|
type: a log level from
|
3036 |
|
|
http://www.postgresql.org/docs/9.3/static/plpgsql-errors-and-messages.html
|
3037 |
|
|
or a condition name from
|
3038 |
|
|
http://www.postgresql.org/docs/9.3/static/errcodes-appendix.html
|
3039 |
|
|
';
|
3040 |
|
|
|
3041 |
|
|
|
3042 |
|
|
--
|
3043 |
12536
|
aaronmk
|
-- Name: raise_error_warning(text); Type: FUNCTION; Schema: util; Owner: -
|
3044 |
12311
|
aaronmk
|
--
|
3045 |
|
|
|
3046 |
12536
|
aaronmk
|
CREATE FUNCTION raise_error_warning(msg text) RETURNS void
|
3047 |
12441
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
3048 |
12311
|
aaronmk
|
AS $_$
|
3049 |
12536
|
aaronmk
|
SELECT util.raise('WARNING', 'ERROR: '||$1)
|
3050 |
12311
|
aaronmk
|
$_$;
|
3051 |
|
|
|
3052 |
|
|
|
3053 |
|
|
--
|
3054 |
10116
|
aaronmk
|
-- Name: raise_undefined_column(col_ref); Type: FUNCTION; Schema: util; Owner: -
|
3055 |
|
|
--
|
3056 |
|
|
|
3057 |
|
|
CREATE FUNCTION raise_undefined_column(col col_ref) RETURNS text
|
3058 |
|
|
LANGUAGE plpgsql IMMUTABLE STRICT
|
3059 |
|
|
AS $$
|
3060 |
|
|
BEGIN
|
3061 |
|
|
RAISE undefined_column USING MESSAGE = concat('undefined column: ', col.name);
|
3062 |
|
|
END;
|
3063 |
|
|
$$;
|
3064 |
|
|
|
3065 |
|
|
|
3066 |
|
|
--
|
3067 |
11657
|
aaronmk
|
-- Name: regexp_matches_group(text, text, integer); Type: FUNCTION; Schema: util; Owner: -
|
3068 |
|
|
--
|
3069 |
|
|
|
3070 |
|
|
CREATE FUNCTION regexp_matches_group(str text, re text, group_ integer DEFAULT 1) RETURNS SETOF text
|
3071 |
|
|
LANGUAGE sql IMMUTABLE
|
3072 |
|
|
AS $_$
|
3073 |
|
|
SELECT regexp_matches[$3] FROM regexp_matches($1, $2, 'g')
|
3074 |
|
|
$_$;
|
3075 |
|
|
|
3076 |
|
|
|
3077 |
|
|
--
|
3078 |
12333
|
aaronmk
|
-- Name: regexp_quote(text); Type: FUNCTION; Schema: util; Owner: -
|
3079 |
|
|
--
|
3080 |
|
|
|
3081 |
|
|
CREATE FUNCTION regexp_quote(str text) RETURNS text
|
3082 |
|
|
LANGUAGE sql IMMUTABLE
|
3083 |
|
|
AS $_$
|
3084 |
|
|
SELECT regexp_replace($1, '\W', /*\char*/'\\\&', 'g')
|
3085 |
|
|
$_$;
|
3086 |
|
|
|
3087 |
|
|
|
3088 |
|
|
--
|
3089 |
12375
|
aaronmk
|
-- Name: regprocedure(text); Type: FUNCTION; Schema: util; Owner: -
|
3090 |
|
|
--
|
3091 |
|
|
|
3092 |
|
|
CREATE FUNCTION regprocedure(func text) RETURNS regprocedure
|
3093 |
|
|
LANGUAGE sql IMMUTABLE
|
3094 |
|
|
AS $_$
|
3095 |
|
|
SELECT (CASE WHEN right($1, 1) = ')'
|
3096 |
12377
|
aaronmk
|
THEN $1::regprocedure ELSE $1::regproc::regprocedure END)
|
3097 |
12375
|
aaronmk
|
$_$;
|
3098 |
|
|
|
3099 |
|
|
|
3100 |
|
|
--
|
3101 |
13492
|
aaronmk
|
-- Name: relation_exists(text); Type: FUNCTION; Schema: util; Owner: -
|
3102 |
|
|
--
|
3103 |
|
|
|
3104 |
|
|
CREATE FUNCTION relation_exists(relation text) RETURNS boolean
|
3105 |
|
|
LANGUAGE sql STABLE
|
3106 |
|
|
AS $_$
|
3107 |
|
|
SELECT $1 IS NOT NULL AND util.is_castable($1, NULL::regclass)
|
3108 |
|
|
$_$;
|
3109 |
|
|
|
3110 |
|
|
|
3111 |
|
|
--
|
3112 |
12344
|
aaronmk
|
-- Name: relation_type(regclass); Type: FUNCTION; Schema: util; Owner: -
|
3113 |
|
|
--
|
3114 |
|
|
|
3115 |
|
|
CREATE FUNCTION relation_type(relation regclass) RETURNS text
|
3116 |
|
|
LANGUAGE sql STABLE
|
3117 |
|
|
AS $_$
|
3118 |
|
|
SELECT util.relation_type(util.relation_type_char($1))
|
3119 |
|
|
$_$;
|
3120 |
|
|
|
3121 |
|
|
|
3122 |
|
|
--
|
3123 |
12340
|
aaronmk
|
-- Name: relation_type("char"); Type: FUNCTION; Schema: util; Owner: -
|
3124 |
12339
|
aaronmk
|
--
|
3125 |
|
|
|
3126 |
12340
|
aaronmk
|
CREATE FUNCTION relation_type(relation_type_char "char") RETURNS text
|
3127 |
12339
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
3128 |
|
|
AS $_$
|
3129 |
12593
|
aaronmk
|
SELECT 'c=>TYPE, r=>TABLE, v=>VIEW'::hstore -> $1
|
3130 |
12339
|
aaronmk
|
$_$;
|
3131 |
|
|
|
3132 |
|
|
|
3133 |
|
|
--
|
3134 |
12588
|
aaronmk
|
-- Name: relation_type(regtype); Type: FUNCTION; Schema: util; Owner: -
|
3135 |
|
|
--
|
3136 |
|
|
|
3137 |
|
|
CREATE FUNCTION relation_type(type regtype) RETURNS text
|
3138 |
|
|
LANGUAGE sql IMMUTABLE
|
3139 |
|
|
AS $$
|
3140 |
|
|
SELECT 'TYPE'::text
|
3141 |
|
|
$$;
|
3142 |
|
|
|
3143 |
|
|
|
3144 |
|
|
--
|
3145 |
12341
|
aaronmk
|
-- Name: relation_type_char(regclass); Type: FUNCTION; Schema: util; Owner: -
|
3146 |
|
|
--
|
3147 |
|
|
|
3148 |
|
|
CREATE FUNCTION relation_type_char(relation regclass) RETURNS "char"
|
3149 |
|
|
LANGUAGE sql STABLE
|
3150 |
|
|
AS $_$
|
3151 |
|
|
SELECT relkind FROM pg_class WHERE oid = $1
|
3152 |
|
|
$_$;
|
3153 |
|
|
|
3154 |
|
|
|
3155 |
|
|
--
|
3156 |
12293
|
aaronmk
|
-- Name: remake_diff_table(text, regclass, regclass, text); Type: FUNCTION; Schema: util; Owner: -
|
3157 |
|
|
--
|
3158 |
|
|
|
3159 |
|
|
CREATE FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) RETURNS void
|
3160 |
|
|
LANGUAGE sql
|
3161 |
|
|
AS $_$
|
3162 |
|
|
/* can't have in_table/out_table inherit from *each other*, because inheritance
|
3163 |
|
|
also causes the rows of the parent table to be included in the child table.
|
3164 |
|
|
instead, they need to inherit from a common, empty table. */
|
3165 |
12382
|
aaronmk
|
SELECT util.create_if_not_exists($$SELECT $$||util.quote_func_call(
|
3166 |
|
|
'util.copy_struct', util.quote_typed($2), util.quote_typed($4)));
|
3167 |
13098
|
aaronmk
|
SELECT util.rm_freq(ARRAY[$4]); -- left/right_table don't have freq yet
|
3168 |
12293
|
aaronmk
|
SELECT util.inherit($2, $4);
|
3169 |
|
|
SELECT util.inherit($3, $4);
|
3170 |
|
|
|
3171 |
|
|
SELECT util.rematerialize_query($1, $$
|
3172 |
|
|
SELECT * FROM util.diff(
|
3173 |
12419
|
aaronmk
|
$$||util.quote_typed($2)||$$
|
3174 |
|
|
, $$||util.quote_typed($3)||$$
|
3175 |
12293
|
aaronmk
|
, NULL::$$||$4||$$)
|
3176 |
|
|
$$);
|
3177 |
12303
|
aaronmk
|
|
3178 |
|
|
/* the table unfortunately cannot be *materialized* in human-readable form,
|
3179 |
|
|
because this would create column name collisions between the two sides */
|
3180 |
12495
|
aaronmk
|
SELECT util.prepend_comment($1, '
|
3181 |
12303
|
aaronmk
|
to view this table in human-readable form (with each side''s tuple column
|
3182 |
|
|
expanded to its component fields):
|
3183 |
12572
|
aaronmk
|
SELECT (left_).*, ('||util.schema($4::regclass)||'.values_(right_)).* FROM '||$1||';
|
3184 |
13092
|
aaronmk
|
|
3185 |
|
|
to display NULL values that are extra or missing:
|
3186 |
|
|
SELECT * FROM '||$1||';
|
3187 |
12303
|
aaronmk
|
');
|
3188 |
12293
|
aaronmk
|
$_$;
|
3189 |
|
|
|
3190 |
|
|
|
3191 |
|
|
--
|
3192 |
|
|
-- Name: FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text); Type: COMMENT; Schema: util; Owner: -
|
3193 |
|
|
--
|
3194 |
|
|
|
3195 |
|
|
COMMENT ON FUNCTION remake_diff_table(diff_table text, left_table regclass, right_table regclass, type_table text) IS '
|
3196 |
|
|
type_table (*required*): table to create as the shared base type
|
3197 |
|
|
';
|
3198 |
|
|
|
3199 |
|
|
|
3200 |
|
|
--
|
3201 |
12265
|
aaronmk
|
-- Name: rematerialize_query(text, text); Type: FUNCTION; Schema: util; Owner: -
|
3202 |
|
|
--
|
3203 |
|
|
|
3204 |
|
|
CREATE FUNCTION rematerialize_query(table_esc text, sql text) RETURNS void
|
3205 |
|
|
LANGUAGE sql
|
3206 |
|
|
AS $_$
|
3207 |
|
|
SELECT util.drop_table($1);
|
3208 |
|
|
SELECT util.materialize_query($1, $2);
|
3209 |
|
|
$_$;
|
3210 |
|
|
|
3211 |
|
|
|
3212 |
|
|
--
|
3213 |
|
|
-- Name: FUNCTION rematerialize_query(table_esc text, sql text); Type: COMMENT; Schema: util; Owner: -
|
3214 |
|
|
--
|
3215 |
|
|
|
3216 |
|
|
COMMENT ON FUNCTION rematerialize_query(table_esc text, sql text) IS '
|
3217 |
|
|
idempotent, but repeats action each time
|
3218 |
|
|
';
|
3219 |
|
|
|
3220 |
|
|
|
3221 |
|
|
--
|
3222 |
12247
|
aaronmk
|
-- Name: rematerialize_view(text, regclass); Type: FUNCTION; Schema: util; Owner: -
|
3223 |
|
|
--
|
3224 |
|
|
|
3225 |
12262
|
aaronmk
|
CREATE FUNCTION rematerialize_view(table_esc text, view_ regclass) RETURNS void
|
3226 |
12247
|
aaronmk
|
LANGUAGE sql
|
3227 |
|
|
AS $_$
|
3228 |
|
|
SELECT util.drop_table($1);
|
3229 |
|
|
SELECT util.materialize_view($1, $2);
|
3230 |
|
|
$_$;
|
3231 |
|
|
|
3232 |
|
|
|
3233 |
|
|
--
|
3234 |
12262
|
aaronmk
|
-- Name: FUNCTION rematerialize_view(table_esc text, view_ regclass); Type: COMMENT; Schema: util; Owner: -
|
3235 |
12247
|
aaronmk
|
--
|
3236 |
|
|
|
3237 |
12262
|
aaronmk
|
COMMENT ON FUNCTION rematerialize_view(table_esc text, view_ regclass) IS '
|
3238 |
12247
|
aaronmk
|
idempotent, but repeats action each time
|
3239 |
|
|
';
|
3240 |
|
|
|
3241 |
|
|
|
3242 |
|
|
--
|
3243 |
8183
|
aaronmk
|
-- Name: rename_cols(regclass, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
3244 |
8137
|
aaronmk
|
--
|
3245 |
|
|
|
3246 |
8148
|
aaronmk
|
CREATE FUNCTION rename_cols(table_ regclass, renames anyelement) RETURNS void
|
3247 |
12446
|
aaronmk
|
LANGUAGE sql
|
3248 |
8137
|
aaronmk
|
AS $_$
|
3249 |
8212
|
aaronmk
|
SELECT util.try_create($$ALTER TABLE $$||$1||$$ RENAME $$
|
3250 |
8137
|
aaronmk
|
||quote_ident(name)||$$ TO $$||quote_ident($2 -> name))
|
3251 |
10309
|
aaronmk
|
FROM util.col_names($1::text::regtype) f (name);
|
3252 |
|
|
SELECT NULL::void; -- don't fold away functions called in previous query
|
3253 |
8137
|
aaronmk
|
$_$;
|
3254 |
|
|
|
3255 |
|
|
|
3256 |
|
|
--
|
3257 |
8183
|
aaronmk
|
-- Name: FUNCTION rename_cols(table_ regclass, renames anyelement); Type: COMMENT; Schema: util; Owner: -
|
3258 |
8137
|
aaronmk
|
--
|
3259 |
|
|
|
3260 |
12235
|
aaronmk
|
COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS '
|
3261 |
|
|
idempotent
|
3262 |
|
|
';
|
3263 |
8137
|
aaronmk
|
|
3264 |
|
|
|
3265 |
|
|
--
|
3266 |
12349
|
aaronmk
|
-- Name: rename_relation(regclass, text); Type: FUNCTION; Schema: util; Owner: -
|
3267 |
|
|
--
|
3268 |
|
|
|
3269 |
|
|
CREATE FUNCTION rename_relation(from_ regclass, to_ text) RETURNS void
|
3270 |
|
|
LANGUAGE sql
|
3271 |
|
|
AS $_$
|
3272 |
12353
|
aaronmk
|
/* use util.qual_name() instead of ::text so that the schema qualifier is always
|
3273 |
|
|
included in the debug SQL */
|
3274 |
|
|
SELECT util.rename_relation(util.qual_name($1), $2)
|
3275 |
12349
|
aaronmk
|
$_$;
|
3276 |
|
|
|
3277 |
|
|
|
3278 |
|
|
--
|
3279 |
|
|
-- Name: rename_relation(text, text); Type: FUNCTION; Schema: util; Owner: -
|
3280 |
|
|
--
|
3281 |
|
|
|
3282 |
12364
|
aaronmk
|
CREATE FUNCTION rename_relation(from_esc text, to_name text) RETURNS void
|
3283 |
12349
|
aaronmk
|
LANGUAGE sql
|
3284 |
|
|
AS $_$
|
3285 |
|
|
/* 'ALTER TABLE can be used with views too'
|
3286 |
|
|
(http://www.postgresql.org/docs/9.3/static/sql-alterview.html) */
|
3287 |
12363
|
aaronmk
|
SELECT util.eval($$ALTER TABLE IF EXISTS $$||$1||$$ RENAME TO $$
|
3288 |
|
|
||quote_ident($2))
|
3289 |
12349
|
aaronmk
|
$_$;
|
3290 |
|
|
|
3291 |
|
|
|
3292 |
|
|
--
|
3293 |
12364
|
aaronmk
|
-- Name: FUNCTION rename_relation(from_esc text, to_name text); Type: COMMENT; Schema: util; Owner: -
|
3294 |
12349
|
aaronmk
|
--
|
3295 |
|
|
|
3296 |
12364
|
aaronmk
|
COMMENT ON FUNCTION rename_relation(from_esc text, to_name text) IS '
|
3297 |
12349
|
aaronmk
|
idempotent
|
3298 |
|
|
';
|
3299 |
|
|
|
3300 |
|
|
|
3301 |
|
|
--
|
3302 |
12358
|
aaronmk
|
-- Name: replace_suffix(text, text, text, integer); Type: FUNCTION; Schema: util; Owner: -
|
3303 |
12350
|
aaronmk
|
--
|
3304 |
|
|
|
3305 |
12358
|
aaronmk
|
CREATE FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer DEFAULT 0) RETURNS text
|
3306 |
12350
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
3307 |
|
|
AS $_$
|
3308 |
12358
|
aaronmk
|
SELECT regexp_replace($1, util.truncated_prefixed_name_regexp($2, $4), '\1'||$3)
|
3309 |
12350
|
aaronmk
|
$_$;
|
3310 |
|
|
|
3311 |
|
|
|
3312 |
|
|
--
|
3313 |
12358
|
aaronmk
|
-- Name: FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer); Type: COMMENT; Schema: util; Owner: -
|
3314 |
|
|
--
|
3315 |
|
|
|
3316 |
|
|
COMMENT ON FUNCTION replace_suffix(str text, old_suffix text, new_suffix text, max_prefix_len integer) IS '
|
3317 |
|
|
max_prefix_len: when str may have been truncated (eg. as a table name) due to the prepending of a prefix, support prefixes up to this length
|
3318 |
|
|
';
|
3319 |
|
|
|
3320 |
|
|
|
3321 |
|
|
--
|
3322 |
10297
|
aaronmk
|
-- Name: reset_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
|
3323 |
|
|
--
|
3324 |
|
|
|
3325 |
|
|
CREATE FUNCTION reset_col_names(table_ regclass, names regclass) RETURNS void
|
3326 |
12446
|
aaronmk
|
LANGUAGE sql
|
3327 |
10297
|
aaronmk
|
AS $_$
|
3328 |
10596
|
aaronmk
|
SELECT util.eval($$DELETE FROM $$||$2||$$ WHERE "from" LIKE ':%'$$);
|
3329 |
|
|
SELECT util.mk_derived_col(($2, 'to'), $$"from"$$, overwrite := true);
|
3330 |
10297
|
aaronmk
|
SELECT util.set_col_names($1, $2);
|
3331 |
|
|
$_$;
|
3332 |
|
|
|
3333 |
|
|
|
3334 |
|
|
--
|
3335 |
|
|
-- Name: FUNCTION reset_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
|
3336 |
|
|
--
|
3337 |
|
|
|
3338 |
12235
|
aaronmk
|
COMMENT ON FUNCTION reset_col_names(table_ regclass, names regclass) IS '
|
3339 |
|
|
idempotent.
|
3340 |
|
|
alters the names table, so it will need to be repopulated after running this function.
|
3341 |
|
|
';
|
3342 |
10297
|
aaronmk
|
|
3343 |
|
|
|
3344 |
|
|
--
|
3345 |
8183
|
aaronmk
|
-- Name: reset_map_table(text); Type: FUNCTION; Schema: util; Owner: -
|
3346 |
8143
|
aaronmk
|
--
|
3347 |
|
|
|
3348 |
|
|
CREATE FUNCTION reset_map_table(table_ text) RETURNS void
|
3349 |
12446
|
aaronmk
|
LANGUAGE sql
|
3350 |
8143
|
aaronmk
|
AS $_$
|
3351 |
10152
|
aaronmk
|
SELECT util.drop_table($1);
|
3352 |
8183
|
aaronmk
|
SELECT util.mk_map_table($1);
|
3353 |
8143
|
aaronmk
|
$_$;
|
3354 |
|
|
|
3355 |
|
|
|
3356 |
|
|
--
|
3357 |
13488
|
aaronmk
|
-- Name: restore_views(restore_views_info); Type: FUNCTION; Schema: util; Owner: -
|
3358 |
13486
|
aaronmk
|
--
|
3359 |
|
|
|
3360 |
13488
|
aaronmk
|
CREATE FUNCTION restore_views(restore_views_info) RETURNS void
|
3361 |
13486
|
aaronmk
|
LANGUAGE sql
|
3362 |
|
|
AS $_$
|
3363 |
|
|
SELECT util.debug_print_var('views', $1);
|
3364 |
13497
|
aaronmk
|
SELECT util.create_if_not_exists((view_).def, (view_).path)
|
3365 |
|
|
/* need to specify view name for manual existence check, in case view def
|
3366 |
|
|
becomes invalid, which would produce nonstandard (uncatchable) exception */
|
3367 |
13491
|
aaronmk
|
FROM unnest($1.views) view_; -- in forward dependency order
|
3368 |
13486
|
aaronmk
|
/* create_if_not_exists() rather than eval(), because cmd might manually
|
3369 |
|
|
re-create a deleted dependent view, causing it to already exist */
|
3370 |
|
|
SELECT NULL::void; -- don't fold away functions called in previous query
|
3371 |
|
|
$_$;
|
3372 |
|
|
|
3373 |
|
|
|
3374 |
|
|
--
|
3375 |
13096
|
aaronmk
|
-- Name: rm_freq(regclass[], text); Type: FUNCTION; Schema: util; Owner: -
|
3376 |
|
|
--
|
3377 |
|
|
|
3378 |
|
|
CREATE FUNCTION rm_freq(tables regclass[], freq_col text DEFAULT 'copies'::text) RETURNS void
|
3379 |
|
|
LANGUAGE sql
|
3380 |
|
|
AS $_$
|
3381 |
|
|
SELECT util.drop_column($1, $2, force := true)
|
3382 |
|
|
$_$;
|
3383 |
|
|
|
3384 |
|
|
|
3385 |
|
|
--
|
3386 |
12356
|
aaronmk
|
-- Name: rtrim_n(text, integer); Type: FUNCTION; Schema: util; Owner: -
|
3387 |
|
|
--
|
3388 |
|
|
|
3389 |
|
|
CREATE FUNCTION rtrim_n(str text, count integer) RETURNS text
|
3390 |
|
|
LANGUAGE sql IMMUTABLE
|
3391 |
|
|
AS $_$
|
3392 |
|
|
SELECT (CASE WHEN $2 <= 0 THEN $1 ELSE left($1, -$2) END)
|
3393 |
|
|
$_$;
|
3394 |
|
|
|
3395 |
|
|
|
3396 |
|
|
--
|
3397 |
12473
|
aaronmk
|
-- Name: runnable_sql(text); Type: FUNCTION; Schema: util; Owner: -
|
3398 |
|
|
--
|
3399 |
|
|
|
3400 |
|
|
CREATE FUNCTION runnable_sql(sql text) RETURNS text
|
3401 |
|
|
LANGUAGE sql IMMUTABLE
|
3402 |
|
|
AS $_$
|
3403 |
12481
|
aaronmk
|
SELECT (CASE WHEN util.is_set_stmt($1) THEN ''
|
3404 |
|
|
ELSE util.mk_set_search_path(for_printing := true)||$$;
|
3405 |
|
|
$$ END)||$1
|
3406 |
12473
|
aaronmk
|
$_$;
|
3407 |
|
|
|
3408 |
|
|
|
3409 |
|
|
--
|
3410 |
11652
|
aaronmk
|
-- Name: save_drop_view(text); Type: FUNCTION; Schema: util; Owner: -
|
3411 |
|
|
--
|
3412 |
|
|
|
3413 |
|
|
CREATE FUNCTION save_drop_view(view_ text) RETURNS text
|
3414 |
|
|
LANGUAGE plpgsql STRICT
|
3415 |
13467
|
aaronmk
|
AS $$
|
3416 |
11652
|
aaronmk
|
DECLARE
|
3417 |
|
|
result text = NULL;
|
3418 |
|
|
BEGIN
|
3419 |
|
|
BEGIN
|
3420 |
13470
|
aaronmk
|
result = util.show_create_view(view_, replace := false);
|
3421 |
|
|
/* replace: no `OR REPLACE` because that causes nonuniform errors
|
3422 |
|
|
(eg. invalid_table_definition), instead of the standard
|
3423 |
|
|
duplicate_table exception caught by util.create_if_not_exists() */
|
3424 |
13467
|
aaronmk
|
PERFORM util.drop_view(view_);
|
3425 |
11652
|
aaronmk
|
EXCEPTION
|
3426 |
|
|
WHEN undefined_table THEN NULL;
|
3427 |
|
|
END;
|
3428 |
|
|
RETURN result;
|
3429 |
|
|
END;
|
3430 |
13467
|
aaronmk
|
$$;
|
3431 |
11652
|
aaronmk
|
|
3432 |
|
|
|
3433 |
|
|
--
|
3434 |
11660
|
aaronmk
|
-- Name: save_drop_views(text[]); Type: FUNCTION; Schema: util; Owner: -
|
3435 |
|
|
--
|
3436 |
|
|
|
3437 |
13488
|
aaronmk
|
CREATE FUNCTION save_drop_views(views text[]) RETURNS restore_views_info
|
3438 |
11660
|
aaronmk
|
LANGUAGE sql
|
3439 |
|
|
AS $_$
|
3440 |
13488
|
aaronmk
|
SELECT ROW(/*return in forward dependency order*/util.array_reverse(array(
|
3441 |
13491
|
aaronmk
|
SELECT (view_, util.save_drop_view(view_))::util.db_item
|
3442 |
13485
|
aaronmk
|
FROM unnest(/*drop in reverse dependency order*/util.array_reverse($1)) view_
|
3443 |
13488
|
aaronmk
|
)))::util.restore_views_info
|
3444 |
11660
|
aaronmk
|
$_$;
|
3445 |
|
|
|
3446 |
|
|
|
3447 |
|
|
--
|
3448 |
12244
|
aaronmk
|
-- Name: schema(oid); Type: FUNCTION; Schema: util; Owner: -
|
3449 |
|
|
--
|
3450 |
|
|
|
3451 |
|
|
CREATE FUNCTION schema(pg_namespace_oid oid) RETURNS text
|
3452 |
|
|
LANGUAGE sql STABLE
|
3453 |
|
|
AS $_$
|
3454 |
|
|
SELECT nspname::text FROM pg_namespace WHERE pg_namespace.oid = $1
|
3455 |
|
|
$_$;
|
3456 |
|
|
|
3457 |
|
|
|
3458 |
|
|
--
|
3459 |
12242
|
aaronmk
|
-- Name: schema(regclass); Type: FUNCTION; Schema: util; Owner: -
|
3460 |
|
|
--
|
3461 |
|
|
|
3462 |
|
|
CREATE FUNCTION schema(table_ regclass) RETURNS text
|
3463 |
|
|
LANGUAGE sql STABLE
|
3464 |
|
|
AS $_$
|
3465 |
12245
|
aaronmk
|
SELECT util.schema(relnamespace) FROM pg_class WHERE oid = $1
|
3466 |
12242
|
aaronmk
|
$_$;
|
3467 |
|
|
|
3468 |
|
|
|
3469 |
|
|
--
|
3470 |
10794
|
aaronmk
|
-- Name: schema(regtype); Type: FUNCTION; Schema: util; Owner: -
|
3471 |
|
|
--
|
3472 |
|
|
|
3473 |
|
|
CREATE FUNCTION schema(type regtype) RETURNS text
|
3474 |
|
|
LANGUAGE sql STABLE
|
3475 |
|
|
AS $_$
|
3476 |
12245
|
aaronmk
|
SELECT util.schema(typnamespace) FROM pg_type WHERE oid = $1
|
3477 |
10794
|
aaronmk
|
$_$;
|
3478 |
|
|
|
3479 |
|
|
|
3480 |
|
|
--
|
3481 |
|
|
-- Name: schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
|
3482 |
|
|
--
|
3483 |
|
|
|
3484 |
|
|
CREATE FUNCTION schema(type_null anyelement) RETURNS text
|
3485 |
|
|
LANGUAGE sql STABLE
|
3486 |
|
|
AS $_$
|
3487 |
|
|
SELECT util.schema(pg_typeof($1))
|
3488 |
|
|
$_$;
|
3489 |
|
|
|
3490 |
|
|
|
3491 |
|
|
--
|
3492 |
12134
|
aaronmk
|
-- Name: schema_bundle_get_schemas(text); Type: FUNCTION; Schema: util; Owner: -
|
3493 |
|
|
--
|
3494 |
|
|
|
3495 |
|
|
CREATE FUNCTION schema_bundle_get_schemas(schema_bundle text) RETURNS SETOF text
|
3496 |
|
|
LANGUAGE sql STABLE
|
3497 |
|
|
AS $_$
|
3498 |
|
|
SELECT nspname::text FROM pg_namespace WHERE nspname ~ ('^'||$1||'(?=\y|_)')
|
3499 |
|
|
$_$;
|
3500 |
|
|
|
3501 |
|
|
|
3502 |
|
|
--
|
3503 |
12135
|
aaronmk
|
-- Name: FUNCTION schema_bundle_get_schemas(schema_bundle text); Type: COMMENT; Schema: util; Owner: -
|
3504 |
|
|
--
|
3505 |
|
|
|
3506 |
12235
|
aaronmk
|
COMMENT ON FUNCTION schema_bundle_get_schemas(schema_bundle text) IS '
|
3507 |
|
|
a schema bundle is a group of schemas with a common prefix
|
3508 |
|
|
';
|
3509 |
12135
|
aaronmk
|
|
3510 |
|
|
|
3511 |
|
|
--
|
3512 |
|
|
-- Name: schema_bundle_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
|
3513 |
|
|
--
|
3514 |
|
|
|
3515 |
|
|
CREATE FUNCTION schema_bundle_rename(old text, new text) RETURNS void
|
3516 |
|
|
LANGUAGE sql
|
3517 |
|
|
AS $_$
|
3518 |
|
|
SELECT util.schema_rename(old_schema,
|
3519 |
|
|
overlay(old_schema placing new from 1 for length(old))) -- replace prefix
|
3520 |
|
|
FROM util.schema_bundle_get_schemas($1) f (old_schema);
|
3521 |
|
|
SELECT NULL::void; -- don't fold away functions called in previous query
|
3522 |
|
|
$_$;
|
3523 |
|
|
|
3524 |
|
|
|
3525 |
|
|
--
|
3526 |
|
|
-- Name: schema_bundle_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
|
3527 |
|
|
--
|
3528 |
|
|
|
3529 |
|
|
CREATE FUNCTION schema_bundle_replace(replace text, with_ text) RETURNS void
|
3530 |
|
|
LANGUAGE plpgsql
|
3531 |
|
|
AS $$
|
3532 |
|
|
BEGIN
|
3533 |
|
|
-- don't schema_bundle_rm() the schema_bundle to keep!
|
3534 |
|
|
IF replace = with_ THEN RETURN; END IF;
|
3535 |
|
|
|
3536 |
|
|
PERFORM util.schema_bundle_rm(replace);
|
3537 |
|
|
PERFORM util.schema_bundle_rename(with_, replace);
|
3538 |
|
|
END;
|
3539 |
|
|
$$;
|
3540 |
|
|
|
3541 |
|
|
|
3542 |
|
|
--
|
3543 |
|
|
-- Name: schema_bundle_rm(text); Type: FUNCTION; Schema: util; Owner: -
|
3544 |
|
|
--
|
3545 |
|
|
|
3546 |
|
|
CREATE FUNCTION schema_bundle_rm(schema_bundle text) RETURNS void
|
3547 |
|
|
LANGUAGE sql
|
3548 |
|
|
AS $_$
|
3549 |
|
|
SELECT util.schema_rm(schema)
|
3550 |
|
|
FROM util.schema_bundle_get_schemas($1) f (schema);
|
3551 |
|
|
SELECT NULL::void; -- don't fold away functions called in previous query
|
3552 |
|
|
$_$;
|
3553 |
|
|
|
3554 |
|
|
|
3555 |
|
|
--
|
3556 |
12238
|
aaronmk
|
-- Name: schema_esc(anyelement); Type: FUNCTION; Schema: util; Owner: -
|
3557 |
10795
|
aaronmk
|
--
|
3558 |
|
|
|
3559 |
12238
|
aaronmk
|
CREATE FUNCTION schema_esc(type_null anyelement) RETURNS text
|
3560 |
10795
|
aaronmk
|
LANGUAGE sql STABLE
|
3561 |
|
|
AS $_$
|
3562 |
|
|
SELECT quote_ident(util.schema($1))
|
3563 |
|
|
$_$;
|
3564 |
|
|
|
3565 |
|
|
|
3566 |
|
|
--
|
3567 |
12324
|
aaronmk
|
-- Name: schema_matches(text, text); Type: FUNCTION; Schema: util; Owner: -
|
3568 |
|
|
--
|
3569 |
|
|
|
3570 |
|
|
CREATE FUNCTION schema_matches(schema text, schema_regexp text) RETURNS boolean
|
3571 |
12334
|
aaronmk
|
LANGUAGE sql IMMUTABLE
|
3572 |
12324
|
aaronmk
|
AS $_$
|
3573 |
|
|
SELECT $1 ~ $2 AND /*in userspace*/$1 !~ '^(?:information_schema|pg_.*)$'
|
3574 |
|
|
$_$;
|
3575 |
|
|
|
3576 |
|
|
|
3577 |
|
|
--
|
3578 |
12304
|
aaronmk
|
-- Name: schema_oid(text); Type: FUNCTION; Schema: util; Owner: -
|
3579 |
|
|
--
|
3580 |
|
|
|
3581 |
|
|
CREATE FUNCTION schema_oid(schema text) RETURNS oid
|
3582 |
|
|
LANGUAGE sql STABLE
|
3583 |
|
|
AS $_$
|
3584 |
|
|
SELECT oid FROM pg_namespace WHERE nspname = $1
|
3585 |
|
|
$_$;
|
3586 |
|
|
|
3587 |
|
|
|
3588 |
|
|
--
|
3589 |
12504
|
aaronmk
|
-- Name: schema_regexp(regclass); Type: FUNCTION; Schema: util; Owner: -
|
3590 |
|
|
--
|
3591 |
|
|
|
3592 |
|
|
CREATE FUNCTION schema_regexp(relation regclass) RETURNS text
|
3593 |
|
|
LANGUAGE sql IMMUTABLE
|
3594 |
|
|
AS $_$
|
3595 |
|
|
SELECT util.schema_regexp(schema_anchor := $1)
|
3596 |
|
|
$_$;
|
3597 |
|
|
|
3598 |
|
|
|
3599 |
|
|
--
|
3600 |
12501
|
aaronmk
|
-- Name: schema_regexp(anyelement); Type: FUNCTION; Schema: util; Owner: -
|
3601 |
|
|
--
|
3602 |
|
|
|
3603 |
|
|
CREATE FUNCTION schema_regexp(schema_anchor anyelement) RETURNS text
|
3604 |
|
|
LANGUAGE sql IMMUTABLE
|
3605 |
|
|
AS $_$
|
3606 |
|
|
SELECT util.str_equality_regexp(util.schema($1))
|
3607 |
|
|
$_$;
|
3608 |
|
|
|
3609 |
|
|
|
3610 |
|
|
--
|
3611 |
12132
|
aaronmk
|
-- Name: schema_rename(text, text); Type: FUNCTION; Schema: util; Owner: -
|
3612 |
|
|
--
|
3613 |
|
|
|
3614 |
|
|
CREATE FUNCTION schema_rename(old text, new text) RETURNS void
|
3615 |
|
|
LANGUAGE sql
|
3616 |
|
|
AS $_$
|
3617 |
|
|
SELECT util.eval($$ALTER SCHEMA $$||quote_ident($1)||$$ RENAME TO $$||quote_ident($2));
|
3618 |
|
|
$_$;
|
3619 |
|
|
|
3620 |
|
|
|
3621 |
|
|
--
|
3622 |
12133
|
aaronmk
|
-- Name: schema_replace(text, text); Type: FUNCTION; Schema: util; Owner: -
|
3623 |
|
|
--
|
3624 |
|
|
|
3625 |
|
|
CREATE FUNCTION schema_replace(replace text, with_ text) RETURNS void
|
3626 |
|
|
LANGUAGE plpgsql
|
3627 |
|
|
AS $$
|
3628 |
|
|
BEGIN
|
3629 |
|
|
-- don't schema_rm() the schema to keep!
|
3630 |
|
|
IF replace = with_ THEN RETURN; END IF;
|
3631 |
|
|
|
3632 |
|
|
PERFORM util.schema_rm(replace);
|
3633 |
|
|
PERFORM util.schema_rename(with_, replace);
|
3634 |
|
|
END;
|
3635 |
|
|
$$;
|
3636 |
|
|
|
3637 |
|
|
|
3638 |
|
|
--
|
3639 |
12132
|
aaronmk
|
-- Name: schema_rm(text); Type: FUNCTION; Schema: util; Owner: -
|
3640 |
|
|
--
|
3641 |
|
|
|
3642 |
|
|
CREATE FUNCTION schema_rm(schema text) RETURNS void
|
3643 |
|
|
LANGUAGE sql
|
3644 |
|
|
AS $_$
|
3645 |
|
|
SELECT util.eval($$DROP SCHEMA IF EXISTS $$||quote_ident($1)||$$ CASCADE$$);
|
3646 |
|
|
$_$;
|
3647 |
|
|
|
3648 |
|
|
|
3649 |
|
|
--
|
3650 |
9825
|
aaronmk
|
-- Name: search_path_append(text); Type: FUNCTION; Schema: util; Owner: -
|
3651 |
|
|
--
|
3652 |
|
|
|
3653 |
|
|
CREATE FUNCTION search_path_append(schemas text) RETURNS void
|
3654 |
12446
|
aaronmk
|
LANGUAGE sql
|
3655 |
9825
|
aaronmk
|
AS $_$
|
3656 |
|
|
SELECT util.eval(
|
3657 |
|
|
$$SET search_path TO $$||current_setting('search_path')||$$, $$||$1);
|
3658 |
|
|
$_$;
|
3659 |
|
|
|
3660 |
|
|
|
3661 |
|
|
--
|
3662 |
8183
|
aaronmk
|
-- Name: set_col_names(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
|
3663 |
8153
|
aaronmk
|
--
|
3664 |
|
|
|
3665 |
|
|
CREATE FUNCTION set_col_names(table_ regclass, names regclass) RETURNS void
|
3666 |
|
|
LANGUAGE plpgsql STRICT
|
3667 |
|
|
AS $_$
|
3668 |
|
|
DECLARE
|
3669 |
8183
|
aaronmk
|
old text[] = ARRAY(SELECT util.col_names(table_));
|
3670 |
|
|
new text[] = ARRAY(SELECT util.map_values(names));
|
3671 |
8153
|
aaronmk
|
BEGIN
|
3672 |
|
|
old = old[1:array_length(new, 1)]; -- truncate to same length
|
3673 |
10345
|
aaronmk
|
PERFORM util.eval($$ALTER TABLE $$||$1||$$ RENAME $$||quote_ident(key)
|
3674 |
|
|
||$$ TO $$||quote_ident(value))
|
3675 |
10149
|
aaronmk
|
FROM each(hstore(old, new))
|
3676 |
|
|
WHERE value != key -- not same name
|
3677 |
|
|
;
|
3678 |
8153
|
aaronmk
|
END;
|
3679 |
|
|
$_$;
|
3680 |
|
|
|
3681 |
|
|
|
3682 |
|
|
--
|
3683 |
8183
|
aaronmk
|
-- Name: FUNCTION set_col_names(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
|
3684 |
8153
|
aaronmk
|
--
|
3685 |
|
|
|
3686 |
12235
|
aaronmk
|
COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS '
|
3687 |
|
|
idempotent
|
3688 |
|
|
';
|
3689 |
8153
|
aaronmk
|
|
3690 |
|
|
|
3691 |
|
|
--
|
3692 |
10145
|
aaronmk
|
-- Name: set_col_names_with_metadata(regclass, regclass); Type: FUNCTION; Schema: util; Owner: -
|
3693 |
|
|
--
|
3694 |
|
|
|
3695 |
|
|
CREATE FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) RETURNS void
|
3696 |
|
|
LANGUAGE plpgsql STRICT
|
3697 |
|
|
AS $_$
|
3698 |
|
|
DECLARE
|
3699 |
|
|
row_ util.map;
|
3700 |
|
|
BEGIN
|
3701 |
10715
|
aaronmk
|
-- rename any metadata cols rather than re-adding them with new names
|
3702 |
|
|
BEGIN
|
3703 |
|
|
PERFORM util.set_col_names(table_, names);
|
3704 |
|
|
EXCEPTION
|
3705 |
|
|
WHEN array_subscript_error THEN -- selective suppress
|
3706 |
|
|
IF SQLERRM LIKE 'arrays must have same bounds' THEN NULL;
|
3707 |
|
|
-- metadata cols not yet added
|
3708 |
12568
|
aaronmk
|
ELSE RAISE;
|
3709 |
10715
|
aaronmk
|
END IF;
|
3710 |
|
|
END;
|
3711 |
|
|
|
3712 |
10157
|
aaronmk
|
FOR row_ IN EXECUTE $$SELECT * FROM $$||names||$$ WHERE "from" LIKE ':%'$$
|
3713 |
10145
|
aaronmk
|
LOOP
|
3714 |
10147
|
aaronmk
|
PERFORM util.mk_const_col((table_, row_."to"),
|
3715 |
|
|
substring(row_."from" from 2));
|
3716 |
10145
|
aaronmk
|
END LOOP;
|
3717 |
|
|
|
3718 |
|
|
PERFORM util.set_col_names(table_, names);
|
3719 |
|
|
END;
|
3720 |
|
|
$_$;
|
3721 |
|
|
|
3722 |
|
|
|
3723 |
|
|
--
|
3724 |
|
|
-- Name: FUNCTION set_col_names_with_metadata(table_ regclass, names regclass); Type: COMMENT; Schema: util; Owner: -
|
3725 |
|
|
--
|
3726 |
|
|
|
3727 |
12235
|
aaronmk
|
COMMENT ON FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) IS '
|
3728 |
|
|
idempotent.
|
3729 |
|
|
the metadata mappings must be *last* in the names table.
|
3730 |
|
|
';
|
3731 |
10145
|
aaronmk
|
|
3732 |
|
|
|
3733 |
|
|
--
|
3734 |
8183
|
aaronmk
|
-- Name: set_col_types(regclass, col_cast[]); Type: FUNCTION; Schema: util; Owner: -
|
3735 |
8107
|
aaronmk
|
--
|
3736 |
|
|
|
3737 |
|
|
CREATE FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) RETURNS void
|
3738 |
12733
|
aaronmk
|
LANGUAGE sql
|
3739 |
8107
|
aaronmk
|
AS $_$
|
3740 |
12734
|
aaronmk
|
SELECT util.eval(COALESCE(
|
3741 |
|
|
$$ALTER TABLE $$||$1||$$
|
3742 |
12732
|
aaronmk
|
$$||(
|
3743 |
|
|
SELECT
|
3744 |
|
|
string_agg($$ALTER COLUMN $$||col_name_sql||$$ TYPE $$||target_type
|
3745 |
|
|
||$$ USING $$||col_name_sql||$$::$$||target_type, $$
|
3746 |
|
|
, $$)
|
3747 |
|
|
FROM
|
3748 |
|
|
(
|
3749 |
|
|
SELECT
|
3750 |
|
|
quote_ident(col_name) AS col_name_sql
|
3751 |
12733
|
aaronmk
|
, util.col_type(($1, col_name)) AS curr_type
|
3752 |
12732
|
aaronmk
|
, type AS target_type
|
3753 |
12733
|
aaronmk
|
FROM unnest($2)
|
3754 |
12732
|
aaronmk
|
) s
|
3755 |
|
|
WHERE curr_type != target_type
|
3756 |
12734
|
aaronmk
|
), ''))
|
3757 |
8107
|
aaronmk
|
$_$;
|
3758 |
|
|
|
3759 |
|
|
|
3760 |
|
|
--
|
3761 |
8183
|
aaronmk
|
-- Name: FUNCTION set_col_types(table_ regclass, col_casts col_cast[]); Type: COMMENT; Schema: util; Owner: -
|
3762 |
8107
|
aaronmk
|
--
|
3763 |
|
|
|
3764 |
12235
|
aaronmk
|
COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS '
|
3765 |
|
|
idempotent
|
3766 |
|
|
';
|
3767 |
8107
|
aaronmk
|
|
3768 |
|
|
|
3769 |
|
|
--
|
3770 |
12302
|
aaronmk
|
-- Name: set_comment(regclass, text); Type: FUNCTION; Schema: util; Owner: -
|
3771 |
|
|
--
|
3772 |
|
|
|
3773 |
|
|
CREATE FUNCTION set_comment(table_ regclass, comment text) RETURNS void
|
3774 |
12446
|
aaronmk
|
LANGUAGE sql
|
3775 |
12302
|
aaronmk
|
AS $_$
|
3776 |
13477
|
aaronmk
|
SELECT util.eval(util.mk_set_comment($1, $2))
|
3777 |
12302
|
aaronmk
|
$_$;
|
3778 |
|
|
|
3779 |
|
|
|
3780 |
|
|
--
|
3781 |
12482
|
aaronmk
|
-- Name: set_search_path(text, boolean); Type: FUNCTION; Schema: util; Owner: -
|
3782 |
|
|
--
|
3783 |
|
|
|
3784 |
|
|
CREATE FUNCTION set_search_path(search_path text, for_session boolean DEFAULT false) RETURNS void
|
3785 |
|
|
LANGUAGE sql
|
3786 |
|
|
AS $_$
|
3787 |
|
|
SELECT util.eval(util.mk_set_search_path($1, $2))
|
3788 |
|
|
$_$;
|
3789 |
|
|
|
3790 |
|
|
|
3791 |
|
|
--
|
3792 |
13468
|
aaronmk
|
-- Name: show_create_view(regclass, boolean); Type: FUNCTION; Schema: util; Owner: -
|
3793 |
11651
|
aaronmk
|
--
|
3794 |
|
|
|
3795 |
13468
|
aaronmk
|
CREATE FUNCTION show_create_view(view_ regclass, replace boolean DEFAULT true) RETURNS text
|
3796 |
11651
|
aaronmk
|
LANGUAGE sql STABLE
|
3797 |
|
|
AS $_$
|
3798 |
13468
|
aaronmk
|
SELECT $$CREATE$$||(CASE WHEN $2 THEN $$ OR REPLACE$$ ELSE '' END)||$$ VIEW $$
|
3799 |
|
|
||$1||$$ AS
|
3800 |
13482
|
aaronmk
|
$$||pg_get_viewdef($1)/*no ; because pg_get_viewdef() includes one*/||$$
|
3801 |
11656
|
aaronmk
|
$$||util.show_grants_for($1)
|
3802 |
13482
|
aaronmk
|
||util.show_set_comment($1)||$$
|
3803 |
|
|
$$
|
3804 |
11651
|
aaronmk
|
$_$;
|
3805 |
|
|
|
3806 |
|
|
|
3807 |
|
|
--
|
3808 |
11655
|
aaronmk
|
-- Name: show_grants_for(regclass); Type: FUNCTION; Schema: util; Owner: -
|
3809 |
|
|
--
|
3810 |
|
|
|
3811 |
|
|
CREATE FUNCTION show_grants_for(table_ regclass) RETURNS text
|
3812 |
|
|
LANGUAGE sql STABLE
|
3813 |
|
|
AS $_$
|
3814 |
12269
|
aaronmk
|
SELECT string_agg(cmd, '')
|
3815 |
11655
|
aaronmk
|
FROM
|
3816 |
|
|
(
|
3817 |
|
|
SELECT (CASE WHEN has_table_privilege(user_, $1, 'SELECT') THEN
|
3818 |
|
|
$$GRANT SELECT ON TABLE $$||$1||$$ TO $$||quote_ident(user_)||$$;
|
3819 |
|
|
$$ ELSE '' END) AS cmd
|
3820 |
|
|
FROM util.grants_users() f (user_)
|
3821 |
|
|
) s
|
3822 |
|
|
$_$;
|
3823 |
|
|
|
3824 |
|
|
|
3825 |
|
|
--
|
3826 |
12325
|
aaronmk
|
-- Name: show_relations_like(text, text, character[]); Type: FUNCTION; Schema: util; Owner: -
|
3827 |
|
|
--
|
3828 |
|
|
|
3829 |
12670
|
aaronmk
|
CREATE FUNCTION show_relations_like(name_regexp text, schema_regexp text DEFAULT ''::text, types character[] DEFAULT ARRAY['c'::text, 'r'::text, 'v'::text]) RETURNS SETOF regclass
|
3830 |
12325
|
aaronmk
|
LANGUAGE sql STABLE
|
3831 |
|
|
AS $_$
|
3832 |
|
|
SELECT oid FROM pg_class
|
3833 |
|
|
WHERE relkind = ANY($3) AND relname ~ $1
|
3834 |
|
|
AND util.schema_matches(util.schema(relnamespace), $2)
|
3835 |
|
|
ORDER BY relname
|
3836 |
|
|
$_$;
|
3837 |
|
|
|
3838 |
|
|
|
3839 |
|
|
--
|
3840 |
13478
|
aaronmk
|
-- Name: show_set_comment(regclass); Type: FUNCTION; Schema: util; Owner: -
|
3841 |
|
|
--
|
3842 |
|
|
|
3843 |
|
|
CREATE FUNCTION show_set_comment(table_ regclass) RETURNS text
|
3844 |
|
|
LANGUAGE sql STABLE
|
3845 |
|
|
AS $_$
|
3846 |
13480
|
aaronmk
|
SELECT util.mk_set_comment($1, util.comment($1))
|
3847 |
13478
|
aaronmk
|
$_$;
|
3848 |
|
|
|
3849 |
|
|
|
3850 |
|
|
--
|
3851 |
12592
|
aaronmk
|
-- Name: show_types_like(text, text); Type: FUNCTION; Schema: util; Owner: -
|
3852 |
|
|
--
|
3853 |
|
|
|
3854 |
|
|
CREATE FUNCTION show_types_like(name_regexp text DEFAULT ''::text, schema_regexp text DEFAULT ''::text) RETURNS SETOF regtype
|
3855 |
|
|
LANGUAGE sql STABLE
|
3856 |
|
|
AS $_$
|
3857 |
|
|
SELECT oid
|
3858 |
|
|
FROM pg_type
|
3859 |
|
|
WHERE typname ~ $1 AND util.schema_matches(util.schema(typnamespace), $2)
|
3860 |
|
|
ORDER BY typname
|
3861 |
|
|
$_$;
|
3862 |
|
|
|
3863 |
|
|
|
3864 |
|
|
--
|
3865 |
12305
|
aaronmk
|
-- Name: show_views_like(text, text); Type: FUNCTION; Schema: util; Owner: -
|
3866 |
|
|
--
|
3867 |
|
|
|
3868 |
12385
|
aaronmk
|
CREATE FUNCTION show_views_like(name_regexp text, schema_regexp text DEFAULT ''::text) RETURNS SETOF regclass
|
3869 |
12305
|
aaronmk
|
LANGUAGE sql STABLE
|
3870 |
|
|
AS $_$
|
3871 |
12385
|
aaronmk
|
SELECT * FROM util.show_relations_like($1, $2, ARRAY['v'])
|
3872 |
12305
|
aaronmk
|
$_$;
|
3873 |
|
|
|
3874 |
|
|
|
3875 |
|
|
--
|
3876 |
12384
|
aaronmk
|
-- Name: str_equality_regexp(text); Type: FUNCTION; Schema: util; Owner: -
|
3877 |
|
|
--
|
3878 |
|
|
|
3879 |
|
|
CREATE FUNCTION str_equality_regexp(literal text) RETURNS text
|
3880 |
|
|
LANGUAGE sql IMMUTABLE
|
3881 |
|
|
AS $_$
|
3882 |
|
|
SELECT '^'||util.regexp_quote($1)||'$'
|
3883 |
|
|
$_$;
|
3884 |
|
|
|
3885 |
|
|
|
3886 |
|
|
--
|
3887 |
8183
|
aaronmk
|
-- Name: table2hstore(regclass); Type: FUNCTION; Schema: util; Owner: -
|
3888 |
8144
|
aaronmk
|
--
|
3889 |
|
|
|
3890 |
|
|
CREATE FUNCTION table2hstore(table_ regclass) RETURNS hstore
|
3891 |
8145
|
aaronmk
|
LANGUAGE plpgsql STABLE STRICT
|
3892 |
8144
|
aaronmk
|
AS $_$
|
3893 |
|
|
DECLARE
|
3894 |
|
|
hstore hstore;
|
3895 |
|
|
BEGIN
|
3896 |
|
|
EXECUTE $$SELECT hstore(ARRAY(SELECT unnest(ARRAY["from", "to"]) FROM $$||
|
3897 |
|
|
table_||$$))$$ INTO STRICT hstore;
|
3898 |
|
|
RETURN hstore;
|
3899 |
|
|
END;
|
3900 |
|
|
$_$;
|
3901 |
|
|
|
3902 |
|
|
|
3903 |
|
|
--
|
3904 |
10184
|
aaronmk
|
-- Name: table_flag__get(regclass, text); Type: FUNCTION; Schema: util; Owner: -
|
3905 |
|
|
--
|
3906 |
|
|
|
3907 |
|
|
CREATE FUNCTION table_flag__get(table_ regclass, flag text) RETURNS boolean
|
3908 |
12446
|
aaronmk
|
LANGUAGE sql STABLE
|
3909 |
10184
|
aaronmk
|
AS $_$
|
3910 |
|
|
SELECT COUNT(*) > 0 FROM pg_constraint
|
3911 |
|
|
WHERE conrelid = $1 AND contype = 'c' AND conname = $2
|
3912 |
|
|
$_$;
|
3913 |
|
|
|
3914 |
|
|
|
3915 |
|
|
--
|
3916 |
|
|
-- Name: FUNCTION table_flag__get(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
|
3917 |
|
|
--
|
3918 |
|
|
|
3919 |
12235
|
aaronmk
|
COMMENT ON FUNCTION table_flag__get(table_ regclass, flag text) IS '
|
3920 |
|
|
gets whether a status flag is set by the presence of a table constraint
|
3921 |
|
|
';
|
3922 |
10184
|
aaronmk
|
|
3923 |
|
|
|
3924 |
|
|
--
|
3925 |
10182
|
aaronmk
|
-- Name: table_flag__set(regclass, text); Type: FUNCTION; Schema: util; Owner: -
|
3926 |
|
|
--
|
3927 |
|
|
|
3928 |
|
|
CREATE FUNCTION table_flag__set(table_ regclass, flag text) RETURNS void
|
3929 |
12446
|
aaronmk
|
LANGUAGE sql
|
3930 |
10182
|
aaronmk
|
AS $_$
|
3931 |
|
|
SELECT util.create_if_not_exists($$ALTER TABLE $$||$1||$$ ADD CONSTRAINT $$
|
3932 |
|
|
||quote_ident($2)||$$ CHECK (true)$$)
|
3933 |
|
|
$_$;
|
3934 |
|
|
|
3935 |
|
|
|
3936 |
|
|
--
|
3937 |
|
|
-- Name: FUNCTION table_flag__set(table_ regclass, flag text); Type: COMMENT; Schema: util; Owner: -
|
3938 |
|
|
--
|
3939 |
|
|
|
3940 |
12235
|
aaronmk
|
COMMENT ON FUNCTION table_flag__set(table_ regclass, flag text) IS '
|
3941 |
|
|
stores a status flag by the presence of a table constraint.
|
3942 |
|
|
idempotent.
|
3943 |
|
|
';
|
3944 |
10182
|
aaronmk
|
|
3945 |
|
|
|
3946 |
|
|
--
|
3947 |
10185
|
aaronmk
|
-- Name: table_nulls_mapped__get(regclass); Type: FUNCTION; Schema: util; Owner: -
|
3948 |
|
|
--
|
3949 |
|
|
|
3950 |
|
|
CREATE FUNCTION table_nulls_mapped__get(table_ regclass) RETURNS boolean
|
3951 |
12446
|
aaronmk
|
LANGUAGE sql STABLE
|
3952 |
10185
|
aaronmk
|
AS $_$
|
3953 |
|
|
SELECT util.table_flag__get($1, 'nulls_mapped')
|
3954 |
|
|
$_$;
|
3955 |
|
|
|
3956 |
|
|
|
3957 |
|
|
--
|
3958 |
|
|
-- Name: FUNCTION table_nulls_mapped__get(table_ regclass); Type: COMMENT; Schema: util; Owner: -
|
3959 |
|
|
--
|
3960 |
|
|
|
3961 |
12235
|
aaronmk
|
COMMENT ON FUNCTION table_nulls_mapped__get(table_ regclass) IS '
|
3962 |
|
|
gets whether a table''s NULL-equivalent strings have been replaced with NULL
|
3963 |
|
|
';
|
3964 |
10185
|
aaronmk
|
|
3965 |
|
|
|
3966 |
|
|
--
|
3967 |
10183
|
aaronmk
|
-- Name: table_nulls_mapped__set(regclass); Type: FUNCTION; Schema: util; Owner: -
|
3968 |
|
|
--
|
3969 |
|
|
|
3970 |
|
|
CREATE FUNCTION table_nulls_mapped__set(table_ regclass) RETURNS void
|
3971 |
12446
|
aaronmk
|
LANGUAGE sql
|
3972 |
10183
|
aaronmk
|
AS $_$
|
3973 |
|
|
SELECT util.table_flag__set($1, 'nulls_mapped')
|
3974 |
|
|
$_$;
|
3975 |
|
|
|
3976 |
|
|
|
3977 |
|
|
--
|
3978 |
|
|
-- Name: FUNCTION table_nulls_mapped__set(table_ regclass); Type: COMMENT; Schema: util; Owner: -
|
3979 |
|
|
--
|
3980 |
|
|
|
3981 |
12235
|
aaronmk
|
COMMENT ON FUNCTION table_nulls_mapped__set(table_ regclass) IS '
|
3982 |
|
|
sets that a table''s NULL-equivalent strings have been replaced with NULL.
|
3983 |
|
|
idempotent.
|
3984 |
|
|
';
|
3985 |
10183
|
aaronmk
|
|
3986 |
|
|
|
3987 |
|
|
--
|
3988 |
12652
|
aaronmk
|
-- Name: to_freq(regclass); Type: FUNCTION; Schema: util; Owner: -
|
3989 |
|
|
--
|
3990 |
|
|
|
3991 |
|
|
CREATE FUNCTION to_freq(table_ regclass) RETURNS void
|
3992 |
|
|
LANGUAGE sql
|
3993 |
|
|
AS $_$
|
3994 |
|
|
-- save data before truncating main table
|
3995 |
|
|
SELECT util.copy_types_and_data($1, 'pg_temp.__copy');
|
3996 |
|
|
|
3997 |
|
|
-- repopulate main table w/ copies column
|
3998 |
|
|
SELECT util.truncate($1);
|
3999 |
|
|
SELECT util.eval($$ALTER TABLE $$||$1||$$ ADD COLUMN copies bigint NOT NULL$$);
|
4000 |
|
|
SELECT util.populate_table($1, $$
|
4001 |
|
|
SELECT (table_).*, copies
|
4002 |
|
|
FROM (
|
4003 |
|
|
SELECT table_, COUNT(*) AS copies
|
4004 |
|
|
FROM pg_temp.__copy table_
|
4005 |
|
|
GROUP BY table_
|
4006 |
|
|
) s
|
4007 |
|
|
$$);
|
4008 |
|
|
|
4009 |
|
|
-- delete temp table so it doesn't stay around until end of connection
|
4010 |
|
|
SELECT util.drop_table('pg_temp.__copy');
|
4011 |
|
|
$_$;
|
4012 |
|
|
|
4013 |
|
|
|
4014 |
|
|
--
|
4015 |
8183
|
aaronmk
|
-- Name: to_global_col_names(regclass); Type: FUNCTION; Schema: util; Owner: -
|
4016 |
8088
|
aaronmk
|
--
|
4017 |
|
|
|
4018 |
|
|
CREATE FUNCTION to_global_col_names(table_ regclass) RETURNS void
|
4019 |
|
|
LANGUAGE plpgsql STRICT
|
4020 |
|
|
AS $_$
|
4021 |
|
|
DECLARE
|
4022 |
|
|
row record;
|
4023 |
|
|
BEGIN
|
4024 |
8183
|
aaronmk
|
FOR row IN SELECT * FROM util.col_global_names(table_::text::regtype)
|
4025 |
8088
|
aaronmk
|
LOOP
|
4026 |
|
|
IF row.global_name != row.name THEN
|
4027 |
|
|
EXECUTE $$ALTER TABLE $$||table_||$$ RENAME $$
|
4028 |
|
|
||quote_ident(row.name)||$$ TO $$||quote_ident(row.global_name);
|
4029 |
|
|
END IF;
|
4030 |
|
|
END LOOP;
|
4031 |
|
|
END;
|
4032 |
|
|
$_$;
|
4033 |
|
|
|
4034 |
|
|
|
4035 |
|
|
--
|
4036 |
8183
|
aaronmk
|
-- Name: FUNCTION to_global_col_names(table_ regclass); Type: COMMENT; Schema: util; Owner: -
|
4037 |
8088
|
aaronmk
|
--
|
4038 |
|
|
|
4039 |
12235
|
aaronmk
|
COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS '
|
4040 |
|
|
idempotent
|
4041 |
|
|
';
|
4042 |
8088
|
aaronmk
|
|
4043 |
|
|
|
4044 |
|
|
--
|
4045 |
12874
|
aaronmk
|
-- Name: trim(regclass, regclass, boolean); Type: FUNCTION; Schema: util; Owner: -
|
4046 |
10365
|
aaronmk
|
--
|
4047 |
|
|
|
4048 |
12874
|
aaronmk
|
CREATE FUNCTION "trim"(table_ regclass, names regclass, force boolean DEFAULT true) RETURNS void
|
4049 |
12446
|
aaronmk
|
LANGUAGE sql
|
4050 |
10365
|
aaronmk
|
AS $_$
|
4051 |
12874
|
aaronmk
|
SELECT util.drop_column(($1, col), $3) FROM util.added_cols($1, $2) f (col);
|
4052 |
10365
|
aaronmk
|
SELECT NULL::void; -- don't fold away functions called in previous query
|
4053 |
|
|
$_$;
|
4054 |
|
|
|
4055 |
|
|
|
4056 |
|
|
--
|
4057 |
12874
|
aaronmk
|
-- Name: FUNCTION "trim"(table_ regclass, names regclass, force boolean); Type: COMMENT; Schema: util; Owner: -
|
4058 |
10365
|
aaronmk
|
--
|
4059 |
|
|
|
4060 |
12874
|
aaronmk
|
COMMENT ON FUNCTION "trim"(table_ regclass, names regclass, force boolean) IS '
|
4061 |
|
|
trims table_ to include only columns in the original data
|
4062 |
|
|
|
4063 |
|
|
by default, cascadingly drops dependent columns so that they don''t prevent
|
4064 |
|
|
trim() from succeeding. note that this requires the dependent columns to then be
|
4065 |
|
|
manually re-created.
|
4066 |
|
|
|
4067 |
|
|
idempotent
|
4068 |
12235
|
aaronmk
|
';
|
4069 |
10365
|
aaronmk
|
|
4070 |
|
|
|
4071 |
|
|
--
|
4072 |
8183
|
aaronmk
|
-- Name: truncate(regclass); Type: FUNCTION; Schema: util; Owner: -
|
4073 |
8142
|
aaronmk
|
--
|
4074 |
|
|
|
4075 |
|
|
CREATE FUNCTION truncate(table_ regclass) RETURNS void
|
4076 |
|
|
LANGUAGE plpgsql STRICT
|
4077 |
|
|
AS $_$
|
4078 |
|
|
BEGIN
|
4079 |
|
|
EXECUTE $$TRUNCATE $$||table_||$$ CASCADE$$;
|
4080 |
|
|
END;
|
4081 |
|
|
$_$;
|
4082 |
|
|
|
4083 |
|
|
|
4084 |
|
|
--
|
4085 |
8183
|
aaronmk
|
-- Name: FUNCTION truncate(table_ regclass); Type: COMMENT; Schema: util; Owner: -
|
4086 |
8142
|
aaronmk
|
--
|
4087 |
|
|
|
4088 |
12235
|
aaronmk
|
COMMENT ON FUNCTION truncate(table_ regclass) IS '
|
4089 |
|
|
idempotent
|
4090 |
|
|
';
|
4091 |
8142
|
aaronmk
|
|
4092 |
|
|
|
4093 |
|
|
--
|
4094 |
12357
|
aaronmk
|
-- Name: truncated_prefixed_name_regexp(text, integer); Type: FUNCTION; Schema: util; Owner: -
|
4095 |
|
|
--
|
4096 |
|
|
|
4097 |
|
|
CREATE FUNCTION truncated_prefixed_name_regexp(name text, max_prefix_len integer) RETURNS text
|
4098 |
|
|
LANGUAGE sql IMMUTABLE
|
4099 |
|
|
AS $_$
|
4100 |
12361
|
aaronmk
|
SELECT '^(.*)'||util._if(util.name_was_truncated($1, $2),
|
4101 |
12362
|
aaronmk
|
util.regexp_quote(util.rtrim_n($1, $2))||'.*', util.regexp_quote($1)) ||'$'
|
4102 |
12357
|
aaronmk
|
$_$;
|
4103 |
|
|
|
4104 |
|
|
|
4105 |
|
|
--
|
4106 |
13135
|
aaronmk
|
-- Name: try_cast(text, anyelement); Type: FUNCTION; Schema: util; Owner: -
|
4107 |
|
|
--
|
4108 |
|
|
|
4109 |
|
|
CREATE FUNCTION try_cast(value text, ret_type_null anyelement) RETURNS anyelement
|
4110 |
|
|
LANGUAGE plpgsql IMMUTABLE
|
4111 |
|
|
AS $$
|
4112 |
|
|
BEGIN
|
4113 |
|
|
/* need explicit cast because some types not implicitly-castable, and also
|
4114 |
|
|
to make the cast happen inside the try block. (*implicit* casts to the
|
4115 |
|
|
return type happen at the end of the function, outside any block.) */
|
4116 |
|
|
RETURN util.cast(value, ret_type_null);
|
4117 |
|
|
EXCEPTION
|
4118 |
13493
|
aaronmk
|
WHEN data_exception
|
4119 |
|
|
OR syntax_error_or_access_rule_violation -- eg. ::regclass
|
4120 |
|
|
THEN
|
4121 |
13135
|
aaronmk
|
PERFORM util.raise('WARNING', SQLERRM);
|
4122 |
|
|
RETURN NULL;
|
4123 |
|
|
END;
|
4124 |
|
|
$$;
|
4125 |
|
|
|
4126 |
|
|
|
4127 |
|
|
--
|
4128 |
|
|
-- Name: FUNCTION try_cast(value text, ret_type_null anyelement); Type: COMMENT; Schema: util; Owner: -
|
4129 |
|
|
--
|
4130 |
|
|
|
4131 |
|
|
COMMENT ON FUNCTION try_cast(value text, ret_type_null anyelement) IS '
|
4132 |
|
|
ret_type_null: NULL::ret_type
|
4133 |
|
|
';
|
4134 |
|
|
|
4135 |
|
|
|
4136 |
|
|
--
|
4137 |
8199
|
aaronmk
|
-- Name: try_create(text); Type: FUNCTION; Schema: util; Owner: -
|
4138 |
|
|
--
|
4139 |
|
|
|
4140 |
|
|
CREATE FUNCTION try_create(sql text) RETURNS void
|
4141 |
|
|
LANGUAGE plpgsql STRICT
|
4142 |
|
|
AS $$
|
4143 |
|
|
BEGIN
|
4144 |
12658
|
aaronmk
|
PERFORM util.eval(sql);
|
4145 |
8199
|
aaronmk
|
EXCEPTION
|
4146 |
12676
|
aaronmk
|
WHEN not_null_violation
|
4147 |
|
|
/* trying to add NOT NULL column to parent table, which cascades to
|
4148 |
|
|
child table whose values for the new column will be NULL */
|
4149 |
|
|
OR wrong_object_type -- trying to alter a view's columns
|
4150 |
|
|
OR undefined_column
|
4151 |
|
|
OR duplicate_column
|
4152 |
|
|
THEN NULL;
|
4153 |
12684
|
aaronmk
|
WHEN datatype_mismatch THEN
|
4154 |
|
|
IF SQLERRM LIKE 'child table is missing column %' THEN NULL;
|
4155 |
|
|
ELSE RAISE; -- rethrow
|
4156 |
|
|
END IF;
|
4157 |
8199
|
aaronmk
|
END;
|
4158 |
|
|
$$;
|
4159 |
|
|
|
4160 |
|
|
|
4161 |
|
|
--
|
4162 |
|
|
-- Name: FUNCTION try_create(sql text); Type: COMMENT; Schema: util; Owner: -
|
4163 |
|
|
--
|
4164 |
|
|
|
4165 |
12235
|
aaronmk
|
COMMENT ON FUNCTION try_create(sql text) IS '
|
4166 |
|
|
idempotent
|
4167 |
|
|
';
|
4168 |
8199
|
aaronmk
|
|
4169 |
|
|
|
4170 |
|
|
--
|
4171 |
8209
|
aaronmk
|
-- Name: try_mk_derived_col(col_ref, text); Type: FUNCTION; Schema: util; Owner: -
|
4172 |
|
|
--
|
4173 |
|
|
|
4174 |
|
|
CREATE FUNCTION try_mk_derived_col(col col_ref, expr text) RETURNS void
|
4175 |
12446
|
aaronmk
|
LANGUAGE sql
|
4176 |
8209
|
aaronmk
|
AS $_$
|
4177 |
|
|
SELECT util.try_create($$SELECT util.mk_derived_col($$||quote_literal($1)||$$, $$||quote_literal($2)||$$)$$)
|
4178 |
|
|
$_$;
|
4179 |
|
|
|
4180 |
|
|
|
4181 |
|
|
--
|
4182 |
|
|
-- Name: FUNCTION try_mk_derived_col(col col_ref, expr text); Type: COMMENT; Schema: util; Owner: -
|
4183 |
|
|
--
|
4184 |
|
|
|
4185 |
12235
|
aaronmk
|
COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS '
|
4186 |
|
|
idempotent
|
4187 |
|
|
';
|
4188 |
8209
|
aaronmk
|
|
4189 |
|
|
|
4190 |
|
|
--
|
4191 |
8189
|
aaronmk
|
-- Name: type_qual(anyelement); Type: FUNCTION; Schema: util; Owner: -
|
4192 |
|
|
--
|
4193 |
|
|
|
4194 |
|
|
CREATE FUNCTION type_qual(value anyelement) RETURNS text
|
4195 |
|
|
LANGUAGE sql IMMUTABLE
|
4196 |
|
|
AS $_$
|
4197 |
|
|
SELECT CASE WHEN $1 IS NULL THEN '' ELSE $$ NOT NULL$$ END
|
4198 |
|
|
$_$;
|
4199 |
|
|
|
4200 |
|
|
|
4201 |
|
|
--
|
4202 |
10161
|
aaronmk
|
-- Name: FUNCTION type_qual(value anyelement); Type: COMMENT; Schema: util; Owner: -
|
4203 |
|
|
--
|
4204 |
|
|
|
4205 |
12235
|
aaronmk
|
COMMENT ON FUNCTION type_qual(value anyelement) IS '
|
4206 |
|
|
a type''s NOT NULL qualifier
|
4207 |
|
|
';
|
4208 |
10161
|
aaronmk
|
|
4209 |
|
|
|
4210 |
|
|
--
|
4211 |
12562
|
aaronmk
|
-- Name: typed_cols(regtype); Type: FUNCTION; Schema: util; Owner: -
|
4212 |
|
|
--
|
4213 |
|
|
|
4214 |
|
|
CREATE FUNCTION typed_cols(type regtype) RETURNS SETOF col_cast
|
4215 |
12590
|
aaronmk
|
LANGUAGE sql STABLE
|
4216 |
12562
|
aaronmk
|
AS $_$
|
4217 |
|
|
SELECT (attname::text, atttypid)::util.col_cast
|
4218 |
|
|
FROM pg_attribute
|
4219 |
|
|
WHERE attrelid = $1::text::regclass AND attnum >= 1 AND NOT attisdropped
|
4220 |
|
|
ORDER BY attnum
|
4221 |
|
|
$_$;
|
4222 |
|
|
|
4223 |
|
|
|
4224 |
|
|
--
|
4225 |
12438
|
aaronmk
|
-- Name: typeof(anyelement); Type: FUNCTION; Schema: util; Owner: -
|
4226 |
|
|
--
|
4227 |
|
|
|
4228 |
|
|
CREATE FUNCTION typeof(value anyelement) RETURNS text
|
4229 |
|
|
LANGUAGE sql IMMUTABLE
|
4230 |
|
|
AS $_$
|
4231 |
|
|
SELECT util.qual_name(pg_typeof($1))
|
4232 |
|
|
$_$;
|
4233 |
|
|
|
4234 |
|
|
|
4235 |
|
|
--
|
4236 |
8185
|
aaronmk
|
-- Name: typeof(text, regtype); Type: FUNCTION; Schema: util; Owner: -
|
4237 |
|
|
--
|
4238 |
|
|
|
4239 |
10160
|
aaronmk
|
CREATE FUNCTION typeof(expr text, table_ regtype DEFAULT NULL::regtype) RETURNS regtype
|
4240 |
|
|
LANGUAGE plpgsql STABLE
|
4241 |
8185
|
aaronmk
|
AS $_$
|
4242 |
|
|
DECLARE
|
4243 |
|
|
type regtype;
|
4244 |
|
|
BEGIN
|
4245 |
10160
|
aaronmk
|
EXECUTE $$SELECT pg_typeof($$||expr||$$)$$||
|
4246 |
|
|
COALESCE($$ FROM (SELECT (NULL::$$||table_||$$).*) _s$$, '') INTO STRICT type;
|
4247 |
8185
|
aaronmk
|
RETURN type;
|
4248 |
|
|
END;
|
4249 |
|
|
$_$;
|
4250 |
|
|
|
4251 |
|
|
|
4252 |
|
|
--
|
4253 |
12490
|
aaronmk
|
-- Name: use_schema(anyelement); Type: FUNCTION; Schema: util; Owner: -
|
4254 |
12483
|
aaronmk
|
--
|
4255 |
|
|
|
4256 |
12490
|
aaronmk
|
CREATE FUNCTION use_schema(schema_anchor anyelement) RETURNS void
|
4257 |
12483
|
aaronmk
|
LANGUAGE sql
|
4258 |
|
|
AS $_$
|
4259 |
12488
|
aaronmk
|
SELECT util.set_search_path(util.mk_search_path(util.schema($1)))
|
4260 |
12483
|
aaronmk
|
$_$;
|
4261 |
|
|
|
4262 |
|
|
|
4263 |
|
|
--
|
4264 |
12490
|
aaronmk
|
-- Name: FUNCTION use_schema(schema_anchor anyelement); Type: COMMENT; Schema: util; Owner: -
|
4265 |
12488
|
aaronmk
|
--
|
4266 |
|
|
|
4267 |
12490
|
aaronmk
|
COMMENT ON FUNCTION use_schema(schema_anchor anyelement) IS '
|
4268 |
12488
|
aaronmk
|
auto-appends util to the search_path to enable use of util operators
|
4269 |
|
|
';
|
4270 |
|
|
|
4271 |
|
|
|
4272 |
|
|
--
|
4273 |
9959
|
aaronmk
|
-- Name: all_same(anyelement); Type: AGGREGATE; Schema: util; Owner: -
|
4274 |
|
|
--
|
4275 |
|
|
|
4276 |
|
|
CREATE AGGREGATE all_same(anyelement) (
|
4277 |
|
|
SFUNC = all_same_transform,
|
4278 |
|
|
STYPE = anyarray,
|
4279 |
|
|
FINALFUNC = all_same_final
|
4280 |
|
|
);
|
4281 |
|
|
|
4282 |
|
|
|
4283 |
|
|
--
|
4284 |
|
|
-- Name: AGGREGATE all_same(anyelement); Type: COMMENT; Schema: util; Owner: -
|
4285 |
|
|
--
|
4286 |
|
|
|
4287 |
12235
|
aaronmk
|
COMMENT ON AGGREGATE all_same(anyelement) IS '
|
4288 |
|
|
includes NULLs in comparison
|
4289 |
|
|
';
|
4290 |
9959
|
aaronmk
|
|
4291 |
|
|
|
4292 |
|
|
--
|
4293 |
8183
|
aaronmk
|
-- Name: join_strs(text, text); Type: AGGREGATE; Schema: util; Owner: -
|
4294 |
2595
|
aaronmk
|
--
|
4295 |
|
|
|
4296 |
|
|
CREATE AGGREGATE join_strs(text, text) (
|
4297 |
4052
|
aaronmk
|
SFUNC = join_strs_transform,
|
4298 |
4010
|
aaronmk
|
STYPE = text
|
4299 |
2595
|
aaronmk
|
);
|
4300 |
|
|
|
4301 |
|
|
|
4302 |
8147
|
aaronmk
|
--
|
4303 |
12423
|
aaronmk
|
-- Name: %==; Type: OPERATOR; Schema: util; Owner: -
|
4304 |
|
|
--
|
4305 |
|
|
|
4306 |
|
|
CREATE OPERATOR %== (
|
4307 |
|
|
PROCEDURE = "%==",
|
4308 |
|
|
LEFTARG = anyelement,
|
4309 |
|
|
RIGHTARG = anyelement
|
4310 |
|
|
);
|
4311 |
|
|
|
4312 |
|
|
|
4313 |
|
|
--
|
4314 |
|
|
-- Name: OPERATOR %== (anyelement, anyelement); Type: COMMENT; Schema: util; Owner: -
|
4315 |
|
|
--
|
4316 |
|
|
|
4317 |
|
|
COMMENT ON OPERATOR %== (anyelement, anyelement) IS '
|
4318 |
|
|
returns whether the map-keys of the compared values are the same
|
4319 |
|
|
(mnemonic: % is the Perl symbol for a hash map)
|
4320 |
|
|
|
4321 |
|
|
should be overridden for types that store both keys and values
|
4322 |
|
|
|
4323 |
|
|
used in a FULL JOIN to select which columns to join on
|
4324 |
|
|
';
|
4325 |
|
|
|
4326 |
|
|
|
4327 |
|
|
--
|
4328 |
8183
|
aaronmk
|
-- Name: ->; Type: OPERATOR; Schema: util; Owner: -
|
4329 |
8147
|
aaronmk
|
--
|
4330 |
|
|
|
4331 |
|
|
CREATE OPERATOR -> (
|
4332 |
|
|
PROCEDURE = map_get,
|
4333 |
|
|
LEFTARG = regclass,
|
4334 |
|
|
RIGHTARG = text
|
4335 |
|
|
);
|
4336 |
|
|
|
4337 |
|
|
|
4338 |
10308
|
aaronmk
|
--
|
4339 |
|
|
-- Name: =>; Type: OPERATOR; Schema: util; Owner: -
|
4340 |
|
|
--
|
4341 |
|
|
|
4342 |
|
|
CREATE OPERATOR => (
|
4343 |
|
|
PROCEDURE = hstore,
|
4344 |
10357
|
aaronmk
|
LEFTARG = text[],
|
4345 |
10608
|
aaronmk
|
RIGHTARG = text
|
4346 |
10308
|
aaronmk
|
);
|
4347 |
|
|
|
4348 |
|
|
|
4349 |
|
|
--
|
4350 |
10608
|
aaronmk
|
-- Name: OPERATOR => (text[], text); Type: COMMENT; Schema: util; Owner: -
|
4351 |
10308
|
aaronmk
|
--
|
4352 |
|
|
|
4353 |
12235
|
aaronmk
|
COMMENT ON OPERATOR => (text[], text) IS '
|
4354 |
|
|
usage: array[''key1'', ...]::text[] => ''value''
|
4355 |
|
|
';
|
4356 |
10308
|
aaronmk
|
|
4357 |
|
|
|
4358 |
10391
|
aaronmk
|
--
|
4359 |
10613
|
aaronmk
|
-- Name: ?*>=; Type: OPERATOR; Schema: util; Owner: -
|
4360 |
|
|
--
|
4361 |
|
|
|
4362 |
|
|
CREATE OPERATOR ?*>= (
|
4363 |
|
|
PROCEDURE = is_populated_more_often_than,
|
4364 |
|
|
LEFTARG = anyelement,
|
4365 |
|
|
RIGHTARG = anyelement
|
4366 |
|
|
);
|
4367 |
|
|
|
4368 |
|
|
|
4369 |
|
|
--
|
4370 |
10391
|
aaronmk
|
-- Name: ?>=; Type: OPERATOR; Schema: util; Owner: -
|
4371 |
|
|
--
|
4372 |
|
|
|
4373 |
|
|
CREATE OPERATOR ?>= (
|
4374 |
|
|
PROCEDURE = is_more_complete_than,
|
4375 |
|
|
LEFTARG = anyelement,
|
4376 |
|
|
RIGHTARG = anyelement
|
4377 |
|
|
);
|
4378 |
|
|
|
4379 |
|
|
|
4380 |
11005
|
aaronmk
|
--
|
4381 |
|
|
-- Name: ||%; Type: OPERATOR; Schema: util; Owner: -
|
4382 |
|
|
--
|
4383 |
|
|
|
4384 |
|
|
CREATE OPERATOR ||% (
|
4385 |
|
|
PROCEDURE = concat_esc,
|
4386 |
|
|
LEFTARG = text,
|
4387 |
|
|
RIGHTARG = text
|
4388 |
|
|
);
|
4389 |
|
|
|
4390 |
|
|
|
4391 |
|
|
--
|
4392 |
|
|
-- Name: OPERATOR ||% (text, text); Type: COMMENT; Schema: util; Owner: -
|
4393 |
|
|
--
|
4394 |
|
|
|
4395 |
12235
|
aaronmk
|
COMMENT ON OPERATOR ||% (text, text) IS '
|
4396 |
|
|
% indicates an identifier, as in Perl hashes and one of the x86 assembler syntaxes for registers
|
4397 |
|
|
';
|
4398 |
11005
|
aaronmk
|
|
4399 |
|
|
|
4400 |
2107
|
aaronmk
|
--
|
4401 |
8183
|
aaronmk
|
-- Name: map; Type: TABLE; Schema: util; Owner: -; Tablespace:
|
4402 |
8140
|
aaronmk
|
--
|
4403 |
|
|
|
4404 |
|
|
CREATE TABLE map (
|
4405 |
|
|
"from" text NOT NULL,
|
4406 |
8158
|
aaronmk
|
"to" text,
|
4407 |
|
|
filter text,
|
4408 |
|
|
notes text
|
4409 |
8140
|
aaronmk
|
);
|
4410 |
|
|
|
4411 |
|
|
|
4412 |
|
|
--
|
4413 |
11834
|
aaronmk
|
-- Data for Name: explain; Type: TABLE DATA; Schema: util; Owner: -
|
4414 |
|
|
--
|
4415 |
|
|
|
4416 |
|
|
|
4417 |
|
|
|
4418 |
|
|
--
|
4419 |
8183
|
aaronmk
|
-- Data for Name: map; Type: TABLE DATA; Schema: util; Owner: -
|
4420 |
8140
|
aaronmk
|
--
|
4421 |
|
|
|
4422 |
|
|
|
4423 |
|
|
|
4424 |
|
|
--
|
4425 |
10342
|
aaronmk
|
-- Name: map__unique__from; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace:
|
4426 |
8140
|
aaronmk
|
--
|
4427 |
|
|
|
4428 |
|
|
ALTER TABLE ONLY map
|
4429 |
10342
|
aaronmk
|
ADD CONSTRAINT map__unique__from UNIQUE ("from");
|
4430 |
8140
|
aaronmk
|
|
4431 |
|
|
|
4432 |
|
|
--
|
4433 |
10343
|
aaronmk
|
-- Name: map__unique__to; Type: CONSTRAINT; Schema: util; Owner: -; Tablespace:
|
4434 |
|
|
--
|
4435 |
|
|
|
4436 |
|
|
ALTER TABLE ONLY map
|
4437 |
|
|
ADD CONSTRAINT map__unique__to UNIQUE ("to");
|
4438 |
|
|
|
4439 |
|
|
|
4440 |
|
|
--
|
4441 |
10110
|
aaronmk
|
-- Name: map_filter_insert; Type: TRIGGER; Schema: util; Owner: -
|
4442 |
|
|
--
|
4443 |
|
|
|
4444 |
|
|
CREATE TRIGGER map_filter_insert BEFORE INSERT ON map FOR EACH ROW EXECUTE PROCEDURE map_filter_insert();
|
4445 |
|
|
|
4446 |
|
|
|
4447 |
|
|
--
|
4448 |
2136
|
aaronmk
|
-- PostgreSQL database dump complete
|
4449 |
|
|
--
|