diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/catalog/information_schema.sql | 331 |
1 files changed, 309 insertions, 22 deletions
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 224e61f0686..f228e96e6b2 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -4,7 +4,7 @@ * * Copyright 2002, PostgreSQL Global Development Group * - * $Id: information_schema.sql,v 1.8 2003/06/11 09:23:55 petere Exp $ + * $Id: information_schema.sql,v 1.9 2003/06/17 18:00:48 petere Exp $ */ @@ -78,13 +78,14 @@ CREATE VIEW check_constraints AS CAST(con.consrc AS character_data) AS check_clause FROM pg_namespace rs, pg_constraint con - LEFT OUTER JOIN pg_class c on (c.oid = con.conrelid) - LEFT OUTER JOIN pg_type t on (t.oid = con.contypid), + LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid) + LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid), pg_user u WHERE rs.oid = con.connamespace AND u.usesysid = coalesce(c.relowner, t.typowner) AND u.usename = current_user - AND con.contype = 'c'; + AND con.contype = 'c' + AND c.relkind = 'r'; GRANT SELECT ON check_constraints TO PUBLIC; @@ -106,9 +107,15 @@ CREATE VIEW column_domain_usage AS FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc, pg_attribute a, pg_user u - WHERE t.typnamespace = nt.oid AND t.typtype = 'd' - AND c.relnamespace = nc.oid AND a.attrelid = c.oid - AND a.atttypid = t.oid AND t.typowner = u.usesysid + WHERE t.typnamespace = nt.oid + AND c.relnamespace = nc.oid + AND a.attrelid = c.oid + AND a.atttypid = t.oid + AND t.typowner = u.usesysid + AND t.typtype = 'd' + AND c.relkind IN ('r', 'v') + AND a.attnum > 0 + AND NOT a.attisdropped AND u.usename = current_user; GRANT SELECT ON column_domain_usage TO PUBLIC; @@ -119,24 +126,71 @@ GRANT SELECT ON column_domain_usage TO PUBLIC; * COLUMN_PRIVILEGES */ --- PostgreSQL does not have column privileges, so this view is empty. --- (Table privileges do not also count as column privileges.) - CREATE VIEW column_privileges AS - SELECT CAST(null AS sql_identifier) AS grantor, - CAST(null AS sql_identifier) AS grantee, - CAST(null AS sql_identifier) AS table_catalog, - CAST(null AS sql_identifier) AS table_schema, - CAST(null AS sql_identifier) AS table_name, - CAST(null AS sql_identifier) AS column_name, - CAST(null AS character_data) AS privilege_type, - CAST(null AS character_data) AS is_grantable - WHERE false; + SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor, + CAST(u_grantee.usename AS sql_identifier) AS grantee, + CAST(current_database() AS sql_identifier) AS table_catalog, + CAST(nc.nspname AS sql_identifier) AS table_schema, + CAST(c.relname AS sql_identifier) AS table_name, + CAST(a.attname AS sql_identifier) AS column_name, + CAST(pr.type AS character_data) AS privilege_type, + CAST( + CASE WHEN aclcontains(c.relacl, + makeaclitem(u_grantee.usesysid, 0, u_grantor.usesysid, pr.type, true)) + THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable + + FROM pg_attribute a, + pg_class c, + pg_namespace nc, + pg_user u_grantor, + (SELECT usesysid, usename FROM pg_user UNION SELECT 0, 'PUBLIC') AS u_grantee, + (SELECT 'SELECT' UNION SELECT 'DELETE' UNION SELECT 'INSERT' UNION SELECT 'UPDATE' + UNION SELECT 'REFERENCES' UNION SELECT 'TRIGGER') AS pr (type) + + WHERE a.attrelid = c.oid + AND c.relnamespace = nc.oid + AND a.attnum > 0 + AND NOT a.attisdropped + AND c.relkind IN ('r', 'v') + AND aclcontains(c.relacl, + makeaclitem(u_grantee.usesysid, 0, u_grantor.usesysid, pr.type, false)) + AND (u_grantor.usename = current_user + OR u_grantee.usename = current_user + OR u_grantee.usename = 'PUBLIC'); GRANT SELECT ON column_privileges TO PUBLIC; /* + * 20.17 + * COLUMN_UDT_USAGE view + */ + +CREATE VIEW column_udt_usage AS + SELECT CAST(current_database() AS sql_identifier) AS udt_catalog, + CAST(coalesce(nbt.nspname, nt.nspname) AS sql_identifier) AS udt_schema, + CAST(coalesce(bt.typname, t.typname) AS sql_identifier) AS udt_name, + CAST(current_database() AS sql_identifier) AS table_catalog, + CAST(nc.nspname AS sql_identifier) AS table_schema, + CAST(c.relname AS sql_identifier) AS table_name, + CAST(a.attname AS sql_identifier) AS column_name + + FROM pg_attribute a, pg_class c, pg_namespace nc, pg_user u, + (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) + LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid)) + ON (t.typtype = 'd' AND t.typbasetype = bt.oid) + + WHERE a.attrelid = c.oid + AND a.atttypid = t.oid + AND u.usesysid = coalesce(bt.typowner, t.typowner) + AND nc.oid = c.relnamespace + AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v') + AND u.usename = current_user; + +GRANT SELECT ON column_udt_usage TO PUBLIC; + + +/* * 20.18 * COLUMNS view */ @@ -305,9 +359,6 @@ GRANT SELECT ON columns TO PUBLIC; * CONSTRAINT_COLUMN_USAGE view */ --- FIXME: This only works for check constraints so far; for the others --- we need a built-in way to convert arrays to virtual tables. - CREATE VIEW constraint_column_usage AS SELECT CAST(current_database() AS sql_identifier) AS table_catalog, CAST(tblschema AS sql_identifier) AS table_schema, @@ -318,6 +369,7 @@ CREATE VIEW constraint_column_usage AS CAST(cstrname AS sql_identifier) AS constraint_name FROM ( + /* check constraints */ SELECT DISTINCT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname FROM pg_namespace nr, pg_class r, pg_attribute a, pg_depend d, pg_namespace nc, pg_constraint c WHERE nr.oid = r.relnamespace @@ -329,6 +381,33 @@ CREATE VIEW constraint_column_usage AS AND d.objid = c.oid AND c.connamespace = nc.oid AND c.contype = 'c' + AND r.relkind = 'r' + AND a.attnum > 0 + AND NOT a.attisdropped + + UNION + + /* unique/primary key/foreign key constraints */ + SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname + FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c, + (select 1 union select 2 union select 3 union select 4 union select 5 union + select 6 union select 7 union select 8 union select 9 union select 10 union + select 11 union select 12 union select 13 union select 14 union select 15 union + select 16 union select 17 union select 18 union select 19 union select 20 union + select 21 union select 22 union select 23 union select 24 union select 25 union + select 26 union select 27 union select 28 union select 29 union select 30 union + select 31 union select 32) AS pos(n) + WHERE nr.oid = r.relnamespace + AND r.oid = a.attrelid + AND r.oid = c.conrelid + AND nc.oid = c.connamespace + AND (CASE WHEN c.contype = 'f' THEN c.confkey[pos.n] = a.attnum + ELSE c.conkey[pos.n] = a.attnum END) + AND a.attnum > 0 + AND NOT a.attisdropped + AND c.contype IN ('p', 'u', 'f') + AND r.relkind = 'r' + ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname), pg_user u @@ -357,6 +436,7 @@ CREATE VIEW constraint_table_usage AS WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid AND ( (c.contype = 'f' AND c.confrelid = r.oid) OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) ) + AND r.relkind = 'r' AND r.relowner = u.usesysid AND u.usename = current_user; GRANT SELECT ON constraint_table_usage TO PUBLIC; @@ -389,6 +469,33 @@ GRANT SELECT ON domain_constraints TO PUBLIC; /* + * 20.25 + * DOMAIN_UDT_USAGE view + */ + +CREATE VIEW domain_udt_usage AS + SELECT CAST(current_database() AS sql_identifier) AS udt_catalog, + CAST(nbt.nspname AS sql_identifier) AS udt_schema, + CAST(bt.typname AS sql_identifier) AS udt_name, + CAST(current_database() AS sql_identifier) AS domain_catalog, + CAST(nt.nspname AS sql_identifier) AS domain_schema, + CAST(t.typname AS sql_identifier) AS domain_name + + FROM pg_type t, pg_namespace nt, + pg_type bt, pg_namespace nbt, + pg_user u + + WHERE t.typnamespace = nt.oid + AND t.typbasetype = bt.oid + AND bt.typnamespace = nbt.oid + AND t.typtype = 'd' + AND bt.typowner = u.usesysid + AND u.usename = current_user; + +GRANT SELECT ON domain_udt_usage TO PUBLIC; + + +/* * 20.26 * DOMAINS view */ @@ -488,6 +595,46 @@ GRANT SELECT ON domains TO PUBLIC; /* + * 20.30 + * KEY_COLUMN_USAGE view + */ + +CREATE VIEW key_column_usage AS + SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, + CAST(nc.nspname AS sql_identifier) AS constraint_schema, + CAST(c.conname AS sql_identifier) AS constraint_name, + CAST(current_database() AS sql_identifier) AS table_catalog, + CAST(nr.nspname AS sql_identifier) AS table_schema, + CAST(r.relname AS sql_identifier) AS table_name, + CAST(a.attname AS sql_identifier) AS column_name, + CAST(pos.n AS cardinal_number) AS ordinal_position + + FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, pg_constraint c, + pg_user u, + (select 1 union select 2 union select 3 union select 4 union select 5 union + select 6 union select 7 union select 8 union select 9 union select 10 union + select 11 union select 12 union select 13 union select 14 union select 15 union + select 16 union select 17 union select 18 union select 19 union select 20 union + select 21 union select 22 union select 23 union select 24 union select 25 union + select 26 union select 27 union select 28 union select 29 union select 30 union + select 31 union select 32) AS pos(n) + + WHERE nr.oid = r.relnamespace + AND r.oid = a.attrelid + AND r.oid = c.conrelid + AND nc.oid = c.connamespace + AND c.conkey[pos.n] = a.attnum + AND a.attnum > 0 + AND NOT a.attisdropped + AND c.contype IN ('p', 'u', 'f') + AND r.relkind = 'r' + AND r.relowner = u.usesysid + AND u.usename = current_user; + +GRANT SELECT ON key_column_usage TO PUBLIC; + + +/* * 20.33 * PARAMETERS view */ @@ -593,6 +740,7 @@ CREATE VIEW referential_constraints AS AND con.confkey = pkc.conkey AND pkc.connamespace = npkc.oid AND c.relowner = u.usesysid + AND c.relkind = 'r' AND u.usename = current_user; GRANT SELECT ON referential_constraints TO PUBLIC; @@ -932,6 +1080,7 @@ CREATE VIEW table_constraints AS WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace AND c.conrelid = r.oid AND r.relowner = u.usesysid + AND r.relkind = 'r' AND u.usename = current_user; -- FIMXE: Not-null constraints are missing here. @@ -965,6 +1114,7 @@ CREATE VIEW table_privileges AS UNION SELECT 'REFERENCES' UNION SELECT 'TRIGGER') AS pr (type) WHERE c.relnamespace = nc.oid + AND c.relkind IN ('r', 'v') AND aclcontains(c.relacl, makeaclitem(u_grantee.usesysid, 0, u_grantor.usesysid, pr.type, false)) AND (u_grantor.usename = current_user @@ -1015,6 +1165,68 @@ GRANT SELECT ON tables TO PUBLIC; /* + * 20.59 + * TRIGGERED_UPDATE_COLUMNS view + */ + +-- PostgreSQL doesn't allow the specification of individual triggered +-- update columns, so this view is empty. + +CREATE VIEW triggered_update_columns AS + SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog, + CAST(null AS sql_identifier) AS trigger_schema, + CAST(null AS sql_identifier) AS trigger_name, + CAST(current_database() AS sql_identifier) AS event_object_catalog, + CAST(null AS sql_identifier) AS event_object_schema, + CAST(null AS sql_identifier) AS event_object_table, + CAST(null AS sql_identifier) AS event_object_column + WHERE false; + +GRANT SELECT ON triggered_update_columns TO PUBLIC; + + +/* + * 20.62 + * TRIGGERS view + */ + +CREATE VIEW triggers AS + SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog, + CAST(n.nspname AS sql_identifier) AS trigger_schema, + CAST(t.tgname AS sql_identifier) AS trigger_name, + CAST(em.text AS character_data) AS event_manipulation, + CAST(current_database() AS sql_identifier) AS event_object_catalog, + CAST(n.nspname AS sql_identifier) AS event_object_schema, + CAST(c.relname AS sql_identifier) AS event_object_table, + CAST(null AS cardinal_number) AS action_order, + CAST(null AS character_data) AS action_condition, + CAST( + substring(pg_get_triggerdef(t.oid) from + position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47) + AS character_data) AS action_statement, + CAST( + CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END + AS character_data) AS action_orientation, + CAST( + CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END + AS character_data) AS condition_timing, + CAST(null AS sql_identifier) AS condition_reference_old_table, + CAST(null AS sql_identifier) AS condition_reference_new_table + + FROM pg_namespace n, pg_class c, pg_trigger t, pg_user u, + (SELECT 4, 'INSERT' UNION SELECT 8, 'DELETE' UNION SELECT 16, 'UPDATE') AS em (num, text) + + WHERE n.oid = c.relnamespace + AND c.oid = t.tgrelid + AND c.relowner = u.usesysid + AND t.tgtype & em.num <> 0 + AND NOT t.tgisconstraint + AND u.usename = current_user; + +GRANT SELECT ON triggers TO PUBLIC; + + +/* * 20.63 * USAGE_PRIVILEGES view */ @@ -1045,6 +1257,81 @@ GRANT SELECT ON usage_privileges TO PUBLIC; /* + * 20.65 + * VIEW_COLUMN_USAGE + */ + +CREATE VIEW view_column_usage AS + SELECT DISTINCT + CAST(current_database() AS sql_identifier) AS view_catalog, + CAST(nv.nspname AS sql_identifier) AS view_schema, + CAST(v.relname AS sql_identifier) AS view_name, + CAST(current_database() AS sql_identifier) AS table_catalog, + CAST(nt.nspname AS sql_identifier) AS table_schema, + CAST(t.relname AS sql_identifier) AS table_name, + CAST(a.attname AS sql_identifier) AS column_name + + FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv, + pg_depend dt, pg_class t, pg_namespace nt, + pg_attribute a, pg_user u + + WHERE nv.oid = v.relnamespace + AND v.relkind = 'v' + AND v.oid = dv.refobjid + AND dv.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class') + AND dv.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_rewrite') + AND dv.deptype = 'i' + AND dv.objid = dt.objid + AND dv.refobjid <> dt.refobjid + AND dt.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_rewrite') + AND dt.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class') + AND dt.refobjid = t.oid + AND t.relnamespace = nt.oid + AND t.relkind IN ('r', 'v') + AND t.oid = a.attrelid + AND dt.refobjsubid = a.attnum + AND t.relowner = u.usesysid AND u.usename = current_user; + +GRANT SELECT ON view_column_usage TO PUBLIC; + + +/* + * 20.66 + * VIEW_TABLE_USAGE + */ + +CREATE VIEW view_table_usage AS + SELECT DISTINCT + CAST(current_database() AS sql_identifier) AS view_catalog, + CAST(nv.nspname AS sql_identifier) AS view_schema, + CAST(v.relname AS sql_identifier) AS view_name, + CAST(current_database() AS sql_identifier) AS table_catalog, + CAST(nt.nspname AS sql_identifier) AS table_schema, + CAST(t.relname AS sql_identifier) AS table_name + + FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv, + pg_depend dt, pg_class t, pg_namespace nt, + pg_user u + + WHERE nv.oid = v.relnamespace + AND v.relkind = 'v' + AND v.oid = dv.refobjid + AND dv.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class') + AND dv.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_rewrite') + AND dv.deptype = 'i' + AND dv.objid = dt.objid + AND dv.refobjid <> dt.refobjid + AND dt.classid = (SELECT oid FROM pg_class WHERE relname = 'pg_rewrite') + AND dt.refclassid = (SELECT oid FROM pg_class WHERE relname = 'pg_class') + AND dt.refobjid = t.oid + AND t.relnamespace = nt.oid + AND t.relkind IN ('r', 'v') + AND t.relowner = u.usesysid AND u.usename = current_user; + +GRANT SELECT ON view_table_usage TO PUBLIC; + + +/* * 20.68 * VIEWS view */ |