2 * SQL Information Schema
3 * as defined in ISO 9075-2:1999 chapter 20
5 * Copyright 2002, PostgreSQL Global Development Group
7 * $Id: information_schema.sql,v 1.4 2003/03/20 05:06:55 momjian Exp $
13 * INFORMATION_SCHEMA schema
16 CREATE SCHEMA information_schema;
17 GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
18 SET search_path TO information_schema, public;
21 -- Note: 20.3 follows later. Some genius screwed up the order in the standard.
26 * CARDINAL_NUMBER domain
29 CREATE DOMAIN cardinal_number AS integer
30 CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);
35 * CHARACTER_DATA domain
38 CREATE DOMAIN character_data AS character varying;
43 * SQL_IDENTIFIER domain
46 CREATE DOMAIN sql_identifier AS character varying;
51 * INFORMATION_SCHEMA_CATALOG_NAME view
54 CREATE VIEW information_schema_catalog_name AS
55 SELECT CAST(current_database() AS sql_identifier) AS catalog_name;
57 GRANT SELECT ON information_schema_catalog_name TO PUBLIC;
65 CREATE DOMAIN time_stamp AS timestamp(2)
66 DEFAULT current_timestamp(2);
71 * CHECK_CONSTRAINTS view
74 CREATE VIEW check_constraints AS
75 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
76 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
77 CAST(con.conname AS sql_identifier) AS constraint_name,
78 CAST(con.consrc AS character_data) AS check_clause
79 FROM pg_namespace rs, pg_constraint con
80 left outer join pg_class c on (c.oid = con.conrelid)
81 left outer join pg_type t on (t.oid = con.contypid),
83 WHERE rs.oid = con.connamespace
84 AND u.usesysid IN (c.relowner, t.typowner)
85 AND u.usename = current_user
86 AND con.contype = 'c';
88 GRANT SELECT ON check_constraints TO PUBLIC;
93 * COLUMN_DOMAIN_USAGE view
96 CREATE VIEW column_domain_usage AS
97 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
98 CAST(nt.nspname AS sql_identifier) AS domain_schema,
99 CAST(t.typname AS sql_identifier) AS domain_name,
100 CAST(current_database() AS sql_identifier) AS table_catalog,
101 CAST(nc.nspname AS sql_identifier) AS table_schema,
102 CAST(c.relname AS sql_identifier) AS table_name,
103 CAST(a.attname AS sql_identifier) AS column_name
105 FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
106 pg_attribute a, pg_user u
108 WHERE t.typnamespace = nt.oid AND t.typtype = 'd'
109 AND c.relnamespace = nc.oid AND a.attrelid = c.oid
110 AND a.atttypid = t.oid AND t.typowner = u.usesysid
111 AND u.usename = current_user;
113 GRANT SELECT ON column_domain_usage TO PUBLIC;
121 -- PostgreSQL does not have column privileges, so this view is empty.
122 -- (Table privileges do not also count as column privileges.)
124 CREATE VIEW column_privileges AS
125 SELECT CAST(null AS sql_identifier) AS grantor,
126 CAST(null AS sql_identifier) AS grantee,
127 CAST(null AS sql_identifier) AS table_catalog,
128 CAST(null AS sql_identifier) AS table_schema,
129 CAST(null AS sql_identifier) AS table_name,
130 CAST(null AS sql_identifier) AS column_name,
131 CAST(null AS character_data) AS privilege_type,
132 CAST(null AS character_data) AS is_grantable
135 GRANT SELECT ON column_privileges TO PUBLIC;
143 CREATE VIEW columns AS
144 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
145 CAST(nc.nspname AS sql_identifier) AS table_schema,
146 CAST(c.relname AS sql_identifier) AS table_name,
147 CAST(a.attname AS sql_identifier) AS column_name,
148 CAST(a.attnum AS cardinal_number) AS ordinal_position,
150 CASE WHEN u.usename = current_user THEN a.adsrc ELSE null END
153 CAST(CASE WHEN a.attnotnull THEN 'NO' ELSE 'YES' END
156 CAST(format_type(a.atttypid, null) AS character_data)
160 CASE WHEN a.atttypid IN (25, 1042, 1043, 1560, 1562) AND a.atttypmod <> -1
164 AS character_maximum_length,
167 CASE WHEN a.atttypid IN (25, 1042, 1043) THEN 2^30 ELSE null END
169 AS character_octet_length,
172 CASE WHEN a.atttypid IN (1700) THEN ((a.atttypmod - 4) >> 16) & 65535 ELSE null END
174 AS numeric_precision,
177 CASE WHEN a.atttypid IN (1700) THEN 10 ELSE null END
179 AS numeric_precision_radix,
182 CASE WHEN a.atttypid IN (1700) THEN (a.atttypmod - 4) & 65535 ELSE null END
187 CASE WHEN a.atttypid IN (1083, 1114, 1184, 1266)
188 THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod ELSE null END)
189 WHEN a.atttypid IN (1186)
190 THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod & 65535 ELSE null END)
193 AS datetime_precision,
195 CAST(null AS character_data) AS interval_type, -- XXX
196 CAST(null AS character_data) AS interval_precision, -- XXX
198 CAST(null AS sql_identifier) AS character_set_catalog,
199 CAST(null AS sql_identifier) AS character_set_schema,
200 CAST(null AS sql_identifier) AS character_set_name,
202 CAST(null AS sql_identifier) AS collation_catalog,
203 CAST(null AS sql_identifier) AS collation_schema,
204 CAST(null AS sql_identifier) AS collation_name,
206 CAST(CASE WHEN t.typbasetype <> 0 THEN current_database() ELSE null END
207 AS sql_identifier) AS domain_catalog,
208 CAST(CASE WHEN t.typbasetype <> 0 THEN nt.nspname ELSE null END
209 AS sql_identifier) AS domain_schema,
210 CAST(CASE WHEN t.typbasetype <> 0 THEN t.typname ELSE null END
211 AS sql_identifier) AS domain_name,
213 CAST(CASE WHEN t.typbasetype = 0 THEN current_database() ELSE null END
214 AS sql_identifier) AS udt_catalog,
215 CAST(CASE WHEN t.typbasetype = 0 THEN nt.nspname ELSE null END
216 AS sql_identifier) AS udt_schema,
217 CAST(CASE WHEN t.typbasetype = 0 THEN t.typname ELSE null END
218 AS sql_identifier) AS udt_name,
220 CAST(null AS sql_identifier) AS scope_catalog,
221 CAST(null AS sql_identifier) AS scope_schema,
222 CAST(null AS sql_identifier) AS scope_name,
224 CAST(null AS cardinal_number) AS maximum_cardinality,
225 CAST(null AS sql_identifier) AS dtd_identifier,
226 CAST('NO' AS character_data) AS is_self_referencing
228 FROM (pg_attribute LEFT JOIN pg_attrdef ON attrelid = adrelid AND attnum = adnum) AS a,
229 pg_class c, pg_namespace nc, pg_type t, pg_namespace nt, pg_user u
231 WHERE a.attrelid = c.oid
232 AND a.atttypid = t.oid
233 AND u.usesysid = c.relowner
234 AND nc.oid = c.relnamespace
235 AND nt.oid = t.typnamespace
236 AND u.usename = current_user
238 AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v');
240 GRANT SELECT ON columns TO PUBLIC;
245 * DOMAIN_CONSTRAINTS view
248 CREATE VIEW domain_constraints AS
249 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
250 CAST(rs.nspname AS sql_identifier) AS constraint_schema,
251 CAST(con.conname AS sql_identifier) AS constraint_name,
252 CAST(current_database() AS sql_identifier) AS domain_catalog,
253 CAST(n.nspname AS sql_identifier) AS domain_schema,
254 CAST(t.typname AS sql_identifier) AS domain_name,
255 CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
256 AS character_data) AS is_deferrable,
257 CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
258 AS character_data) AS initially_deferred
259 FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t, pg_user u
260 WHERE rs.oid = con.connamespace
261 AND n.oid = t.typnamespace
262 AND u.usesysid = t.typowner
263 AND u.usename = current_user
264 AND t.oid = con.contypid;
266 GRANT SELECT ON domain_constraints TO PUBLIC;
274 CREATE VIEW domains AS
275 SELECT CAST(current_database() AS sql_identifier) AS domain_catalog,
276 CAST(rs.nspname AS sql_identifier) AS domain_schema,
277 CAST(t.typname AS sql_identifier) AS domain_name,
278 CAST(format_type(t.typbasetype, null) AS character_data)
282 CASE WHEN t.typbasetype IN (25, 1042, 1043, 1560, 1562) AND t.typtypmod <> -1
286 AS character_maximum_length,
289 CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END
291 AS character_octet_length,
292 CAST(null AS sql_identifier) AS character_set_catalog,
293 CAST(null AS sql_identifier) AS character_set_schema,
294 CAST(null AS sql_identifier) AS character_set_name,
296 CAST(null AS sql_identifier) AS collation_catalog,
297 CAST(null AS sql_identifier) AS collation_schema,
298 CAST(null AS sql_identifier) AS collation_name,
301 CASE WHEN t.typbasetype IN (1700) THEN ((t.typtypmod - 4) >> 16) & 65535 ELSE null END
303 AS numeric_precision,
306 CASE WHEN t.typbasetype IN (1700) THEN 10 ELSE null END
308 AS numeric_precision_radix,
311 CASE WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535 ELSE null END
316 CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266)
317 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END)
318 WHEN t.typbasetype IN (1186)
319 THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END)
322 AS datetime_precision,
324 CAST(null AS character_data) AS interval_type, -- XXX
325 CAST(null AS character_data) AS interval_precision, -- XXX
327 CAST(typdefault AS character_data) AS domain_default,
329 CAST(CASE WHEN t.typbasetype = 0 THEN current_database() ELSE null END
330 AS sql_identifier) AS udt_catalog,
331 CAST(CASE WHEN t.typbasetype = 0 THEN rs.nspname ELSE null END
332 AS sql_identifier) AS udt_schema,
333 CAST(CASE WHEN t.typbasetype = 0 THEN t.typname ELSE null END
334 AS sql_identifier) AS udt_name,
336 CAST(null AS sql_identifier) AS scope_catalog,
337 CAST(null AS sql_identifier) AS scope_schema,
338 CAST(null AS sql_identifier) AS scope_name,
340 CAST(null AS cardinal_number) AS maximum_cardinality,
341 CAST(null AS sql_identifier) AS dtd_identifier
343 FROM pg_namespace rs,
347 WHERE rs.oid = t.typnamespace
349 AND t.typowner = u.usesysid
350 AND (u.usename = CURRENT_USER
353 WHERE rs.nspowner = u2.usesysid
354 AND u2.usename = CURRENT_USER)
359 WHERE u3.usesysid = c3.relowner
360 AND a3.attrelid = c3.oid
361 AND a3.atttypid = t.oid));
364 GRANT SELECT ON domains TO PUBLIC;
369 * REFERENTIAL_CONSTRAINTS view
372 CREATE VIEW referential_constraints AS
373 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
374 CAST(ncon.nspname AS sql_identifier) AS constraint_schema,
375 CAST(con.conname AS sql_identifier) AS constraint_name,
376 CAST(current_database() AS sql_identifier) AS unique_constraint_catalog,
377 CAST(null AS sql_identifier) AS unique_constraint_schema, -- XXX
378 CAST(null AS sql_identifier) AS unique_constraint_name, -- XXX
381 CASE con.confmatchtype WHEN 'f' THEN 'FULL'
382 WHEN 'p' THEN 'PARTIAL'
383 WHEN 'u' THEN 'NONE' END
384 AS character_data) AS match_option,
387 CASE con.confupdtype WHEN 'c' THEN 'CASCADE'
388 WHEN 'n' THEN 'SET NULL'
389 WHEN 'd' THEN 'SET DEFAULT'
390 WHEN 'r' THEN 'RESTRICT'
391 WHEN 'a' THEN 'NOACTION' END
392 AS character_data) AS update_rule,
395 CASE con.confdeltype WHEN 'c' THEN 'CASCADE'
396 WHEN 'n' THEN 'SET NULL'
397 WHEN 'd' THEN 'SET DEFAULT'
398 WHEN 'r' THEN 'RESTRICT'
399 WHEN 'a' THEN 'NOACTION' END
400 AS character_data) AS delete_rule
402 FROM pg_namespace ncon,
407 WHERE ncon.oid = con.connamespace
408 AND con.conrelid = r.oid AND r.relowner = u.usesysid
409 AND u.usename = current_user;
411 GRANT SELECT ON referential_constraints TO PUBLIC;
419 CREATE VIEW schemata AS
420 SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
421 CAST(n.nspname AS sql_identifier) AS schema_name,
422 CAST(u.usename AS sql_identifier) AS schema_owner,
423 CAST(null AS sql_identifier) AS default_character_set_catalog,
424 CAST(null AS sql_identifier) AS default_character_set_schema,
425 CAST(null AS sql_identifier) AS default_character_set_name,
426 CAST(null AS character_data) AS sql_path
427 FROM pg_namespace n, pg_user u
428 WHERE n.nspowner = u.usesysid AND u.usename = current_user;
430 GRANT SELECT ON schemata TO PUBLIC;
438 CREATE TABLE sql_features (
439 feature_id character_data,
440 feature_name character_data,
441 sub_feature_id character_data,
442 sub_feature_name character_data,
443 is_supported character_data,
444 is_verified_by character_data,
445 comments character_data
448 -- Will be filled with external data by initdb.
450 GRANT SELECT ON sql_features TO PUBLIC;
455 * SQL_IMPLEMENTATION_INFO table
458 -- Note: Implementation information items are defined in ISO 9075-3:1999,
461 CREATE TABLE sql_implementation_info (
462 implementation_info_id character_data,
463 implementation_info_name character_data,
464 integer_value cardinal_number,
465 character_value character_data,
466 comments character_data
469 INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
470 INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
471 INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
472 INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
473 INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
474 INSERT INTO sql_implementation_info VALUES ('18', 'DBMS VERSION', NULL, '???', NULL); -- filled by initdb
475 INSERT INTO sql_implementation_info VALUES ('26', 'DEFAULT TRANSACTION ISOLATION', 2, NULL, 'READ COMMITED; user-settable');
476 INSERT INTO sql_implementation_info VALUES ('28', 'IDENTIFIER CASE', 3, NULL, 'stored in mixed case - case sensitive');
477 INSERT INTO sql_implementation_info VALUES ('85', 'NULL COLLATION', 0, NULL, 'nulls higher than non-nulls');
478 INSERT INTO sql_implementation_info VALUES ('13', 'SERVER NAME', NULL, '', NULL);
479 INSERT INTO sql_implementation_info VALUES ('94', 'SPECIAL CHARACTERS', NULL, '', 'all non-ASCII characters allowed');
480 INSERT INTO sql_implementation_info VALUES ('46', 'TRANSACTION CAPABLE', 2, NULL, 'both DML and DDL');
482 GRANT SELECT ON sql_implementation_info TO PUBLIC;
487 * SQL_LANGUAGES table
490 CREATE TABLE sql_languages (
491 sql_language_source character_data,
492 sql_language_year character_data,
493 sql_language_conformance character_data,
494 sql_language_integrity character_data,
495 sql_language_implementation character_data,
496 sql_language_binding_style character_data,
497 sql_language_programming_language character_data
500 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
501 INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
503 GRANT SELECT ON sql_languages TO PUBLIC;
511 CREATE TABLE sql_packages (
512 feature_id character_data,
513 feature_name character_data,
514 is_supported character_data,
515 is_verified_by character_data,
516 comments character_data
519 INSERT INTO sql_packages VALUES ('PKG000', 'Core', 'NO', NULL, '');
520 INSERT INTO sql_packages VALUES ('PKG001', 'Enhanced datetime facilities', 'YES', NULL, '');
521 INSERT INTO sql_packages VALUES ('PKG002', 'Enhanced integrity management', 'NO', NULL, '');
522 INSERT INTO sql_packages VALUES ('PKG003', 'OLAP facilities', 'NO', NULL, '');
523 INSERT INTO sql_packages VALUES ('PKG004', 'PSM', 'NO', NULL, 'PL/pgSQL is similar.');
524 INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.');
525 INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
526 INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
527 INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
528 INSERT INTO sql_packages VALUES ('PKG009', 'SQL/MM support', 'NO', NULL, '');
530 GRANT SELECT ON sql_packages TO PUBLIC;
538 -- Note: Sizing items are defined in ISO 9075-3:1999, clause 7.2.
540 CREATE TABLE sql_sizing (
541 sizing_id cardinal_number,
542 sizing_name character_data,
543 supported_value cardinal_number,
544 comments character_data
547 INSERT INTO sql_sizing VALUES (34, 'MAXIMUM CATALOG NAME LENGTH', 63, NULL);
548 INSERT INTO sql_sizing VALUES (30, 'MAXIMUM COLUMN NAME LENGTH', 63, NULL);
549 INSERT INTO sql_sizing VALUES (97, 'MAXIMUM COLUMNS IN GROUP BY', 0, NULL);
550 INSERT INTO sql_sizing VALUES (99, 'MAXIMUM COLUMNS IN ORDER BY', 0, NULL);
551 INSERT INTO sql_sizing VALUES (100, 'MAXIMUM COLUMNS IN SELECT', 0, NULL);
552 INSERT INTO sql_sizing VALUES (101, 'MAXIMUM COLUMNS IN TABLE', 1600, NULL); -- match MaxHeapAttributeNumber
553 INSERT INTO sql_sizing VALUES (1, 'MAXIMUM CONCURRENT ACTIVITIES', 0, NULL);
554 INSERT INTO sql_sizing VALUES (31, 'MAXIMUM CURSOR NAME LENGTH', 63, NULL);
555 INSERT INTO sql_sizing VALUES (0, 'MAXIMUM DRIVER CONNECTIONS', NULL, NULL);
556 INSERT INTO sql_sizing VALUES (10005, 'MAXIMUM IDENTIFIER LENGTH', 63, NULL);
557 INSERT INTO sql_sizing VALUES (32, 'MAXIMUM SCHEMA NAME LENGTH', 63, NULL);
558 INSERT INTO sql_sizing VALUES (20000, 'MAXIMUM STATEMENT OCTETS', 0, NULL);
559 INSERT INTO sql_sizing VALUES (20001, 'MAXIMUM STATEMENT OCTETS DATA', 0, NULL);
560 INSERT INTO sql_sizing VALUES (20002, 'MAXIMUM STATEMENT OCTETS SCHEMA', 0, NULL);
561 INSERT INTO sql_sizing VALUES (35, 'MAXIMUM TABLE NAME LENGTH', 63, NULL);
562 INSERT INTO sql_sizing VALUES (106, 'MAXIMUM TABLES IN SELECT', 0, NULL);
563 INSERT INTO sql_sizing VALUES (107, 'MAXIMUM USER NAME LENGTH', 63, NULL);
564 INSERT INTO sql_sizing VALUES (25000, 'MAXIMUM CURRENT DEFAULT TRANSFORM GROUP LENGTH', NULL, NULL);
565 INSERT INTO sql_sizing VALUES (25001, 'MAXIMUM CURRENT TRANSFORM GROUP LENGTH', NULL, NULL);
566 INSERT INTO sql_sizing VALUES (25002, 'MAXIMUM CURRENT PATH LENGTH', 0, NULL);
567 INSERT INTO sql_sizing VALUES (25003, 'MAXIMUM CURRENT ROLE LENGTH', NULL, NULL);
568 INSERT INTO sql_sizing VALUES (25004, 'MAXIMUM SESSION USER LENGTH', 63, NULL);
569 INSERT INTO sql_sizing VALUES (25005, 'MAXIMUM SYSTEM USER LENGTH', 63, NULL);
572 SET supported_value = (SELECT typlen-1 FROM pg_catalog.pg_type WHERE typname = 'name'),
573 comments = 'Might be less, depending on character set.'
574 WHERE supported_value = 63;
576 GRANT SELECT ON sql_sizing TO PUBLIC;
581 * SQL_SIZING_PROFILES table
584 -- The data in this table are defined by various profiles of SQL.
585 -- Since we don't have any information about such profiles, we provide
588 CREATE TABLE sql_sizing_profiles (
589 sizing_id cardinal_number,
590 sizing_name character_data,
591 profile_id character_data,
592 required_value cardinal_number,
593 comments character_data
596 GRANT SELECT ON sql_sizing_profiles TO PUBLIC;
601 * TABLE_CONSTRAINTS view
604 CREATE VIEW table_constraints AS
605 SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
606 CAST(nc.nspname AS sql_identifier) AS constraint_schema,
607 CAST(c.conname AS sql_identifier) AS constraint_name,
608 CAST(current_database() AS sql_identifier) AS table_catalog,
609 CAST(nr.nspname AS sql_identifier) AS table_schema,
610 CAST(r.relname AS sql_identifier) AS table_name,
612 CASE c.contype WHEN 'c' THEN 'CHECK'
613 WHEN 'f' THEN 'FOREIGN KEY'
614 WHEN 'p' THEN 'PRIMARY KEY'
615 WHEN 'u' THEN 'UNIQUE' END
616 AS character_data) AS constraint_type,
617 CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
619 CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
620 AS initially_deferred
622 FROM pg_namespace nc,
628 WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
629 AND c.conrelid = r.oid AND r.relowner = u.usesysid
630 AND u.usename = current_user;
632 -- FIMXE: Not-null constraints are missing here.
634 GRANT SELECT ON table_constraints TO PUBLIC;
639 * TABLE_PRIVILEGES view
642 CREATE VIEW table_privileges AS
643 SELECT CAST(u_owner.usename AS sql_identifier) AS grantor,
644 CAST(u_grantee.usename AS sql_identifier) AS grantee,
645 CAST(current_database() AS sql_identifier) AS table_catalog,
646 CAST(nc.nspname AS sql_identifier) AS table_schema,
647 CAST(c.relname AS sql_identifier) AS table_name,
648 CAST(pr.type AS character_data) AS privilege_type,
649 CAST('NO' AS character_data) AS is_grantable,
650 CAST('NO' AS character_data) AS with_hierarchy
652 FROM pg_user u_owner,
656 (SELECT 'SELECT' UNION SELECT 'DELETE' UNION SELECT 'INSERT' UNION SELECT 'UPDATE'
657 UNION SELECT 'REFERENCES' UNION SELECT 'TRIGGER') AS pr (type)
659 WHERE u_owner.usesysid = c.relowner
660 AND c.relnamespace = nc.oid
661 AND has_table_privilege(u_grantee.usename, c.oid, pr.type)
663 AND (u_owner.usename = current_user OR u_grantee.usename = current_user);
665 GRANT SELECT ON table_privileges TO PUBLIC;
673 CREATE VIEW tables AS
674 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
675 CAST(nc.nspname AS sql_identifier) AS table_schema,
676 CAST(c.relname AS sql_identifier) AS table_name,
679 CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY'
680 WHEN c.relkind = 'r' THEN 'BASE TABLE'
681 WHEN c.relkind = 'v' THEN 'VIEW'
683 AS character_data) AS table_type,
685 CAST(null AS sql_identifier) AS self_referencing_column_name,
686 CAST(null AS character_data) AS reference_generation,
688 CAST(null AS sql_identifier) AS user_defined_type_catalog,
689 CAST(null AS sql_identifier) AS user_defined_type_schema,
690 CAST(null AS sql_identifier) AS user_defined_name
692 FROM pg_namespace nc, pg_class c, pg_user u
694 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
695 AND (u.usename = current_user
696 OR EXISTS(SELECT 1 FROM information_schema.table_privileges tp
697 WHERE tp.table_schema = nc.nspname
698 AND tp.table_name = c.relname
699 AND tp.grantee = current_user))
701 AND c.relkind IN ('r', 'v');
703 GRANT SELECT ON tables TO PUBLIC;
708 * USAGE_PRIVILEGES view
711 -- Of the things currently implemented in PostgreSQL, usage privileges
712 -- apply only to domains. Since domains have no real privileges, we
713 -- represent all domains with implicit usage privilege here.
715 CREATE VIEW usage_privileges AS
716 SELECT CAST(u.usename AS sql_identifier) AS grantor,
717 CAST('PUBLIC' AS sql_identifier) AS grantee,
718 CAST(current_database() AS sql_identifier) AS object_catalog,
719 CAST(n.nspname AS sql_identifier) AS object_schema,
720 CAST(t.typname AS sql_identifier) AS object_name,
721 CAST('DOMAIN' AS character_data) AS object_type,
722 CAST('USAGE' AS character_data) AS privilege_type,
723 CAST('NO' AS character_data) AS is_grantable
729 WHERE u.usesysid = t.typowner
730 AND t.typnamespace = n.oid
733 GRANT SELECT ON usage_privileges TO PUBLIC;
742 SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
743 CAST(nc.nspname AS sql_identifier) AS table_schema,
744 CAST(c.relname AS sql_identifier) AS table_name,
747 CASE WHEN u.usename = current_user THEN pg_get_viewdef(c.oid)
749 AS character_data) AS view_definition,
751 CAST('NONE' AS character_data) AS check_option,
752 CAST(null AS character_data) AS is_updatable, -- FIXME
753 CAST(null AS character_data) AS is_insertable_into -- FIXME
755 FROM pg_namespace nc, pg_class c, pg_user u
757 WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
758 AND (u.usename = current_user
759 OR EXISTS(SELECT 1 FROM information_schema.table_privileges tp
760 WHERE tp.table_schema = nc.nspname
761 AND tp.table_name = c.relname
762 AND tp.grantee = current_user))
766 GRANT SELECT ON views TO PUBLIC;