2 * SQL Information Schema
3 * as defined in ISO 9075-2:1999 chapter 20
5 * Copyright 2003, PostgreSQL Global Development Group
7 * $Id: information_schema.sql,v 1.11 2003/06/29 10:18:26 petere Exp $
11 * Note: Generally, the definitions in this file should be ordered
12 * according to the clause numbers in the SQL standard, which is also the
13 * alphabetical order. In some cases it is convenient or necessary to
14 * define one information schema view by using another one; in that case,
15 * put the referencing view at the very end and leave a note where it
16 * should have been put.
22 * INFORMATION_SCHEMA schema
25 CREATE SCHEMA information_schema;
26 GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
27 SET search_path TO information_schema, public;
30 -- 20.3 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
35 * CARDINAL_NUMBER domain
38 CREATE DOMAIN cardinal_number AS integer
39 CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);
44 * CHARACTER_DATA domain
47 CREATE DOMAIN character_data AS character varying;
52 * SQL_IDENTIFIER domain
55 CREATE DOMAIN sql_identifier AS character varying;
60 * INFORMATION_SCHEMA_CATALOG_NAME view
63 CREATE VIEW information_schema_catalog_name AS
64 SELECT CAST(current_database() AS sql_identifier) AS catalog_name;
66 GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
74 CREATE DOMAIN time_stamp AS timestamp(2)
75 DEFAULT current_timestamp(2);
80 * CHECK_CONSTRAINTS view
83 CREATE VIEW check_constraints AS
84 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
85 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
86 CAST(con.conname AS sql_identifier) AS constraint_name,
87 CAST(con.consrc AS character_data) AS check_clause
90 LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid)
91 LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid),
93 WHERE rs.oid = con.connamespace
94 AND u.usesysid = coalesce(c.relowner, t.typowner)
95 AND u.usename = current_user
99 GRANT SELECT ON check_constraints TO PUBLIC;
104 * COLUMN_DOMAIN_USAGE view
107 CREATE VIEW column_domain_usage AS
108 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
109 CAST(nt.nspname AS sql_identifier) AS domain_schema,
110 CAST(t.typname AS sql_identifier) AS domain_name,
111 CAST(current_database() AS sql_identifier) AS table_catalog,
112 CAST(nc.nspname AS sql_identifier) AS table_schema,
113 CAST(c.relname AS sql_identifier) AS table_name,
114 CAST(a.attname AS sql_identifier) AS column_name
116 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
117 pg_attribute a, pg_user u
119 WHERE t.typnamespace = nt.oid
120 AND c.relnamespace = nc.oid
121 AND a.attrelid = c.oid
122 AND a.atttypid = t.oid
123 AND t.typowner = u.usesysid
125 AND c.relkind IN ('r', 'v')
127 AND NOT a.attisdropped
128 AND u.usename = current_user;
130 GRANT SELECT ON column_domain_usage TO PUBLIC;
138 CREATE VIEW column_privileges AS
139 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
140 CAST(u_grantee.usename AS sql_identifier) AS grantee,
141 CAST(current_database() AS sql_identifier) AS table_catalog,
142 CAST(nc.nspname AS sql_identifier) AS table_schema,
143 CAST(c.relname AS sql_identifier) AS table_name,
144 CAST(a.attname AS sql_identifier) AS column_name,
145 CAST(pr.type AS character_data) AS privilege_type,
147 CASE WHEN aclcontains(c.relacl,
148 makeaclitem(u_grantee.usesysid, 0, u_grantor.usesysid, pr.type, true))
149 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
155 (SELECT usesysid, usename FROM pg_user UNION SELECT 0, 'PUBLIC') AS u_grantee,
156 (SELECT 'SELECT' UNION SELECT 'DELETE' UNION SELECT 'INSERT' UNION SELECT 'UPDATE'
157 UNION SELECT 'REFERENCES' UNION SELECT 'TRIGGER') AS pr (type)
159 WHERE a.attrelid = c.oid
160 AND c.relnamespace = nc.oid
162 AND NOT a.attisdropped
163 AND c.relkind IN ('r', 'v')
164 AND aclcontains(c.relacl,
165 makeaclitem(u_grantee.usesysid, 0, u_grantor.usesysid, pr.type, false))
166 AND (u_grantor.usename = current_user
167 OR u_grantee.usename = current_user
168 OR u_grantee.usename = 'PUBLIC');
170 GRANT SELECT ON column_privileges TO PUBLIC;
175 * COLUMN_UDT_USAGE view
178 CREATE VIEW column_udt_usage AS
179 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
180 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
181 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
182 CAST(current_database() AS sql_identifier) AS table_catalog,
183 CAST(nc.nspname AS sql_identifier) AS table_schema,
184 CAST(c.relname AS sql_identifier) AS table_name,
185 CAST(a.attname AS sql_identifier) AS column_name
187 FROM pg_attribute a, pg_class c, pg_namespace nc, pg_user u,
188 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
189 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
190 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
192 WHERE a.attrelid = c.oid
193 AND a.atttypid = t.oid
194 AND u.usesysid = coalesce(bt.typowner, t.typowner)
195 AND nc.oid = c.relnamespace
196 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
197 AND u.usename = current_user;
199 GRANT SELECT ON column_udt_usage TO PUBLIC;
207 CREATE VIEW columns AS
208 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
209 CAST(nc.nspname AS sql_identifier) AS table_schema,
210 CAST(c.relname AS sql_identifier) AS table_name,
211 CAST(a.attname AS sql_identifier) AS column_name,
212 CAST(a.attnum AS cardinal_number) AS ordinal_position,
214 CASE WHEN u.usename = current_user THEN a.adsrc ELSE null END
217 CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
222 CASE WHEN t.typtype = 'd' THEN
223 CASE WHEN bt.typelem <> 0 AND bt.typlen = -1 THEN 'ARRAY'
224 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
225 ELSE 'USER-DEFINED' END
227 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
228 WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
229 ELSE 'USER-DEFINED' END
235 CASE WHEN t.typtype = 'd' THEN
236 CASE WHEN t.typbasetype IN (25, 1042, 1043, 1560, 1562) AND t.typtypmod <> -1
240 CASE WHEN a.atttypid IN (25, 1042, 1043, 1560, 1562) AND a.atttypmod <> -1
245 AS character_maximum_length,
248 CASE WHEN t.typtype = 'd' THEN
249 CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END
251 CASE WHEN a.atttypid IN (25, 1042, 1043) THEN 2^30 ELSE null END
254 AS character_octet_length,
257 CASE (CASE WHEN t.typtype = 'd' THEN t.typbasetype ELSE a.atttypid END)
258 WHEN 21 /*int2*/ THEN 16
259 WHEN 23 /*int4*/ THEN 32
260 WHEN 20 /*int8*/ THEN 64
261 WHEN 1700 /*numeric*/ THEN ((CASE WHEN t.typtype = 'd' THEN t.typtypmod ELSE a.atttypmod END - 4) >> 16) & 65535
262 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
263 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
266 AS numeric_precision,
269 CASE WHEN t.typtype = 'd' THEN
270 CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2
271 WHEN t.typbasetype IN (1700) THEN 10
274 CASE WHEN a.atttypid IN (21, 23, 20, 700, 701) THEN 2
275 WHEN a.atttypid IN (1700) THEN 10
279 AS numeric_precision_radix,
282 CASE WHEN t.typtype = 'd' THEN
283 CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0
284 WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535
287 CASE WHEN a.atttypid IN (21, 23, 20) THEN 0
288 WHEN a.atttypid IN (1700) THEN (a.atttypmod - 4) & 65535
295 CASE WHEN t.typtype = 'd' THEN
296 CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266)
297 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END)
298 WHEN t.typbasetype IN (1186)
299 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END)
302 CASE WHEN a.atttypid IN (1083, 1114, 1184, 1266)
303 THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod ELSE null END)
304 WHEN a.atttypid IN (1186)
305 THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod & 65535 ELSE null END)
309 AS datetime_precision,
311 CAST(null AS character_data) AS interval_type, -- XXX
312 CAST(null AS character_data) AS interval_precision, -- XXX
314 CAST(null AS sql_identifier) AS character_set_catalog,
315 CAST(null AS sql_identifier) AS character_set_schema,
316 CAST(null AS sql_identifier) AS character_set_name,
318 CAST(null AS sql_identifier) AS collation_catalog,
319 CAST(null AS sql_identifier) AS collation_schema,
320 CAST(null AS sql_identifier) AS collation_name,
322 CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
323 AS sql_identifier) AS domain_catalog,
324 CAST(CASE WHEN t.typtype = 'd' THEN nt.nspname ELSE null END
325 AS sql_identifier) AS domain_schema,
326 CAST(CASE WHEN t.typtype = 'd' THEN t.typname ELSE null END
327 AS sql_identifier) AS domain_name,
329 CAST(current_database() AS sql_identifier) AS udt_catalog,
330 CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema,
331 CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name,
333 CAST(null AS sql_identifier) AS scope_catalog,
334 CAST(null AS sql_identifier) AS scope_schema,
335 CAST(null AS sql_identifier) AS scope_name,
337 CAST(null AS cardinal_number) AS maximum_cardinality,
338 CAST(a.attnum AS sql_identifier) AS dtd_identifier,
339 CAST('NO' AS character_data) AS is_self_referencing
341 FROM (pg_attribute LEFT JOIN pg_attrdef ON attrelid = adrelid AND attnum = adnum) AS a,
342 pg_class c, pg_namespace nc, pg_user u,
343 (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid))
344 LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
345 ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
347 WHERE a.attrelid = c.oid
348 AND a.atttypid = t.oid
349 AND u.usesysid = c.relowner
350 AND nc.oid = c.relnamespace
352 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
354 AND (u.usename = current_user
355 OR has_table_privilege(c.oid, 'SELECT')
356 OR has_table_privilege(c.oid, 'INSERT')
357 OR has_table_privilege(c.oid, 'UPDATE')
358 OR has_table_privilege(c.oid, 'DELETE')
359 OR has_table_privilege(c.oid, 'RULE')
360 OR has_table_privilege(c.oid, 'RERERENCES')
361 OR has_table_privilege(c.oid, 'TRIGGER') );
363 GRANT SELECT ON columns TO PUBLIC;
368 * CONSTRAINT_COLUMN_USAGE view
371 CREATE VIEW constraint_column_usage AS
372 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
373 CAST(tblschema AS sql_identifier) AS table_schema,
374 CAST(tblname AS sql_identifier) AS table_name,
375 CAST(colname AS sql_identifier) AS column_name,
376 CAST(current_database() AS sql_identifier) AS constraint_catalog,
377 CAST(cstrschema AS sql_identifier) AS constraint_schema,
378 CAST(cstrname AS sql_identifier) AS constraint_name
381 /* check constraints */
382 SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
383 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c
384 WHERE nr.oid = r.relnamespace
385 AND r.oid = a.attrelid
386 AND d.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class')
387 AND d.refobjid = r.oid
388 AND d.refobjsubid = a.attnum
389 AND d.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_constraint')
391 AND c.connamespace = nc.oid
395 AND NOT a.attisdropped
399 /* unique/primary key/foreign key constraints */
400 SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname
401 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c,
402 (select 1 union select 2 union select 3 union select 4 union select 5 union
403 select 6 union select 7 union select 8 union select 9 union select 10 union
404 select 11 union select 12 union select 13 union select 14 union select 15 union
405 select 16 union select 17 union select 18 union select 19 union select 20 union
406 select 21 union select 22 union select 23 union select 24 union select 25 union
407 select 26 union select 27 union select 28 union select 29 union select 30 union
408 select 31 union select 32) AS pos(n)
409 WHERE nr.oid = r.relnamespace
410 AND r.oid = a.attrelid
411 AND r.oid = c.conrelid
412 AND nc.oid = c.connamespace
413 AND (CASE WHEN c.contype = 'f' THEN c.confkey[pos.n] = a.attnum
414 ELSE c.conkey[pos.n] = a.attnum END)
416 AND NOT a.attisdropped
417 AND c.contype IN ('p', 'u', 'f')
420 ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname),
423 WHERE x.tblowner = u.usesysid AND u.usename = current_user;
425 GRANT SELECT ON constraint_column_usage TO PUBLIC;
430 * CONSTRAINT_TABLE_USAGE view
433 CREATE VIEW constraint_table_usage AS
434 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
435 CAST(nr.nspname AS sql_identifier) AS table_schema,
436 CAST(r.relname AS sql_identifier) AS table_name,
437 CAST(current_database() AS sql_identifier) AS constraint_catalog,
438 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
439 CAST(c.conname AS sql_identifier) AS constraint_name
441 FROM pg_constraint c, pg_namespace nc,
442 pg_class r, pg_namespace nr,
445 WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid
446 AND ( (c.contype = 'f' AND c.confrelid = r.oid)
447 OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) )
449 AND r.relowner = u.usesysid AND u.usename = current_user;
451 GRANT SELECT ON constraint_table_usage TO PUBLIC;
454 -- 20.21 DATA_TYPE_PRIVILEGES view appears later.
459 * DOMAIN_CONSTRAINTS view
462 CREATE VIEW domain_constraints AS
463 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
464 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
465 CAST(con.conname AS sql_identifier) AS constraint_name,
466 CAST(current_database() AS sql_identifier) AS domain_catalog,
467 CAST(n.nspname AS sql_identifier) AS domain_schema,
468 CAST(t.typname AS sql_identifier) AS domain_name,
469 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
470 AS character_data) AS is_deferrable,
471 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
472 AS character_data) AS initially_deferred
473 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t, pg_user u
474 WHERE rs.oid = con.connamespace
475 AND n.oid = t.typnamespace
476 AND u.usesysid = t.typowner
477 AND u.usename = current_user
478 AND t.oid = con.contypid;
480 GRANT SELECT ON domain_constraints TO PUBLIC;
485 * DOMAIN_UDT_USAGE view
488 CREATE VIEW domain_udt_usage AS
489 SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
490 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
491 CAST(bt.typname AS sql_identifier) AS udt_name,
492 CAST(current_database() AS sql_identifier) AS domain_catalog,
493 CAST(nt.nspname AS sql_identifier) AS domain_schema,
494 CAST(t.typname AS sql_identifier) AS domain_name
496 FROM pg_type t, pg_namespace nt,
497 pg_type bt, pg_namespace nbt,
500 WHERE t.typnamespace = nt.oid
501 AND t.typbasetype = bt.oid
502 AND bt.typnamespace = nbt.oid
504 AND bt.typowner = u.usesysid
505 AND u.usename = current_user;
507 GRANT SELECT ON domain_udt_usage TO PUBLIC;
515 CREATE VIEW domains AS
516 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
517 CAST(nt.nspname AS sql_identifier) AS domain_schema,
518 CAST(t.typname AS sql_identifier) AS domain_name,
521 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
522 WHEN nbt.nspname = 'pg_catalog' THEN format_type(t.typbasetype, null)
523 ELSE 'USER-DEFINED' END
528 CASE WHEN t.typbasetype IN (25, 1042, 1043, 1560, 1562) AND t.typtypmod <> -1
532 AS character_maximum_length,
535 CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END
537 AS character_octet_length,
539 CAST(null AS sql_identifier) AS character_set_catalog,
540 CAST(null AS sql_identifier) AS character_set_schema,
541 CAST(null AS sql_identifier) AS character_set_name,
543 CAST(null AS sql_identifier) AS collation_catalog,
544 CAST(null AS sql_identifier) AS collation_schema,
545 CAST(null AS sql_identifier) AS collation_name,
549 WHEN 21 /*int2*/ THEN 16
550 WHEN 23 /*int4*/ THEN 32
551 WHEN 20 /*int8*/ THEN 64
552 WHEN 1700 /*numeric*/ THEN ((t.typtypmod - 4) >> 16) & 65535
553 WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
554 WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
557 AS numeric_precision,
560 CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2
561 WHEN t.typbasetype IN (1700) THEN 10
564 AS numeric_precision_radix,
567 CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0
568 WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535
574 CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266)
575 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END)
576 WHEN t.typbasetype IN (1186)
577 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END)
580 AS datetime_precision,
582 CAST(null AS character_data) AS interval_type, -- XXX
583 CAST(null AS character_data) AS interval_precision, -- XXX
585 CAST(t.typdefault AS character_data) AS domain_default,
587 CAST(current_database() AS sql_identifier) AS udt_catalog,
588 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
589 CAST(bt.typname AS sql_identifier) AS udt_name,
591 CAST(null AS sql_identifier) AS scope_catalog,
592 CAST(null AS sql_identifier) AS scope_schema,
593 CAST(null AS sql_identifier) AS scope_name,
595 CAST(null AS cardinal_number) AS maximum_cardinality,
596 CAST(1 AS sql_identifier) AS dtd_identifier
598 FROM pg_type t, pg_namespace nt,
599 pg_type bt, pg_namespace nbt
601 WHERE t.typnamespace = nt.oid
602 AND t.typbasetype = bt.oid
603 AND bt.typnamespace = nbt.oid
606 GRANT SELECT ON domains TO PUBLIC;
609 -- 20.27 ELEMENT_TYPES view appears later.
614 * KEY_COLUMN_USAGE view
617 CREATE VIEW key_column_usage AS
618 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
619 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
620 CAST(c.conname AS sql_identifier) AS constraint_name,
621 CAST(current_database() AS sql_identifier) AS table_catalog,
622 CAST(nr.nspname AS sql_identifier) AS table_schema,
623 CAST(r.relname AS sql_identifier) AS table_name,
624 CAST(a.attname AS sql_identifier) AS column_name,
625 CAST(pos.n AS cardinal_number) AS ordinal_position
627 FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c,
629 (select 1 union select 2 union select 3 union select 4 union select 5 union
630 select 6 union select 7 union select 8 union select 9 union select 10 union
631 select 11 union select 12 union select 13 union select 14 union select 15 union
632 select 16 union select 17 union select 18 union select 19 union select 20 union
633 select 21 union select 22 union select 23 union select 24 union select 25 union
634 select 26 union select 27 union select 28 union select 29 union select 30 union
635 select 31 union select 32) AS pos(n)
637 WHERE nr.oid = r.relnamespace
638 AND r.oid = a.attrelid
639 AND r.oid = c.conrelid
640 AND nc.oid = c.connamespace
641 AND c.conkey[pos.n] = a.attnum
643 AND NOT a.attisdropped
644 AND c.contype IN ('p', 'u', 'f')
646 AND r.relowner = u.usesysid
647 AND u.usename = current_user;
649 GRANT SELECT ON key_column_usage TO PUBLIC;
657 CREATE VIEW parameters AS
658 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
659 CAST(n.nspname AS sql_identifier) AS specific_schema,
660 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
661 CAST(n + 1 AS cardinal_number) AS ordinal_position,
662 CAST('IN' AS character_data) AS parameter_mode,
663 CAST('NO' AS character_data) AS is_result,
664 CAST('NO' AS character_data) AS as_locator,
665 CAST(null AS sql_identifier) AS parameter_name,
667 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
668 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
669 ELSE 'USER-DEFINED' END AS character_data)
671 CAST(null AS cardinal_number) AS character_maximum_length,
672 CAST(null AS cardinal_number) AS character_octet_length,
673 CAST(null AS sql_identifier) AS character_set_catalog,
674 CAST(null AS sql_identifier) AS character_set_schema,
675 CAST(null AS sql_identifier) AS character_set_name,
676 CAST(null AS sql_identifier) AS collation_catalog,
677 CAST(null AS sql_identifier) AS collation_schema,
678 CAST(null AS sql_identifier) AS collation_name,
679 CAST(null AS cardinal_number) AS numeric_precision,
680 CAST(null AS cardinal_number) AS numeric_precision_radix,
681 CAST(null AS cardinal_number) AS numeric_scale,
682 CAST(null AS cardinal_number) AS datetime_precision,
683 CAST(null AS character_data) AS interval_type,
684 CAST(null AS character_data) AS interval_precision,
685 CAST(current_database() AS sql_identifier) AS udt_catalog,
686 CAST(nt.nspname AS sql_identifier) AS udt_schema,
687 CAST(t.typname AS sql_identifier) AS udt_name,
688 CAST(null AS sql_identifier) AS scope_catalog,
689 CAST(null AS sql_identifier) AS scope_schema,
690 CAST(null AS sql_identifier) AS scope_name,
691 CAST(null AS cardinal_number) AS maximum_cardinality,
692 CAST(n + 1 AS sql_identifier) AS dtd_identifier
694 FROM pg_namespace n, pg_proc p, pg_type t, pg_namespace nt, pg_user u,
695 (select 0 union select 1 union select 2 union select 3 union select 4 union
696 select 5 union select 6 union select 7 union select 8 union select 9 union
697 select 10 union select 11 union select 12 union select 13 union select 14 union
698 select 15 union select 16 union select 17 union select 18 union select 19 union
699 select 20 union select 21 union select 22 union select 23 union select 24 union
700 select 25 union select 26 union select 27 union select 28 union select 29 union
701 select 30 union select 31) AS pos(n)
703 WHERE n.oid = p.pronamespace AND p.pronargs > pos.n
704 AND p.proargtypes[n] = t.oid AND t.typnamespace = nt.oid
705 AND p.proowner = u.usesysid
706 AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE'));
708 GRANT SELECT ON parameters TO PUBLIC;
713 * REFERENTIAL_CONSTRAINTS view
716 CREATE VIEW referential_constraints AS
717 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
718 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
719 CAST(con.conname AS sql_identifier) AS constraint_name,
720 CAST(current_database() AS sql_identifier) AS unique_constraint_catalog,
721 CAST(npkc.nspname AS sql_identifier) AS unique_constraint_schema,
722 CAST(pkc.conname AS sql_identifier) AS unique_constraint_name,
725 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
726 WHEN 'p' THEN 'PARTIAL'
727 WHEN 'u' THEN 'NONE' END
728 AS character_data) AS match_option,
731 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
732 WHEN 'n' THEN 'SET NULL'
733 WHEN 'd' THEN 'SET DEFAULT'
734 WHEN 'r' THEN 'RESTRICT'
735 WHEN 'a' THEN 'NOACTION' END
736 AS character_data) AS update_rule,
739 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
740 WHEN 'n' THEN 'SET NULL'
741 WHEN 'd' THEN 'SET DEFAULT'
742 WHEN 'r' THEN 'RESTRICT'
743 WHEN 'a' THEN 'NOACTION' END
744 AS character_data) AS delete_rule
746 FROM pg_namespace ncon,
753 WHERE ncon.oid = con.connamespace
754 AND con.conrelid = c.oid
755 AND con.confkey = pkc.conkey
756 AND pkc.connamespace = npkc.oid
757 AND c.relowner = u.usesysid
759 AND u.usename = current_user;
761 GRANT SELECT ON referential_constraints TO PUBLIC;
766 * ROUTINE_PRIVILEGES view
769 CREATE VIEW routine_privileges AS
770 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
771 CAST(u_grantee.usename AS sql_identifier) AS grantee,
772 CAST(current_database() AS sql_identifier) AS specific_catalog,
773 CAST(n.nspname AS sql_identifier) AS specific_schema,
774 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
775 CAST(current_database() AS sql_identifier) AS routine_catalog,
776 CAST(n.nspname AS sql_identifier) AS routine_schema,
777 CAST(p.proname AS sql_identifier) AS routine_name,
778 CAST('EXECUTE' AS character_data) AS privilege_type,
780 CASE WHEN aclcontains(p.proacl,
781 makeaclitem(u_grantee.usesysid, 0, u_grantor.usesysid, 'EXECUTE', true))
782 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
787 (SELECT usesysid, usename FROM pg_user UNION SELECT 0, 'PUBLIC') AS u_grantee
789 WHERE p.pronamespace = n.oid
790 AND aclcontains(p.proacl,
791 makeaclitem(u_grantee.usesysid, 0, u_grantor.usesysid, 'EXECUTE', false))
792 AND (u_grantor.usename = current_user
793 OR u_grantee.usename = current_user
794 OR u_grantee.usename = 'PUBLIC');
796 GRANT SELECT ON routine_privileges TO PUBLIC;
804 CREATE VIEW routines AS
805 SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
806 CAST(n.nspname AS sql_identifier) AS specific_schema,
807 CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
808 CAST(current_database() AS sql_identifier) AS routine_catalog,
809 CAST(n.nspname AS sql_identifier) AS routine_schema,
810 CAST(p.proname AS sql_identifier) AS routine_name,
811 CAST('FUNCTION' AS character_data) AS routine_type,
812 CAST(null AS sql_identifier) AS module_catalog,
813 CAST(null AS sql_identifier) AS module_schema,
814 CAST(null AS sql_identifier) AS module_name,
815 CAST(null AS sql_identifier) AS udt_catalog,
816 CAST(null AS sql_identifier) AS udt_schema,
817 CAST(null AS sql_identifier) AS udt_name,
820 CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
821 WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
822 ELSE 'USER-DEFINED' END AS character_data)
824 CAST(null AS cardinal_number) AS character_maximum_length,
825 CAST(null AS cardinal_number) AS character_octet_length,
826 CAST(null AS sql_identifier) AS character_set_catalog,
827 CAST(null AS sql_identifier) AS character_set_schema,
828 CAST(null AS sql_identifier) AS character_set_name,
829 CAST(null AS sql_identifier) AS collation_catalog,
830 CAST(null AS sql_identifier) AS collation_schema,
831 CAST(null AS sql_identifier) AS collation_name,
832 CAST(null AS cardinal_number) AS numeric_precision,
833 CAST(null AS cardinal_number) AS numeric_precision_radix,
834 CAST(null AS cardinal_number) AS numeric_scale,
835 CAST(null AS cardinal_number) AS datetime_precision,
836 CAST(null AS character_data) AS interval_type,
837 CAST(null AS character_data) AS interval_precision,
838 CAST(current_database() AS sql_identifier) AS type_udt_catalog,
839 CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
840 CAST(t.typname AS sql_identifier) AS type_udt_name,
841 CAST(null AS sql_identifier) AS scope_catalog,
842 CAST(null AS sql_identifier) AS scope_schema,
843 CAST(null AS sql_identifier) AS scope_name,
844 CAST(null AS cardinal_number) AS maximum_cardinality,
845 CAST(0 AS sql_identifier) AS dtd_identifier,
847 CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
850 CASE WHEN u.usename = current_user THEN p.prosrc ELSE null END
851 AS character_data) AS routine_definition,
853 CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
854 AS character_data) AS external_name,
855 CAST(upper(l.lanname) AS character_data) AS external_language,
857 CAST('GENERAL' AS character_data) AS parameter_style,
858 CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic,
859 CAST('MODIFIES' AS character_data) AS sql_data_access,
860 CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call,
861 CAST(null AS character_data) AS sql_path,
862 CAST('YES' AS character_data) AS schema_level_routine,
863 CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
864 CAST(null AS character_data) AS is_user_defined_cast,
865 CAST(null AS character_data) AS is_implicitly_invocable,
866 CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
867 CAST(null AS sql_identifier) AS to_sql_specific_catalog,
868 CAST(null AS sql_identifier) AS to_sql_specific_schema,
869 CAST(null AS sql_identifier) AS to_sql_specific_name,
870 CAST('NO' AS character_data) AS as_locator
872 FROM pg_namespace n, pg_proc p, pg_language l, pg_user u,
873 pg_type t, pg_namespace nt
875 WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.proowner = u.usesysid
876 AND p.prorettype = t.oid AND t.typnamespace = nt.oid
877 AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE'));
879 GRANT SELECT ON routines TO PUBLIC;
887 CREATE VIEW schemata AS
888 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
889 CAST(n.nspname AS sql_identifier) AS schema_name,
890 CAST(u.usename AS sql_identifier) AS schema_owner,
891 CAST(null AS sql_identifier) AS default_character_set_catalog,
892 CAST(null AS sql_identifier) AS default_character_set_schema,
893 CAST(null AS sql_identifier) AS default_character_set_name,
894 CAST(null AS character_data) AS sql_path
895 FROM pg_namespace n, pg_user u
896 WHERE n.nspowner = u.usesysid AND u.usename = current_user;
898 GRANT SELECT ON schemata TO PUBLIC;
906 CREATE TABLE sql_features (
907 feature_id character_data,
908 feature_name character_data,
909 sub_feature_id character_data,
910 sub_feature_name character_data,
911 is_supported character_data,
912 is_verified_by character_data,
913 comments character_data
916 -- Will be filled with external data by initdb.
918 GRANT SELECT ON sql_features TO PUBLIC;
923 * SQL_IMPLEMENTATION_INFO table
926 -- Note: Implementation information items are defined in ISO 9075-3:1999,
929 CREATE TABLE sql_implementation_info (
930 implementation_info_id character_data,
931 implementation_info_name character_data,
932 integer_value cardinal_number,
933 character_value character_data,
934 comments character_data
937 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
938 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
939 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
940 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
941 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
942 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
943 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITED; user-settable');
944 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
945 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
946 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
947 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
948 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
950 GRANT SELECT ON sql_implementation_info TO PUBLIC;
955 * SQL_LANGUAGES table
958 CREATE TABLE sql_languages (
959 sql_language_source character_data,
960 sql_language_year character_data,
961 sql_language_conformance character_data,
962 sql_language_integrity character_data,
963 sql_language_implementation character_data,
964 sql_language_binding_style character_data,
965 sql_language_programming_language character_data
968 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
969 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
971 GRANT SELECT ON sql_languages TO PUBLIC;
979 CREATE TABLE sql_packages (
980 feature_id character_data,
981 feature_name character_data,
982 is_supported character_data,
983 is_verified_by character_data,
984 comments character_data
987 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
988 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
989 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
990 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
991 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
992 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
993 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
994 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
995 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
996 INSERT INTO sql_packages VALUES ('PKG009', 'SQL/MM support', 'NO', NULL, '');
998 GRANT SELECT ON sql_packages TO PUBLIC;
1006 -- Note: Sizing items are defined in ISO 9075-3:1999, clause 7.2.
1008 CREATE TABLE sql_sizing (
1009 sizing_id cardinal_number,
1010 sizing_name character_data,
1011 supported_value cardinal_number,
1012 comments character_data
1015 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
1016 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
1017 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
1018 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
1019 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 0, NULL);
1020 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
1021 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
1022 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
1023 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
1024 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
1025 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
1026 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
1027 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
1028 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
1029 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
1030 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
1031 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
1032 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
1033 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
1034 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
1035 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
1036 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
1037 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
1040 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
1041 comments = 'Might be less, depending on character set.'
1042 WHERE supported_value = 63;
1044 GRANT SELECT ON sql_sizing TO PUBLIC;
1049 * SQL_SIZING_PROFILES table
1052 -- The data in this table are defined by various profiles of SQL.
1053 -- Since we don't have any information about such profiles, we provide
1056 CREATE TABLE sql_sizing_profiles (
1057 sizing_id cardinal_number,
1058 sizing_name character_data,
1059 profile_id character_data,
1060 required_value cardinal_number,
1061 comments character_data
1064 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
1069 * TABLE_CONSTRAINTS view
1072 CREATE VIEW table_constraints AS
1073 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
1074 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
1075 CAST(c.conname AS sql_identifier) AS constraint_name,
1076 CAST(current_database() AS sql_identifier) AS table_catalog,
1077 CAST(nr.nspname AS sql_identifier) AS table_schema,
1078 CAST(r.relname AS sql_identifier) AS table_name,
1080 CASE c.contype WHEN 'c' THEN 'CHECK'
1081 WHEN 'f' THEN 'FOREIGN KEY'
1082 WHEN 'p' THEN 'PRIMARY KEY'
1083 WHEN 'u' THEN 'UNIQUE' END
1084 AS character_data) AS constraint_type,
1085 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
1087 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
1088 AS initially_deferred
1090 FROM pg_namespace nc,
1096 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
1097 AND c.conrelid = r.oid AND r.relowner = u.usesysid
1099 AND u.usename = current_user;
1101 -- FIMXE: Not-null constraints are missing here.
1103 GRANT SELECT ON table_constraints TO PUBLIC;
1108 * TABLE_PRIVILEGES view
1111 CREATE VIEW table_privileges AS
1112 SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
1113 CAST(u_grantee.usename AS sql_identifier) AS grantee,
1114 CAST(current_database() AS sql_identifier) AS table_catalog,
1115 CAST(nc.nspname AS sql_identifier) AS table_schema,
1116 CAST(c.relname AS sql_identifier) AS table_name,
1117 CAST(pr.type AS character_data) AS privilege_type,
1119 CASE WHEN aclcontains(c.relacl,
1120 makeaclitem(u_grantee.usesysid, 0, u_grantor.usesysid, pr.type, true))
1121 THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
1122 CAST('NO' AS character_data) AS with_hierarchy
1127 (SELECT usesysid, usename FROM pg_user UNION SELECT 0, 'PUBLIC') AS u_grantee,
1128 (SELECT 'SELECT' UNION SELECT 'DELETE' UNION SELECT 'INSERT' UNION SELECT 'UPDATE'
1129 UNION SELECT 'REFERENCES' UNION SELECT 'TRIGGER') AS pr (type)
1131 WHERE c.relnamespace = nc.oid
1132 AND c.relkind IN ('r', 'v')
1133 AND aclcontains(c.relacl,
1134 makeaclitem(u_grantee.usesysid, 0, u_grantor.usesysid, pr.type, false))
1135 AND (u_grantor.usename = current_user
1136 OR u_grantee.usename = current_user
1137 OR u_grantee.usename = 'PUBLIC');
1139 GRANT SELECT ON table_privileges TO PUBLIC;
1147 CREATE VIEW tables AS
1148 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1149 CAST(nc.nspname AS sql_identifier) AS table_schema,
1150 CAST(c.relname AS sql_identifier) AS table_name,
1153 CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY'
1154 WHEN c.relkind = 'r' THEN 'BASE TABLE'
1155 WHEN c.relkind = 'v' THEN 'VIEW'
1157 AS character_data) AS table_type,
1159 CAST(null AS sql_identifier) AS self_referencing_column_name,
1160 CAST(null AS character_data) AS reference_generation,
1162 CAST(null AS sql_identifier) AS user_defined_type_catalog,
1163 CAST(null AS sql_identifier) AS user_defined_type_schema,
1164 CAST(null AS sql_identifier) AS user_defined_name
1166 FROM pg_namespace nc, pg_class c, pg_user u
1168 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
1169 AND c.relkind IN ('r', 'v')
1170 AND (u.usename = current_user
1171 OR has_table_privilege(c.oid, 'SELECT')
1172 OR has_table_privilege(c.oid, 'INSERT')
1173 OR has_table_privilege(c.oid, 'UPDATE')
1174 OR has_table_privilege(c.oid, 'DELETE')
1175 OR has_table_privilege(c.oid, 'RULE')
1176 OR has_table_privilege(c.oid, 'RERERENCES')
1177 OR has_table_privilege(c.oid, 'TRIGGER') );
1179 GRANT SELECT ON tables TO PUBLIC;
1184 * TRIGGERED_UPDATE_COLUMNS view
1187 -- PostgreSQL doesn't allow the specification of individual triggered
1188 -- update columns, so this view is empty.
1190 CREATE VIEW triggered_update_columns AS
1191 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1192 CAST(null AS sql_identifier) AS trigger_schema,
1193 CAST(null AS sql_identifier) AS trigger_name,
1194 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1195 CAST(null AS sql_identifier) AS event_object_schema,
1196 CAST(null AS sql_identifier) AS event_object_table,
1197 CAST(null AS sql_identifier) AS event_object_column
1200 GRANT SELECT ON triggered_update_columns TO PUBLIC;
1208 CREATE VIEW triggers AS
1209 SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog,
1210 CAST(n.nspname AS sql_identifier) AS trigger_schema,
1211 CAST(t.tgname AS sql_identifier) AS trigger_name,
1212 CAST(em.text AS character_data) AS event_manipulation,
1213 CAST(current_database() AS sql_identifier) AS event_object_catalog,
1214 CAST(n.nspname AS sql_identifier) AS event_object_schema,
1215 CAST(c.relname AS sql_identifier) AS event_object_table,
1216 CAST(null AS cardinal_number) AS action_order,
1217 CAST(null AS character_data) AS action_condition,
1219 substring(pg_get_triggerdef(t.oid) from
1220 position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47)
1221 AS character_data) AS action_statement,
1223 CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END
1224 AS character_data) AS action_orientation,
1226 CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END
1227 AS character_data) AS condition_timing,
1228 CAST(null AS sql_identifier) AS condition_reference_old_table,
1229 CAST(null AS sql_identifier) AS condition_reference_new_table
1231 FROM pg_namespace n, pg_class c, pg_trigger t, pg_user u,
1232 (SELECT 4, 'INSERT' UNION SELECT 8, 'DELETE' UNION SELECT 16, 'UPDATE') AS em (num, text)
1234 WHERE n.oid = c.relnamespace
1235 AND c.oid = t.tgrelid
1236 AND c.relowner = u.usesysid
1237 AND t.tgtype & em.num <> 0
1238 AND NOT t.tgisconstraint
1239 AND u.usename = current_user;
1241 GRANT SELECT ON triggers TO PUBLIC;
1246 * USAGE_PRIVILEGES view
1249 -- Of the things currently implemented in PostgreSQL, usage privileges
1250 -- apply only to domains. Since domains have no real privileges, we
1251 -- represent all domains with implicit usage privilege here.
1253 CREATE VIEW usage_privileges AS
1254 SELECT CAST(u.usename AS sql_identifier) AS grantor,
1255 CAST('PUBLIC' AS sql_identifier) AS grantee,
1256 CAST(current_database() AS sql_identifier) AS object_catalog,
1257 CAST(n.nspname AS sql_identifier) AS object_schema,
1258 CAST(t.typname AS sql_identifier) AS object_name,
1259 CAST('DOMAIN' AS character_data) AS object_type,
1260 CAST('USAGE' AS character_data) AS privilege_type,
1261 CAST('NO' AS character_data) AS is_grantable
1267 WHERE u.usesysid = t.typowner
1268 AND t.typnamespace = n.oid
1269 AND t.typtype = 'd';
1271 GRANT SELECT ON usage_privileges TO PUBLIC;
1279 CREATE VIEW view_column_usage AS
1281 CAST(current_database() AS sql_identifier) AS view_catalog,
1282 CAST(nv.nspname AS sql_identifier) AS view_schema,
1283 CAST(v.relname AS sql_identifier) AS view_name,
1284 CAST(current_database() AS sql_identifier) AS table_catalog,
1285 CAST(nt.nspname AS sql_identifier) AS table_schema,
1286 CAST(t.relname AS sql_identifier) AS table_name,
1287 CAST(a.attname AS sql_identifier) AS column_name
1289 FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv,
1290 pg_depend dt, pg_class t, pg_namespace nt,
1291 pg_attribute a, pg_user u
1293 WHERE nv.oid = v.relnamespace
1295 AND v.oid = dv.refobjid
1296 AND dv.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class')
1297 AND dv.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_rewrite')
1298 AND dv.deptype = 'i'
1299 AND dv.objid = dt.objid
1300 AND dv.refobjid <> dt.refobjid
1301 AND dt.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_rewrite')
1302 AND dt.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class')
1303 AND dt.refobjid = t.oid
1304 AND t.relnamespace = nt.oid
1305 AND t.relkind IN ('r', 'v')
1306 AND t.oid = a.attrelid
1307 AND dt.refobjsubid = a.attnum
1308 AND t.relowner = u.usesysid AND u.usename = current_user;
1310 GRANT SELECT ON view_column_usage TO PUBLIC;
1318 CREATE VIEW view_table_usage AS
1320 CAST(current_database() AS sql_identifier) AS view_catalog,
1321 CAST(nv.nspname AS sql_identifier) AS view_schema,
1322 CAST(v.relname AS sql_identifier) AS view_name,
1323 CAST(current_database() AS sql_identifier) AS table_catalog,
1324 CAST(nt.nspname AS sql_identifier) AS table_schema,
1325 CAST(t.relname AS sql_identifier) AS table_name
1327 FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv,
1328 pg_depend dt, pg_class t, pg_namespace nt,
1331 WHERE nv.oid = v.relnamespace
1333 AND v.oid = dv.refobjid
1334 AND dv.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class')
1335 AND dv.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_rewrite')
1336 AND dv.deptype = 'i'
1337 AND dv.objid = dt.objid
1338 AND dv.refobjid <> dt.refobjid
1339 AND dt.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_rewrite')
1340 AND dt.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class')
1341 AND dt.refobjid = t.oid
1342 AND t.relnamespace = nt.oid
1343 AND t.relkind IN ('r', 'v')
1344 AND t.relowner = u.usesysid AND u.usename = current_user;
1346 GRANT SELECT ON view_table_usage TO PUBLIC;
1354 CREATE VIEW views AS
1355 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
1356 CAST(nc.nspname AS sql_identifier) AS table_schema,
1357 CAST(c.relname AS sql_identifier) AS table_name,
1360 CASE WHEN u.usename = current_user THEN pg_get_viewdef(c.oid)
1362 AS character_data) AS view_definition,
1364 CAST('NONE' AS character_data) AS check_option,
1365 CAST(null AS character_data) AS is_updatable, -- FIXME
1366 CAST(null AS character_data) AS is_insertable_into -- FIXME
1368 FROM pg_namespace nc, pg_class c, pg_user u
1370 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
1372 AND (u.usename = current_user
1373 OR has_table_privilege(c.oid, 'SELECT')
1374 OR has_table_privilege(c.oid, 'INSERT')
1375 OR has_table_privilege(c.oid, 'UPDATE')
1376 OR has_table_privilege(c.oid, 'DELETE')
1377 OR has_table_privilege(c.oid, 'RULE')
1378 OR has_table_privilege(c.oid, 'RERERENCES')
1379 OR has_table_privilege(c.oid, 'TRIGGER') );
1381 GRANT SELECT ON views TO PUBLIC;
1384 -- The following views have dependencies that force them to appear out of order.
1388 * DATA_TYPE_PRIVILEGES view
1391 CREATE VIEW data_type_privileges AS
1392 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
1393 CAST(x.objschema AS sql_identifier) AS object_schema,
1394 CAST(x.objname AS sql_identifier) AS object_name,
1395 CAST(x.objtype AS character_data) AS object_type,
1396 CAST(x.objdtdid AS sql_identifier) AS dtd_identifier
1400 SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
1402 SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
1404 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM parameters
1406 SELECT specific_schema, specific_name, 'ROUTINE'::text, dtd_identifier FROM routines
1407 ) AS x (objschema, objname, objtype, objdtdid);
1409 GRANT SELECT ON data_type_privileges TO PUBLIC;
1414 * ELEMENT_TYPES view
1417 CREATE VIEW element_types AS
1418 SELECT CAST(current_database() AS sql_identifier) AS object_catalog,
1419 CAST(n.nspname AS sql_identifier) AS object_schema,
1420 CAST(x.objname AS sql_identifier) AS object_name,
1421 CAST(x.objtype AS character_data) AS object_type,
1422 CAST(x.objdtdid AS sql_identifier) AS array_type_identifier,
1424 CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
1425 ELSE 'USER-DEFINED' END AS character_data) AS data_type,
1427 CAST(null AS cardinal_number) AS character_maximum_length,
1428 CAST(null AS cardinal_number) AS character_octet_length,
1429 CAST(null AS sql_identifier) AS character_set_catalog,
1430 CAST(null AS sql_identifier) AS character_set_schema,
1431 CAST(null AS sql_identifier) AS character_set_name,
1432 CAST(null AS sql_identifier) AS collation_catalog,
1433 CAST(null AS sql_identifier) AS collation_schema,
1434 CAST(null AS sql_identifier) AS collation_name,
1435 CAST(null AS cardinal_number) AS numeric_precision,
1436 CAST(null AS cardinal_number) AS numeric_precision_radix,
1437 CAST(null AS cardinal_number) AS numeric_scale,
1438 CAST(null AS cardinal_number) AS datetime_precision,
1439 CAST(null AS character_data) AS interval_type,
1440 CAST(null AS character_data) AS interval_precision,
1442 CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
1444 CAST(current_database() AS sql_identifier) AS udt_catalog,
1445 CAST(nbt.nspname AS sql_identifier) AS udt_schema,
1446 CAST(bt.typname AS sql_identifier) AS udt_name,
1448 CAST(null AS sql_identifier) AS scope_catalog,
1449 CAST(null AS sql_identifier) AS scope_schema,
1450 CAST(null AS sql_identifier) AS scope_name,
1452 CAST(null AS cardinal_number) AS maximum_cardinality,
1453 CAST('a' || x.objdtdid AS sql_identifier) AS dtd_identifier
1455 FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
1458 SELECT c.relnamespace, c.relname, 'TABLE'::text, a.attnum, a.atttypid
1459 FROM pg_class c, pg_attribute a
1460 WHERE c.oid = a.attrelid
1461 AND c.relkind IN ('r', 'v')
1462 AND attnum > 0 AND NOT attisdropped
1467 SELECT t.typnamespace, t.typname, 'DOMAIN'::text, 1, t.typbasetype
1469 WHERE t.typtype = 'd'
1474 SELECT p.pronamespace, p.proname, 'ROUTINE'::text, pos.n + 1, p.proargtypes[n]
1476 (select 0 union select 1 union select 2 union select 3 union select 4 union
1477 select 5 union select 6 union select 7 union select 8 union select 9 union
1478 select 10 union select 11 union select 12 union select 13 union select 14 union
1479 select 15 union select 16 union select 17 union select 18 union select 19 union
1480 select 20 union select 21 union select 22 union select 23 union select 24 union
1481 select 25 union select 26 union select 27 union select 28 union select 29 union
1482 select 30 union select 31) AS pos(n)
1483 WHERE p.pronargs > pos.n
1488 SELECT p.pronamespace, p.proname, 'ROUTINE'::text, 0, p.prorettype
1491 ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
1493 WHERE n.oid = x.objschema
1494 AND at.oid = x.objtypeid
1495 AND (at.typelem <> 0 AND at.typlen = -1)
1496 AND at.typelem = bt.oid
1497 AND nbt.oid = bt.typnamespace
1499 AND (n.nspname, x.objname, x.objtype, x.objtypeid) IN
1500 ( SELECT object_schema, object_name, object_type, dtd_identifier
1501 FROM data_type_privileges );
1503 GRANT SELECT ON element_types TO PUBLIC;