· 6 years ago · Jun 08, 2019, 01:34 AM
1██████╗ ███████╗███╗ ███╗ ██████╗ ███╗ ██╗███████╗ █████╗ ██████╗
2██╔══██╗██╔════╝████╗ ████║██╔═══██╗████╗ ██║██╔════╝██╔══██╗██╔══██╗
3██║ ██║█████╗ ██╔████╔██║██║ ██║██╔██╗ ██║███████╗███████║██║ ██║
4██║ ██║██╔══╝ ██║╚██╔╝██║██║ ██║██║╚██╗██║╚════██║██╔══██║██║ ██║
5██████╔╝███████╗██║ ╚═╝ ██║╚██████╔╝██║ ╚████║███████║██║ ██║██████╔╝
6╚═════╝ ╚══════╝╚═╝ ╚═╝ ╚═════╝ ╚═╝ ╚═══╝╚══════╝╚═╝ ╚═╝╚═════╝
7
8I am DemonSad <3
9
10Target : http://www.linea.gov.br/ ( Laboratório Interinstitucional de Astronomia )
11
12Greetings: TioSan, Angel, Cloudmore, NedLock
13
14----------------------------------------------------------------------------
15
16/*
17 * SQL Information Schema
18 * as defined in ISO/IEC 9075-11:2008
19 *
20 * Copyright (c) 2003-2012, PostgreSQL Global Development Group
21 *
22 * src/backend/catalog/information_schema.sql
23 */
24
25/*
26 * Note: Generally, the definitions in this file should be ordered
27 * according to the clause numbers in the SQL standard, which is also the
28 * alphabetical order. In some cases it is convenient or necessary to
29 * define one information schema view by using another one; in that case,
30 * put the referencing view at the very end and leave a note where it
31 * should have been put.
32 */
33
34
35/*
36 * 5.1
37 * INFORMATION_SCHEMA schema
38 */
39
40CREATE SCHEMA information_schema;
41GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
42SET search_path TO information_schema;
43
44
45/*
46 * A few supporting functions first ...
47 */
48
49/* Expand any 1-D array into a set with integers 1..N */
50CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int)
51 RETURNS SETOF RECORD
52 LANGUAGE sql STRICT IMMUTABLE
53 AS 'select $1[s], s - pg_catalog.array_lower($1,1) + 1
54 from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
55 pg_catalog.array_upper($1,1),
56 1) as g(s)';
57
58CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
59 LANGUAGE sql IMMUTABLE -- intentionally not STRICT, to allow inlining
60 AS 'select $1 operator(pg_catalog.<@) $2 and $2 operator(pg_catalog.<@) $1';
61
62/* Given an index's OID and an underlying-table column number, return the
63 * column's position in the index (NULL if not there) */
64CREATE FUNCTION _pg_index_position(oid, smallint) RETURNS int
65 LANGUAGE sql STRICT STABLE
66 AS $$
67SELECT (ss.a).n FROM
68 (SELECT information_schema._pg_expandarray(indkey) AS a
69 FROM pg_catalog.pg_index WHERE indexrelid = $1) ss
70 WHERE (ss.a).x = $2;
71$$;
72
73CREATE FUNCTION _pg_truetypid(pg_attribute, pg_type) RETURNS oid
74 LANGUAGE sql
75 IMMUTABLE
76 RETURNS NULL ON NULL INPUT
77 AS
78$$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typbasetype ELSE $1.atttypid END$$;
79
80CREATE FUNCTION _pg_truetypmod(pg_attribute, pg_type) RETURNS int4
81 LANGUAGE sql
82 IMMUTABLE
83 RETURNS NULL ON NULL INPUT
84 AS
85$$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typtypmod ELSE $1.atttypmod END$$;
86
87-- these functions encapsulate knowledge about the encoding of typmod:
88
89CREATE FUNCTION _pg_char_max_length(typid oid, typmod int4) RETURNS integer
90 LANGUAGE sql
91 IMMUTABLE
92 RETURNS NULL ON NULL INPUT
93 AS
94$$SELECT
95 CASE WHEN $2 = -1 /* default typmod */
96 THEN null
97 WHEN $1 IN (1042, 1043) /* char, varchar */
98 THEN $2 - 4
99 WHEN $1 IN (1560, 1562) /* bit, varbit */
100 THEN $2
101 ELSE null
102 END$$;
103
104CREATE FUNCTION _pg_char_octet_length(typid oid, typmod int4) RETURNS integer
105 LANGUAGE sql
106 IMMUTABLE
107 RETURNS NULL ON NULL INPUT
108 AS
109$$SELECT
110 CASE WHEN $1 IN (25, 1042, 1043) /* text, char, varchar */
111 THEN CASE WHEN $2 = -1 /* default typmod */
112 THEN CAST(2^30 AS integer)
113 ELSE information_schema._pg_char_max_length($1, $2) *
114 pg_catalog.pg_encoding_max_length((SELECT encoding FROM pg_catalog.pg_database WHERE datname = pg_catalog.current_database()))
115 END
116 ELSE null
117 END$$;
118
119CREATE FUNCTION _pg_numeric_precision(typid oid, typmod int4) RETURNS integer
120 LANGUAGE sql
121 IMMUTABLE
122 RETURNS NULL ON NULL INPUT
123 AS
124$$SELECT
125 CASE $1
126 WHEN 21 /*int2*/ THEN 16
127 WHEN 23 /*int4*/ THEN 32
128 WHEN 20 /*int8*/ THEN 64
129 WHEN 1700 /*numeric*/ THEN
130 CASE WHEN $2 = -1
131 THEN null
132 ELSE (($2 - 4) >> 16) & 65535
133 END
134 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
135 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
136 ELSE null
137 END$$;
138
139CREATE FUNCTION _pg_numeric_precision_radix(typid oid, typmod int4) RETURNS integer
140 LANGUAGE sql
141 IMMUTABLE
142 RETURNS NULL ON NULL INPUT
143 AS
144$$SELECT
145 CASE WHEN $1 IN (21, 23, 20, 700, 701) THEN 2
146 WHEN $1 IN (1700) THEN 10
147 ELSE null
148 END$$;
149
150CREATE FUNCTION _pg_numeric_scale(typid oid, typmod int4) RETURNS integer
151 LANGUAGE sql
152 IMMUTABLE
153 RETURNS NULL ON NULL INPUT
154 AS
155$$SELECT
156 CASE WHEN $1 IN (21, 23, 20) THEN 0
157 WHEN $1 IN (1700) THEN
158 CASE WHEN $2 = -1
159 THEN null
160 ELSE ($2 - 4) & 65535
161 END
162 ELSE null
163 END$$;
164
165CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer
166 LANGUAGE sql
167 IMMUTABLE
168 RETURNS NULL ON NULL INPUT
169 AS
170$$SELECT
171 CASE WHEN $1 IN (1082) /* date */
172 THEN 0
173 WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */
174 THEN CASE WHEN $2 < 0 THEN 6 ELSE $2 END
175 WHEN $1 IN (1186) /* interval */
176 THEN CASE WHEN $2 < 0 OR $2 & 65535 = 65535 THEN 6 ELSE $2 & 65535 END
177 ELSE null
178 END$$;
179
180CREATE FUNCTION _pg_interval_type(typid oid, mod int4) RETURNS text
181 LANGUAGE sql
182 IMMUTABLE
183 RETURNS NULL ON NULL INPUT
184 AS
185$$SELECT
186 CASE WHEN $1 IN (1186) /* interval */
187 THEN upper(substring(format_type($1, $2) from 'interval[()0-9]* #"%#"' for '#'))
188 ELSE null
189 END$$;
190
191
192-- 5.2 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
193
194
195/*
196 * 5.3
197 * CARDINAL_NUMBER domain
198 */
199
200CREATE DOMAIN cardinal_number AS integer
201 CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);
202
203
204/*
205 * 5.4
206 * CHARACTER_DATA domain
207 */
208
209CREATE DOMAIN character_data AS character varying;
210
211
212/*
213 * 5.5
214 * SQL_IDENTIFIER domain
215 */
216
217CREATE DOMAIN sql_identifier AS character varying;
218
219
220/*
221 * 5.2
222 * INFORMATION_SCHEMA_CATALOG_NAME view
223 */
224
225CREATE VIEW information_schema_catalog_name AS
226 SELECT CAST(current_database() AS sql_identifier) AS catalog_name;
227
228GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
229
230
231/*
232 * 5.6
233 * TIME_STAMP domain
234 */
235
236CREATE DOMAIN time_stamp AS timestamp(2) with time zone
237 DEFAULT current_timestamp(2);
238
239/*
240 * 5.7
241 * YES_OR_NO domain
242 */
243
244CREATE DOMAIN yes_or_no AS character varying(3)
245 CONSTRAINT yes_or_no_check CHECK (value IN ('YES', 'NO'));
246
247
248-- 5.8 ADMINISTRABLE_ROLE_AUTHORIZATIONS view appears later.
249
250
251/*
252 * 5.9
253 * APPLICABLE_ROLES view
254 */
255
256CREATE VIEW applicable_roles AS
257 SELECT CAST(a.rolname AS sql_identifier) AS grantee,
258 CAST(b.rolname AS sql_identifier) AS role_name,
259 CAST(CASE WHEN m.admin_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
260 FROM pg_auth_members m
261 JOIN pg_authid a ON (m.member = a.oid)
262 JOIN pg_authid b ON (m.roleid = b.oid)
263 WHERE pg_has_role(a.oid, 'USAGE');
264
265GRANT SELECT ON applicable_roles TO PUBLIC;
266
267
268/*
269 * 5.8
270 * ADMINISTRABLE_ROLE_AUTHORIZATIONS view
271 */
272
273CREATE VIEW administrable_role_authorizations AS
274 SELECT *
275 FROM applicable_roles
276 WHERE is_grantable = 'YES';
277
278GRANT SELECT ON administrable_role_authorizations TO PUBLIC;
279
280
281/*
282 * 5.10
283 * ASSERTIONS view
284 */
285
286-- feature not supported
287
288
289/*
290 * 5.11
291 * ATTRIBUTES view
292 */
293
294CREATE VIEW attributes AS
295 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
296 CAST(nc.nspname AS sql_identifier) AS udt_schema,
297 CAST(c.relname AS sql_identifier) AS udt_name,
298 CAST(a.attname AS sql_identifier) AS attribute_name,
299 CAST(a.attnum AS cardinal_number) AS ordinal_position,
300 CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS attribute_default,
301 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
302 AS yes_or_no)
303 AS is_nullable, -- This column was apparently removed between SQL:2003 and SQL:2008.
304
305 CAST(
306 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
307 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
308 ELSE 'USER-DEFINED' END
309 AS character_data)
310 AS data_type,
311
312 CAST(
313 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
314 AS cardinal_number)
315 AS character_maximum_length,
316
317 CAST(
318 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
319 AS cardinal_number)
320 AS character_octet_length,
321
322 CAST(null AS sql_identifier) AS character_set_catalog,
323 CAST(null AS sql_identifier) AS character_set_schema,
324 CAST(null AS sql_identifier) AS character_set_name,
325
326 CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
327 CAST(nco.nspname AS sql_identifier) AS collation_schema,
328 CAST(co.collname AS sql_identifier) AS collation_name,
329
330 CAST(
331 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
332 AS cardinal_number)
333 AS numeric_precision,
334
335 CAST(
336 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
337 AS cardinal_number)
338 AS numeric_precision_radix,
339
340 CAST(
341 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
342 AS cardinal_number)
343 AS numeric_scale,
344
345 CAST(
346 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
347 AS cardinal_number)
348 AS datetime_precision,
349
350 CAST(
351 _pg_interval_type(_pg_truetypid(a, t), _pg_truetypmod(a, t))
352 AS character_data)
353 AS interval_type,
354 CAST(null AS cardinal_number) AS interval_precision,
355
356 CAST(current_database() AS sql_identifier) AS attribute_udt_catalog,
357 CAST(nt.nspname AS sql_identifier) AS attribute_udt_schema,
358 CAST(t.typname AS sql_identifier) AS attribute_udt_name,
359
360 CAST(null AS sql_identifier) AS scope_catalog,
361 CAST(null AS sql_identifier) AS scope_schema,
362 CAST(null AS sql_identifier) AS scope_name,
363
364 CAST(null AS cardinal_number) AS maximum_cardinality,
365 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
366 CAST('NO' AS yes_or_no) AS is_derived_reference_attribute
367
368 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)
369 JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid
370 JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid
371 LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
372 ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
373
374 WHERE a.attnum > 0 AND NOT a.attisdropped
375 AND c.relkind in ('c')
376 AND (pg_has_role(c.relowner, 'USAGE')
377 OR has_type_privilege(c.reltype, 'USAGE'));
378
379GRANT SELECT ON attributes TO PUBLIC;
380
381
382/*
383 * 5.12
384 * CHARACTER_SETS view
385 */
386
387CREATE VIEW character_sets AS
388 SELECT CAST(null AS sql_identifier) AS character_set_catalog,
389 CAST(null AS sql_identifier) AS character_set_schema,
390 CAST(getdatabaseencoding() AS sql_identifier) AS character_set_name,
391 CAST(CASE WHEN getdatabaseencoding() = 'UTF8' THEN 'UCS' ELSE getdatabaseencoding() END AS sql_identifier) AS character_repertoire,
392 CAST(getdatabaseencoding() AS sql_identifier) AS form_of_use,
393 CAST(current_database() AS sql_identifier) AS default_collate_catalog,
394 CAST(nc.nspname AS sql_identifier) AS default_collate_schema,
395 CAST(c.collname AS sql_identifier) AS default_collate_name
396 FROM pg_database d
397 LEFT JOIN (pg_collation c JOIN pg_namespace nc ON (c.collnamespace = nc.oid))
398 ON (datcollate = collcollate AND datctype = collctype)
399 WHERE d.datname = current_database()
400 ORDER BY char_length(c.collname) DESC, c.collname ASC -- prefer full/canonical name
401 LIMIT 1;
402
403GRANT SELECT ON character_sets TO PUBLIC;
404
405
406/*
407 * 5.13
408 * CHECK_CONSTRAINT_ROUTINE_USAGE view
409 */
410
411CREATE VIEW check_constraint_routine_usage AS
412 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
413 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
414 CAST(c.conname AS sql_identifier) AS constraint_name,
415 CAST(current_database() AS sql_identifier) AS specific_catalog,
416 CAST(np.nspname AS sql_identifier) AS specific_schema,
417 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
418 FROM pg_namespace nc, pg_constraint c, pg_depend d, pg_proc p, pg_namespace np
419 WHERE nc.oid = c.connamespace
420 AND c.contype = 'c'
421 AND c.oid = d.objid
422 AND d.classid = 'pg_catalog.pg_constraint'::regclass
423 AND d.refobjid = p.oid
424 AND d.refclassid = 'pg_catalog.pg_proc'::regclass
425 AND p.pronamespace = np.oid
426 AND pg_has_role(p.proowner, 'USAGE');
427
428GRANT SELECT ON check_constraint_routine_usage TO PUBLIC;
429
430
431/*
432 * 5.14
433 * CHECK_CONSTRAINTS view
434 */
435
436CREATE VIEW check_constraints AS
437 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
438 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
439 CAST(con.conname AS sql_identifier) AS constraint_name,
440 CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
441 AS check_clause
442 FROM pg_constraint con
443 LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace)
444 LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
445 LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid)
446 WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE')
447 AND con.contype = 'c'
448
449 UNION
450 -- not-null constraints
451
452 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
453 CAST(n.nspname AS sql_identifier) AS constraint_schema,
454 CAST(CAST(n.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX
455 CAST(a.attname || ' IS NOT NULL' AS character_data)
456 AS check_clause
457 FROM pg_namespace n, pg_class r, pg_attribute a
458 WHERE n.oid = r.relnamespace
459 AND r.oid = a.attrelid
460 AND a.attnum > 0
461 AND NOT a.attisdropped
462 AND a.attnotnull
463 AND r.relkind = 'r'
464 AND pg_has_role(r.relowner, 'USAGE');
465
466GRANT SELECT ON check_constraints TO PUBLIC;
467
468
469/*
470 * 5.15
471 * COLLATIONS view
472 */
473
474CREATE VIEW collations AS
475 SELECT CAST(current_database() AS sql_identifier) AS collation_catalog,
476 CAST(nc.nspname AS sql_identifier) AS collation_schema,
477 CAST(c.collname AS sql_identifier) AS collation_name,
478 CAST('NO PAD' AS character_data) AS pad_attribute
479 FROM pg_collation c, pg_namespace nc
480 WHERE c.collnamespace = nc.oid
481 AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()));
482
483GRANT SELECT ON collations TO PUBLIC;
484
485
486/*
487 * 5.16
488 * COLLATION_CHARACTER_SET_APPLICABILITY view
489 */
490
491CREATE VIEW collation_character_set_applicability AS
492 SELECT CAST(current_database() AS sql_identifier) AS collation_catalog,
493 CAST(nc.nspname AS sql_identifier) AS collation_schema,
494 CAST(c.collname AS sql_identifier) AS collation_name,
495 CAST(null AS sql_identifier) AS character_set_catalog,
496 CAST(null AS sql_identifier) AS character_set_schema,
497 CAST(getdatabaseencoding() AS sql_identifier) AS character_set_name
498 FROM pg_collation c, pg_namespace nc
499 WHERE c.collnamespace = nc.oid
500 AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()));
501
502GRANT SELECT ON collation_character_set_applicability TO PUBLIC;
503
504
505/*
506 * 5.17
507 * COLUMN_COLUMN_USAGE view
508 */
509
510-- feature not supported
511
512
513/*
514 * 5.18
515 * COLUMN_DOMAIN_USAGE view
516 */
517
518CREATE VIEW column_domain_usage AS
519 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
520 CAST(nt.nspname AS sql_identifier) AS domain_schema,
521 CAST(t.typname AS sql_identifier) AS domain_name,
522 CAST(current_database() AS sql_identifier) AS table_catalog,
523 CAST(nc.nspname AS sql_identifier) AS table_schema,
524 CAST(c.relname AS sql_identifier) AS table_name,
525 CAST(a.attname AS sql_identifier) AS column_name
526
527 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
528 pg_attribute a
529
530 WHERE t.typnamespace = nt.oid
531 AND c.relnamespace = nc.oid
532 AND a.attrelid = c.oid
533 AND a.atttypid = t.oid
534 AND t.typtype = 'd'
535 AND c.relkind IN ('r', 'v', 'f')
536 AND a.attnum > 0
537 AND NOT a.attisdropped
538 AND pg_has_role(t.typowner, 'USAGE');
539
540GRANT SELECT ON column_domain_usage TO PUBLIC;
541
542
543/*
544 * 5.19
545 * COLUMN_PRIVILEGES
546 */
547
548CREATE VIEW column_privileges AS
549 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
550 CAST(grantee.rolname AS sql_identifier) AS grantee,
551 CAST(current_database() AS sql_identifier) AS table_catalog,
552 CAST(nc.nspname AS sql_identifier) AS table_schema,
553 CAST(x.relname AS sql_identifier) AS table_name,
554 CAST(x.attname AS sql_identifier) AS column_name,
555 CAST(x.prtype AS character_data) AS privilege_type,
556 CAST(
557 CASE WHEN
558 -- object owner always has grant options
559 pg_has_role(x.grantee, x.relowner, 'USAGE')
560 OR x.grantable
561 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
562
563 FROM (
564 SELECT pr_c.grantor,
565 pr_c.grantee,
566 attname,
567 relname,
568 relnamespace,
569 pr_c.prtype,
570 pr_c.grantable,
571 pr_c.relowner
572 FROM (SELECT oid, relname, relnamespace, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).*
573 FROM pg_class
574 WHERE relkind IN ('r', 'v', 'f')
575 ) pr_c (oid, relname, relnamespace, relowner, grantor, grantee, prtype, grantable),
576 pg_attribute a
577 WHERE a.attrelid = pr_c.oid
578 AND a.attnum > 0
579 AND NOT a.attisdropped
580 UNION
581 SELECT pr_a.grantor,
582 pr_a.grantee,
583 attname,
584 relname,
585 relnamespace,
586 pr_a.prtype,
587 pr_a.grantable,
588 c.relowner
589 FROM (SELECT attrelid, attname, (aclexplode(coalesce(attacl, acldefault('c', relowner)))).*
590 FROM pg_attribute a JOIN pg_class cc ON (a.attrelid = cc.oid)
591 WHERE attnum > 0
592 AND NOT attisdropped
593 ) pr_a (attrelid, attname, grantor, grantee, prtype, grantable),
594 pg_class c
595 WHERE pr_a.attrelid = c.oid
596 AND relkind IN ('r', 'v', 'f')
597 ) x,
598 pg_namespace nc,
599 pg_authid u_grantor,
600 (
601 SELECT oid, rolname FROM pg_authid
602 UNION ALL
603 SELECT 0::oid, 'PUBLIC'
604 ) AS grantee (oid, rolname)
605
606 WHERE x.relnamespace = nc.oid
607 AND x.grantee = grantee.oid
608 AND x.grantor = u_grantor.oid
609 AND x.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'REFERENCES')
610 AND (pg_has_role(u_grantor.oid, 'USAGE')
611 OR pg_has_role(grantee.oid, 'USAGE')
612 OR grantee.rolname = 'PUBLIC');
613
614GRANT SELECT ON column_privileges TO PUBLIC;
615
616
617/*
618 * 5.20
619 * COLUMN_UDT_USAGE view
620 */
621
622CREATE VIEW column_udt_usage AS
623 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
624 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
625 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
626 CAST(current_database() AS sql_identifier) AS table_catalog,
627 CAST(nc.nspname AS sql_identifier) AS table_schema,
628 CAST(c.relname AS sql_identifier) AS table_name,
629 CAST(a.attname AS sql_identifier) AS column_name
630
631 FROM pg_attribute a, pg_class c, pg_namespace nc,
632 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
633 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
634 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
635
636 WHERE a.attrelid = c.oid
637 AND a.atttypid = t.oid
638 AND nc.oid = c.relnamespace
639 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f')
640 AND pg_has_role(coalesce(bt.typowner, t.typowner), 'USAGE');
641
642GRANT SELECT ON column_udt_usage TO PUBLIC;
643
644
645/*
646 * 5.21
647 * COLUMNS view
648 */
649
650CREATE VIEW columns AS
651 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
652 CAST(nc.nspname AS sql_identifier) AS table_schema,
653 CAST(c.relname AS sql_identifier) AS table_name,
654 CAST(a.attname AS sql_identifier) AS column_name,
655 CAST(a.attnum AS cardinal_number) AS ordinal_position,
656 CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS column_default,
657 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
658 AS yes_or_no)
659 AS is_nullable,
660
661 CAST(
662 CASE WHEN t.typtype = 'd' THEN
663 CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
664 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
665 ELSE 'USER-DEFINED' END
666 ELSE
667 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
668 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
669 ELSE 'USER-DEFINED' END
670 END
671 AS character_data)
672 AS data_type,
673
674 CAST(
675 _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
676 AS cardinal_number)
677 AS character_maximum_length,
678
679 CAST(
680 _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
681 AS cardinal_number)
682 AS character_octet_length,
683
684 CAST(
685 _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
686 AS cardinal_number)
687 AS numeric_precision,
688
689 CAST(
690 _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
691 AS cardinal_number)
692 AS numeric_precision_radix,
693
694 CAST(
695 _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
696 AS cardinal_number)
697 AS numeric_scale,
698
699 CAST(
700 _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
701 AS cardinal_number)
702 AS datetime_precision,
703
704 CAST(
705 _pg_interval_type(_pg_truetypid(a, t), _pg_truetypmod(a, t))
706 AS character_data)
707 AS interval_type,
708 CAST(null AS cardinal_number) AS interval_precision,
709
710 CAST(null AS sql_identifier) AS character_set_catalog,
711 CAST(null AS sql_identifier) AS character_set_schema,
712 CAST(null AS sql_identifier) AS character_set_name,
713
714 CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
715 CAST(nco.nspname AS sql_identifier) AS collation_schema,
716 CAST(co.collname AS sql_identifier) AS collation_name,
717
718 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
719 AS sql_identifier) AS domain_catalog,
720 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
721 AS sql_identifier) AS domain_schema,
722 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
723 AS sql_identifier) AS domain_name,
724
725 CAST(current_database() AS sql_identifier) AS udt_catalog,
726 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
727 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
728
729 CAST(null AS sql_identifier) AS scope_catalog,
730 CAST(null AS sql_identifier) AS scope_schema,
731 CAST(null AS sql_identifier) AS scope_name,
732
733 CAST(null AS cardinal_number) AS maximum_cardinality,
734 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
735 CAST('NO' AS yes_or_no) AS is_self_referencing,
736
737 CAST('NO' AS yes_or_no) AS is_identity,
738 CAST(null AS character_data) AS identity_generation,
739 CAST(null AS character_data) AS identity_start,
740 CAST(null AS character_data) AS identity_increment,
741 CAST(null AS character_data) AS identity_maximum,
742 CAST(null AS character_data) AS identity_minimum,
743 CAST(null AS yes_or_no) AS identity_cycle,
744
745 CAST('NEVER' AS character_data) AS is_generated,
746 CAST(null AS character_data) AS generation_expression,
747
748 CAST(CASE WHEN c.relkind = 'r'
749 OR (c.relkind = 'v'
750 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
751 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead))
752 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable
753
754 FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)
755 JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid
756 JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid
757 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
758 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
759 LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
760 ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
761
762 WHERE (NOT pg_is_other_temp_schema(nc.oid))
763
764 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f')
765
766 AND (pg_has_role(c.relowner, 'USAGE')
767 OR has_column_privilege(c.oid, a.attnum,
768 'SELECT, INSERT, UPDATE, REFERENCES'));
769
770GRANT SELECT ON columns TO PUBLIC;
771
772
773/*
774 * 5.22
775 * CONSTRAINT_COLUMN_USAGE view
776 */
777
778CREATE VIEW constraint_column_usage AS
779 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
780 CAST(tblschema AS sql_identifier) AS table_schema,
781 CAST(tblname AS sql_identifier) AS table_name,
782 CAST(colname AS sql_identifier) AS column_name,
783 CAST(current_database() AS sql_identifier) AS constraint_catalog,
784 CAST(cstrschema AS sql_identifier) AS constraint_schema,
785 CAST(cstrname AS sql_identifier) AS constraint_name
786
787 FROM (
788 /* check constraints */
789 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
790 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
791 WHERE nr.oid = r.relnamespace
792 AND r.oid = a.attrelid
793 AND d.refclassid = 'pg_catalog.pg_class'::regclass
794 AND d.refobjid = r.oid
795 AND d.refobjsubid = a.attnum
796 AND d.classid = 'pg_catalog.pg_constraint'::regclass
797 AND d.objid = c.oid
798 AND c.connamespace = nc.oid
799 AND c.contype = 'c'
800 AND r.relkind = 'r'
801 AND NOT a.attisdropped
802
803 UNION ALL
804
805 /* unique/primary key/foreign key constraints */
806 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
807 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc,
808 pg_constraint c
809 WHERE nr.oid = r.relnamespace
810 AND r.oid = a.attrelid
811 AND nc.oid = c.connamespace
812 AND (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND a.attnum = ANY (c.confkey)
813 ELSE r.oid = c.conrelid AND a.attnum = ANY (c.conkey) END)
814 AND NOT a.attisdropped
815 AND c.contype IN ('p', 'u', 'f')
816 AND r.relkind = 'r'
817
818 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname)
819
820 WHERE pg_has_role(x.tblowner, 'USAGE');
821
822GRANT SELECT ON constraint_column_usage TO PUBLIC;
823
824
825/*
826 * 5.23
827 * CONSTRAINT_TABLE_USAGE view
828 */
829
830CREATE VIEW constraint_table_usage AS
831 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
832 CAST(nr.nspname AS sql_identifier) AS table_schema,
833 CAST(r.relname AS sql_identifier) AS table_name,
834 CAST(current_database() AS sql_identifier) AS constraint_catalog,
835 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
836 CAST(c.conname AS sql_identifier) AS constraint_name
837
838 FROM pg_constraint c, pg_namespace nc,
839 pg_class r, pg_namespace nr
840
841 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
842 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
843 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
844 AND r.relkind = 'r'
845 AND pg_has_role(r.relowner, 'USAGE');
846
847GRANT SELECT ON constraint_table_usage TO PUBLIC;
848
849
850-- 5.24 DATA_TYPE_PRIVILEGES view appears later.
851
852
853/*
854 * 5.25
855 * DIRECT_SUPERTABLES view
856 */
857
858-- feature not supported
859
860
861/*
862 * 5.26
863 * DIRECT_SUPERTYPES view
864 */
865
866-- feature not supported
867
868
869/*
870 * 5.27
871 * DOMAIN_CONSTRAINTS view
872 */
873
874CREATE VIEW domain_constraints AS
875 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
876 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
877 CAST(con.conname AS sql_identifier) AS constraint_name,
878 CAST(current_database() AS sql_identifier) AS domain_catalog,
879 CAST(n.nspname AS sql_identifier) AS domain_schema,
880 CAST(t.typname AS sql_identifier) AS domain_name,
881 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
882 AS yes_or_no) AS is_deferrable,
883 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
884 AS yes_or_no) AS initially_deferred
885 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t
886 WHERE rs.oid = con.connamespace
887 AND n.oid = t.typnamespace
888 AND t.oid = con.contypid
889 AND (pg_has_role(t.typowner, 'USAGE')
890 OR has_type_privilege(t.oid, 'USAGE'));
891
892GRANT SELECT ON domain_constraints TO PUBLIC;
893
894
895/*
896 * DOMAIN_UDT_USAGE view
897 * apparently removed in SQL:2003
898 */
899
900CREATE VIEW domain_udt_usage AS
901 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
902 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
903 CAST(bt.typname AS sql_identifier) AS udt_name,
904 CAST(current_database() AS sql_identifier) AS domain_catalog,
905 CAST(nt.nspname AS sql_identifier) AS domain_schema,
906 CAST(t.typname AS sql_identifier) AS domain_name
907
908 FROM pg_type t, pg_namespace nt,
909 pg_type bt, pg_namespace nbt
910
911 WHERE t.typnamespace = nt.oid
912 AND t.typbasetype = bt.oid
913 AND bt.typnamespace = nbt.oid
914 AND t.typtype = 'd'
915 AND pg_has_role(bt.typowner, 'USAGE');
916
917GRANT SELECT ON domain_udt_usage TO PUBLIC;
918
919
920/*
921 * 5.28
922 * DOMAINS view
923 */
924
925CREATE VIEW domains AS
926 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
927 CAST(nt.nspname AS sql_identifier) AS domain_schema,
928 CAST(t.typname AS sql_identifier) AS domain_name,
929
930 CAST(
931 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
932 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
933 ELSE 'USER-DEFINED' END
934 AS character_data)
935 AS data_type,
936
937 CAST(
938 _pg_char_max_length(t.typbasetype, t.typtypmod)
939 AS cardinal_number)
940 AS character_maximum_length,
941
942 CAST(
943 _pg_char_octet_length(t.typbasetype, t.typtypmod)
944 AS cardinal_number)
945 AS character_octet_length,
946
947 CAST(null AS sql_identifier) AS character_set_catalog,
948 CAST(null AS sql_identifier) AS character_set_schema,
949 CAST(null AS sql_identifier) AS character_set_name,
950
951 CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
952 CAST(nco.nspname AS sql_identifier) AS collation_schema,
953 CAST(co.collname AS sql_identifier) AS collation_name,
954
955 CAST(
956 _pg_numeric_precision(t.typbasetype, t.typtypmod)
957 AS cardinal_number)
958 AS numeric_precision,
959
960 CAST(
961 _pg_numeric_precision_radix(t.typbasetype, t.typtypmod)
962 AS cardinal_number)
963 AS numeric_precision_radix,
964
965 CAST(
966 _pg_numeric_scale(t.typbasetype, t.typtypmod)
967 AS cardinal_number)
968 AS numeric_scale,
969
970 CAST(
971 _pg_datetime_precision(t.typbasetype, t.typtypmod)
972 AS cardinal_number)
973 AS datetime_precision,
974
975 CAST(
976 _pg_interval_type(t.typbasetype, t.typtypmod)
977 AS character_data)
978 AS interval_type,
979 CAST(null AS cardinal_number) AS interval_precision,
980
981 CAST(t.typdefault AS character_data) AS domain_default,
982
983 CAST(current_database() AS sql_identifier) AS udt_catalog,
984 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
985 CAST(bt.typname AS sql_identifier) AS udt_name,
986
987 CAST(null AS sql_identifier) AS scope_catalog,
988 CAST(null AS sql_identifier) AS scope_schema,
989 CAST(null AS sql_identifier) AS scope_name,
990
991 CAST(null AS cardinal_number) AS maximum_cardinality,
992 CAST(1 AS sql_identifier) AS dtd_identifier
993
994 FROM (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid)
995 JOIN (pg_type bt JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid)
996 ON (t.typbasetype = bt.oid AND t.typtype = 'd')
997 LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
998 ON t.typcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
999
1000 WHERE (pg_has_role(t.typowner, 'USAGE')
1001 OR has_type_privilege(t.oid, 'USAGE'));
1002
1003GRANT SELECT ON domains TO PUBLIC;
1004
1005
1006-- 5.29 ELEMENT_TYPES view appears later.
1007
1008
1009/*
1010 * 5.30
1011 * ENABLED_ROLES view
1012 */
1013
1014CREATE VIEW enabled_roles AS
1015 SELECT CAST(a.rolname AS sql_identifier) AS role_name
1016 FROM pg_authid a
1017 WHERE pg_has_role(a.oid, 'USAGE');
1018
1019GRANT SELECT ON enabled_roles TO PUBLIC;
1020
1021
1022/*
1023 * 5.31
1024 * FIELDS view
1025 */
1026
1027-- feature not supported
1028
1029
1030/*
1031 * 5.32
1032 * KEY_COLUMN_USAGE view
1033 */
1034
1035CREATE VIEW key_column_usage AS
1036 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1037 CAST(nc_nspname AS sql_identifier) AS constraint_schema,
1038 CAST(conname AS sql_identifier) AS constraint_name,
1039 CAST(current_database() AS sql_identifier) AS table_catalog,
1040 CAST(nr_nspname AS sql_identifier) AS table_schema,
1041 CAST(relname AS sql_identifier) AS table_name,
1042 CAST(a.attname AS sql_identifier) AS column_name,
1043 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
1044 CAST(CASE WHEN contype = 'f' THEN
1045 _pg_index_position(ss.conindid, ss.confkey[(ss.x).n])
1046 ELSE NULL
1047 END AS cardinal_number)
1048 AS position_in_unique_constraint
1049 FROM pg_attribute a,
1050 (SELECT r.oid AS roid, r.relname, r.relowner,
1051 nc.nspname AS nc_nspname, nr.nspname AS nr_nspname,
1052 c.oid AS coid, c.conname, c.contype, c.conindid,
1053 c.confkey, c.confrelid,
1054 _pg_expandarray(c.conkey) AS x
1055 FROM pg_namespace nr, pg_class r, pg_namespace nc,
1056 pg_constraint c
1057 WHERE nr.oid = r.relnamespace
1058 AND r.oid = c.conrelid
1059 AND nc.oid = c.connamespace
1060 AND c.contype IN ('p', 'u', 'f')
1061 AND r.relkind = 'r'
1062 AND (NOT pg_is_other_temp_schema(nr.oid)) ) AS ss
1063 WHERE ss.roid = a.attrelid
1064 AND a.attnum = (ss.x).x
1065 AND NOT a.attisdropped
1066 AND (pg_has_role(relowner, 'USAGE')
1067 OR has_column_privilege(roid, a.attnum,
1068 'SELECT, INSERT, UPDATE, REFERENCES'));
1069
1070GRANT SELECT ON key_column_usage TO PUBLIC;
1071
1072
1073/*
1074 * 5.33
1075 * METHOD_SPECIFICATION_PARAMETERS view
1076 */
1077
1078-- feature not supported
1079
1080
1081/*
1082 * 5.34
1083 * METHOD_SPECIFICATIONS view
1084 */
1085
1086-- feature not supported
1087
1088
1089/*
1090 * 5.35
1091 * PARAMETERS view
1092 */
1093
1094CREATE VIEW parameters AS
1095 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1096 CAST(n_nspname AS sql_identifier) AS specific_schema,
1097 CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name,
1098 CAST((ss.x).n AS cardinal_number) AS ordinal_position,
1099 CAST(
1100 CASE WHEN proargmodes IS NULL THEN 'IN'
1101 WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN'
1102 WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'
1103 WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'
1104 WHEN proargmodes[(ss.x).n] = 'v' THEN 'IN'
1105 WHEN proargmodes[(ss.x).n] = 't' THEN 'OUT'
1106 END AS character_data) AS parameter_mode,
1107 CAST('NO' AS yes_or_no) AS is_result,
1108 CAST('NO' AS yes_or_no) AS as_locator,
1109 CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,
1110 CAST(
1111 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1112 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1113 ELSE 'USER-DEFINED' END AS character_data)
1114 AS data_type,
1115 CAST(null AS cardinal_number) AS character_maximum_length,
1116 CAST(null AS cardinal_number) AS character_octet_length,
1117 CAST(null AS sql_identifier) AS character_set_catalog,
1118 CAST(null AS sql_identifier) AS character_set_schema,
1119 CAST(null AS sql_identifier) AS character_set_name,
1120 CAST(null AS sql_identifier) AS collation_catalog,
1121 CAST(null AS sql_identifier) AS collation_schema,
1122 CAST(null AS sql_identifier) AS collation_name,
1123 CAST(null AS cardinal_number) AS numeric_precision,
1124 CAST(null AS cardinal_number) AS numeric_precision_radix,
1125 CAST(null AS cardinal_number) AS numeric_scale,
1126 CAST(null AS cardinal_number) AS datetime_precision,
1127 CAST(null AS character_data) AS interval_type,
1128 CAST(null AS cardinal_number) AS interval_precision,
1129 CAST(current_database() AS sql_identifier) AS udt_catalog,
1130 CAST(nt.nspname AS sql_identifier) AS udt_schema,
1131 CAST(t.typname AS sql_identifier) AS udt_name,
1132 CAST(null AS sql_identifier) AS scope_catalog,
1133 CAST(null AS sql_identifier) AS scope_schema,
1134 CAST(null AS sql_identifier) AS scope_name,
1135 CAST(null AS cardinal_number) AS maximum_cardinality,
1136 CAST((ss.x).n AS sql_identifier) AS dtd_identifier
1137
1138 FROM pg_type t, pg_namespace nt,
1139 (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid,
1140 p.proargnames, p.proargmodes,
1141 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
1142 FROM pg_namespace n, pg_proc p
1143 WHERE n.oid = p.pronamespace
1144 AND (pg_has_role(p.proowner, 'USAGE') OR
1145 has_function_privilege(p.oid, 'EXECUTE'))) AS ss
1146 WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
1147
1148GRANT SELECT ON parameters TO PUBLIC;
1149
1150
1151/*
1152 * 5.36
1153 * REFERENCED_TYPES view
1154 */
1155
1156-- feature not supported
1157
1158
1159/*
1160 * 5.37
1161 * REFERENTIAL_CONSTRAINTS view
1162 */
1163
1164CREATE VIEW referential_constraints AS
1165 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1166 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
1167 CAST(con.conname AS sql_identifier) AS constraint_name,
1168 CAST(
1169 CASE WHEN npkc.nspname IS NULL THEN NULL
1170 ELSE current_database() END
1171 AS sql_identifier) AS unique_constraint_catalog,
1172 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
1173 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
1174
1175 CAST(
1176 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
1177 WHEN 'p' THEN 'PARTIAL'
1178 WHEN 'u' THEN 'NONE' END
1179 AS character_data) AS match_option,
1180
1181 CAST(
1182 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
1183 WHEN 'n' THEN 'SET NULL'
1184 WHEN 'd' THEN 'SET DEFAULT'
1185 WHEN 'r' THEN 'RESTRICT'
1186 WHEN 'a' THEN 'NO ACTION' END
1187 AS character_data) AS update_rule,
1188
1189 CAST(
1190 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
1191 WHEN 'n' THEN 'SET NULL'
1192 WHEN 'd' THEN 'SET DEFAULT'
1193 WHEN 'r' THEN 'RESTRICT'
1194 WHEN 'a' THEN 'NO ACTION' END
1195 AS character_data) AS delete_rule
1196
1197 FROM (pg_namespace ncon
1198 INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
1199 INNER JOIN pg_class c ON con.conrelid = c.oid AND con.contype = 'f')
1200 LEFT JOIN pg_depend d1 -- find constraint's dependency on an index
1201 ON d1.objid = con.oid AND d1.classid = 'pg_constraint'::regclass
1202 AND d1.refclassid = 'pg_class'::regclass AND d1.refobjsubid = 0
1203 LEFT JOIN pg_depend d2 -- find pkey/unique constraint for that index
1204 ON d2.refclassid = 'pg_constraint'::regclass
1205 AND d2.classid = 'pg_class'::regclass
1206 AND d2.objid = d1.refobjid AND d2.objsubid = 0
1207 AND d2.deptype = 'i'
1208 LEFT JOIN pg_constraint pkc ON pkc.oid = d2.refobjid
1209 AND pkc.contype IN ('p', 'u')
1210 AND pkc.conrelid = con.confrelid
1211 LEFT JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid
1212
1213 WHERE pg_has_role(c.relowner, 'USAGE')
1214 -- SELECT privilege omitted, per SQL standard
1215 OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1216 OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') ;
1217
1218GRANT SELECT ON referential_constraints TO PUBLIC;
1219
1220
1221/*
1222 * 5.38
1223 * ROLE_COLUMN_GRANTS view
1224 */
1225
1226CREATE VIEW role_column_grants AS
1227 SELECT grantor,
1228 grantee,
1229 table_catalog,
1230 table_schema,
1231 table_name,
1232 column_name,
1233 privilege_type,
1234 is_grantable
1235 FROM column_privileges
1236 WHERE grantor IN (SELECT role_name FROM enabled_roles)
1237 OR grantee IN (SELECT role_name FROM enabled_roles);
1238
1239GRANT SELECT ON role_column_grants TO PUBLIC;
1240
1241
1242-- 5.39 ROLE_ROUTINE_GRANTS view is based on 5.45 ROUTINE_PRIVILEGES and is defined there instead.
1243
1244
1245-- 5.40 ROLE_TABLE_GRANTS view is based on 5.60 TABLE_PRIVILEGES and is defined there instead.
1246
1247
1248/*
1249 * 5.41
1250 * ROLE_TABLE_METHOD_GRANTS view
1251 */
1252
1253-- feature not supported
1254
1255
1256
1257-- 5.42 ROLE_USAGE_GRANTS view is based on 5.71 USAGE_PRIVILEGES and is defined there instead.
1258
1259
1260-- 5.43 ROLE_UDT_GRANTS view is based on 5.70 UDT_PRIVILEGES and is defined there instead.
1261
1262
1263/*
1264 * 5.44
1265 * ROUTINE_COLUMN_USAGE view
1266 */
1267
1268-- not tracked by PostgreSQL
1269
1270
1271/*
1272 * 5.45
1273 * ROUTINE_PRIVILEGES view
1274 */
1275
1276CREATE VIEW routine_privileges AS
1277 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1278 CAST(grantee.rolname AS sql_identifier) AS grantee,
1279 CAST(current_database() AS sql_identifier) AS specific_catalog,
1280 CAST(n.nspname AS sql_identifier) AS specific_schema,
1281 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1282 CAST(current_database() AS sql_identifier) AS routine_catalog,
1283 CAST(n.nspname AS sql_identifier) AS routine_schema,
1284 CAST(p.proname AS sql_identifier) AS routine_name,
1285 CAST('EXECUTE' AS character_data) AS privilege_type,
1286 CAST(
1287 CASE WHEN
1288 -- object owner always has grant options
1289 pg_has_role(grantee.oid, p.proowner, 'USAGE')
1290 OR p.grantable
1291 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
1292
1293 FROM (
1294 SELECT oid, proname, proowner, pronamespace, (aclexplode(coalesce(proacl, acldefault('f', proowner)))).* FROM pg_proc
1295 ) p (oid, proname, proowner, pronamespace, grantor, grantee, prtype, grantable),
1296 pg_namespace n,
1297 pg_authid u_grantor,
1298 (
1299 SELECT oid, rolname FROM pg_authid
1300 UNION ALL
1301 SELECT 0::oid, 'PUBLIC'
1302 ) AS grantee (oid, rolname)
1303
1304 WHERE p.pronamespace = n.oid
1305 AND grantee.oid = p.grantee
1306 AND u_grantor.oid = p.grantor
1307 AND p.prtype IN ('EXECUTE')
1308 AND (pg_has_role(u_grantor.oid, 'USAGE')
1309 OR pg_has_role(grantee.oid, 'USAGE')
1310 OR grantee.rolname = 'PUBLIC');
1311
1312GRANT SELECT ON routine_privileges TO PUBLIC;
1313
1314
1315/*
1316 * 5.39
1317 * ROLE_ROUTINE_GRANTS view
1318 */
1319
1320CREATE VIEW role_routine_grants AS
1321 SELECT grantor,
1322 grantee,
1323 specific_catalog,
1324 specific_schema,
1325 specific_name,
1326 routine_catalog,
1327 routine_schema,
1328 routine_name,
1329 privilege_type,
1330 is_grantable
1331 FROM routine_privileges
1332 WHERE grantor IN (SELECT role_name FROM enabled_roles)
1333 OR grantee IN (SELECT role_name FROM enabled_roles);
1334
1335GRANT SELECT ON role_routine_grants TO PUBLIC;
1336
1337
1338/*
1339 * 5.46
1340 * ROUTINE_ROUTINE_USAGE view
1341 */
1342
1343-- not tracked by PostgreSQL
1344
1345
1346/*
1347 * 5.47
1348 * ROUTINE_SEQUENCE_USAGE view
1349 */
1350
1351-- not tracked by PostgreSQL
1352
1353
1354/*
1355 * 5.48
1356 * ROUTINE_TABLE_USAGE view
1357 */
1358
1359-- not tracked by PostgreSQL
1360
1361
1362/*
1363 * 5.49
1364 * ROUTINES view
1365 */
1366
1367CREATE VIEW routines AS
1368 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
1369 CAST(n.nspname AS sql_identifier) AS specific_schema,
1370 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
1371 CAST(current_database() AS sql_identifier) AS routine_catalog,
1372 CAST(n.nspname AS sql_identifier) AS routine_schema,
1373 CAST(p.proname AS sql_identifier) AS routine_name,
1374 CAST('FUNCTION' AS character_data) AS routine_type,
1375 CAST(null AS sql_identifier) AS module_catalog,
1376 CAST(null AS sql_identifier) AS module_schema,
1377 CAST(null AS sql_identifier) AS module_name,
1378 CAST(null AS sql_identifier) AS udt_catalog,
1379 CAST(null AS sql_identifier) AS udt_schema,
1380 CAST(null AS sql_identifier) AS udt_name,
1381
1382 CAST(
1383 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
1384 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
1385 ELSE 'USER-DEFINED' END AS character_data)
1386 AS data_type,
1387 CAST(null AS cardinal_number) AS character_maximum_length,
1388 CAST(null AS cardinal_number) AS character_octet_length,
1389 CAST(null AS sql_identifier) AS character_set_catalog,
1390 CAST(null AS sql_identifier) AS character_set_schema,
1391 CAST(null AS sql_identifier) AS character_set_name,
1392 CAST(null AS sql_identifier) AS collation_catalog,
1393 CAST(null AS sql_identifier) AS collation_schema,
1394 CAST(null AS sql_identifier) AS collation_name,
1395 CAST(null AS cardinal_number) AS numeric_precision,
1396 CAST(null AS cardinal_number) AS numeric_precision_radix,
1397 CAST(null AS cardinal_number) AS numeric_scale,
1398 CAST(null AS cardinal_number) AS datetime_precision,
1399 CAST(null AS character_data) AS interval_type,
1400 CAST(null AS cardinal_number) AS interval_precision,
1401 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
1402 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
1403 CAST(t.typname AS sql_identifier) AS type_udt_name,
1404 CAST(null AS sql_identifier) AS scope_catalog,
1405 CAST(null AS sql_identifier) AS scope_schema,
1406 CAST(null AS sql_identifier) AS scope_name,
1407 CAST(null AS cardinal_number) AS maximum_cardinality,
1408 CAST(0 AS sql_identifier) AS dtd_identifier,
1409
1410 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
1411 AS routine_body,
1412 CAST(
1413 CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END
1414 AS character_data) AS routine_definition,
1415 CAST(
1416 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
1417 AS character_data) AS external_name,
1418 CAST(upper(l.lanname) AS character_data) AS external_language,
1419
1420 CAST('GENERAL' AS character_data) AS parameter_style,
1421 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_deterministic,
1422 CAST('MODIFIES' AS character_data) AS sql_data_access,
1423 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_null_call,
1424 CAST(null AS character_data) AS sql_path,
1425 CAST('YES' AS yes_or_no) AS schema_level_routine,
1426 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
1427 CAST(null AS yes_or_no) AS is_user_defined_cast,
1428 CAST(null AS yes_or_no) AS is_implicitly_invocable,
1429 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
1430 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
1431 CAST(null AS sql_identifier) AS to_sql_specific_schema,
1432 CAST(null AS sql_identifier) AS to_sql_specific_name,
1433 CAST('NO' AS yes_or_no) AS as_locator,
1434 CAST(null AS time_stamp) AS created,
1435 CAST(null AS time_stamp) AS last_altered,
1436 CAST(null AS yes_or_no) AS new_savepoint_level,
1437 CAST('NO' AS yes_or_no) AS is_udt_dependent,
1438
1439 CAST(null AS character_data) AS result_cast_from_data_type,
1440 CAST(null AS yes_or_no) AS result_cast_as_locator,
1441 CAST(null AS cardinal_number) AS result_cast_char_max_length,
1442 CAST(null AS cardinal_number) AS result_cast_char_octet_length,
1443 CAST(null AS sql_identifier) AS result_cast_char_set_catalog,
1444 CAST(null AS sql_identifier) AS result_cast_char_set_schema,
1445 CAST(null AS sql_identifier) AS result_cast_character_set_name,
1446 CAST(null AS sql_identifier) AS result_cast_collation_catalog,
1447 CAST(null AS sql_identifier) AS result_cast_collation_schema,
1448 CAST(null AS sql_identifier) AS result_cast_collation_name,
1449 CAST(null AS cardinal_number) AS result_cast_numeric_precision,
1450 CAST(null AS cardinal_number) AS result_cast_numeric_precision_radix,
1451 CAST(null AS cardinal_number) AS result_cast_numeric_scale,
1452 CAST(null AS cardinal_number) AS result_cast_datetime_precision,
1453 CAST(null AS character_data) AS result_cast_interval_type,
1454 CAST(null AS cardinal_number) AS result_cast_interval_precision,
1455 CAST(null AS sql_identifier) AS result_cast_type_udt_catalog,
1456 CAST(null AS sql_identifier) AS result_cast_type_udt_schema,
1457 CAST(null AS sql_identifier) AS result_cast_type_udt_name,
1458 CAST(null AS sql_identifier) AS result_cast_scope_catalog,
1459 CAST(null AS sql_identifier) AS result_cast_scope_schema,
1460 CAST(null AS sql_identifier) AS result_cast_scope_name,
1461 CAST(null AS cardinal_number) AS result_cast_maximum_cardinality,
1462 CAST(null AS sql_identifier) AS result_cast_dtd_identifier
1463
1464 FROM pg_namespace n, pg_proc p, pg_language l,
1465 pg_type t, pg_namespace nt
1466
1467 WHERE n.oid = p.pronamespace AND p.prolang = l.oid
1468 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
1469 AND (pg_has_role(p.proowner, 'USAGE')
1470 OR has_function_privilege(p.oid, 'EXECUTE'));
1471
1472GRANT SELECT ON routines TO PUBLIC;
1473
1474
1475/*
1476 * 5.50
1477 * SCHEMATA view
1478 */
1479
1480CREATE VIEW schemata AS
1481 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
1482 CAST(n.nspname AS sql_identifier) AS schema_name,
1483 CAST(u.rolname AS sql_identifier) AS schema_owner,
1484 CAST(null AS sql_identifier) AS default_character_set_catalog,
1485 CAST(null AS sql_identifier) AS default_character_set_schema,
1486 CAST(null AS sql_identifier) AS default_character_set_name,
1487 CAST(null AS character_data) AS sql_path
1488 FROM pg_namespace n, pg_authid u
1489 WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE');
1490
1491GRANT SELECT ON schemata TO PUBLIC;
1492
1493
1494/*
1495 * 5.51
1496 * SEQUENCES view
1497 */
1498
1499CREATE VIEW sequences AS
1500 SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
1501 CAST(nc.nspname AS sql_identifier) AS sequence_schema,
1502 CAST(c.relname AS sql_identifier) AS sequence_name,
1503 CAST('bigint' AS character_data) AS data_type,
1504 CAST(64 AS cardinal_number) AS numeric_precision,
1505 CAST(2 AS cardinal_number) AS numeric_precision_radix,
1506 CAST(0 AS cardinal_number) AS numeric_scale,
1507 -- XXX: The following could be improved if we had LATERAL.
1508 CAST((pg_sequence_parameters(c.oid)).start_value AS character_data) AS start_value,
1509 CAST((pg_sequence_parameters(c.oid)).minimum_value AS character_data) AS minimum_value,
1510 CAST((pg_sequence_parameters(c.oid)).maximum_value AS character_data) AS maximum_value,
1511 CAST((pg_sequence_parameters(c.oid)).increment AS character_data) AS increment,
1512 CAST(CASE WHEN (pg_sequence_parameters(c.oid)).cycle_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS cycle_option
1513 FROM pg_namespace nc, pg_class c
1514 WHERE c.relnamespace = nc.oid
1515 AND c.relkind = 'S'
1516 AND (NOT pg_is_other_temp_schema(nc.oid))
1517 AND (pg_has_role(c.relowner, 'USAGE')
1518 OR has_sequence_privilege(c.oid, 'SELECT, UPDATE, USAGE') );
1519
1520GRANT SELECT ON sequences TO PUBLIC;
1521
1522
1523/*
1524 * 5.52
1525 * SQL_FEATURES table
1526 */
1527
1528CREATE TABLE sql_features (
1529 feature_id character_data,
1530 feature_name character_data,
1531 sub_feature_id character_data,
1532 sub_feature_name character_data,
1533 is_supported yes_or_no,
1534 is_verified_by character_data,
1535 comments character_data
1536) WITHOUT OIDS;
1537
1538-- Will be filled with external data by initdb.
1539
1540GRANT SELECT ON sql_features TO PUBLIC;
1541
1542
1543/*
1544 * 5.53
1545 * SQL_IMPLEMENTATION_INFO table
1546 */
1547
1548-- Note: Implementation information items are defined in ISO/IEC 9075-3:2008,
1549-- clause 9.1.
1550
1551CREATE TABLE sql_implementation_info (
1552 implementation_info_id character_data,
1553 implementation_info_name character_data,
1554 integer_value cardinal_number,
1555 character_value character_data,
1556 comments character_data
1557) WITHOUT OIDS;
1558
1559INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
1560INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, (SELECT default_collate_name FROM character_sets), NULL);
1561INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
1562INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
1563INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
1564INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
1565INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITTED; user-settable');
1566INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
1567INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
1568INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
1569INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
1570INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
1571
1572GRANT SELECT ON sql_implementation_info TO PUBLIC;
1573
1574
1575/*
1576 * SQL_LANGUAGES table
1577 * apparently removed in SQL:2008
1578 */
1579
1580CREATE TABLE sql_languages (
1581 sql_language_source character_data,
1582 sql_language_year character_data,
1583 sql_language_conformance character_data,
1584 sql_language_integrity character_data,
1585 sql_language_implementation character_data,
1586 sql_language_binding_style character_data,
1587 sql_language_programming_language character_data
1588) WITHOUT OIDS;
1589
1590INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
1591INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1592INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
1593INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
1594
1595GRANT SELECT ON sql_languages TO PUBLIC;
1596
1597
1598/*
1599 * 5.54
1600 * SQL_PACKAGES table
1601 */
1602
1603CREATE TABLE sql_packages (
1604 feature_id character_data,
1605 feature_name character_data,
1606 is_supported yes_or_no,
1607 is_verified_by character_data,
1608 comments character_data
1609) WITHOUT OIDS;
1610
1611INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
1612INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
1613INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
1614INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
1615INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
1616INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
1617INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
1618INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
1619INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
1620INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
1621
1622GRANT SELECT ON sql_packages TO PUBLIC;
1623
1624
1625/*
1626 * 5.55
1627 * SQL_PARTS table
1628 */
1629
1630CREATE TABLE sql_parts (
1631 feature_id character_data,
1632 feature_name character_data,
1633 is_supported yes_or_no,
1634 is_verified_by character_data,
1635 comments character_data
1636) WITHOUT OIDS;
1637
1638INSERT INTO sql_parts VALUES ('1', 'Framework (SQL/Framework)', 'NO', NULL, '');
1639INSERT INTO sql_parts VALUES ('2', 'Foundation (SQL/Foundation)', 'NO', NULL, '');
1640INSERT INTO sql_parts VALUES ('3', 'Call-Level Interface (SQL/CLI)', 'NO', NULL, '');
1641INSERT INTO sql_parts VALUES ('4', 'Persistent Stored Modules (SQL/PSM)', 'NO', NULL, '');
1642INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', 'NO', NULL, '');
1643INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', 'NO', NULL, '');
1644INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', 'NO', NULL, '');
1645INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', 'NO', NULL, '');
1646INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', 'YES', NULL, '');
1647
1648
1649/*
1650 * 5.56
1651 * SQL_SIZING table
1652 */
1653
1654-- Note: Sizing items are defined in ISO/IEC 9075-3:2008, clause 9.2.
1655
1656CREATE TABLE sql_sizing (
1657 sizing_id cardinal_number,
1658 sizing_name character_data,
1659 supported_value cardinal_number,
1660 comments character_data
1661) WITHOUT OIDS;
1662
1663INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1664INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1665INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1666INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1667INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 1664, NULL); -- match MaxTupleAttributeNumber
1668INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1669INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1670INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1671INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1672INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1673INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1674INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1675INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1676INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1677INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1678INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1679INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1680INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1681INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1682INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1683INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1684INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1685INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1686
1687UPDATE sql_sizing
1688 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1689 comments = 'Might be less, depending on character set.'
1690 WHERE supported_value = 63;
1691
1692GRANT SELECT ON sql_sizing TO PUBLIC;
1693
1694
1695/*
1696 * 5.57
1697 * SQL_SIZING_PROFILES table
1698 */
1699
1700-- The data in this table are defined by various profiles of SQL.
1701-- Since we don't have any information about such profiles, we provide
1702-- an empty table.
1703
1704CREATE TABLE sql_sizing_profiles (
1705 sizing_id cardinal_number,
1706 sizing_name character_data,
1707 profile_id character_data,
1708 required_value cardinal_number,
1709 comments character_data
1710) WITHOUT OIDS;
1711
1712GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1713
1714
1715/*
1716 * 5.58
1717 * TABLE_CONSTRAINTS view
1718 */
1719
1720CREATE VIEW table_constraints AS
1721 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1722 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1723 CAST(c.conname AS sql_identifier) AS constraint_name,
1724 CAST(current_database() AS sql_identifier) AS table_catalog,
1725 CAST(nr.nspname AS sql_identifier) AS table_schema,
1726 CAST(r.relname AS sql_identifier) AS table_name,
1727 CAST(
1728 CASE c.contype WHEN 'c' THEN 'CHECK'
1729 WHEN 'f' THEN 'FOREIGN KEY'
1730 WHEN 'p' THEN 'PRIMARY KEY'
1731 WHEN 'u' THEN 'UNIQUE' END
1732 AS character_data) AS constraint_type,
1733 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS yes_or_no)
1734 AS is_deferrable,
1735 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
1736 AS initially_deferred
1737
1738 FROM pg_namespace nc,
1739 pg_namespace nr,
1740 pg_constraint c,
1741 pg_class r
1742
1743 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1744 AND c.conrelid = r.oid
1745 AND c.contype NOT IN ('t', 'x') -- ignore nonstandard constraints
1746 AND r.relkind = 'r'
1747 AND (NOT pg_is_other_temp_schema(nr.oid))
1748 AND (pg_has_role(r.relowner, 'USAGE')
1749 -- SELECT privilege omitted, per SQL standard
1750 OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1751 OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') )
1752
1753 UNION ALL
1754
1755 -- not-null constraints
1756
1757 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1758 CAST(nr.nspname AS sql_identifier) AS constraint_schema,
1759 CAST(CAST(nr.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX
1760 CAST(current_database() AS sql_identifier) AS table_catalog,
1761 CAST(nr.nspname AS sql_identifier) AS table_schema,
1762 CAST(r.relname AS sql_identifier) AS table_name,
1763 CAST('CHECK' AS character_data) AS constraint_type,
1764 CAST('NO' AS yes_or_no) AS is_deferrable,
1765 CAST('NO' AS yes_or_no) AS initially_deferred
1766
1767 FROM pg_namespace nr,
1768 pg_class r,
1769 pg_attribute a
1770
1771 WHERE nr.oid = r.relnamespace
1772 AND r.oid = a.attrelid
1773 AND a.attnotnull
1774 AND a.attnum > 0
1775 AND NOT a.attisdropped
1776 AND r.relkind = 'r'
1777 AND (NOT pg_is_other_temp_schema(nr.oid))
1778 AND (pg_has_role(r.relowner, 'USAGE')
1779 -- SELECT privilege omitted, per SQL standard
1780 OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1781 OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') );
1782
1783GRANT SELECT ON table_constraints TO PUBLIC;
1784
1785
1786/*
1787 * 5.59
1788 * TABLE_METHOD_PRIVILEGES view
1789 */
1790
1791-- feature not supported
1792
1793
1794/*
1795 * 5.60
1796 * TABLE_PRIVILEGES view
1797 */
1798
1799CREATE VIEW table_privileges AS
1800 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
1801 CAST(grantee.rolname AS sql_identifier) AS grantee,
1802 CAST(current_database() AS sql_identifier) AS table_catalog,
1803 CAST(nc.nspname AS sql_identifier) AS table_schema,
1804 CAST(c.relname AS sql_identifier) AS table_name,
1805 CAST(c.prtype AS character_data) AS privilege_type,
1806 CAST(
1807 CASE WHEN
1808 -- object owner always has grant options
1809 pg_has_role(grantee.oid, c.relowner, 'USAGE')
1810 OR c.grantable
1811 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
1812 CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS with_hierarchy
1813
1814 FROM (
1815 SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
1816 ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
1817 pg_namespace nc,
1818 pg_authid u_grantor,
1819 (
1820 SELECT oid, rolname FROM pg_authid
1821 UNION ALL
1822 SELECT 0::oid, 'PUBLIC'
1823 ) AS grantee (oid, rolname)
1824
1825 WHERE c.relnamespace = nc.oid
1826 AND c.relkind IN ('r', 'v')
1827 AND c.grantee = grantee.oid
1828 AND c.grantor = u_grantor.oid
1829 AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
1830 AND (pg_has_role(u_grantor.oid, 'USAGE')
1831 OR pg_has_role(grantee.oid, 'USAGE')
1832 OR grantee.rolname = 'PUBLIC');
1833
1834GRANT SELECT ON table_privileges TO PUBLIC;
1835
1836
1837/*
1838 * 5.40
1839 * ROLE_TABLE_GRANTS view
1840 */
1841
1842CREATE VIEW role_table_grants AS
1843 SELECT grantor,
1844 grantee,
1845 table_catalog,
1846 table_schema,
1847 table_name,
1848 privilege_type,
1849 is_grantable,
1850 with_hierarchy
1851 FROM table_privileges
1852 WHERE grantor IN (SELECT role_name FROM enabled_roles)
1853 OR grantee IN (SELECT role_name FROM enabled_roles);
1854
1855GRANT SELECT ON role_table_grants TO PUBLIC;
1856
1857
1858/*
1859 * 5.61
1860 * TABLES view
1861 */
1862
1863CREATE VIEW tables AS
1864 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1865 CAST(nc.nspname AS sql_identifier) AS table_schema,
1866 CAST(c.relname AS sql_identifier) AS table_name,
1867
1868 CAST(
1869 CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'
1870 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1871 WHEN c.relkind = 'v' THEN 'VIEW'
1872 WHEN c.relkind = 'f' THEN 'FOREIGN TABLE'
1873 ELSE null END
1874 AS character_data) AS table_type,
1875
1876 CAST(null AS sql_identifier) AS self_referencing_column_name,
1877 CAST(null AS character_data) AS reference_generation,
1878
1879 CAST(CASE WHEN t.typname IS NOT NULL THEN current_database() ELSE null END AS sql_identifier) AS user_defined_type_catalog,
1880 CAST(nt.nspname AS sql_identifier) AS user_defined_type_schema,
1881 CAST(t.typname AS sql_identifier) AS user_defined_type_name,
1882
1883 CAST(CASE WHEN c.relkind = 'r'
1884 OR (c.relkind = 'v'
1885 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead))
1886 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into,
1887
1888 CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed,
1889 CAST(null AS character_data) AS commit_action
1890
1891 FROM pg_namespace nc JOIN pg_class c ON (nc.oid = c.relnamespace)
1892 LEFT JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON (c.reloftype = t.oid)
1893
1894 WHERE c.relkind IN ('r', 'v', 'f')
1895 AND (NOT pg_is_other_temp_schema(nc.oid))
1896 AND (pg_has_role(c.relowner, 'USAGE')
1897 OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
1898 OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
1899
1900GRANT SELECT ON tables TO PUBLIC;
1901
1902
1903/*
1904 * 5.62
1905 * TRANSFORMS view
1906 */
1907
1908-- feature not supported
1909
1910
1911/*
1912 * 5.63
1913 * TRANSLATIONS view
1914 */
1915
1916-- feature not supported
1917
1918
1919/*
1920 * 5.64
1921 * TRIGGERED_UPDATE_COLUMNS view
1922 */
1923
1924CREATE VIEW triggered_update_columns AS
1925 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1926 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1927 CAST(t.tgname AS sql_identifier) AS trigger_name,
1928 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1929 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1930 CAST(c.relname AS sql_identifier) AS event_object_table,
1931 CAST(a.attname AS sql_identifier) AS event_object_column
1932
1933 FROM pg_namespace n, pg_class c, pg_trigger t,
1934 (SELECT tgoid, (ta0.tgat).x AS tgattnum, (ta0.tgat).n AS tgattpos
1935 FROM (SELECT oid AS tgoid, information_schema._pg_expandarray(tgattr) AS tgat FROM pg_trigger) AS ta0) AS ta,
1936 pg_attribute a
1937
1938 WHERE n.oid = c.relnamespace
1939 AND c.oid = t.tgrelid
1940 AND t.oid = ta.tgoid
1941 AND (a.attrelid, a.attnum) = (t.tgrelid, ta.tgattnum)
1942 AND NOT t.tgisinternal
1943 AND (NOT pg_is_other_temp_schema(n.oid))
1944 AND (pg_has_role(c.relowner, 'USAGE')
1945 -- SELECT privilege omitted, per SQL standard
1946 OR has_column_privilege(c.oid, a.attnum, 'INSERT, UPDATE, REFERENCES') );
1947
1948GRANT SELECT ON triggered_update_columns TO PUBLIC;
1949
1950
1951/*
1952 * 5.65
1953 * TRIGGER_COLUMN_USAGE view
1954 */
1955
1956-- not tracked by PostgreSQL
1957
1958
1959/*
1960 * 5.66
1961 * TRIGGER_ROUTINE_USAGE view
1962 */
1963
1964-- not tracked by PostgreSQL
1965
1966
1967/*
1968 * 5.67
1969 * TRIGGER_SEQUENCE_USAGE view
1970 */
1971
1972-- not tracked by PostgreSQL
1973
1974
1975/*
1976 * 5.68
1977 * TRIGGER_TABLE_USAGE view
1978 */
1979
1980-- not tracked by PostgreSQL
1981
1982
1983/*
1984 * 5.69
1985 * TRIGGERS view
1986 */
1987
1988CREATE VIEW triggers AS
1989 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1990 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1991 CAST(t.tgname AS sql_identifier) AS trigger_name,
1992 CAST(em.text AS character_data) AS event_manipulation,
1993 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1994 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1995 CAST(c.relname AS sql_identifier) AS event_object_table,
1996 CAST(null AS cardinal_number) AS action_order,
1997 -- XXX strange hacks follow
1998 CAST(
1999 CASE WHEN pg_has_role(c.relowner, 'USAGE')
2000 THEN (SELECT m[1] FROM regexp_matches(pg_get_triggerdef(t.oid), E'.{35,} WHEN \\((.+)\\) EXECUTE PROCEDURE') AS rm(m) LIMIT 1)
2001 ELSE null END
2002 AS character_data) AS action_condition,
2003 CAST(
2004 substring(pg_get_triggerdef(t.oid) from
2005 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
2006 AS character_data) AS action_statement,
2007 CAST(
2008 -- hard-wired reference to TRIGGER_TYPE_ROW
2009 CASE t.tgtype & 1 WHEN 1 THEN 'ROW' ELSE 'STATEMENT' END
2010 AS character_data) AS action_orientation,
2011 CAST(
2012 -- hard-wired refs to TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSTEAD
2013 CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE' WHEN 64 THEN 'INSTEAD OF' ELSE 'AFTER' END
2014 AS character_data) AS action_timing,
2015 CAST(null AS sql_identifier) AS action_reference_old_table,
2016 CAST(null AS sql_identifier) AS action_reference_new_table,
2017 CAST(null AS sql_identifier) AS action_reference_old_row,
2018 CAST(null AS sql_identifier) AS action_reference_new_row,
2019 CAST(null AS time_stamp) AS created
2020
2021 FROM pg_namespace n, pg_class c, pg_trigger t,
2022 -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE,
2023 -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE
2024 (VALUES (4, 'INSERT'),
2025 (8, 'DELETE'),
2026 (16, 'UPDATE')) AS em (num, text)
2027
2028 WHERE n.oid = c.relnamespace
2029 AND c.oid = t.tgrelid
2030 AND t.tgtype & em.num <> 0
2031 AND NOT t.tgisinternal
2032 AND (NOT pg_is_other_temp_schema(n.oid))
2033 AND (pg_has_role(c.relowner, 'USAGE')
2034 -- SELECT privilege omitted, per SQL standard
2035 OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2036 OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') );
2037
2038GRANT SELECT ON triggers TO PUBLIC;
2039
2040
2041/*
2042 * 5.70
2043 * UDT_PRIVILEGES view
2044 */
2045
2046CREATE VIEW udt_privileges AS
2047 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2048 CAST(grantee.rolname AS sql_identifier) AS grantee,
2049 CAST(current_database() AS sql_identifier) AS udt_catalog,
2050 CAST(n.nspname AS sql_identifier) AS udt_schema,
2051 CAST(t.typname AS sql_identifier) AS udt_name,
2052 CAST('TYPE USAGE' AS character_data) AS privilege_type, -- sic
2053 CAST(
2054 CASE WHEN
2055 -- object owner always has grant options
2056 pg_has_role(grantee.oid, t.typowner, 'USAGE')
2057 OR t.grantable
2058 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2059
2060 FROM (
2061 SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
2062 ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
2063 pg_namespace n,
2064 pg_authid u_grantor,
2065 (
2066 SELECT oid, rolname FROM pg_authid
2067 UNION ALL
2068 SELECT 0::oid, 'PUBLIC'
2069 ) AS grantee (oid, rolname)
2070
2071 WHERE t.typnamespace = n.oid
2072 AND t.typtype = 'c'
2073 AND t.grantee = grantee.oid
2074 AND t.grantor = u_grantor.oid
2075 AND t.prtype IN ('USAGE')
2076 AND (pg_has_role(u_grantor.oid, 'USAGE')
2077 OR pg_has_role(grantee.oid, 'USAGE')
2078 OR grantee.rolname = 'PUBLIC');
2079
2080GRANT SELECT ON udt_privileges TO PUBLIC;
2081
2082
2083/*
2084 * 5.43
2085 * ROLE_UDT_GRANTS view
2086 */
2087
2088CREATE VIEW role_udt_grants AS
2089 SELECT grantor,
2090 grantee,
2091 udt_catalog,
2092 udt_schema,
2093 udt_name,
2094 privilege_type,
2095 is_grantable
2096 FROM udt_privileges
2097 WHERE grantor IN (SELECT role_name FROM enabled_roles)
2098 OR grantee IN (SELECT role_name FROM enabled_roles);
2099
2100GRANT SELECT ON role_udt_grants TO PUBLIC;
2101
2102
2103/*
2104 * 5.71
2105 * USAGE_PRIVILEGES view
2106 */
2107
2108CREATE VIEW usage_privileges AS
2109
2110 /* collations */
2111 -- Collations have no real privileges, so we represent all collations with implicit usage privilege here.
2112 SELECT CAST(u.rolname AS sql_identifier) AS grantor,
2113 CAST('PUBLIC' AS sql_identifier) AS grantee,
2114 CAST(current_database() AS sql_identifier) AS object_catalog,
2115 CAST(n.nspname AS sql_identifier) AS object_schema,
2116 CAST(c.collname AS sql_identifier) AS object_name,
2117 CAST('COLLATION' AS character_data) AS object_type,
2118 CAST('USAGE' AS character_data) AS privilege_type,
2119 CAST('NO' AS yes_or_no) AS is_grantable
2120
2121 FROM pg_authid u,
2122 pg_namespace n,
2123 pg_collation c
2124
2125 WHERE u.oid = c.collowner
2126 AND c.collnamespace = n.oid
2127 AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()))
2128
2129 UNION ALL
2130
2131 /* domains */
2132 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2133 CAST(grantee.rolname AS sql_identifier) AS grantee,
2134 CAST(current_database() AS sql_identifier) AS object_catalog,
2135 CAST(n.nspname AS sql_identifier) AS object_schema,
2136 CAST(t.typname AS sql_identifier) AS object_name,
2137 CAST('DOMAIN' AS character_data) AS object_type,
2138 CAST('USAGE' AS character_data) AS privilege_type,
2139 CAST(
2140 CASE WHEN
2141 -- object owner always has grant options
2142 pg_has_role(grantee.oid, t.typowner, 'USAGE')
2143 OR t.grantable
2144 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2145
2146 FROM (
2147 SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
2148 ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
2149 pg_namespace n,
2150 pg_authid u_grantor,
2151 (
2152 SELECT oid, rolname FROM pg_authid
2153 UNION ALL
2154 SELECT 0::oid, 'PUBLIC'
2155 ) AS grantee (oid, rolname)
2156
2157 WHERE t.typnamespace = n.oid
2158 AND t.typtype = 'd'
2159 AND t.grantee = grantee.oid
2160 AND t.grantor = u_grantor.oid
2161 AND t.prtype IN ('USAGE')
2162 AND (pg_has_role(u_grantor.oid, 'USAGE')
2163 OR pg_has_role(grantee.oid, 'USAGE')
2164 OR grantee.rolname = 'PUBLIC')
2165
2166 UNION ALL
2167
2168 /* foreign-data wrappers */
2169 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2170 CAST(grantee.rolname AS sql_identifier) AS grantee,
2171 CAST(current_database() AS sql_identifier) AS object_catalog,
2172 CAST('' AS sql_identifier) AS object_schema,
2173 CAST(fdw.fdwname AS sql_identifier) AS object_name,
2174 CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
2175 CAST('USAGE' AS character_data) AS privilege_type,
2176 CAST(
2177 CASE WHEN
2178 -- object owner always has grant options
2179 pg_has_role(grantee.oid, fdw.fdwowner, 'USAGE')
2180 OR fdw.grantable
2181 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2182
2183 FROM (
2184 SELECT fdwname, fdwowner, (aclexplode(coalesce(fdwacl, acldefault('F', fdwowner)))).* FROM pg_foreign_data_wrapper
2185 ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
2186 pg_authid u_grantor,
2187 (
2188 SELECT oid, rolname FROM pg_authid
2189 UNION ALL
2190 SELECT 0::oid, 'PUBLIC'
2191 ) AS grantee (oid, rolname)
2192
2193 WHERE u_grantor.oid = fdw.grantor
2194 AND grantee.oid = fdw.grantee
2195 AND fdw.prtype IN ('USAGE')
2196 AND (pg_has_role(u_grantor.oid, 'USAGE')
2197 OR pg_has_role(grantee.oid, 'USAGE')
2198 OR grantee.rolname = 'PUBLIC')
2199
2200 UNION ALL
2201
2202 /* foreign servers */
2203 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2204 CAST(grantee.rolname AS sql_identifier) AS grantee,
2205 CAST(current_database() AS sql_identifier) AS object_catalog,
2206 CAST('' AS sql_identifier) AS object_schema,
2207 CAST(srv.srvname AS sql_identifier) AS object_name,
2208 CAST('FOREIGN SERVER' AS character_data) AS object_type,
2209 CAST('USAGE' AS character_data) AS privilege_type,
2210 CAST(
2211 CASE WHEN
2212 -- object owner always has grant options
2213 pg_has_role(grantee.oid, srv.srvowner, 'USAGE')
2214 OR srv.grantable
2215 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2216
2217 FROM (
2218 SELECT srvname, srvowner, (aclexplode(coalesce(srvacl, acldefault('S', srvowner)))).* FROM pg_foreign_server
2219 ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
2220 pg_authid u_grantor,
2221 (
2222 SELECT oid, rolname FROM pg_authid
2223 UNION ALL
2224 SELECT 0::oid, 'PUBLIC'
2225 ) AS grantee (oid, rolname)
2226
2227 WHERE u_grantor.oid = srv.grantor
2228 AND grantee.oid = srv.grantee
2229 AND srv.prtype IN ('USAGE')
2230 AND (pg_has_role(u_grantor.oid, 'USAGE')
2231 OR pg_has_role(grantee.oid, 'USAGE')
2232 OR grantee.rolname = 'PUBLIC')
2233
2234 UNION ALL
2235
2236 /* sequences */
2237 SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
2238 CAST(grantee.rolname AS sql_identifier) AS grantee,
2239 CAST(current_database() AS sql_identifier) AS object_catalog,
2240 CAST(n.nspname AS sql_identifier) AS object_schema,
2241 CAST(c.relname AS sql_identifier) AS object_name,
2242 CAST('SEQUENCE' AS character_data) AS object_type,
2243 CAST('USAGE' AS character_data) AS privilege_type,
2244 CAST(
2245 CASE WHEN
2246 -- object owner always has grant options
2247 pg_has_role(grantee.oid, c.relowner, 'USAGE')
2248 OR c.grantable
2249 THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
2250
2251 FROM (
2252 SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
2253 ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
2254 pg_namespace n,
2255 pg_authid u_grantor,
2256 (
2257 SELECT oid, rolname FROM pg_authid
2258 UNION ALL
2259 SELECT 0::oid, 'PUBLIC'
2260 ) AS grantee (oid, rolname)
2261
2262 WHERE c.relnamespace = n.oid
2263 AND c.relkind = 'S'
2264 AND c.grantee = grantee.oid
2265 AND c.grantor = u_grantor.oid
2266 AND c.prtype IN ('USAGE')
2267 AND (pg_has_role(u_grantor.oid, 'USAGE')
2268 OR pg_has_role(grantee.oid, 'USAGE')
2269 OR grantee.rolname = 'PUBLIC');
2270
2271GRANT SELECT ON usage_privileges TO PUBLIC;
2272
2273
2274/*
2275 * 5.42
2276 * ROLE_USAGE_GRANTS view
2277 */
2278
2279CREATE VIEW role_usage_grants AS
2280 SELECT grantor,
2281 grantee,
2282 object_catalog,
2283 object_schema,
2284 object_name,
2285 object_type,
2286 privilege_type,
2287 is_grantable
2288 FROM usage_privileges
2289 WHERE grantor IN (SELECT role_name FROM enabled_roles)
2290 OR grantee IN (SELECT role_name FROM enabled_roles);
2291
2292GRANT SELECT ON role_usage_grants TO PUBLIC;
2293
2294
2295/*
2296 * 5.72
2297 * USER_DEFINED_TYPES view
2298 */
2299
2300CREATE VIEW user_defined_types AS
2301 SELECT CAST(current_database() AS sql_identifier) AS user_defined_type_catalog,
2302 CAST(n.nspname AS sql_identifier) AS user_defined_type_schema,
2303 CAST(c.relname AS sql_identifier) AS user_defined_type_name,
2304 CAST('STRUCTURED' AS character_data) AS user_defined_type_category,
2305 CAST('YES' AS yes_or_no) AS is_instantiable,
2306 CAST(null AS yes_or_no) AS is_final,
2307 CAST(null AS character_data) AS ordering_form,
2308 CAST(null AS character_data) AS ordering_category,
2309 CAST(null AS sql_identifier) AS ordering_routine_catalog,
2310 CAST(null AS sql_identifier) AS ordering_routine_schema,
2311 CAST(null AS sql_identifier) AS ordering_routine_name,
2312 CAST(null AS character_data) AS reference_type,
2313 CAST(null AS character_data) AS data_type,
2314 CAST(null AS cardinal_number) AS character_maximum_length,
2315 CAST(null AS cardinal_number) AS character_octet_length,
2316 CAST(null AS sql_identifier) AS character_set_catalog,
2317 CAST(null AS sql_identifier) AS character_set_schema,
2318 CAST(null AS sql_identifier) AS character_set_name,
2319 CAST(null AS sql_identifier) AS collation_catalog,
2320 CAST(null AS sql_identifier) AS collation_schema,
2321 CAST(null AS sql_identifier) AS collation_name,
2322 CAST(null AS cardinal_number) AS numeric_precision,
2323 CAST(null AS cardinal_number) AS numeric_precision_radix,
2324 CAST(null AS cardinal_number) AS numeric_scale,
2325 CAST(null AS cardinal_number) AS datetime_precision,
2326 CAST(null AS character_data) AS interval_type,
2327 CAST(null AS cardinal_number) AS interval_precision,
2328 CAST(null AS sql_identifier) AS source_dtd_identifier,
2329 CAST(null AS sql_identifier) AS ref_dtd_identifier
2330
2331 FROM pg_namespace n, pg_class c, pg_type t
2332
2333 WHERE n.oid = c.relnamespace
2334 AND t.typrelid = c.oid
2335 AND c.relkind = 'c'
2336 AND (pg_has_role(t.typowner, 'USAGE')
2337 OR has_type_privilege(t.oid, 'USAGE'));
2338
2339GRANT SELECT ON user_defined_types TO PUBLIC;
2340
2341
2342/*
2343 * 5.73
2344 * VIEW_COLUMN_USAGE
2345 */
2346
2347CREATE VIEW view_column_usage AS
2348 SELECT DISTINCT
2349 CAST(current_database() AS sql_identifier) AS view_catalog,
2350 CAST(nv.nspname AS sql_identifier) AS view_schema,
2351 CAST(v.relname AS sql_identifier) AS view_name,
2352 CAST(current_database() AS sql_identifier) AS table_catalog,
2353 CAST(nt.nspname AS sql_identifier) AS table_schema,
2354 CAST(t.relname AS sql_identifier) AS table_name,
2355 CAST(a.attname AS sql_identifier) AS column_name
2356
2357 FROM pg_namespace nv, pg_class v, pg_depend dv,
2358 pg_depend dt, pg_class t, pg_namespace nt,
2359 pg_attribute a
2360
2361 WHERE nv.oid = v.relnamespace
2362 AND v.relkind = 'v'
2363 AND v.oid = dv.refobjid
2364 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2365 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2366 AND dv.deptype = 'i'
2367 AND dv.objid = dt.objid
2368 AND dv.refobjid <> dt.refobjid
2369 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2370 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2371 AND dt.refobjid = t.oid
2372 AND t.relnamespace = nt.oid
2373 AND t.relkind IN ('r', 'v', 'f')
2374 AND t.oid = a.attrelid
2375 AND dt.refobjsubid = a.attnum
2376 AND pg_has_role(t.relowner, 'USAGE');
2377
2378GRANT SELECT ON view_column_usage TO PUBLIC;
2379
2380
2381/*
2382 * 5.74
2383 * VIEW_ROUTINE_USAGE
2384 */
2385
2386CREATE VIEW view_routine_usage AS
2387 SELECT DISTINCT
2388 CAST(current_database() AS sql_identifier) AS table_catalog,
2389 CAST(nv.nspname AS sql_identifier) AS table_schema,
2390 CAST(v.relname AS sql_identifier) AS table_name,
2391 CAST(current_database() AS sql_identifier) AS specific_catalog,
2392 CAST(np.nspname AS sql_identifier) AS specific_schema,
2393 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
2394
2395 FROM pg_namespace nv, pg_class v, pg_depend dv,
2396 pg_depend dp, pg_proc p, pg_namespace np
2397
2398 WHERE nv.oid = v.relnamespace
2399 AND v.relkind = 'v'
2400 AND v.oid = dv.refobjid
2401 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2402 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2403 AND dv.deptype = 'i'
2404 AND dv.objid = dp.objid
2405 AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
2406 AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
2407 AND dp.refobjid = p.oid
2408 AND p.pronamespace = np.oid
2409 AND pg_has_role(p.proowner, 'USAGE');
2410
2411GRANT SELECT ON view_routine_usage TO PUBLIC;
2412
2413
2414/*
2415 * 5.75
2416 * VIEW_TABLE_USAGE
2417 */
2418
2419CREATE VIEW view_table_usage AS
2420 SELECT DISTINCT
2421 CAST(current_database() AS sql_identifier) AS view_catalog,
2422 CAST(nv.nspname AS sql_identifier) AS view_schema,
2423 CAST(v.relname AS sql_identifier) AS view_name,
2424 CAST(current_database() AS sql_identifier) AS table_catalog,
2425 CAST(nt.nspname AS sql_identifier) AS table_schema,
2426 CAST(t.relname AS sql_identifier) AS table_name
2427
2428 FROM pg_namespace nv, pg_class v, pg_depend dv,
2429 pg_depend dt, pg_class t, pg_namespace nt
2430
2431 WHERE nv.oid = v.relnamespace
2432 AND v.relkind = 'v'
2433 AND v.oid = dv.refobjid
2434 AND dv.refclassid = 'pg_catalog.pg_class'::regclass
2435 AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
2436 AND dv.deptype = 'i'
2437 AND dv.objid = dt.objid
2438 AND dv.refobjid <> dt.refobjid
2439 AND dt.classid = 'pg_catalog.pg_rewrite'::regclass
2440 AND dt.refclassid = 'pg_catalog.pg_class'::regclass
2441 AND dt.refobjid = t.oid
2442 AND t.relnamespace = nt.oid
2443 AND t.relkind IN ('r', 'v', 'f')
2444 AND pg_has_role(t.relowner, 'USAGE');
2445
2446GRANT SELECT ON view_table_usage TO PUBLIC;
2447
2448
2449/*
2450 * 5.76
2451 * VIEWS view
2452 */
2453
2454CREATE VIEW views AS
2455 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2456 CAST(nc.nspname AS sql_identifier) AS table_schema,
2457 CAST(c.relname AS sql_identifier) AS table_name,
2458
2459 CAST(
2460 CASE WHEN pg_has_role(c.relowner, 'USAGE')
2461 THEN pg_get_viewdef(c.oid)
2462 ELSE null END
2463 AS character_data) AS view_definition,
2464
2465 CAST('NONE' AS character_data) AS check_option,
2466
2467 CAST(
2468 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '2' AND is_instead)
2469 AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '4' AND is_instead)
2470 THEN 'YES' ELSE 'NO' END
2471 AS yes_or_no) AS is_updatable,
2472
2473 CAST(
2474 CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = '3' AND is_instead)
2475 THEN 'YES' ELSE 'NO' END
2476 AS yes_or_no) AS is_insertable_into,
2477
2478 CAST(
2479 -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_UPDATE
2480 CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81)
2481 THEN 'YES' ELSE 'NO' END
2482 AS yes_or_no) AS is_trigger_updatable,
2483
2484 CAST(
2485 -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_DELETE
2486 CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73)
2487 THEN 'YES' ELSE 'NO' END
2488 AS yes_or_no) AS is_trigger_deletable,
2489
2490 CAST(
2491 -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_INSERT
2492 CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69)
2493 THEN 'YES' ELSE 'NO' END
2494 AS yes_or_no) AS is_trigger_insertable_into
2495
2496 FROM pg_namespace nc, pg_class c
2497
2498 WHERE c.relnamespace = nc.oid
2499 AND c.relkind = 'v'
2500 AND (NOT pg_is_other_temp_schema(nc.oid))
2501 AND (pg_has_role(c.relowner, 'USAGE')
2502 OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2503 OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
2504
2505GRANT SELECT ON views TO PUBLIC;
2506
2507
2508-- The following views have dependencies that force them to appear out of order.
2509
2510/*
2511 * 5.24
2512 * DATA_TYPE_PRIVILEGES view
2513 */
2514
2515CREATE VIEW data_type_privileges AS
2516 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2517 CAST(x.objschema AS sql_identifier) AS object_schema,
2518 CAST(x.objname AS sql_identifier) AS object_name,
2519 CAST(x.objtype AS character_data) AS object_type,
2520 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
2521
2522 FROM
2523 (
2524 SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
2525 UNION ALL
2526 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
2527 UNION ALL
2528 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
2529 UNION ALL
2530 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
2531 UNION ALL
2532 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
2533 ) AS x (objschema, objname, objtype, objdtdid);
2534
2535GRANT SELECT ON data_type_privileges TO PUBLIC;
2536
2537
2538/*
2539 * 5.29
2540 * ELEMENT_TYPES view
2541 */
2542
2543CREATE VIEW element_types AS
2544 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
2545 CAST(n.nspname AS sql_identifier) AS object_schema,
2546 CAST(x.objname AS sql_identifier) AS object_name,
2547 CAST(x.objtype AS character_data) AS object_type,
2548 CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
2549 CAST(
2550 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
2551 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
2552
2553 CAST(null AS cardinal_number) AS character_maximum_length,
2554 CAST(null AS cardinal_number) AS character_octet_length,
2555 CAST(null AS sql_identifier) AS character_set_catalog,
2556 CAST(null AS sql_identifier) AS character_set_schema,
2557 CAST(null AS sql_identifier) AS character_set_name,
2558 CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
2559 CAST(nco.nspname AS sql_identifier) AS collation_schema,
2560 CAST(co.collname AS sql_identifier) AS collation_name,
2561 CAST(null AS cardinal_number) AS numeric_precision,
2562 CAST(null AS cardinal_number) AS numeric_precision_radix,
2563 CAST(null AS cardinal_number) AS numeric_scale,
2564 CAST(null AS cardinal_number) AS datetime_precision,
2565 CAST(null AS character_data) AS interval_type,
2566 CAST(null AS cardinal_number) AS interval_precision,
2567
2568 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
2569
2570 CAST(current_database() AS sql_identifier) AS udt_catalog,
2571 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
2572 CAST(bt.typname AS sql_identifier) AS udt_name,
2573
2574 CAST(null AS sql_identifier) AS scope_catalog,
2575 CAST(null AS sql_identifier) AS scope_schema,
2576 CAST(null AS sql_identifier) AS scope_name,
2577
2578 CAST(null AS cardinal_number) AS maximum_cardinality,
2579 CAST('a' || CAST(x.objdtdid AS text) AS sql_identifier) AS dtd_identifier
2580
2581 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
2582 (
2583 /* columns, attributes */
2584 SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
2585 CASE WHEN c.relkind = 'c' THEN 'USER-DEFINED TYPE'::text ELSE 'TABLE'::text END,
2586 a.attnum, a.atttypid, a.attcollation
2587 FROM pg_class c, pg_attribute a
2588 WHERE c.oid = a.attrelid
2589 AND c.relkind IN ('r', 'v', 'f', 'c')
2590 AND attnum > 0 AND NOT attisdropped
2591
2592 UNION ALL
2593
2594 /* domains */
2595 SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
2596 'DOMAIN'::text, 1, t.typbasetype, t.typcollation
2597 FROM pg_type t
2598 WHERE t.typtype = 'd'
2599
2600 UNION ALL
2601
2602 /* parameters */
2603 SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
2604 'ROUTINE'::text, (ss.x).n, (ss.x).x, 0
2605 FROM (SELECT p.pronamespace, p.proname, p.oid,
2606 _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
2607 FROM pg_proc p) AS ss
2608
2609 UNION ALL
2610
2611 /* result types */
2612 SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
2613 'ROUTINE'::text, 0, p.prorettype, 0
2614 FROM pg_proc p
2615
2616 ) AS x (objschema, objname, objtype, objdtdid, objtypeid, objcollation)
2617 LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
2618 ON x.objcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
2619
2620 WHERE n.oid = x.objschema
2621 AND at.oid = x.objtypeid
2622 AND (at.typelem <> 0 AND at.typlen = -1)
2623 AND at.typelem = bt.oid
2624 AND nbt.oid = bt.typnamespace
2625
2626 AND (n.nspname, x.objname, x.objtype, CAST(x.objdtdid AS sql_identifier)) IN
2627 ( SELECT object_schema, object_name, object_type, dtd_identifier
2628 FROM data_type_privileges );
2629
2630GRANT SELECT ON element_types TO PUBLIC;
2631
2632
2633-- SQL/MED views; these use section numbers from part 9 of the standard.
2634
2635/* Base view for foreign table columns */
2636CREATE VIEW _pg_foreign_table_columns AS
2637 SELECT n.nspname,
2638 c.relname,
2639 a.attname,
2640 a.attfdwoptions
2641 FROM pg_foreign_table t, pg_authid u, pg_namespace n, pg_class c,
2642 pg_attribute a
2643 WHERE u.oid = c.relowner
2644 AND (pg_has_role(c.relowner, 'USAGE')
2645 OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'))
2646 AND n.oid = c.relnamespace
2647 AND c.oid = t.ftrelid
2648 AND c.relkind = 'f'
2649 AND a.attrelid = c.oid
2650 AND a.attnum > 0;
2651
2652/*
2653 * 24.2
2654 * COLUMN_OPTIONS view
2655 */
2656CREATE VIEW column_options AS
2657 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
2658 c.nspname AS table_schema,
2659 c.relname AS table_name,
2660 c.attname AS column_name,
2661 CAST((pg_options_to_table(c.attfdwoptions)).option_name AS sql_identifier) AS option_name,
2662 CAST((pg_options_to_table(c.attfdwoptions)).option_value AS character_data) AS option_value
2663 FROM _pg_foreign_table_columns c;
2664
2665GRANT SELECT ON column_options TO PUBLIC;
2666
2667
2668/* Base view for foreign-data wrappers */
2669CREATE VIEW _pg_foreign_data_wrappers AS
2670 SELECT w.oid,
2671 w.fdwowner,
2672 w.fdwoptions,
2673 CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2674 CAST(fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2675 CAST(u.rolname AS sql_identifier) AS authorization_identifier,
2676 CAST('c' AS character_data) AS foreign_data_wrapper_language
2677 FROM pg_foreign_data_wrapper w, pg_authid u
2678 WHERE u.oid = w.fdwowner
2679 AND (pg_has_role(fdwowner, 'USAGE')
2680 OR has_foreign_data_wrapper_privilege(w.oid, 'USAGE'));
2681
2682
2683/*
2684 * 24.4
2685 * FOREIGN_DATA_WRAPPER_OPTIONS view
2686 */
2687CREATE VIEW foreign_data_wrapper_options AS
2688 SELECT foreign_data_wrapper_catalog,
2689 foreign_data_wrapper_name,
2690 CAST((pg_options_to_table(w.fdwoptions)).option_name AS sql_identifier) AS option_name,
2691 CAST((pg_options_to_table(w.fdwoptions)).option_value AS character_data) AS option_value
2692 FROM _pg_foreign_data_wrappers w;
2693
2694GRANT SELECT ON foreign_data_wrapper_options TO PUBLIC;
2695
2696
2697/*
2698 * 24.5
2699 * FOREIGN_DATA_WRAPPERS view
2700 */
2701CREATE VIEW foreign_data_wrappers AS
2702 SELECT foreign_data_wrapper_catalog,
2703 foreign_data_wrapper_name,
2704 authorization_identifier,
2705 CAST(NULL AS character_data) AS library_name,
2706 foreign_data_wrapper_language
2707 FROM _pg_foreign_data_wrappers w;
2708
2709GRANT SELECT ON foreign_data_wrappers TO PUBLIC;
2710
2711
2712/* Base view for foreign servers */
2713CREATE VIEW _pg_foreign_servers AS
2714 SELECT s.oid,
2715 s.srvoptions,
2716 CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2717 CAST(srvname AS sql_identifier) AS foreign_server_name,
2718 CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
2719 CAST(w.fdwname AS sql_identifier) AS foreign_data_wrapper_name,
2720 CAST(srvtype AS character_data) AS foreign_server_type,
2721 CAST(srvversion AS character_data) AS foreign_server_version,
2722 CAST(u.rolname AS sql_identifier) AS authorization_identifier
2723 FROM pg_foreign_server s, pg_foreign_data_wrapper w, pg_authid u
2724 WHERE w.oid = s.srvfdw
2725 AND u.oid = s.srvowner
2726 AND (pg_has_role(s.srvowner, 'USAGE')
2727 OR has_server_privilege(s.oid, 'USAGE'));
2728
2729
2730/*
2731 * 24.6
2732 * FOREIGN_SERVER_OPTIONS view
2733 */
2734CREATE VIEW foreign_server_options AS
2735 SELECT foreign_server_catalog,
2736 foreign_server_name,
2737 CAST((pg_options_to_table(s.srvoptions)).option_name AS sql_identifier) AS option_name,
2738 CAST((pg_options_to_table(s.srvoptions)).option_value AS character_data) AS option_value
2739 FROM _pg_foreign_servers s;
2740
2741GRANT SELECT ON TABLE foreign_server_options TO PUBLIC;
2742
2743
2744/*
2745 * 24.7
2746 * FOREIGN_SERVERS view
2747 */
2748CREATE VIEW foreign_servers AS
2749 SELECT foreign_server_catalog,
2750 foreign_server_name,
2751 foreign_data_wrapper_catalog,
2752 foreign_data_wrapper_name,
2753 foreign_server_type,
2754 foreign_server_version,
2755 authorization_identifier
2756 FROM _pg_foreign_servers;
2757
2758GRANT SELECT ON foreign_servers TO PUBLIC;
2759
2760
2761/* Base view for foreign tables */
2762CREATE VIEW _pg_foreign_tables AS
2763 SELECT
2764 CAST(current_database() AS sql_identifier) AS foreign_table_catalog,
2765 n.nspname AS foreign_table_schema,
2766 c.relname AS foreign_table_name,
2767 t.ftoptions AS ftoptions,
2768 CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
2769 CAST(srvname AS sql_identifier) AS foreign_server_name,
2770 CAST(u.rolname AS sql_identifier) AS authorization_identifier
2771 FROM pg_foreign_table t, pg_foreign_server s, pg_foreign_data_wrapper w,
2772 pg_authid u, pg_namespace n, pg_class c
2773 WHERE w.oid = s.srvfdw
2774 AND u.oid = c.relowner
2775 AND (pg_has_role(c.relowner, 'USAGE')
2776 OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
2777 OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'))
2778 AND n.oid = c.relnamespace
2779 AND c.oid = t.ftrelid
2780 AND c.relkind = 'f'
2781 AND s.oid = t.ftserver;
2782
2783
2784/*
2785 * 24.8
2786 * FOREIGN_TABLE_OPTIONS view
2787 */
2788CREATE VIEW foreign_table_options AS
2789 SELECT foreign_table_catalog,
2790 foreign_table_schema,
2791 foreign_table_name,
2792 CAST((pg_options_to_table(t.ftoptions)).option_name AS sql_identifier) AS option_name,
2793 CAST((pg_options_to_table(t.ftoptions)).option_value AS character_data) AS option_value
2794 FROM _pg_foreign_tables t;
2795
2796GRANT SELECT ON TABLE foreign_table_options TO PUBLIC;
2797
2798
2799/*
2800 * 24.9
2801 * FOREIGN_TABLES view
2802 */
2803CREATE VIEW foreign_tables AS
2804 SELECT foreign_table_catalog,
2805 foreign_table_schema,
2806 foreign_table_name,
2807 foreign_server_catalog,
2808 foreign_server_name
2809 FROM _pg_foreign_tables;
2810
2811GRANT SELECT ON foreign_tables TO PUBLIC;
2812
2813
2814
2815/* Base view for user mappings */
2816CREATE VIEW _pg_user_mappings AS
2817 SELECT um.oid,
2818 um.umoptions,
2819 um.umuser,
2820 CAST(COALESCE(u.rolname,'PUBLIC') AS sql_identifier ) AS authorization_identifier,
2821 s.foreign_server_catalog,
2822 s.foreign_server_name,
2823 s.authorization_identifier AS srvowner
2824 FROM pg_user_mapping um LEFT JOIN pg_authid u ON (u.oid = um.umuser),
2825 _pg_foreign_servers s
2826 WHERE s.oid = um.umserver;
2827
2828
2829/*
2830 * 24.12
2831 * USER_MAPPING_OPTIONS view
2832 */
2833CREATE VIEW user_mapping_options AS
2834 SELECT authorization_identifier,
2835 foreign_server_catalog,
2836 foreign_server_name,
2837 CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name,
2838 CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user)
2839 OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE'))
2840 OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) THEN (pg_options_to_table(um.umoptions)).option_value
2841 ELSE NULL END AS character_data) AS option_value
2842 FROM _pg_user_mappings um;
2843
2844GRANT SELECT ON user_mapping_options TO PUBLIC;
2845
2846
2847/*
2848 * 24.13
2849 * USER_MAPPINGS view
2850 */
2851CREATE VIEW user_mappings AS
2852 SELECT authorization_identifier,
2853 foreign_server_catalog,
2854 foreign_server_name
2855 FROM _pg_user_mappings;
2856
2857GRANT SELECT ON user_mappings TO PUBLIC;