/*
* SQL Information Schema
- * as defined in ISO 9075-2:1999 chapter 20
+ * as defined in ISO/IEC 9075-11:2011
*
- * Copyright 2003, PostgreSQL Global Development Group
+ * Copyright (c) 2003-2016, PostgreSQL Global Development Group
*
- * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.17 2003/11/29 22:39:40 pgsql Exp $
+ * src/backend/catalog/information_schema.sql
+ *
+ * Note: this file is read in single-user -j mode, which means that the
+ * command terminator is semicolon-newline-newline; whenever the backend
+ * sees that, it stops and executes what it's got. If you write a lot of
+ * statements without empty lines between, they'll all get quoted to you
+ * in any error message about one of them, so don't do that. Also, you
+ * cannot write a semicolon immediately followed by an empty line in a
+ * string literal (including a function body!) or a multiline comment.
*/
/*
/*
- * 20.2
+ * 5.1
* INFORMATION_SCHEMA schema
*/
CREATE SCHEMA information_schema;
GRANT USAGE ON SCHEMA information_schema TO PUBLIC;
-SET search_path TO information_schema, public;
+SET search_path TO information_schema;
+
+
+/*
+ * A few supporting functions first ...
+ */
+
+/* Expand any 1-D array into a set with integers 1..N */
+CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int)
+ RETURNS SETOF RECORD
+ LANGUAGE sql STRICT IMMUTABLE
+ AS 'select $1[s], s - pg_catalog.array_lower($1,1) + 1
+ from pg_catalog.generate_series(pg_catalog.array_lower($1,1),
+ pg_catalog.array_upper($1,1),
+ 1) as g(s)';
+
+CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
+ LANGUAGE sql IMMUTABLE -- intentionally not STRICT, to allow inlining
+ AS 'select $1 operator(pg_catalog.<@) $2 and $2 operator(pg_catalog.<@) $1';
+
+/* Given an index's OID and an underlying-table column number, return the
+ * column's position in the index (NULL if not there) */
+CREATE FUNCTION _pg_index_position(oid, smallint) RETURNS int
+ LANGUAGE sql STRICT STABLE
+ AS $$
+SELECT (ss.a).n FROM
+ (SELECT information_schema._pg_expandarray(indkey) AS a
+ FROM pg_catalog.pg_index WHERE indexrelid = $1) ss
+ WHERE (ss.a).x = $2;
+$$;
+
+CREATE FUNCTION _pg_truetypid(pg_attribute, pg_type) RETURNS oid
+ LANGUAGE sql
+ IMMUTABLE
+ RETURNS NULL ON NULL INPUT
+ AS
+$$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typbasetype ELSE $1.atttypid END$$;
+
+CREATE FUNCTION _pg_truetypmod(pg_attribute, pg_type) RETURNS int4
+ LANGUAGE sql
+ IMMUTABLE
+ RETURNS NULL ON NULL INPUT
+ AS
+$$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typtypmod ELSE $1.atttypmod END$$;
+
+-- these functions encapsulate knowledge about the encoding of typmod:
+
+CREATE FUNCTION _pg_char_max_length(typid oid, typmod int4) RETURNS integer
+ LANGUAGE sql
+ IMMUTABLE
+ RETURNS NULL ON NULL INPUT
+ AS
+$$SELECT
+ CASE WHEN $2 = -1 /* default typmod */
+ THEN null
+ WHEN $1 IN (1042, 1043) /* char, varchar */
+ THEN $2 - 4
+ WHEN $1 IN (1560, 1562) /* bit, varbit */
+ THEN $2
+ ELSE null
+ END$$;
+
+CREATE FUNCTION _pg_char_octet_length(typid oid, typmod int4) RETURNS integer
+ LANGUAGE sql
+ IMMUTABLE
+ RETURNS NULL ON NULL INPUT
+ AS
+$$SELECT
+ CASE WHEN $1 IN (25, 1042, 1043) /* text, char, varchar */
+ THEN CASE WHEN $2 = -1 /* default typmod */
+ THEN CAST(2^30 AS integer)
+ ELSE information_schema._pg_char_max_length($1, $2) *
+ pg_catalog.pg_encoding_max_length((SELECT encoding FROM pg_catalog.pg_database WHERE datname = pg_catalog.current_database()))
+ END
+ ELSE null
+ END$$;
+
+CREATE FUNCTION _pg_numeric_precision(typid oid, typmod int4) RETURNS integer
+ LANGUAGE sql
+ IMMUTABLE
+ RETURNS NULL ON NULL INPUT
+ AS
+$$SELECT
+ CASE $1
+ WHEN 21 /*int2*/ THEN 16
+ WHEN 23 /*int4*/ THEN 32
+ WHEN 20 /*int8*/ THEN 64
+ WHEN 1700 /*numeric*/ THEN
+ CASE WHEN $2 = -1
+ THEN null
+ ELSE (($2 - 4) >> 16) & 65535
+ END
+ WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
+ WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
+ ELSE null
+ END$$;
+
+CREATE FUNCTION _pg_numeric_precision_radix(typid oid, typmod int4) RETURNS integer
+ LANGUAGE sql
+ IMMUTABLE
+ RETURNS NULL ON NULL INPUT
+ AS
+$$SELECT
+ CASE WHEN $1 IN (21, 23, 20, 700, 701) THEN 2
+ WHEN $1 IN (1700) THEN 10
+ ELSE null
+ END$$;
+
+CREATE FUNCTION _pg_numeric_scale(typid oid, typmod int4) RETURNS integer
+ LANGUAGE sql
+ IMMUTABLE
+ RETURNS NULL ON NULL INPUT
+ AS
+$$SELECT
+ CASE WHEN $1 IN (21, 23, 20) THEN 0
+ WHEN $1 IN (1700) THEN
+ CASE WHEN $2 = -1
+ THEN null
+ ELSE ($2 - 4) & 65535
+ END
+ ELSE null
+ END$$;
+
+CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer
+ LANGUAGE sql
+ IMMUTABLE
+ RETURNS NULL ON NULL INPUT
+ AS
+$$SELECT
+ CASE WHEN $1 IN (1082) /* date */
+ THEN 0
+ WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */
+ THEN CASE WHEN $2 < 0 THEN 6 ELSE $2 END
+ WHEN $1 IN (1186) /* interval */
+ THEN CASE WHEN $2 < 0 OR $2 & 65535 = 65535 THEN 6 ELSE $2 & 65535 END
+ ELSE null
+ END$$;
+
+CREATE FUNCTION _pg_interval_type(typid oid, mod int4) RETURNS text
+ LANGUAGE sql
+ IMMUTABLE
+ RETURNS NULL ON NULL INPUT
+ AS
+$$SELECT
+ CASE WHEN $1 IN (1186) /* interval */
+ THEN upper(substring(format_type($1, $2) from 'interval[()0-9]* #"%#"' for '#'))
+ ELSE null
+ END$$;
--- 20.3 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
+-- 5.2 INFORMATION_SCHEMA_CATALOG_NAME view appears later.
/*
- * 20.4
+ * 5.3
* CARDINAL_NUMBER domain
*/
/*
- * 20.5
+ * 5.4
* CHARACTER_DATA domain
*/
/*
- * 20.6
+ * 5.5
* SQL_IDENTIFIER domain
*/
/*
- * 20.3
+ * 5.2
* INFORMATION_SCHEMA_CATALOG_NAME view
*/
/*
- * 20.7
+ * 5.6
* TIME_STAMP domain
*/
-CREATE DOMAIN time_stamp AS timestamp(2)
+CREATE DOMAIN time_stamp AS timestamp(2) with time zone
DEFAULT current_timestamp(2);
+/*
+ * 5.7
+ * YES_OR_NO domain
+ */
+
+CREATE DOMAIN yes_or_no AS character varying(3)
+ CONSTRAINT yes_or_no_check CHECK (value IN ('YES', 'NO'));
+
+
+-- 5.8 ADMINISTRABLE_ROLE_AUTHORIZATIONS view appears later.
+
/*
- * 20.9
+ * 5.9
* APPLICABLE_ROLES view
*/
CREATE VIEW applicable_roles AS
- SELECT CAST(current_user AS sql_identifier) AS grantee,
- CAST(g.groname AS sql_identifier) AS role_name,
- CAST('NO' AS character_data) AS is_grantable
+ SELECT CAST(a.rolname AS sql_identifier) AS grantee,
+ CAST(b.rolname AS sql_identifier) AS role_name,
+ CAST(CASE WHEN m.admin_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
+ FROM pg_auth_members m
+ JOIN pg_authid a ON (m.member = a.oid)
+ JOIN pg_authid b ON (m.roleid = b.oid)
+ WHERE pg_has_role(a.oid, 'USAGE');
- FROM pg_group g, pg_user u
+GRANT SELECT ON applicable_roles TO PUBLIC;
- WHERE u.usesysid = ANY (g.grolist)
- AND u.usename = current_user;
-GRANT SELECT ON applicable_roles TO PUBLIC;
+/*
+ * 5.8
+ * ADMINISTRABLE_ROLE_AUTHORIZATIONS view
+ */
+
+CREATE VIEW administrable_role_authorizations AS
+ SELECT *
+ FROM applicable_roles
+ WHERE is_grantable = 'YES';
+
+GRANT SELECT ON administrable_role_authorizations TO PUBLIC;
+
+
+/*
+ * 5.10
+ * ASSERTIONS view
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.11
+ * ATTRIBUTES view
+ */
+
+CREATE VIEW attributes AS
+ SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
+ CAST(nc.nspname AS sql_identifier) AS udt_schema,
+ CAST(c.relname AS sql_identifier) AS udt_name,
+ CAST(a.attname AS sql_identifier) AS attribute_name,
+ CAST(a.attnum AS cardinal_number) AS ordinal_position,
+ CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS attribute_default,
+ CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
+ AS yes_or_no)
+ AS is_nullable, -- This column was apparently removed between SQL:2003 and SQL:2008.
+
+ CAST(
+ CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
+ WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null)
+ ELSE 'USER-DEFINED' END
+ AS character_data)
+ AS data_type,
+
+ CAST(
+ _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
+ AS cardinal_number)
+ AS character_maximum_length,
+
+ CAST(
+ _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
+ AS cardinal_number)
+ AS character_octet_length,
+
+ CAST(null AS sql_identifier) AS character_set_catalog,
+ CAST(null AS sql_identifier) AS character_set_schema,
+ CAST(null AS sql_identifier) AS character_set_name,
+
+ CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
+ CAST(nco.nspname AS sql_identifier) AS collation_schema,
+ CAST(co.collname AS sql_identifier) AS collation_name,
+
+ CAST(
+ _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
+ AS cardinal_number)
+ AS numeric_precision,
+
+ CAST(
+ _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
+ AS cardinal_number)
+ AS numeric_precision_radix,
+
+ CAST(
+ _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
+ AS cardinal_number)
+ AS numeric_scale,
+
+ CAST(
+ _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
+ AS cardinal_number)
+ AS datetime_precision,
+
+ CAST(
+ _pg_interval_type(_pg_truetypid(a, t), _pg_truetypmod(a, t))
+ AS character_data)
+ AS interval_type,
+ CAST(null AS cardinal_number) AS interval_precision,
+
+ CAST(current_database() AS sql_identifier) AS attribute_udt_catalog,
+ CAST(nt.nspname AS sql_identifier) AS attribute_udt_schema,
+ CAST(t.typname AS sql_identifier) AS attribute_udt_name,
+
+ CAST(null AS sql_identifier) AS scope_catalog,
+ CAST(null AS sql_identifier) AS scope_schema,
+ CAST(null AS sql_identifier) AS scope_name,
+
+ CAST(null AS cardinal_number) AS maximum_cardinality,
+ CAST(a.attnum AS sql_identifier) AS dtd_identifier,
+ CAST('NO' AS yes_or_no) AS is_derived_reference_attribute
+
+ FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)
+ JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid
+ JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid
+ LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
+ ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
+
+ WHERE a.attnum > 0 AND NOT a.attisdropped
+ AND c.relkind in ('c')
+ AND (pg_has_role(c.relowner, 'USAGE')
+ OR has_type_privilege(c.reltype, 'USAGE'));
+
+GRANT SELECT ON attributes TO PUBLIC;
+
+
+/*
+ * 5.12
+ * CHARACTER_SETS view
+ */
+
+CREATE VIEW character_sets AS
+ SELECT CAST(null AS sql_identifier) AS character_set_catalog,
+ CAST(null AS sql_identifier) AS character_set_schema,
+ CAST(getdatabaseencoding() AS sql_identifier) AS character_set_name,
+ CAST(CASE WHEN getdatabaseencoding() = 'UTF8' THEN 'UCS' ELSE getdatabaseencoding() END AS sql_identifier) AS character_repertoire,
+ CAST(getdatabaseencoding() AS sql_identifier) AS form_of_use,
+ CAST(current_database() AS sql_identifier) AS default_collate_catalog,
+ CAST(nc.nspname AS sql_identifier) AS default_collate_schema,
+ CAST(c.collname AS sql_identifier) AS default_collate_name
+ FROM pg_database d
+ LEFT JOIN (pg_collation c JOIN pg_namespace nc ON (c.collnamespace = nc.oid))
+ ON (datcollate = collcollate AND datctype = collctype)
+ WHERE d.datname = current_database()
+ ORDER BY char_length(c.collname) DESC, c.collname ASC -- prefer full/canonical name
+ LIMIT 1;
+
+GRANT SELECT ON character_sets TO PUBLIC;
+
+
+/*
+ * 5.13
+ * CHECK_CONSTRAINT_ROUTINE_USAGE view
+ */
+
+CREATE VIEW check_constraint_routine_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 specific_catalog,
+ CAST(np.nspname AS sql_identifier) AS specific_schema,
+ CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
+ FROM pg_namespace nc, pg_constraint c, pg_depend d, pg_proc p, pg_namespace np
+ WHERE nc.oid = c.connamespace
+ AND c.contype = 'c'
+ AND c.oid = d.objid
+ AND d.classid = 'pg_catalog.pg_constraint'::regclass
+ AND d.refobjid = p.oid
+ AND d.refclassid = 'pg_catalog.pg_proc'::regclass
+ AND p.pronamespace = np.oid
+ AND pg_has_role(p.proowner, 'USAGE');
+
+GRANT SELECT ON check_constraint_routine_usage TO PUBLIC;
/*
- * 20.13
+ * 5.14
* CHECK_CONSTRAINTS view
*/
CAST(con.conname AS sql_identifier) AS constraint_name,
CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data)
AS check_clause
- FROM pg_namespace rs,
- pg_constraint con
+ FROM pg_constraint con
+ LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace)
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 c.relkind = 'r';
+ LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid)
+ WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE')
+ AND con.contype = 'c'
+
+ UNION
+ -- not-null constraints
+
+ SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
+ CAST(n.nspname AS sql_identifier) AS constraint_schema,
+ 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
+ CAST(a.attname || ' IS NOT NULL' AS character_data)
+ AS check_clause
+ FROM pg_namespace n, pg_class r, pg_attribute a
+ WHERE n.oid = r.relnamespace
+ AND r.oid = a.attrelid
+ AND a.attnum > 0
+ AND NOT a.attisdropped
+ AND a.attnotnull
+ AND r.relkind = 'r'
+ AND pg_has_role(r.relowner, 'USAGE');
GRANT SELECT ON check_constraints TO PUBLIC;
/*
- * 20.15
+ * 5.15
+ * COLLATIONS view
+ */
+
+CREATE VIEW collations AS
+ SELECT CAST(current_database() AS sql_identifier) AS collation_catalog,
+ CAST(nc.nspname AS sql_identifier) AS collation_schema,
+ CAST(c.collname AS sql_identifier) AS collation_name,
+ CAST('NO PAD' AS character_data) AS pad_attribute
+ FROM pg_collation c, pg_namespace nc
+ WHERE c.collnamespace = nc.oid
+ AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()));
+
+GRANT SELECT ON collations TO PUBLIC;
+
+
+/*
+ * 5.16
+ * COLLATION_CHARACTER_SET_APPLICABILITY view
+ */
+
+CREATE VIEW collation_character_set_applicability AS
+ SELECT CAST(current_database() AS sql_identifier) AS collation_catalog,
+ CAST(nc.nspname AS sql_identifier) AS collation_schema,
+ CAST(c.collname AS sql_identifier) AS collation_name,
+ CAST(null AS sql_identifier) AS character_set_catalog,
+ CAST(null AS sql_identifier) AS character_set_schema,
+ CAST(getdatabaseencoding() AS sql_identifier) AS character_set_name
+ FROM pg_collation c, pg_namespace nc
+ WHERE c.collnamespace = nc.oid
+ AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()));
+
+GRANT SELECT ON collation_character_set_applicability TO PUBLIC;
+
+
+/*
+ * 5.17
+ * COLUMN_COLUMN_USAGE view
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.18
* COLUMN_DOMAIN_USAGE view
*/
CAST(a.attname AS sql_identifier) AS column_name
FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc,
- pg_attribute a, pg_user u
+ pg_attribute a
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 c.relkind IN ('r', 'v', 'f')
AND a.attnum > 0
AND NOT a.attisdropped
- AND u.usename = current_user;
+ AND pg_has_role(t.typowner, 'USAGE');
GRANT SELECT ON column_domain_usage TO PUBLIC;
/*
- * 20.16
+ * 5.19
* COLUMN_PRIVILEGES
*/
CREATE VIEW column_privileges AS
- SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
- CAST(grantee.name AS sql_identifier) AS grantee,
+ SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+ CAST(grantee.rolname 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(x.relname AS sql_identifier) AS table_name,
+ CAST(x.attname AS sql_identifier) AS column_name,
+ CAST(x.prtype AS character_data) AS privilege_type,
CAST(
- CASE WHEN aclcontains(c.relacl,
- makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true))
- THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
+ CASE WHEN
+ -- object owner always has grant options
+ pg_has_role(x.grantee, x.relowner, 'USAGE')
+ OR x.grantable
+ THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
- FROM pg_attribute a,
- pg_class c,
+ FROM (
+ SELECT pr_c.grantor,
+ pr_c.grantee,
+ attname,
+ relname,
+ relnamespace,
+ pr_c.prtype,
+ pr_c.grantable,
+ pr_c.relowner
+ FROM (SELECT oid, relname, relnamespace, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).*
+ FROM pg_class
+ WHERE relkind IN ('r', 'v', 'f')
+ ) pr_c (oid, relname, relnamespace, relowner, grantor, grantee, prtype, grantable),
+ pg_attribute a
+ WHERE a.attrelid = pr_c.oid
+ AND a.attnum > 0
+ AND NOT a.attisdropped
+ UNION
+ SELECT pr_a.grantor,
+ pr_a.grantee,
+ attname,
+ relname,
+ relnamespace,
+ pr_a.prtype,
+ pr_a.grantable,
+ c.relowner
+ FROM (SELECT attrelid, attname, (aclexplode(coalesce(attacl, acldefault('c', relowner)))).*
+ FROM pg_attribute a JOIN pg_class cc ON (a.attrelid = cc.oid)
+ WHERE attnum > 0
+ AND NOT attisdropped
+ ) pr_a (attrelid, attname, grantor, grantee, prtype, grantable),
+ pg_class c
+ WHERE pr_a.attrelid = c.oid
+ AND relkind IN ('r', 'v', 'f')
+ ) x,
pg_namespace nc,
- pg_user u_grantor,
+ pg_authid u_grantor,
(
- SELECT usesysid, 0, usename FROM pg_user
- UNION ALL
- SELECT 0, grosysid, groname FROM pg_group
+ SELECT oid, rolname FROM pg_authid
UNION ALL
- SELECT 0, 0, 'PUBLIC'
- ) AS grantee (usesysid, grosysid, name),
- (SELECT 'SELECT' UNION ALL
- SELECT 'INSERT' UNION ALL
- SELECT 'UPDATE' UNION ALL
- SELECT 'REFERENCES') AS pr (type)
+ SELECT 0::oid, 'PUBLIC'
+ ) AS grantee (oid, rolname)
- 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(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false))
- AND (u_grantor.usename = current_user
- OR grantee.name = current_user
- OR grantee.name = 'PUBLIC');
+ WHERE x.relnamespace = nc.oid
+ AND x.grantee = grantee.oid
+ AND x.grantor = u_grantor.oid
+ AND x.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'REFERENCES')
+ AND (pg_has_role(u_grantor.oid, 'USAGE')
+ OR pg_has_role(grantee.oid, 'USAGE')
+ OR grantee.rolname = 'PUBLIC');
GRANT SELECT ON column_privileges TO PUBLIC;
/*
- * 20.17
+ * 5.20
* COLUMN_UDT_USAGE view
*/
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,
+ FROM pg_attribute a, pg_class c, pg_namespace nc,
(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;
+ AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f')
+ AND pg_has_role(coalesce(bt.typowner, t.typowner), 'USAGE');
GRANT SELECT ON column_udt_usage TO PUBLIC;
/*
- * 20.18
+ * 5.21
* COLUMNS view
*/
CAST(c.relname AS sql_identifier) AS table_name,
CAST(a.attname AS sql_identifier) AS column_name,
CAST(a.attnum AS cardinal_number) AS ordinal_position,
- CAST(
- CASE WHEN u.usename = current_user THEN a.adsrc ELSE null END
- AS character_data)
- AS column_default,
+ CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS column_default,
CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
- AS character_data)
+ AS yes_or_no)
AS is_nullable,
CAST(
AS data_type,
CAST(
- CASE WHEN t.typtype = 'd' THEN
- CASE WHEN t.typbasetype IN (25, 1042, 1043, 1560, 1562) AND t.typtypmod <> -1
- THEN t.typtypmod - 4
- ELSE null END
- ELSE
- CASE WHEN a.atttypid IN (25, 1042, 1043, 1560, 1562) AND a.atttypmod <> -1
- THEN a.atttypmod - 4
- ELSE null END
- END
+ _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
AS cardinal_number)
AS character_maximum_length,
CAST(
- CASE WHEN t.typtype = 'd' THEN
- CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END
- ELSE
- CASE WHEN a.atttypid IN (25, 1042, 1043) THEN 2^30 ELSE null END
- END
+ _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t))
AS cardinal_number)
AS character_octet_length,
CAST(
- CASE (CASE WHEN t.typtype = 'd' THEN t.typbasetype ELSE a.atttypid END)
- WHEN 21 /*int2*/ THEN 16
- WHEN 23 /*int4*/ THEN 32
- WHEN 20 /*int8*/ THEN 64
- WHEN 1700 /*numeric*/ THEN ((CASE WHEN t.typtype = 'd' THEN t.typtypmod ELSE a.atttypmod END - 4) >> 16) & 65535
- WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
- WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
- ELSE null END
+ _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
AS cardinal_number)
AS numeric_precision,
CAST(
- CASE WHEN t.typtype = 'd' THEN
- CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2
- WHEN t.typbasetype IN (1700) THEN 10
- ELSE null END
- ELSE
- CASE WHEN a.atttypid IN (21, 23, 20, 700, 701) THEN 2
- WHEN a.atttypid IN (1700) THEN 10
- ELSE null END
- END
+ _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t))
AS cardinal_number)
AS numeric_precision_radix,
CAST(
- CASE WHEN t.typtype = 'd' THEN
- CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0
- WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535
- ELSE null END
- ELSE
- CASE WHEN a.atttypid IN (21, 23, 20) THEN 0
- WHEN a.atttypid IN (1700) THEN (a.atttypmod - 4) & 65535
- ELSE null END
- END
+ _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t))
AS cardinal_number)
AS numeric_scale,
CAST(
- CASE WHEN t.typtype = 'd' THEN
- CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266)
- THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END)
- WHEN t.typbasetype IN (1186)
- THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END)
- ELSE null END
- ELSE
- CASE WHEN a.atttypid IN (1083, 1114, 1184, 1266)
- THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod ELSE null END)
- WHEN a.atttypid IN (1186)
- THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod & 65535 ELSE null END)
- ELSE null END
- END
+ _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t))
AS cardinal_number)
AS datetime_precision,
- CAST(null AS character_data) AS interval_type, -- XXX
- CAST(null AS character_data) AS interval_precision, -- XXX
+ CAST(
+ _pg_interval_type(_pg_truetypid(a, t), _pg_truetypmod(a, t))
+ AS character_data)
+ AS interval_type,
+ CAST(null AS cardinal_number) AS interval_precision,
CAST(null AS sql_identifier) AS character_set_catalog,
CAST(null AS sql_identifier) AS character_set_schema,
CAST(null AS sql_identifier) AS character_set_name,
- CAST(null AS sql_identifier) AS collation_catalog,
- CAST(null AS sql_identifier) AS collation_schema,
- CAST(null AS sql_identifier) AS collation_name,
+ CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
+ CAST(nco.nspname AS sql_identifier) AS collation_schema,
+ CAST(co.collname AS sql_identifier) AS collation_name,
CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END
AS sql_identifier) AS domain_catalog,
CAST(null AS cardinal_number) AS maximum_cardinality,
CAST(a.attnum AS sql_identifier) AS dtd_identifier,
- CAST('NO' AS character_data) AS is_self_referencing
-
- FROM (pg_attribute LEFT JOIN pg_attrdef ON attrelid = adrelid AND attnum = adnum) AS 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))
+ CAST('NO' AS yes_or_no) AS is_self_referencing,
+
+ CAST('NO' AS yes_or_no) AS is_identity,
+ CAST(null AS character_data) AS identity_generation,
+ CAST(null AS character_data) AS identity_start,
+ CAST(null AS character_data) AS identity_increment,
+ CAST(null AS character_data) AS identity_maximum,
+ CAST(null AS character_data) AS identity_minimum,
+ CAST(null AS yes_or_no) AS identity_cycle,
+
+ CAST('NEVER' AS character_data) AS is_generated,
+ CAST(null AS character_data) AS generation_expression,
+
+ CAST(CASE WHEN c.relkind = 'r' OR
+ (c.relkind IN ('v', 'f') AND
+ pg_column_is_updatable(c.oid, a.attnum, false))
+ THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable
+
+ FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum)
+ JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid
+ JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid
+ LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid))
ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
+ LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
+ ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
- WHERE a.attrelid = c.oid
- AND a.atttypid = t.oid
- AND u.usesysid = c.relowner
- AND nc.oid = c.relnamespace
+ WHERE (NOT pg_is_other_temp_schema(nc.oid))
- AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v')
+ AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f')
- AND (u.usename = current_user
- OR has_table_privilege(c.oid, 'SELECT')
- OR has_table_privilege(c.oid, 'INSERT')
- OR has_table_privilege(c.oid, 'UPDATE')
- OR has_table_privilege(c.oid, 'REFERENCES') );
+ AND (pg_has_role(c.relowner, 'USAGE')
+ OR has_column_privilege(c.oid, a.attnum,
+ 'SELECT, INSERT, UPDATE, REFERENCES'));
GRANT SELECT ON columns TO PUBLIC;
/*
- * 20.19
+ * 5.22
* CONSTRAINT_COLUMN_USAGE view
*/
-/* This returns the integers from 1 to INDEX_MAX_KEYS/FUNC_MAX_ARGS */
-CREATE FUNCTION _pg_keypositions() RETURNS SETOF integer
- LANGUAGE sql
- IMMUTABLE
- AS 'select 1 union all select 2 union all select 3 union all
- select 4 union all select 5 union all select 6 union all
- select 7 union all select 8 union all select 9 union all
- select 10 union all select 11 union all select 12 union all
- select 13 union all select 14 union all select 15 union all
- select 16 union all select 17 union all select 18 union all
- select 19 union all select 20 union all select 21 union all
- select 22 union all select 23 union all select 24 union all
- select 25 union all select 26 union all select 27 union all
- select 28 union all select 29 union all select 30 union all
- select 31 union all select 32';
-
CREATE VIEW constraint_column_usage AS
SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
CAST(tblschema AS sql_identifier) AS table_schema,
AND c.connamespace = nc.oid
AND c.contype = 'c'
AND r.relkind = 'r'
- AND a.attnum > 0
AND NOT a.attisdropped
UNION ALL
/* 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, _pg_keypositions() AS pos(n)
+ pg_constraint c
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 (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND a.attnum = ANY (c.confkey)
+ ELSE r.oid = c.conrelid AND a.attnum = ANY (c.conkey) END)
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
+ ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname)
- WHERE x.tblowner = u.usesysid AND u.usename = current_user;
+ WHERE pg_has_role(x.tblowner, 'USAGE');
GRANT SELECT ON constraint_column_usage TO PUBLIC;
/*
- * 20.20
+ * 5.23
+ * CONSTRAINT_PERIOD_USAGE view
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.24
* CONSTRAINT_TABLE_USAGE view
*/
CAST(c.conname AS sql_identifier) AS constraint_name
FROM pg_constraint c, pg_namespace nc,
- pg_class r, pg_namespace nr,
- pg_user u
+ pg_class r, pg_namespace nr
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;
+ AND pg_has_role(r.relowner, 'USAGE');
GRANT SELECT ON constraint_table_usage TO PUBLIC;
--- 20.21 DATA_TYPE_PRIVILEGES view appears later.
+-- 5.25 DATA_TYPE_PRIVILEGES view appears later.
+
+
+/*
+ * 5.26
+ * DIRECT_SUPERTABLES view
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.27
+ * DIRECT_SUPERTYPES view
+ */
+
+-- feature not supported
/*
- * 20.24
+ * 5.28
* DOMAIN_CONSTRAINTS view
*/
CAST(n.nspname AS sql_identifier) AS domain_schema,
CAST(t.typname AS sql_identifier) AS domain_name,
CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END
- AS character_data) AS is_deferrable,
+ AS yes_or_no) AS is_deferrable,
CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END
- AS character_data) AS initially_deferred
- FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t, pg_user u
+ AS yes_or_no) AS initially_deferred
+ FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t
WHERE rs.oid = con.connamespace
AND n.oid = t.typnamespace
- AND u.usesysid = t.typowner
- AND u.usename = current_user
- AND t.oid = con.contypid;
+ AND t.oid = con.contypid
+ AND (pg_has_role(t.typowner, 'USAGE')
+ OR has_type_privilege(t.oid, 'USAGE'));
GRANT SELECT ON domain_constraints TO PUBLIC;
/*
- * 20.25
* DOMAIN_UDT_USAGE view
+ * apparently removed in SQL:2003
*/
CREATE VIEW domain_udt_usage AS
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
+ pg_type bt, pg_namespace nbt
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;
+ AND pg_has_role(bt.typowner, 'USAGE');
GRANT SELECT ON domain_udt_usage TO PUBLIC;
/*
- * 20.26
+ * 5.29
* DOMAINS view
*/
AS data_type,
CAST(
- CASE WHEN t.typbasetype IN (25, 1042, 1043, 1560, 1562) AND t.typtypmod <> -1
- THEN t.typtypmod - 4
- ELSE null END
+ _pg_char_max_length(t.typbasetype, t.typtypmod)
AS cardinal_number)
AS character_maximum_length,
CAST(
- CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END
+ _pg_char_octet_length(t.typbasetype, t.typtypmod)
AS cardinal_number)
AS character_octet_length,
CAST(null AS sql_identifier) AS character_set_schema,
CAST(null AS sql_identifier) AS character_set_name,
- CAST(null AS sql_identifier) AS collation_catalog,
- CAST(null AS sql_identifier) AS collation_schema,
- CAST(null AS sql_identifier) AS collation_name,
+ CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
+ CAST(nco.nspname AS sql_identifier) AS collation_schema,
+ CAST(co.collname AS sql_identifier) AS collation_name,
CAST(
- CASE t.typbasetype
- WHEN 21 /*int2*/ THEN 16
- WHEN 23 /*int4*/ THEN 32
- WHEN 20 /*int8*/ THEN 64
- WHEN 1700 /*numeric*/ THEN ((t.typtypmod - 4) >> 16) & 65535
- WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
- WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
- ELSE null END
+ _pg_numeric_precision(t.typbasetype, t.typtypmod)
AS cardinal_number)
AS numeric_precision,
CAST(
- CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2
- WHEN t.typbasetype IN (1700) THEN 10
- ELSE null END
+ _pg_numeric_precision_radix(t.typbasetype, t.typtypmod)
AS cardinal_number)
AS numeric_precision_radix,
CAST(
- CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0
- WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535
- ELSE null END
+ _pg_numeric_scale(t.typbasetype, t.typtypmod)
AS cardinal_number)
AS numeric_scale,
CAST(
- CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266)
- THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END)
- WHEN t.typbasetype IN (1186)
- THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END)
- ELSE null END
+ _pg_datetime_precision(t.typbasetype, t.typtypmod)
AS cardinal_number)
AS datetime_precision,
- CAST(null AS character_data) AS interval_type, -- XXX
- CAST(null AS character_data) AS interval_precision, -- XXX
+ CAST(
+ _pg_interval_type(t.typbasetype, t.typtypmod)
+ AS character_data)
+ AS interval_type,
+ CAST(null AS cardinal_number) AS interval_precision,
CAST(t.typdefault AS character_data) AS domain_default,
CAST(null AS cardinal_number) AS maximum_cardinality,
CAST(1 AS sql_identifier) AS dtd_identifier
- FROM pg_type t, pg_namespace nt,
- pg_type bt, pg_namespace nbt
+ FROM (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid)
+ JOIN (pg_type bt JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid)
+ ON (t.typbasetype = bt.oid AND t.typtype = 'd')
+ LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
+ ON t.typcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
- WHERE t.typnamespace = nt.oid
- AND t.typbasetype = bt.oid
- AND bt.typnamespace = nbt.oid
- AND t.typtype = 'd';
+ WHERE (pg_has_role(t.typowner, 'USAGE')
+ OR has_type_privilege(t.oid, 'USAGE'));
GRANT SELECT ON domains TO PUBLIC;
--- 20.27 ELEMENT_TYPES view appears later.
+-- 5.30 ELEMENT_TYPES view appears later.
/*
- * 20.28
+ * 5.31
* ENABLED_ROLES view
*/
CREATE VIEW enabled_roles AS
- SELECT CAST(g.groname AS sql_identifier) AS role_name
- FROM pg_group g, pg_user u
- WHERE u.usesysid = ANY (g.grolist)
- AND u.usename = current_user;
+ SELECT CAST(a.rolname AS sql_identifier) AS role_name
+ FROM pg_authid a
+ WHERE pg_has_role(a.oid, 'USAGE');
GRANT SELECT ON enabled_roles TO PUBLIC;
/*
- * 20.30
+ * 5.32
+ * FIELDS view
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.33
* 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(nc_nspname AS sql_identifier) AS constraint_schema,
+ CAST(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(nr_nspname AS sql_identifier) AS table_schema,
+ CAST(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, _pg_keypositions() 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
+ CAST((ss.x).n AS cardinal_number) AS ordinal_position,
+ CAST(CASE WHEN contype = 'f' THEN
+ _pg_index_position(ss.conindid, ss.confkey[(ss.x).n])
+ ELSE NULL
+ END AS cardinal_number)
+ AS position_in_unique_constraint
+ FROM pg_attribute a,
+ (SELECT r.oid AS roid, r.relname, r.relowner,
+ nc.nspname AS nc_nspname, nr.nspname AS nr_nspname,
+ c.oid AS coid, c.conname, c.contype, c.conindid,
+ c.confkey, c.confrelid,
+ _pg_expandarray(c.conkey) AS x
+ FROM pg_namespace nr, pg_class r, pg_namespace nc,
+ pg_constraint c
+ WHERE nr.oid = r.relnamespace
+ AND r.oid = c.conrelid
+ AND nc.oid = c.connamespace
+ AND c.contype IN ('p', 'u', 'f')
+ AND r.relkind = 'r'
+ AND (NOT pg_is_other_temp_schema(nr.oid)) ) AS ss
+ WHERE ss.roid = a.attrelid
+ AND a.attnum = (ss.x).x
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;
+ AND (pg_has_role(relowner, 'USAGE')
+ OR has_column_privilege(roid, a.attnum,
+ 'SELECT, INSERT, UPDATE, REFERENCES'));
GRANT SELECT ON key_column_usage TO PUBLIC;
/*
- * 20.33
- * PARAMETERS view
+ * 5.34
+ * KEY_PERIOD_USAGE view
*/
-CREATE VIEW parameters AS
- SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
- CAST(n.nspname AS sql_identifier) AS specific_schema,
- CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
- CAST(pos.n AS cardinal_number) AS ordinal_position,
- CAST('IN' AS character_data) AS parameter_mode,
- CAST('NO' AS character_data) AS is_result,
- CAST('NO' AS character_data) AS as_locator,
- CAST(null AS sql_identifier) AS parameter_name,
- CAST(
- CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
- WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
- ELSE 'USER-DEFINED' END AS character_data)
- AS data_type,
- CAST(null AS cardinal_number) AS character_maximum_length,
- CAST(null AS cardinal_number) AS character_octet_length,
- CAST(null AS sql_identifier) AS character_set_catalog,
+-- feature not supported
+
+
+/*
+ * 5.35
+ * METHOD_SPECIFICATION_PARAMETERS view
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.36
+ * METHOD_SPECIFICATIONS view
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.37
+ * PARAMETERS view
+ */
+
+CREATE VIEW parameters AS
+ SELECT CAST(current_database() AS sql_identifier) AS specific_catalog,
+ CAST(n_nspname AS sql_identifier) AS specific_schema,
+ CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name,
+ CAST((ss.x).n AS cardinal_number) AS ordinal_position,
+ CAST(
+ CASE WHEN proargmodes IS NULL THEN 'IN'
+ WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN'
+ WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'
+ WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'
+ WHEN proargmodes[(ss.x).n] = 'v' THEN 'IN'
+ WHEN proargmodes[(ss.x).n] = 't' THEN 'OUT'
+ END AS character_data) AS parameter_mode,
+ CAST('NO' AS yes_or_no) AS is_result,
+ CAST('NO' AS yes_or_no) AS as_locator,
+ CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name,
+ CAST(
+ CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY'
+ WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null)
+ ELSE 'USER-DEFINED' END AS character_data)
+ AS data_type,
+ CAST(null AS cardinal_number) AS character_maximum_length,
+ CAST(null AS cardinal_number) AS character_octet_length,
+ CAST(null AS sql_identifier) AS character_set_catalog,
CAST(null AS sql_identifier) AS character_set_schema,
CAST(null AS sql_identifier) AS character_set_name,
CAST(null AS sql_identifier) AS collation_catalog,
CAST(null AS cardinal_number) AS numeric_scale,
CAST(null AS cardinal_number) AS datetime_precision,
CAST(null AS character_data) AS interval_type,
- CAST(null AS character_data) AS interval_precision,
+ CAST(null AS cardinal_number) AS interval_precision,
CAST(current_database() AS sql_identifier) AS udt_catalog,
CAST(nt.nspname AS sql_identifier) AS udt_schema,
CAST(t.typname AS sql_identifier) AS udt_name,
CAST(null AS sql_identifier) AS scope_schema,
CAST(null AS sql_identifier) AS scope_name,
CAST(null AS cardinal_number) AS maximum_cardinality,
- CAST(pos.n AS sql_identifier) AS dtd_identifier
-
- FROM pg_namespace n, pg_proc p, pg_type t, pg_namespace nt, pg_user u,
- _pg_keypositions() AS pos(n)
+ CAST((ss.x).n AS sql_identifier) AS dtd_identifier,
+ CAST(
+ CASE WHEN pg_has_role(proowner, 'USAGE')
+ THEN pg_get_function_arg_default(p_oid, (ss.x).n)
+ ELSE NULL END
+ AS character_data) AS parameter_default
- WHERE n.oid = p.pronamespace AND p.pronargs >= pos.n
- AND p.proargtypes[pos.n-1] = t.oid AND t.typnamespace = nt.oid
- AND p.proowner = u.usesysid
- AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE'));
+ FROM pg_type t, pg_namespace nt,
+ (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid, p.proowner,
+ p.proargnames, p.proargmodes,
+ _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
+ FROM pg_namespace n, pg_proc p
+ WHERE n.oid = p.pronamespace
+ AND (pg_has_role(p.proowner, 'USAGE') OR
+ has_function_privilege(p.oid, 'EXECUTE'))) AS ss
+ WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid;
GRANT SELECT ON parameters TO PUBLIC;
/*
- * 20.35
- * REFERENTIAL_CONSTRAINTS view
+ * 5.38
+ * PERIODS view
*/
-CREATE FUNCTION _pg_keyissubset(smallint[], smallint[]) RETURNS boolean
- LANGUAGE sql
- IMMUTABLE
- RETURNS NULL ON NULL INPUT
- AS 'select $1[1] is null or ($1[1] = any ($2) and coalesce(information_schema._pg_keyissubset($1[2:pg_catalog.array_upper($1,1)], $2), true))';
+-- feature not supported
-CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean
- LANGUAGE sql
- IMMUTABLE
- RETURNS NULL ON NULL INPUT
- AS 'select information_schema._pg_keyissubset($1, $2) and information_schema._pg_keyissubset($2, $1)';
+
+/*
+ * 5.39
+ * REFERENCED_TYPES view
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.40
+ * REFERENTIAL_CONSTRAINTS view
+ */
CREATE VIEW referential_constraints AS
SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
CAST(
CASE con.confmatchtype WHEN 'f' THEN 'FULL'
WHEN 'p' THEN 'PARTIAL'
- WHEN 'u' THEN 'NONE' END
+ WHEN 's' THEN 'NONE' END
AS character_data) AS match_option,
CAST(
WHEN 'a' THEN 'NO ACTION' END
AS character_data) AS delete_rule
- FROM (pg_namespace ncon INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
- INNER JOIN pg_class c ON con.conrelid = c.oid
- INNER JOIN pg_user u ON c.relowner = u.usesysid)
- LEFT JOIN
- (pg_constraint pkc INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid)
- ON con.confrelid = pkc.conrelid AND _pg_keysequal(con.confkey, pkc.conkey)
-
- WHERE c.relkind = 'r'
- AND con.contype = 'f'
- AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL)
- AND u.usename = current_user;
+ FROM (pg_namespace ncon
+ INNER JOIN pg_constraint con ON ncon.oid = con.connamespace
+ INNER JOIN pg_class c ON con.conrelid = c.oid AND con.contype = 'f')
+ LEFT JOIN pg_depend d1 -- find constraint's dependency on an index
+ ON d1.objid = con.oid AND d1.classid = 'pg_constraint'::regclass
+ AND d1.refclassid = 'pg_class'::regclass AND d1.refobjsubid = 0
+ LEFT JOIN pg_depend d2 -- find pkey/unique constraint for that index
+ ON d2.refclassid = 'pg_constraint'::regclass
+ AND d2.classid = 'pg_class'::regclass
+ AND d2.objid = d1.refobjid AND d2.objsubid = 0
+ AND d2.deptype = 'i'
+ LEFT JOIN pg_constraint pkc ON pkc.oid = d2.refobjid
+ AND pkc.contype IN ('p', 'u')
+ AND pkc.conrelid = con.confrelid
+ LEFT JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid
+
+ WHERE pg_has_role(c.relowner, 'USAGE')
+ -- SELECT privilege omitted, per SQL standard
+ OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
+ OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') ;
GRANT SELECT ON referential_constraints TO PUBLIC;
/*
- * 20.36
+ * 5.41
* ROLE_COLUMN_GRANTS view
*/
CREATE VIEW role_column_grants AS
- SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
- CAST(g_grantee.groname 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(0, g_grantee.grosysid, 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,
- pg_group g_grantee,
- (SELECT 'SELECT' UNION ALL
- SELECT 'INSERT' UNION ALL
- SELECT 'UPDATE' UNION ALL
- SELECT 'REFERENCES') 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(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false))
- AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
+ SELECT grantor,
+ grantee,
+ table_catalog,
+ table_schema,
+ table_name,
+ column_name,
+ privilege_type,
+ is_grantable
+ FROM column_privileges
+ WHERE grantor IN (SELECT role_name FROM enabled_roles)
+ OR grantee IN (SELECT role_name FROM enabled_roles);
GRANT SELECT ON role_column_grants TO PUBLIC;
-/*
- * 20.37
- * ROLE_ROUTINE_GRANTS view
- */
+-- 5.42 ROLE_ROUTINE_GRANTS view is based on 5.49 ROUTINE_PRIVILEGES and is defined there instead.
-CREATE VIEW role_routine_grants AS
- SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
- CAST(g_grantee.groname AS sql_identifier) AS grantee,
- CAST(current_database() AS sql_identifier) AS specific_catalog,
- CAST(n.nspname AS sql_identifier) AS specific_schema,
- CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
- CAST(current_database() AS sql_identifier) AS routine_catalog,
- CAST(n.nspname AS sql_identifier) AS routine_schema,
- CAST(p.proname AS sql_identifier) AS routine_name,
- CAST('EXECUTE' AS character_data) AS privilege_type,
- CAST(
- CASE WHEN aclcontains(p.proacl,
- makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true))
- THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
- FROM pg_proc p,
- pg_namespace n,
- pg_user u_grantor,
- pg_group g_grantee
-
- WHERE p.pronamespace = n.oid
- AND aclcontains(p.proacl,
- makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false))
- AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
-
-GRANT SELECT ON role_routine_grants TO PUBLIC;
+-- 5.43 ROLE_TABLE_GRANTS view is based on 5.62 TABLE_PRIVILEGES and is defined there instead.
/*
- * 20.38
- * ROLE_TABLE_GRANTS view
+ * 5.44
+ * ROLE_TABLE_METHOD_GRANTS view
*/
-CREATE VIEW role_table_grants AS
- SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
- CAST(g_grantee.groname 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(pr.type AS character_data) AS privilege_type,
- CAST(
- CASE WHEN aclcontains(c.relacl,
- makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, true))
- THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
- CAST('NO' AS character_data) AS with_hierarchy
+-- feature not supported
- FROM pg_class c,
- pg_namespace nc,
- pg_user u_grantor,
- pg_group g_grantee,
- (SELECT 'SELECT' UNION ALL
- SELECT 'DELETE' UNION ALL
- SELECT 'INSERT' UNION ALL
- SELECT 'UPDATE' UNION ALL
- SELECT 'REFERENCES' UNION ALL
- SELECT 'RULE' UNION ALL
- SELECT 'TRIGGER') AS pr (type)
- WHERE c.relnamespace = nc.oid
- AND c.relkind IN ('r', 'v')
- AND aclcontains(c.relacl,
- makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false))
- AND g_grantee.groname IN (SELECT role_name FROM enabled_roles);
-GRANT SELECT ON role_table_grants TO PUBLIC;
+-- 5.45 ROLE_USAGE_GRANTS view is based on 5.74 USAGE_PRIVILEGES and is defined there instead.
+
+
+-- 5.46 ROLE_UDT_GRANTS view is based on 5.73 UDT_PRIVILEGES and is defined there instead.
/*
- * 20.40
- * ROLE_USAGE_GRANTS view
+ * 5.47
+ * ROUTINE_COLUMN_USAGE view
*/
--- See USAGE_PRIVILEGES.
+-- not tracked by PostgreSQL
-CREATE VIEW role_usage_grants AS
- SELECT CAST(null AS sql_identifier) AS grantor,
- CAST(null AS sql_identifier) AS grantee,
- CAST(current_database() AS sql_identifier) AS object_catalog,
- CAST(null AS sql_identifier) AS object_schema,
- CAST(null AS sql_identifier) AS object_name,
- CAST(null AS character_data) AS object_type,
- CAST('USAGE' AS character_data) AS privilege_type,
- CAST(null AS character_data) AS is_grantable
- WHERE false;
+/*
+ * 5.48
+ * ROUTINE_PERIOD_USAGE view
+ */
-GRANT SELECT ON role_usage_grants TO PUBLIC;
+-- feature not supported
/*
- * 20.43
+ * 5.49
* ROUTINE_PRIVILEGES view
*/
CREATE VIEW routine_privileges AS
- SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
- CAST(grantee.name AS sql_identifier) AS grantee,
+ SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+ CAST(grantee.rolname AS sql_identifier) AS grantee,
CAST(current_database() AS sql_identifier) AS specific_catalog,
CAST(n.nspname AS sql_identifier) AS specific_schema,
CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
CAST(p.proname AS sql_identifier) AS routine_name,
CAST('EXECUTE' AS character_data) AS privilege_type,
CAST(
- CASE WHEN aclcontains(p.proacl,
- makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true))
- THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable
+ CASE WHEN
+ -- object owner always has grant options
+ pg_has_role(grantee.oid, p.proowner, 'USAGE')
+ OR p.grantable
+ THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
- FROM pg_proc p,
+ FROM (
+ SELECT oid, proname, proowner, pronamespace, (aclexplode(coalesce(proacl, acldefault('f', proowner)))).* FROM pg_proc
+ ) p (oid, proname, proowner, pronamespace, grantor, grantee, prtype, grantable),
pg_namespace n,
- pg_user u_grantor,
+ pg_authid u_grantor,
(
- SELECT usesysid, 0, usename FROM pg_user
- UNION ALL
- SELECT 0, grosysid, groname FROM pg_group
+ SELECT oid, rolname FROM pg_authid
UNION ALL
- SELECT 0, 0, 'PUBLIC'
- ) AS grantee (usesysid, grosysid, name)
+ SELECT 0::oid, 'PUBLIC'
+ ) AS grantee (oid, rolname)
WHERE p.pronamespace = n.oid
- AND aclcontains(p.proacl,
- makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false))
- AND (u_grantor.usename = current_user
- OR grantee.name = current_user
- OR grantee.name = 'PUBLIC');
+ AND grantee.oid = p.grantee
+ AND u_grantor.oid = p.grantor
+ AND p.prtype IN ('EXECUTE')
+ AND (pg_has_role(u_grantor.oid, 'USAGE')
+ OR pg_has_role(grantee.oid, 'USAGE')
+ OR grantee.rolname = 'PUBLIC');
GRANT SELECT ON routine_privileges TO PUBLIC;
/*
- * 20.45
+ * 5.42
+ * ROLE_ROUTINE_GRANTS view
+ */
+
+CREATE VIEW role_routine_grants AS
+ SELECT grantor,
+ grantee,
+ specific_catalog,
+ specific_schema,
+ specific_name,
+ routine_catalog,
+ routine_schema,
+ routine_name,
+ privilege_type,
+ is_grantable
+ FROM routine_privileges
+ WHERE grantor IN (SELECT role_name FROM enabled_roles)
+ OR grantee IN (SELECT role_name FROM enabled_roles);
+
+GRANT SELECT ON role_routine_grants TO PUBLIC;
+
+
+/*
+ * 5.50
+ * ROUTINE_ROUTINE_USAGE view
+ */
+
+-- not tracked by PostgreSQL
+
+
+/*
+ * 5.51
+ * ROUTINE_SEQUENCE_USAGE view
+ */
+
+-- not tracked by PostgreSQL
+
+
+/*
+ * 5.52
+ * ROUTINE_TABLE_USAGE view
+ */
+
+-- not tracked by PostgreSQL
+
+
+/*
+ * 5.53
* ROUTINES view
*/
CAST(null AS cardinal_number) AS numeric_scale,
CAST(null AS cardinal_number) AS datetime_precision,
CAST(null AS character_data) AS interval_type,
- CAST(null AS character_data) AS interval_precision,
+ CAST(null AS cardinal_number) AS interval_precision,
CAST(current_database() AS sql_identifier) AS type_udt_catalog,
CAST(nt.nspname AS sql_identifier) AS type_udt_schema,
CAST(t.typname AS sql_identifier) AS type_udt_name,
CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data)
AS routine_body,
CAST(
- CASE WHEN u.usename = current_user THEN p.prosrc ELSE null END
+ CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END
AS character_data) AS routine_definition,
CAST(
CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END
CAST(upper(l.lanname) AS character_data) AS external_language,
CAST('GENERAL' AS character_data) AS parameter_style,
- CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic,
+ CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_deterministic,
CAST('MODIFIES' AS character_data) AS sql_data_access,
- CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call,
+ CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_null_call,
CAST(null AS character_data) AS sql_path,
- CAST('YES' AS character_data) AS schema_level_routine,
+ CAST('YES' AS yes_or_no) AS schema_level_routine,
CAST(0 AS cardinal_number) AS max_dynamic_result_sets,
- CAST(null AS character_data) AS is_user_defined_cast,
- CAST(null AS character_data) AS is_implicitly_invocable,
+ CAST(null AS yes_or_no) AS is_user_defined_cast,
+ CAST(null AS yes_or_no) AS is_implicitly_invocable,
CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type,
CAST(null AS sql_identifier) AS to_sql_specific_catalog,
CAST(null AS sql_identifier) AS to_sql_specific_schema,
CAST(null AS sql_identifier) AS to_sql_specific_name,
- CAST('NO' AS character_data) AS as_locator
-
- FROM pg_namespace n, pg_proc p, pg_language l, pg_user u,
+ CAST('NO' AS yes_or_no) AS as_locator,
+ CAST(null AS time_stamp) AS created,
+ CAST(null AS time_stamp) AS last_altered,
+ CAST(null AS yes_or_no) AS new_savepoint_level,
+ CAST('NO' AS yes_or_no) AS is_udt_dependent,
+
+ CAST(null AS character_data) AS result_cast_from_data_type,
+ CAST(null AS yes_or_no) AS result_cast_as_locator,
+ CAST(null AS cardinal_number) AS result_cast_char_max_length,
+ CAST(null AS cardinal_number) AS result_cast_char_octet_length,
+ CAST(null AS sql_identifier) AS result_cast_char_set_catalog,
+ CAST(null AS sql_identifier) AS result_cast_char_set_schema,
+ CAST(null AS sql_identifier) AS result_cast_character_set_name,
+ CAST(null AS sql_identifier) AS result_cast_collation_catalog,
+ CAST(null AS sql_identifier) AS result_cast_collation_schema,
+ CAST(null AS sql_identifier) AS result_cast_collation_name,
+ CAST(null AS cardinal_number) AS result_cast_numeric_precision,
+ CAST(null AS cardinal_number) AS result_cast_numeric_precision_radix,
+ CAST(null AS cardinal_number) AS result_cast_numeric_scale,
+ CAST(null AS cardinal_number) AS result_cast_datetime_precision,
+ CAST(null AS character_data) AS result_cast_interval_type,
+ CAST(null AS cardinal_number) AS result_cast_interval_precision,
+ CAST(null AS sql_identifier) AS result_cast_type_udt_catalog,
+ CAST(null AS sql_identifier) AS result_cast_type_udt_schema,
+ CAST(null AS sql_identifier) AS result_cast_type_udt_name,
+ CAST(null AS sql_identifier) AS result_cast_scope_catalog,
+ CAST(null AS sql_identifier) AS result_cast_scope_schema,
+ CAST(null AS sql_identifier) AS result_cast_scope_name,
+ CAST(null AS cardinal_number) AS result_cast_maximum_cardinality,
+ CAST(null AS sql_identifier) AS result_cast_dtd_identifier
+
+ FROM pg_namespace n, pg_proc p, pg_language l,
pg_type t, pg_namespace nt
- WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.proowner = u.usesysid
+ WHERE n.oid = p.pronamespace AND p.prolang = l.oid
AND p.prorettype = t.oid AND t.typnamespace = nt.oid
- AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE'));
+ AND (pg_has_role(p.proowner, 'USAGE')
+ OR has_function_privilege(p.oid, 'EXECUTE'));
GRANT SELECT ON routines TO PUBLIC;
/*
- * 20.46
+ * 5.54
* SCHEMATA view
*/
CREATE VIEW schemata AS
SELECT CAST(current_database() AS sql_identifier) AS catalog_name,
CAST(n.nspname AS sql_identifier) AS schema_name,
- CAST(u.usename AS sql_identifier) AS schema_owner,
+ CAST(u.rolname AS sql_identifier) AS schema_owner,
CAST(null AS sql_identifier) AS default_character_set_catalog,
CAST(null AS sql_identifier) AS default_character_set_schema,
CAST(null AS sql_identifier) AS default_character_set_name,
CAST(null AS character_data) AS sql_path
- FROM pg_namespace n, pg_user u
- WHERE n.nspowner = u.usesysid AND u.usename = current_user;
+ FROM pg_namespace n, pg_authid u
+ WHERE n.nspowner = u.oid
+ AND (pg_has_role(n.nspowner, 'USAGE')
+ OR has_schema_privilege(n.oid, 'CREATE, USAGE'));
GRANT SELECT ON schemata TO PUBLIC;
/*
- * 20.47
+ * 5.55
+ * SEQUENCES view
+ */
+
+CREATE VIEW sequences AS
+ SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog,
+ CAST(nc.nspname AS sql_identifier) AS sequence_schema,
+ CAST(c.relname AS sql_identifier) AS sequence_name,
+ CAST('bigint' AS character_data) AS data_type,
+ CAST(64 AS cardinal_number) AS numeric_precision,
+ CAST(2 AS cardinal_number) AS numeric_precision_radix,
+ CAST(0 AS cardinal_number) AS numeric_scale,
+ CAST(p.start_value AS character_data) AS start_value,
+ CAST(p.minimum_value AS character_data) AS minimum_value,
+ CAST(p.maximum_value AS character_data) AS maximum_value,
+ CAST(p.increment AS character_data) AS increment,
+ CAST(CASE WHEN p.cycle_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS cycle_option
+ FROM pg_namespace nc, pg_class c, LATERAL pg_sequence_parameters(c.oid) p
+ WHERE c.relnamespace = nc.oid
+ AND c.relkind = 'S'
+ AND (NOT pg_is_other_temp_schema(nc.oid))
+ AND (pg_has_role(c.relowner, 'USAGE')
+ OR has_sequence_privilege(c.oid, 'SELECT, UPDATE, USAGE') );
+
+GRANT SELECT ON sequences TO PUBLIC;
+
+
+/*
+ * 5.56
* SQL_FEATURES table
*/
feature_name character_data,
sub_feature_id character_data,
sub_feature_name character_data,
- is_supported character_data,
+ is_supported yes_or_no,
is_verified_by character_data,
comments character_data
) WITHOUT OIDS;
/*
- * 20.48
+ * 5.57
* SQL_IMPLEMENTATION_INFO table
*/
--- Note: Implementation information items are defined in ISO 9075-3:1999,
--- clause 7.1.
+-- Note: Implementation information items are defined in ISO/IEC 9075-3:2008,
+-- clause 9.1.
CREATE TABLE sql_implementation_info (
implementation_info_id character_data,
) WITHOUT OIDS;
INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL);
-INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported');
+INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, (SELECT default_collate_name FROM character_sets), NULL);
INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements');
INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL);
INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL);
/*
- * 20.49
* SQL_LANGUAGES table
+ * apparently removed in SQL:2008
*/
CREATE TABLE sql_languages (
INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL);
INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
+INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL);
+INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C');
GRANT SELECT ON sql_languages TO PUBLIC;
/*
- * 20.50
* SQL_PACKAGES table
+ * removed in SQL:2011
*/
CREATE TABLE sql_packages (
feature_id character_data,
feature_name character_data,
- is_supported character_data,
+ is_supported yes_or_no,
is_verified_by character_data,
comments character_data
) WITHOUT OIDS;
INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, '');
INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, '');
INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, '');
-INSERT INTO sql_packages VALUES ('PKG009', 'SQL/MM support', 'NO', NULL, '');
+INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO');
GRANT SELECT ON sql_packages TO PUBLIC;
/*
- * 20.51
+ * 5.58
+ * SQL_PARTS table
+ */
+
+CREATE TABLE sql_parts (
+ feature_id character_data,
+ feature_name character_data,
+ is_supported yes_or_no,
+ is_verified_by character_data,
+ comments character_data
+) WITHOUT OIDS;
+
+INSERT INTO sql_parts VALUES ('1', 'Framework (SQL/Framework)', 'NO', NULL, '');
+INSERT INTO sql_parts VALUES ('2', 'Foundation (SQL/Foundation)', 'NO', NULL, '');
+INSERT INTO sql_parts VALUES ('3', 'Call-Level Interface (SQL/CLI)', 'NO', NULL, '');
+INSERT INTO sql_parts VALUES ('4', 'Persistent Stored Modules (SQL/PSM)', 'NO', NULL, '');
+INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', 'NO', NULL, '');
+INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', 'NO', NULL, '');
+INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', 'NO', NULL, '');
+INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', 'NO', NULL, '');
+INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', 'YES', NULL, '');
+
+
+/*
+ * 5.59
* SQL_SIZING table
*/
--- Note: Sizing items are defined in ISO 9075-3:1999, clause 7.2.
+-- Note: Sizing items are defined in ISO/IEC 9075-3:2008, clause 9.2.
CREATE TABLE sql_sizing (
sizing_id cardinal_number,
/*
- * 20.52
* SQL_SIZING_PROFILES table
+ * removed in SQL:2011
*/
-- The data in this table are defined by various profiles of SQL.
/*
- * 20.53
+ * 5.60
* TABLE_CONSTRAINTS view
*/
WHEN 'p' THEN 'PRIMARY KEY'
WHEN 'u' THEN 'UNIQUE' END
AS character_data) AS constraint_type,
- CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data)
+ CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS yes_or_no)
AS is_deferrable,
- CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data)
+ CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
AS initially_deferred
FROM pg_namespace nc,
pg_namespace nr,
pg_constraint c,
- pg_class r,
- pg_user u
+ pg_class r
WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace
- AND c.conrelid = r.oid AND r.relowner = u.usesysid
+ AND c.conrelid = r.oid
+ AND c.contype NOT IN ('t', 'x') -- ignore nonstandard constraints
AND r.relkind = 'r'
- AND u.usename = current_user;
+ AND (NOT pg_is_other_temp_schema(nr.oid))
+ AND (pg_has_role(r.relowner, 'USAGE')
+ -- SELECT privilege omitted, per SQL standard
+ OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
+ OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') )
+
+ UNION ALL
+
+ -- not-null constraints
--- FIMXE: Not-null constraints are missing here.
+ SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
+ CAST(nr.nspname AS sql_identifier) AS constraint_schema,
+ 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
+ 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('CHECK' AS character_data) AS constraint_type,
+ CAST('NO' AS yes_or_no) AS is_deferrable,
+ CAST('NO' AS yes_or_no) AS initially_deferred
+
+ FROM pg_namespace nr,
+ pg_class r,
+ pg_attribute a
+
+ WHERE nr.oid = r.relnamespace
+ AND r.oid = a.attrelid
+ AND a.attnotnull
+ AND a.attnum > 0
+ AND NOT a.attisdropped
+ AND r.relkind = 'r'
+ AND (NOT pg_is_other_temp_schema(nr.oid))
+ AND (pg_has_role(r.relowner, 'USAGE')
+ -- SELECT privilege omitted, per SQL standard
+ OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
+ OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') );
GRANT SELECT ON table_constraints TO PUBLIC;
/*
- * 20.55
+ * 5.61
+ * TABLE_METHOD_PRIVILEGES view
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.62
* TABLE_PRIVILEGES view
*/
CREATE VIEW table_privileges AS
- SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor,
- CAST(grantee.name AS sql_identifier) AS grantee,
+ SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+ CAST(grantee.rolname 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(pr.type AS character_data) AS privilege_type,
+ CAST(c.prtype AS character_data) AS privilege_type,
CAST(
- CASE WHEN aclcontains(c.relacl,
- makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true))
- THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable,
- CAST('NO' AS character_data) AS with_hierarchy
+ CASE WHEN
+ -- object owner always has grant options
+ pg_has_role(grantee.oid, c.relowner, 'USAGE')
+ OR c.grantable
+ THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
+ CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS with_hierarchy
- FROM pg_class c,
+ FROM (
+ SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
+ ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
pg_namespace nc,
- pg_user u_grantor,
+ pg_authid u_grantor,
(
- SELECT usesysid, 0, usename FROM pg_user
- UNION ALL
- SELECT 0, grosysid, groname FROM pg_group
+ SELECT oid, rolname FROM pg_authid
UNION ALL
- SELECT 0, 0, 'PUBLIC'
- ) AS grantee (usesysid, grosysid, name),
- (SELECT 'SELECT' UNION ALL
- SELECT 'DELETE' UNION ALL
- SELECT 'INSERT' UNION ALL
- SELECT 'UPDATE' UNION ALL
- SELECT 'REFERENCES' UNION ALL
- SELECT 'RULE' UNION ALL
- SELECT 'TRIGGER') AS pr (type)
+ SELECT 0::oid, 'PUBLIC'
+ ) AS grantee (oid, rolname)
WHERE c.relnamespace = nc.oid
AND c.relkind IN ('r', 'v')
- AND aclcontains(c.relacl,
- makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false))
- AND (u_grantor.usename = current_user
- OR grantee.name = current_user
- OR grantee.name = 'PUBLIC');
+ AND c.grantee = grantee.oid
+ AND c.grantor = u_grantor.oid
+ AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
+ AND (pg_has_role(u_grantor.oid, 'USAGE')
+ OR pg_has_role(grantee.oid, 'USAGE')
+ OR grantee.rolname = 'PUBLIC');
GRANT SELECT ON table_privileges TO PUBLIC;
/*
- * 20.56
+ * 5.43
+ * ROLE_TABLE_GRANTS view
+ */
+
+CREATE VIEW role_table_grants AS
+ SELECT grantor,
+ grantee,
+ table_catalog,
+ table_schema,
+ table_name,
+ privilege_type,
+ is_grantable,
+ with_hierarchy
+ FROM table_privileges
+ WHERE grantor IN (SELECT role_name FROM enabled_roles)
+ OR grantee IN (SELECT role_name FROM enabled_roles);
+
+GRANT SELECT ON role_table_grants TO PUBLIC;
+
+
+/*
+ * 5.63
* TABLES view
*/
CAST(c.relname AS sql_identifier) AS table_name,
CAST(
- CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY'
+ CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY'
WHEN c.relkind = 'r' THEN 'BASE TABLE'
WHEN c.relkind = 'v' THEN 'VIEW'
+ WHEN c.relkind = 'f' THEN 'FOREIGN TABLE'
ELSE null END
AS character_data) AS table_type,
CAST(null AS sql_identifier) AS self_referencing_column_name,
CAST(null AS character_data) AS reference_generation,
- CAST(null AS sql_identifier) AS user_defined_type_catalog,
- CAST(null AS sql_identifier) AS user_defined_type_schema,
- CAST(null AS sql_identifier) AS user_defined_name
+ CAST(CASE WHEN t.typname IS NOT NULL THEN current_database() ELSE null END AS sql_identifier) AS user_defined_type_catalog,
+ CAST(nt.nspname AS sql_identifier) AS user_defined_type_schema,
+ CAST(t.typname AS sql_identifier) AS user_defined_type_name,
- FROM pg_namespace nc, pg_class c, pg_user u
+ CAST(CASE WHEN c.relkind = 'r' OR
+ (c.relkind IN ('v', 'f') AND
+ -- 1 << CMD_INSERT
+ pg_relation_is_updatable(c.oid, false) & 8 = 8)
+ THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into,
- WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
- AND c.relkind IN ('r', 'v')
- AND (u.usename = current_user
- OR has_table_privilege(c.oid, 'SELECT')
- OR has_table_privilege(c.oid, 'INSERT')
- OR has_table_privilege(c.oid, 'UPDATE')
- OR has_table_privilege(c.oid, 'DELETE')
- OR has_table_privilege(c.oid, 'RULE')
- OR has_table_privilege(c.oid, 'REFERENCES')
- OR has_table_privilege(c.oid, 'TRIGGER') );
+ CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed,
+ CAST(null AS character_data) AS commit_action
+
+ FROM pg_namespace nc JOIN pg_class c ON (nc.oid = c.relnamespace)
+ LEFT JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON (c.reloftype = t.oid)
+
+ WHERE c.relkind IN ('r', 'v', 'f')
+ AND (NOT pg_is_other_temp_schema(nc.oid))
+ AND (pg_has_role(c.relowner, 'USAGE')
+ OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
+ OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
GRANT SELECT ON tables TO PUBLIC;
/*
- * 20.59
- * TRIGGERED_UPDATE_COLUMNS view
+ * 5.64
+ * TRANSFORMS view
+ */
+
+CREATE VIEW transforms AS
+ SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
+ CAST(nt.nspname AS sql_identifier) AS udt_schema,
+ CAST(t.typname AS sql_identifier) AS udt_name,
+ CAST(current_database() AS sql_identifier) AS specific_catalog,
+ CAST(np.nspname AS sql_identifier) AS specific_schema,
+ CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
+ CAST(l.lanname AS sql_identifier) AS group_name,
+ CAST('FROM SQL' AS character_data) AS transform_type
+ FROM pg_type t JOIN pg_transform x ON t.oid = x.trftype
+ JOIN pg_language l ON x.trflang = l.oid
+ JOIN pg_proc p ON x.trffromsql = p.oid
+ JOIN pg_namespace nt ON t.typnamespace = nt.oid
+ JOIN pg_namespace np ON p.pronamespace = np.oid
+
+ UNION
+
+ SELECT CAST(current_database() AS sql_identifier) AS udt_catalog,
+ CAST(nt.nspname AS sql_identifier) AS udt_schema,
+ CAST(t.typname AS sql_identifier) AS udt_name,
+ CAST(current_database() AS sql_identifier) AS specific_catalog,
+ CAST(np.nspname AS sql_identifier) AS specific_schema,
+ CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name,
+ CAST(l.lanname AS sql_identifier) AS group_name,
+ CAST('TO SQL' AS character_data) AS transform_type
+ FROM pg_type t JOIN pg_transform x ON t.oid = x.trftype
+ JOIN pg_language l ON x.trflang = l.oid
+ JOIN pg_proc p ON x.trftosql = p.oid
+ JOIN pg_namespace nt ON t.typnamespace = nt.oid
+ JOIN pg_namespace np ON p.pronamespace = np.oid
+
+ ORDER BY udt_catalog, udt_schema, udt_name, group_name, transform_type -- some sensible grouping for interactive use
+;
+
+
+/*
+ * 5.65
+ * TRANSLATIONS view
*/
--- PostgreSQL doesn't allow the specification of individual triggered
--- update columns, so this view is empty.
+-- feature not supported
+
+
+/*
+ * 5.66
+ * TRIGGERED_UPDATE_COLUMNS view
+ */
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(n.nspname AS sql_identifier) AS trigger_schema,
+ CAST(t.tgname 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;
+ CAST(n.nspname AS sql_identifier) AS event_object_schema,
+ CAST(c.relname AS sql_identifier) AS event_object_table,
+ CAST(a.attname AS sql_identifier) AS event_object_column
+
+ FROM pg_namespace n, pg_class c, pg_trigger t,
+ (SELECT tgoid, (ta0.tgat).x AS tgattnum, (ta0.tgat).n AS tgattpos
+ FROM (SELECT oid AS tgoid, information_schema._pg_expandarray(tgattr) AS tgat FROM pg_trigger) AS ta0) AS ta,
+ pg_attribute a
+
+ WHERE n.oid = c.relnamespace
+ AND c.oid = t.tgrelid
+ AND t.oid = ta.tgoid
+ AND (a.attrelid, a.attnum) = (t.tgrelid, ta.tgattnum)
+ AND NOT t.tgisinternal
+ AND (NOT pg_is_other_temp_schema(n.oid))
+ AND (pg_has_role(c.relowner, 'USAGE')
+ -- SELECT privilege omitted, per SQL standard
+ OR has_column_privilege(c.oid, a.attnum, 'INSERT, UPDATE, REFERENCES') );
GRANT SELECT ON triggered_update_columns TO PUBLIC;
/*
- * 20.62
+ * 5.67
+ * TRIGGER_COLUMN_USAGE view
+ */
+
+-- not tracked by PostgreSQL
+
+
+/*
+ * 5.68
+ * TRIGGER_PERIOD_USAGE view
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.69
+ * TRIGGER_ROUTINE_USAGE view
+ */
+
+-- not tracked by PostgreSQL
+
+
+/*
+ * 5.70
+ * TRIGGER_SEQUENCE_USAGE view
+ */
+
+-- not tracked by PostgreSQL
+
+
+/*
+ * 5.71
+ * TRIGGER_TABLE_USAGE view
+ */
+
+-- not tracked by PostgreSQL
+
+
+/*
+ * 5.72
* TRIGGERS view
*/
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,
+ -- XXX strange hacks follow
+ CAST(
+ CASE WHEN pg_has_role(c.relowner, 'USAGE')
+ THEN (SELECT m[1] FROM regexp_matches(pg_get_triggerdef(t.oid), E'.{35,} WHEN \\((.+)\\) EXECUTE PROCEDURE') AS rm(m) LIMIT 1)
+ ELSE null END
+ 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
+ -- hard-wired reference to TRIGGER_TYPE_ROW
+ CASE t.tgtype & 1 WHEN 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 ALL
- SELECT 8, 'DELETE' UNION ALL
- SELECT 16, 'UPDATE') AS em (num, text)
+ -- hard-wired refs to TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSTEAD
+ CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE' WHEN 64 THEN 'INSTEAD OF' ELSE 'AFTER' END
+ AS character_data) AS action_timing,
+ CAST(null AS sql_identifier) AS action_reference_old_table,
+ CAST(null AS sql_identifier) AS action_reference_new_table,
+ CAST(null AS sql_identifier) AS action_reference_old_row,
+ CAST(null AS sql_identifier) AS action_reference_new_row,
+ CAST(null AS time_stamp) AS created
+
+ FROM pg_namespace n, pg_class c, pg_trigger t,
+ -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE,
+ -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE
+ (VALUES (4, 'INSERT'),
+ (8, 'DELETE'),
+ (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;
+ AND NOT t.tgisinternal
+ AND (NOT pg_is_other_temp_schema(n.oid))
+ AND (pg_has_role(c.relowner, 'USAGE')
+ -- SELECT privilege omitted, per SQL standard
+ OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
+ OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') );
GRANT SELECT ON triggers TO PUBLIC;
/*
- * 20.63
- * USAGE_PRIVILEGES view
+ * 5.73
+ * UDT_PRIVILEGES view
*/
--- Of the things currently implemented in PostgreSQL, usage privileges
--- apply only to domains. Since domains have no real privileges, we
--- represent all domains with implicit usage privilege here.
+CREATE VIEW udt_privileges AS
+ SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+ CAST(grantee.rolname AS sql_identifier) AS grantee,
+ CAST(current_database() AS sql_identifier) AS udt_catalog,
+ CAST(n.nspname AS sql_identifier) AS udt_schema,
+ CAST(t.typname AS sql_identifier) AS udt_name,
+ CAST('TYPE USAGE' AS character_data) AS privilege_type, -- sic
+ CAST(
+ CASE WHEN
+ -- object owner always has grant options
+ pg_has_role(grantee.oid, t.typowner, 'USAGE')
+ OR t.grantable
+ THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
+
+ FROM (
+ SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
+ ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
+ pg_namespace n,
+ pg_authid u_grantor,
+ (
+ SELECT oid, rolname FROM pg_authid
+ UNION ALL
+ SELECT 0::oid, 'PUBLIC'
+ ) AS grantee (oid, rolname)
+
+ WHERE t.typnamespace = n.oid
+ AND t.typtype = 'c'
+ AND t.grantee = grantee.oid
+ AND t.grantor = u_grantor.oid
+ AND t.prtype IN ('USAGE')
+ AND (pg_has_role(u_grantor.oid, 'USAGE')
+ OR pg_has_role(grantee.oid, 'USAGE')
+ OR grantee.rolname = 'PUBLIC');
+
+GRANT SELECT ON udt_privileges TO PUBLIC;
+
+
+/*
+ * 5.46
+ * ROLE_UDT_GRANTS view
+ */
+
+CREATE VIEW role_udt_grants AS
+ SELECT grantor,
+ grantee,
+ udt_catalog,
+ udt_schema,
+ udt_name,
+ privilege_type,
+ is_grantable
+ FROM udt_privileges
+ WHERE grantor IN (SELECT role_name FROM enabled_roles)
+ OR grantee IN (SELECT role_name FROM enabled_roles);
+
+GRANT SELECT ON role_udt_grants TO PUBLIC;
+
+
+/*
+ * 5.74
+ * USAGE_PRIVILEGES view
+ */
CREATE VIEW usage_privileges AS
- SELECT CAST(u.usename AS sql_identifier) AS grantor,
+
+ /* collations */
+ -- Collations have no real privileges, so we represent all collations with implicit usage privilege here.
+ SELECT CAST(u.rolname AS sql_identifier) AS grantor,
CAST('PUBLIC' AS sql_identifier) AS grantee,
CAST(current_database() AS sql_identifier) AS object_catalog,
CAST(n.nspname AS sql_identifier) AS object_schema,
+ CAST(c.collname AS sql_identifier) AS object_name,
+ CAST('COLLATION' AS character_data) AS object_type,
+ CAST('USAGE' AS character_data) AS privilege_type,
+ CAST('NO' AS yes_or_no) AS is_grantable
+
+ FROM pg_authid u,
+ pg_namespace n,
+ pg_collation c
+
+ WHERE u.oid = c.collowner
+ AND c.collnamespace = n.oid
+ AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database()))
+
+ UNION ALL
+
+ /* domains */
+ SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+ CAST(grantee.rolname AS sql_identifier) AS grantee,
+ CAST(current_database() AS sql_identifier) AS object_catalog,
+ CAST(n.nspname AS sql_identifier) AS object_schema,
CAST(t.typname AS sql_identifier) AS object_name,
CAST('DOMAIN' AS character_data) AS object_type,
CAST('USAGE' AS character_data) AS privilege_type,
- CAST('NO' AS character_data) AS is_grantable
+ CAST(
+ CASE WHEN
+ -- object owner always has grant options
+ pg_has_role(grantee.oid, t.typowner, 'USAGE')
+ OR t.grantable
+ THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
- FROM pg_user u,
+ FROM (
+ SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type
+ ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable),
pg_namespace n,
- pg_type t
+ pg_authid u_grantor,
+ (
+ SELECT oid, rolname FROM pg_authid
+ UNION ALL
+ SELECT 0::oid, 'PUBLIC'
+ ) AS grantee (oid, rolname)
+
+ WHERE t.typnamespace = n.oid
+ AND t.typtype = 'd'
+ AND t.grantee = grantee.oid
+ AND t.grantor = u_grantor.oid
+ AND t.prtype IN ('USAGE')
+ AND (pg_has_role(u_grantor.oid, 'USAGE')
+ OR pg_has_role(grantee.oid, 'USAGE')
+ OR grantee.rolname = 'PUBLIC')
+
+ UNION ALL
+
+ /* foreign-data wrappers */
+ SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+ CAST(grantee.rolname AS sql_identifier) AS grantee,
+ CAST(current_database() AS sql_identifier) AS object_catalog,
+ CAST('' AS sql_identifier) AS object_schema,
+ CAST(fdw.fdwname AS sql_identifier) AS object_name,
+ CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type,
+ CAST('USAGE' AS character_data) AS privilege_type,
+ CAST(
+ CASE WHEN
+ -- object owner always has grant options
+ pg_has_role(grantee.oid, fdw.fdwowner, 'USAGE')
+ OR fdw.grantable
+ THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
+
+ FROM (
+ SELECT fdwname, fdwowner, (aclexplode(coalesce(fdwacl, acldefault('F', fdwowner)))).* FROM pg_foreign_data_wrapper
+ ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable),
+ pg_authid u_grantor,
+ (
+ SELECT oid, rolname FROM pg_authid
+ UNION ALL
+ SELECT 0::oid, 'PUBLIC'
+ ) AS grantee (oid, rolname)
+
+ WHERE u_grantor.oid = fdw.grantor
+ AND grantee.oid = fdw.grantee
+ AND fdw.prtype IN ('USAGE')
+ AND (pg_has_role(u_grantor.oid, 'USAGE')
+ OR pg_has_role(grantee.oid, 'USAGE')
+ OR grantee.rolname = 'PUBLIC')
+
+ UNION ALL
+
+ /* foreign servers */
+ SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+ CAST(grantee.rolname AS sql_identifier) AS grantee,
+ CAST(current_database() AS sql_identifier) AS object_catalog,
+ CAST('' AS sql_identifier) AS object_schema,
+ CAST(srv.srvname AS sql_identifier) AS object_name,
+ CAST('FOREIGN SERVER' AS character_data) AS object_type,
+ CAST('USAGE' AS character_data) AS privilege_type,
+ CAST(
+ CASE WHEN
+ -- object owner always has grant options
+ pg_has_role(grantee.oid, srv.srvowner, 'USAGE')
+ OR srv.grantable
+ THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
+
+ FROM (
+ SELECT srvname, srvowner, (aclexplode(coalesce(srvacl, acldefault('S', srvowner)))).* FROM pg_foreign_server
+ ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable),
+ pg_authid u_grantor,
+ (
+ SELECT oid, rolname FROM pg_authid
+ UNION ALL
+ SELECT 0::oid, 'PUBLIC'
+ ) AS grantee (oid, rolname)
- WHERE u.usesysid = t.typowner
- AND t.typnamespace = n.oid
- AND t.typtype = 'd';
+ WHERE u_grantor.oid = srv.grantor
+ AND grantee.oid = srv.grantee
+ AND srv.prtype IN ('USAGE')
+ AND (pg_has_role(u_grantor.oid, 'USAGE')
+ OR pg_has_role(grantee.oid, 'USAGE')
+ OR grantee.rolname = 'PUBLIC')
+
+ UNION ALL
+
+ /* sequences */
+ SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+ CAST(grantee.rolname AS sql_identifier) AS grantee,
+ CAST(current_database() AS sql_identifier) AS object_catalog,
+ CAST(n.nspname AS sql_identifier) AS object_schema,
+ CAST(c.relname AS sql_identifier) AS object_name,
+ CAST('SEQUENCE' AS character_data) AS object_type,
+ CAST('USAGE' AS character_data) AS privilege_type,
+ CAST(
+ CASE WHEN
+ -- object owner always has grant options
+ pg_has_role(grantee.oid, c.relowner, 'USAGE')
+ OR c.grantable
+ THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable
+
+ FROM (
+ SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
+ ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
+ pg_namespace n,
+ pg_authid u_grantor,
+ (
+ SELECT oid, rolname FROM pg_authid
+ UNION ALL
+ SELECT 0::oid, 'PUBLIC'
+ ) AS grantee (oid, rolname)
+
+ WHERE c.relnamespace = n.oid
+ AND c.relkind = 'S'
+ AND c.grantee = grantee.oid
+ AND c.grantor = u_grantor.oid
+ AND c.prtype IN ('USAGE')
+ AND (pg_has_role(u_grantor.oid, 'USAGE')
+ OR pg_has_role(grantee.oid, 'USAGE')
+ OR grantee.rolname = 'PUBLIC');
GRANT SELECT ON usage_privileges TO PUBLIC;
/*
- * 20.65
+ * 5.45
+ * ROLE_USAGE_GRANTS view
+ */
+
+CREATE VIEW role_usage_grants AS
+ SELECT grantor,
+ grantee,
+ object_catalog,
+ object_schema,
+ object_name,
+ object_type,
+ privilege_type,
+ is_grantable
+ FROM usage_privileges
+ WHERE grantor IN (SELECT role_name FROM enabled_roles)
+ OR grantee IN (SELECT role_name FROM enabled_roles);
+
+GRANT SELECT ON role_usage_grants TO PUBLIC;
+
+
+/*
+ * 5.75
+ * USER_DEFINED_TYPES view
+ */
+
+CREATE VIEW user_defined_types AS
+ SELECT CAST(current_database() AS sql_identifier) AS user_defined_type_catalog,
+ CAST(n.nspname AS sql_identifier) AS user_defined_type_schema,
+ CAST(c.relname AS sql_identifier) AS user_defined_type_name,
+ CAST('STRUCTURED' AS character_data) AS user_defined_type_category,
+ CAST('YES' AS yes_or_no) AS is_instantiable,
+ CAST(null AS yes_or_no) AS is_final,
+ CAST(null AS character_data) AS ordering_form,
+ CAST(null AS character_data) AS ordering_category,
+ CAST(null AS sql_identifier) AS ordering_routine_catalog,
+ CAST(null AS sql_identifier) AS ordering_routine_schema,
+ CAST(null AS sql_identifier) AS ordering_routine_name,
+ CAST(null AS character_data) AS reference_type,
+ CAST(null AS character_data) AS data_type,
+ CAST(null AS cardinal_number) AS character_maximum_length,
+ CAST(null AS cardinal_number) AS character_octet_length,
+ CAST(null AS sql_identifier) AS character_set_catalog,
+ CAST(null AS sql_identifier) AS character_set_schema,
+ CAST(null AS sql_identifier) AS character_set_name,
+ CAST(null AS sql_identifier) AS collation_catalog,
+ CAST(null AS sql_identifier) AS collation_schema,
+ CAST(null AS sql_identifier) AS collation_name,
+ CAST(null AS cardinal_number) AS numeric_precision,
+ CAST(null AS cardinal_number) AS numeric_precision_radix,
+ CAST(null AS cardinal_number) AS numeric_scale,
+ CAST(null AS cardinal_number) AS datetime_precision,
+ CAST(null AS character_data) AS interval_type,
+ CAST(null AS cardinal_number) AS interval_precision,
+ CAST(null AS sql_identifier) AS source_dtd_identifier,
+ CAST(null AS sql_identifier) AS ref_dtd_identifier
+
+ FROM pg_namespace n, pg_class c, pg_type t
+
+ WHERE n.oid = c.relnamespace
+ AND t.typrelid = c.oid
+ AND c.relkind = 'c'
+ AND (pg_has_role(t.typowner, 'USAGE')
+ OR has_type_privilege(t.oid, 'USAGE'));
+
+GRANT SELECT ON user_defined_types TO PUBLIC;
+
+
+/*
+ * 5.76
* VIEW_COLUMN_USAGE
*/
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,
+ FROM pg_namespace nv, pg_class v, pg_depend dv,
pg_depend dt, pg_class t, pg_namespace nt,
- pg_attribute a, pg_user u
+ pg_attribute a
WHERE nv.oid = v.relnamespace
AND v.relkind = 'v'
AND dt.refclassid = 'pg_catalog.pg_class'::regclass
AND dt.refobjid = t.oid
AND t.relnamespace = nt.oid
- AND t.relkind IN ('r', 'v')
+ AND t.relkind IN ('r', 'v', 'f')
AND t.oid = a.attrelid
AND dt.refobjsubid = a.attnum
- AND t.relowner = u.usesysid AND u.usename = current_user;
+ AND pg_has_role(t.relowner, 'USAGE');
GRANT SELECT ON view_column_usage TO PUBLIC;
/*
- * 20.66
+ * 5.77
+ * VIEW_PERIOD_USAGE
+ */
+
+-- feature not supported
+
+
+/*
+ * 5.78
+ * VIEW_ROUTINE_USAGE
+ */
+
+CREATE VIEW view_routine_usage AS
+ SELECT DISTINCT
+ CAST(current_database() AS sql_identifier) AS table_catalog,
+ CAST(nv.nspname AS sql_identifier) AS table_schema,
+ CAST(v.relname AS sql_identifier) AS table_name,
+ CAST(current_database() AS sql_identifier) AS specific_catalog,
+ CAST(np.nspname AS sql_identifier) AS specific_schema,
+ CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name
+
+ FROM pg_namespace nv, pg_class v, pg_depend dv,
+ pg_depend dp, pg_proc p, pg_namespace np
+
+ WHERE nv.oid = v.relnamespace
+ AND v.relkind = 'v'
+ AND v.oid = dv.refobjid
+ AND dv.refclassid = 'pg_catalog.pg_class'::regclass
+ AND dv.classid = 'pg_catalog.pg_rewrite'::regclass
+ AND dv.deptype = 'i'
+ AND dv.objid = dp.objid
+ AND dp.classid = 'pg_catalog.pg_rewrite'::regclass
+ AND dp.refclassid = 'pg_catalog.pg_proc'::regclass
+ AND dp.refobjid = p.oid
+ AND p.pronamespace = np.oid
+ AND pg_has_role(p.proowner, 'USAGE');
+
+GRANT SELECT ON view_routine_usage TO PUBLIC;
+
+
+/*
+ * 5.79
* VIEW_TABLE_USAGE
*/
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
+ FROM pg_namespace nv, pg_class v, pg_depend dv,
+ pg_depend dt, pg_class t, pg_namespace nt
WHERE nv.oid = v.relnamespace
AND v.relkind = 'v'
AND dt.refclassid = 'pg_catalog.pg_class'::regclass
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;
+ AND t.relkind IN ('r', 'v', 'f')
+ AND pg_has_role(t.relowner, 'USAGE');
GRANT SELECT ON view_table_usage TO PUBLIC;
/*
- * 20.68
+ * 5.80
* VIEWS view
*/
CAST(c.relname AS sql_identifier) AS table_name,
CAST(
- CASE WHEN u.usename = current_user THEN pg_get_viewdef(c.oid)
+ CASE WHEN pg_has_role(c.relowner, 'USAGE')
+ THEN pg_get_viewdef(c.oid)
ELSE null END
AS character_data) AS view_definition,
- CAST('NONE' AS character_data) AS check_option,
- CAST(null AS character_data) AS is_updatable, -- FIXME
- CAST(null AS character_data) AS is_insertable_into -- FIXME
+ CAST(
+ CASE WHEN 'check_option=cascaded' = ANY (c.reloptions)
+ THEN 'CASCADED'
+ WHEN 'check_option=local' = ANY (c.reloptions)
+ THEN 'LOCAL'
+ ELSE 'NONE' END
+ AS character_data) AS check_option,
+
+ CAST(
+ -- (1 << CMD_UPDATE) + (1 << CMD_DELETE)
+ CASE WHEN pg_relation_is_updatable(c.oid, false) & 20 = 20
+ THEN 'YES' ELSE 'NO' END
+ AS yes_or_no) AS is_updatable,
+
+ CAST(
+ -- 1 << CMD_INSERT
+ CASE WHEN pg_relation_is_updatable(c.oid, false) & 8 = 8
+ THEN 'YES' ELSE 'NO' END
+ AS yes_or_no) AS is_insertable_into,
- FROM pg_namespace nc, pg_class c, pg_user u
+ CAST(
+ -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_UPDATE
+ CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81)
+ THEN 'YES' ELSE 'NO' END
+ AS yes_or_no) AS is_trigger_updatable,
+
+ CAST(
+ -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_DELETE
+ CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73)
+ THEN 'YES' ELSE 'NO' END
+ AS yes_or_no) AS is_trigger_deletable,
- WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner
+ CAST(
+ -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_INSERT
+ CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69)
+ THEN 'YES' ELSE 'NO' END
+ AS yes_or_no) AS is_trigger_insertable_into
+
+ FROM pg_namespace nc, pg_class c
+
+ WHERE c.relnamespace = nc.oid
AND c.relkind = 'v'
- AND (u.usename = current_user
- OR has_table_privilege(c.oid, 'SELECT')
- OR has_table_privilege(c.oid, 'INSERT')
- OR has_table_privilege(c.oid, 'UPDATE')
- OR has_table_privilege(c.oid, 'DELETE')
- OR has_table_privilege(c.oid, 'RULE')
- OR has_table_privilege(c.oid, 'REFERENCES')
- OR has_table_privilege(c.oid, 'TRIGGER') );
+ AND (NOT pg_is_other_temp_schema(nc.oid))
+ AND (pg_has_role(c.relowner, 'USAGE')
+ OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
+ OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') );
GRANT SELECT ON views TO PUBLIC;
-- The following views have dependencies that force them to appear out of order.
/*
- * 20.21
+ * 5.25
* DATA_TYPE_PRIVILEGES view
*/
FROM
(
+ SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes
+ UNION ALL
SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns
UNION ALL
SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains
/*
- * 20.27
+ * 5.30
* ELEMENT_TYPES view
*/
CAST(n.nspname AS sql_identifier) AS object_schema,
CAST(x.objname AS sql_identifier) AS object_name,
CAST(x.objtype AS character_data) AS object_type,
- CAST(x.objdtdid AS sql_identifier) AS array_type_identifier,
+ CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier,
CAST(
CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null)
ELSE 'USER-DEFINED' END AS character_data) AS data_type,
CAST(null AS sql_identifier) AS character_set_catalog,
CAST(null AS sql_identifier) AS character_set_schema,
CAST(null AS sql_identifier) AS character_set_name,
- CAST(null AS sql_identifier) AS collation_catalog,
- CAST(null AS sql_identifier) AS collation_schema,
- CAST(null AS sql_identifier) AS collation_name,
+ CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog,
+ CAST(nco.nspname AS sql_identifier) AS collation_schema,
+ CAST(co.collname AS sql_identifier) AS collation_name,
CAST(null AS cardinal_number) AS numeric_precision,
CAST(null AS cardinal_number) AS numeric_precision_radix,
CAST(null AS cardinal_number) AS numeric_scale,
CAST(null AS cardinal_number) AS datetime_precision,
CAST(null AS character_data) AS interval_type,
- CAST(null AS character_data) AS interval_precision,
-
+ CAST(null AS cardinal_number) AS interval_precision,
+
CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard
CAST(current_database() AS sql_identifier) AS udt_catalog,
CAST(null AS sql_identifier) AS scope_name,
CAST(null AS cardinal_number) AS maximum_cardinality,
- CAST('a' || x.objdtdid AS sql_identifier) AS dtd_identifier
+ CAST('a' || CAST(x.objdtdid AS text) AS sql_identifier) AS dtd_identifier
FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt,
(
- /* columns */
+ /* columns, attributes */
SELECT c.relnamespace, CAST(c.relname AS sql_identifier),
- 'TABLE'::text, a.attnum, a.atttypid
+ CASE WHEN c.relkind = 'c' THEN 'USER-DEFINED TYPE'::text ELSE 'TABLE'::text END,
+ a.attnum, a.atttypid, a.attcollation
FROM pg_class c, pg_attribute a
WHERE c.oid = a.attrelid
- AND c.relkind IN ('r', 'v')
+ AND c.relkind IN ('r', 'v', 'f', 'c')
AND attnum > 0 AND NOT attisdropped
UNION ALL
/* domains */
SELECT t.typnamespace, CAST(t.typname AS sql_identifier),
- 'DOMAIN'::text, 1, t.typbasetype
+ 'DOMAIN'::text, 1, t.typbasetype, t.typcollation
FROM pg_type t
WHERE t.typtype = 'd'
UNION ALL
/* parameters */
- SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
- 'ROUTINE'::text, pos.n, p.proargtypes[pos.n-1]
- FROM pg_proc p, _pg_keypositions() AS pos(n)
- WHERE p.pronargs >= pos.n
+ SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier),
+ 'ROUTINE'::text, (ss.x).n, (ss.x).x, 0
+ FROM (SELECT p.pronamespace, p.proname, p.oid,
+ _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
+ FROM pg_proc p) AS ss
UNION ALL
/* result types */
SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier),
- 'ROUTINE'::text, 0, p.prorettype
+ 'ROUTINE'::text, 0, p.prorettype, 0
FROM pg_proc p
- ) AS x (objschema, objname, objtype, objdtdid, objtypeid)
+ ) AS x (objschema, objname, objtype, objdtdid, objtypeid, objcollation)
+ LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
+ ON x.objcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
WHERE n.oid = x.objschema
AND at.oid = x.objtypeid
AND at.typelem = bt.oid
AND nbt.oid = bt.typnamespace
- AND (n.nspname, x.objname, x.objtype, x.objdtdid) IN
+ AND (n.nspname, x.objname, x.objtype, CAST(x.objdtdid AS sql_identifier)) IN
( SELECT object_schema, object_name, object_type, dtd_identifier
FROM data_type_privileges );
GRANT SELECT ON element_types TO PUBLIC;
+
+
+-- SQL/MED views; these use section numbers from part 9 of the standard.
+-- (still SQL:2008; there is no SQL:2011 SQL/MED)
+
+/* Base view for foreign table columns */
+CREATE VIEW _pg_foreign_table_columns AS
+ SELECT n.nspname,
+ c.relname,
+ a.attname,
+ a.attfdwoptions
+ FROM pg_foreign_table t, pg_authid u, pg_namespace n, pg_class c,
+ pg_attribute a
+ WHERE u.oid = c.relowner
+ AND (pg_has_role(c.relowner, 'USAGE')
+ OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'))
+ AND n.oid = c.relnamespace
+ AND c.oid = t.ftrelid
+ AND c.relkind = 'f'
+ AND a.attrelid = c.oid
+ AND a.attnum > 0;
+
+/*
+ * 24.2
+ * COLUMN_OPTIONS view
+ */
+CREATE VIEW column_options AS
+ SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
+ c.nspname AS table_schema,
+ c.relname AS table_name,
+ c.attname AS column_name,
+ CAST((pg_options_to_table(c.attfdwoptions)).option_name AS sql_identifier) AS option_name,
+ CAST((pg_options_to_table(c.attfdwoptions)).option_value AS character_data) AS option_value
+ FROM _pg_foreign_table_columns c;
+
+GRANT SELECT ON column_options TO PUBLIC;
+
+
+/* Base view for foreign-data wrappers */
+CREATE VIEW _pg_foreign_data_wrappers AS
+ SELECT w.oid,
+ w.fdwowner,
+ w.fdwoptions,
+ CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
+ CAST(fdwname AS sql_identifier) AS foreign_data_wrapper_name,
+ CAST(u.rolname AS sql_identifier) AS authorization_identifier,
+ CAST('c' AS character_data) AS foreign_data_wrapper_language
+ FROM pg_foreign_data_wrapper w, pg_authid u
+ WHERE u.oid = w.fdwowner
+ AND (pg_has_role(fdwowner, 'USAGE')
+ OR has_foreign_data_wrapper_privilege(w.oid, 'USAGE'));
+
+
+/*
+ * 24.4
+ * FOREIGN_DATA_WRAPPER_OPTIONS view
+ */
+CREATE VIEW foreign_data_wrapper_options AS
+ SELECT foreign_data_wrapper_catalog,
+ foreign_data_wrapper_name,
+ CAST((pg_options_to_table(w.fdwoptions)).option_name AS sql_identifier) AS option_name,
+ CAST((pg_options_to_table(w.fdwoptions)).option_value AS character_data) AS option_value
+ FROM _pg_foreign_data_wrappers w;
+
+GRANT SELECT ON foreign_data_wrapper_options TO PUBLIC;
+
+
+/*
+ * 24.5
+ * FOREIGN_DATA_WRAPPERS view
+ */
+CREATE VIEW foreign_data_wrappers AS
+ SELECT foreign_data_wrapper_catalog,
+ foreign_data_wrapper_name,
+ authorization_identifier,
+ CAST(NULL AS character_data) AS library_name,
+ foreign_data_wrapper_language
+ FROM _pg_foreign_data_wrappers w;
+
+GRANT SELECT ON foreign_data_wrappers TO PUBLIC;
+
+
+/* Base view for foreign servers */
+CREATE VIEW _pg_foreign_servers AS
+ SELECT s.oid,
+ s.srvoptions,
+ CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
+ CAST(srvname AS sql_identifier) AS foreign_server_name,
+ CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
+ CAST(w.fdwname AS sql_identifier) AS foreign_data_wrapper_name,
+ CAST(srvtype AS character_data) AS foreign_server_type,
+ CAST(srvversion AS character_data) AS foreign_server_version,
+ CAST(u.rolname AS sql_identifier) AS authorization_identifier
+ FROM pg_foreign_server s, pg_foreign_data_wrapper w, pg_authid u
+ WHERE w.oid = s.srvfdw
+ AND u.oid = s.srvowner
+ AND (pg_has_role(s.srvowner, 'USAGE')
+ OR has_server_privilege(s.oid, 'USAGE'));
+
+
+/*
+ * 24.6
+ * FOREIGN_SERVER_OPTIONS view
+ */
+CREATE VIEW foreign_server_options AS
+ SELECT foreign_server_catalog,
+ foreign_server_name,
+ CAST((pg_options_to_table(s.srvoptions)).option_name AS sql_identifier) AS option_name,
+ CAST((pg_options_to_table(s.srvoptions)).option_value AS character_data) AS option_value
+ FROM _pg_foreign_servers s;
+
+GRANT SELECT ON TABLE foreign_server_options TO PUBLIC;
+
+
+/*
+ * 24.7
+ * FOREIGN_SERVERS view
+ */
+CREATE VIEW foreign_servers AS
+ SELECT foreign_server_catalog,
+ foreign_server_name,
+ foreign_data_wrapper_catalog,
+ foreign_data_wrapper_name,
+ foreign_server_type,
+ foreign_server_version,
+ authorization_identifier
+ FROM _pg_foreign_servers;
+
+GRANT SELECT ON foreign_servers TO PUBLIC;
+
+
+/* Base view for foreign tables */
+CREATE VIEW _pg_foreign_tables AS
+ SELECT
+ CAST(current_database() AS sql_identifier) AS foreign_table_catalog,
+ n.nspname AS foreign_table_schema,
+ c.relname AS foreign_table_name,
+ t.ftoptions AS ftoptions,
+ CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
+ CAST(srvname AS sql_identifier) AS foreign_server_name,
+ CAST(u.rolname AS sql_identifier) AS authorization_identifier
+ FROM pg_foreign_table t, pg_foreign_server s, pg_foreign_data_wrapper w,
+ pg_authid u, pg_namespace n, pg_class c
+ WHERE w.oid = s.srvfdw
+ AND u.oid = c.relowner
+ AND (pg_has_role(c.relowner, 'USAGE')
+ OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
+ OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'))
+ AND n.oid = c.relnamespace
+ AND c.oid = t.ftrelid
+ AND c.relkind = 'f'
+ AND s.oid = t.ftserver;
+
+
+/*
+ * 24.8
+ * FOREIGN_TABLE_OPTIONS view
+ */
+CREATE VIEW foreign_table_options AS
+ SELECT foreign_table_catalog,
+ foreign_table_schema,
+ foreign_table_name,
+ CAST((pg_options_to_table(t.ftoptions)).option_name AS sql_identifier) AS option_name,
+ CAST((pg_options_to_table(t.ftoptions)).option_value AS character_data) AS option_value
+ FROM _pg_foreign_tables t;
+
+GRANT SELECT ON TABLE foreign_table_options TO PUBLIC;
+
+
+/*
+ * 24.9
+ * FOREIGN_TABLES view
+ */
+CREATE VIEW foreign_tables AS
+ SELECT foreign_table_catalog,
+ foreign_table_schema,
+ foreign_table_name,
+ foreign_server_catalog,
+ foreign_server_name
+ FROM _pg_foreign_tables;
+
+GRANT SELECT ON foreign_tables TO PUBLIC;
+
+
+
+/* Base view for user mappings */
+CREATE VIEW _pg_user_mappings AS
+ SELECT um.oid,
+ um.umoptions,
+ um.umuser,
+ CAST(COALESCE(u.rolname,'PUBLIC') AS sql_identifier ) AS authorization_identifier,
+ s.foreign_server_catalog,
+ s.foreign_server_name,
+ s.authorization_identifier AS srvowner
+ FROM pg_user_mapping um LEFT JOIN pg_authid u ON (u.oid = um.umuser),
+ _pg_foreign_servers s
+ WHERE s.oid = um.umserver;
+
+
+/*
+ * 24.12
+ * USER_MAPPING_OPTIONS view
+ */
+CREATE VIEW user_mapping_options AS
+ SELECT authorization_identifier,
+ foreign_server_catalog,
+ foreign_server_name,
+ CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name,
+ CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user)
+ OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE'))
+ OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) THEN (pg_options_to_table(um.umoptions)).option_value
+ ELSE NULL END AS character_data) AS option_value
+ FROM _pg_user_mappings um;
+
+GRANT SELECT ON user_mapping_options TO PUBLIC;
+
+
+/*
+ * 24.13
+ * USER_MAPPINGS view
+ */
+CREATE VIEW user_mappings AS
+ SELECT authorization_identifier,
+ foreign_server_catalog,
+ foreign_server_name
+ FROM _pg_user_mappings;
+
+GRANT SELECT ON user_mappings TO PUBLIC;
projects / postgresql / blobdiff