diff options
| author | Peter Eisentraut <peter_e@gmx.net> | 2006-04-02 17:38:13 +0000 |
|---|---|---|
| committer | Peter Eisentraut <peter_e@gmx.net> | 2006-04-02 17:38:13 +0000 |
| commit | f7ae90041ef16e2e1a5296b0cc6fed1202acf312 (patch) | |
| tree | 8af74fc44e9911d7642db88afad987925595bad2 | |
| parent | 643b022bed88dda60953833624f77ef1d365d516 (diff) | |
| download | postgresql-f7ae90041ef16e2e1a5296b0cc6fed1202acf312.tar.gz postgresql-f7ae90041ef16e2e1a5296b0cc6fed1202acf312.zip | |
Update information schema for SQL:2003 and new PostgreSQL features.
| -rw-r--r-- | doc/src/sgml/information_schema.sgml | 1130 | ||||
| -rw-r--r-- | src/backend/catalog/information_schema.sql | 765 |
2 files changed, 1666 insertions, 229 deletions
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 57d9e1a248a..6f3498f3bec 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -1,4 +1,4 @@ -<!-- $PostgreSQL: pgsql/doc/src/sgml/information_schema.sgml,v 1.24 2006/01/18 21:02:55 momjian Exp $ --> +<!-- $PostgreSQL: pgsql/doc/src/sgml/information_schema.sgml,v 1.25 2006/04/02 17:38:13 petere Exp $ --> <chapter id="information-schema"> <title>The Information Schema</title> @@ -92,7 +92,7 @@ <term><type>time_stamp</type></term> <listitem> <para> - A domain over the type <type>timestamp</type> + A domain over the type <type>timestamp with time zone</type> </para> </listitem> </varlistentry> @@ -144,15 +144,65 @@ </table> </sect1> + <sect1 id="infoschema-administrable-role-authorizations"> + <title><literal>administrable_role_authorizations</literal></title> + + <para> + The view <literal>administrable_role_authorizations</literal> + identifies all roles that the current user has the admin option + for. + </para> + + <table> + <title><literal>administrable_role_authorizations</literal> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Data Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>grantee</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the role to which this role membership was granted (may + be the current user, or a different role in case of nested role + memberships) + </entry> + </row> + + <row> + <entry><literal>role_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of a role</entry> + </row> + + <row> + <entry><literal>is_grantable</literal></entry> + <entry><type>character_data</type></entry> + <entry>Always <literal>YES</literal></entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + <sect1 id="infoschema-applicable-roles"> <title><literal>applicable_roles</literal></title> <para> - The view <literal>applicable_roles</literal> identifies all groups - that the current user is a member of. (A role is the same thing as - a group.) Generally, it is better to use the view - <literal>enabled_roles</literal> instead of this one; see also - there. + The view <literal>applicable_roles</literal> identifies all roles + whose privileges the current user can use. This means there is + some chain of role grants from the current user to the role in + question. The current user itself is also an applicable role. The + set of applicable roles is generally used for permission checking. + <indexterm><primary>applicable role</primary></indexterm> + <indexterm><primary>role</primary><secondary>applicable</secondary></indexterm> </para> <table> @@ -171,23 +221,339 @@ <row> <entry><literal>grantee</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>Always the name of the current user</entry> + <entry> + Name of the role to which this role membership was granted (may + be the current user, or a different role in case of nested role + memberships) + </entry> </row> <row> <entry><literal>role_name</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>Name of a group</entry> + <entry>Name of a role</entry> </row> <row> <entry><literal>is_grantable</literal></entry> <entry><type>character_data</type></entry> + <entry> + <literal>YES</literal> if the grantee has the admin option on + the role, <literal>NO</literal> if not + </entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + + <sect1 id="infoschema-attributes"> + <title><literal>attributes</literal></title> + + <para> + The view <literal>attributes</literal> contains information about + the attributes of composite data types defined in the database. + (Note that the view does not give information about table columns, + which are sometimes called attributes in PostgreSQL contexts.) + </para> + + <table> + <title><literal>attributes</literal> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Data Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>udt_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the database containing the data type (always the current database)</entry> + </row> + + <row> + <entry><literal>udt_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the schema containing the data type</entry> + </row> + + <row> + <entry><literal>udt_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the data type</entry> + </row> + + <row> + <entry><literal>attribute_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the attribute</entry> + </row> + + <row> + <entry><literal>ordinal_position</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Ordinal position of the attribute within the data type (count starts at 1)</entry> + </row> + + <row> + <entry><literal>attribute_default</literal></entry> + <entry><type>character_data</type></entry> + <entry>Default expression of the attribute</entry> + </row> + + <row> + <entry><literal>is_nullable</literal></entry> + <entry><type>character_data</type></entry> + <entry> + <literal>YES</literal> if the attribute is possibly nullable, + <literal>NO</literal> if it is known not nullable. + </entry> + </row> + + <row> + <entry><literal>data_type</literal></entry> + <entry><type>character_data</type></entry> + <entry> + Data type of the attribute, if it is a built-in type, or + <literal>ARRAY</literal> if it is some array (in that case, see + the view <literal>element_types</literal>), else + <literal>USER-DEFINED</literal> (in that case, the type is + identified in <literal>attribute_udt_name</literal> and + associated columns). + </entry> + </row> + + <row> + <entry><literal>character_maximum_length</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry> + If <literal>data_type</literal> identifies a character or bit + string type, the declared maximum length; null for all other + data types or if no maximum length was declared. + </entry> + </row> + + <row> + <entry><literal>character_octet_length</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry> + If <literal>data_type</literal> identifies a character type, + the maximum possible length in octets (bytes) of a datum (this + should not be of concern to + <productname>PostgreSQL</productname> users); null for all + other data types. + </entry> + </row> + + <row> + <entry><literal>numeric_precision</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry> + If <literal>data_type</literal> identifies a numeric type, this + column contains the (declared or implicit) precision of the + type for this attribute. The precision indicates the number of + significant digits. It may be expressed in decimal (base 10) + or binary (base 2) terms, as specified in the column + <literal>numeric_precision_radix</literal>. For all other data + types, this column is null. + </entry> + </row> + + <row> + <entry><literal>numeric_precision_radix</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry> + If <literal>data_type</literal> identifies a numeric type, this + column indicates in which base the values in the columns + <literal>numeric_precision</literal> and + <literal>numeric_scale</literal> are expressed. The value is + either 2 or 10. For all other data types, this column is null. + </entry> + </row> + + <row> + <entry><literal>numeric_scale</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry> + If <literal>data_type</literal> identifies an exact numeric + type, this column contains the (declared or implicit) scale of + the type for this attribute. The scale indicates the number of + significant digits to the right of the decimal point. It may + be expressed in decimal (base 10) or binary (base 2) terms, as + specified in the column + <literal>numeric_precision_radix</literal>. For all other data + types, this column is null. + </entry> + </row> + + <row> + <entry><literal>datetime_precision</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry> + If <literal>data_type</literal> identifies a date, time, or + interval type, the declared precision; null for all other data + types or if no precision was declared. + </entry> + </row> + + <row> + <entry><literal>interval_type</literal></entry> + <entry><type>character_data</type></entry> + <entry>Not yet implemented</entry> + </row> + + <row> + <entry><literal>interval_precision</literal></entry> + <entry><type>character_data</type></entry> + <entry>Not yet implemented</entry> + </row> + + <row> + <entry><literal>attribute_udt_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the database that the attribute data type is defined in + (always the current database) + </entry> + </row> + + <row> + <entry><literal>attribute_udt_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the schema that the attribute data type is defined in + </entry> + </row> + + <row> + <entry><literal>attribute_udt_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the attribute data type + </entry> + </row> + + <row> + <entry><literal>scope_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>scope_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>scope_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>maximum_cardinality</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Always null, because arrays always have unlimited maximum cardinality in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>dtd_identifier</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + An identifier of the data type descriptor of the column, unique + among the data type descriptors pertaining to the table. This + is mainly useful for joining with other instances of such + identifiers. (The specific format of the identifier is not + defined and not guaranteed to remain the same in future + versions.) + </entry> + </row> + + <row> + <entry><literal>is_derived_reference_attribute</literal></entry> + <entry><type>character_data</type></entry> <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> </row> </tbody> </tgroup> </table> + + <para> + See also under <xref linkend="infoschema-columns">, a similarly + structured view, for further information on some of the columns. + </para> + </sect1> + + <sect1 id="infoschema-check-constraint-routine-usage"> + <title><literal>check_constraint_routine_usage</literal></title> + + <para> + The view <literal>check_constraint_routine_usage</literal> + identifies routines (functions and procedures) that are used by a + check constraint. Only those routines are shown that are owned by + a currently enabled role. + </para> + + <table> + <title><literal>check_constraint_routine_usage</literal> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Data Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>constraint_catalog</literal></entry> + <entry><literal>sql_identifier</literal></entry> + <entry>Name of the database containing the constraint (always the current database)</entry> + </row> + + <row> + <entry><literal>constraint_schema</literal></entry> + <entry><literal>sql_identifier</literal></entry> + <entry>Name of the schema containing the constraint</entry> + </row> + + <row> + <entry><literal>constraint_name</literal></entry> + <entry><literal>sql_identifier</literal></entry> + <entry>Name of the constraint</entry> + </row> + + <row> + <entry><literal>specific_catalog</literal></entry> + <entry><literal>sql_identifier</literal></entry> + <entry>Name of the database containing the function (always the current database)</entry> + </row> + + <row> + <entry><literal>specific_schema</literal></entry> + <entry><literal>sql_identifier</literal></entry> + <entry>Name of the schema containing the function</entry> + </row> + + <row> + <entry><literal>specific_name</literal></entry> + <entry><literal>sql_identifier</literal></entry> + <entry> + The <quote>specific name</quote> of the function. See <xref + linkend="infoschema-routines"> for more information. + </entry> + </row> + </tbody> + </tgroup> + </table> </sect1> <sect1 id="infoschema-check-constraints"> @@ -196,8 +562,8 @@ <para> The view <literal>check_constraints</literal> contains all check constraints, either defined on a table or on a domain, that are - owned by the current user. (The owner of the table or domain is - the owner of the constraint.) + owned by a currently enabled role. (The owner of the table or + domain is the owner of the constraint.) </para> <table> @@ -247,7 +613,7 @@ <para> The view <literal>column_domain_usage</literal> identifies all columns (of a table or a view) that make use of some domain defined - in the current database and owned by the current user. + in the current database and owned by a currently enabled role. </para> <table> @@ -314,10 +680,9 @@ <para> The view <literal>column_privileges</literal> identifies all - privileges granted on columns to the current user or by the current - user. There is one row for each combination of column, grantor, - and grantee. Privileges granted to groups are identified in the - view <literal>role_column_grants</literal>. + privileges granted on columns to a currently enabled role or by a + currently enabled role. There is one row for each combination of + column, grantor, and grantee. </para> <para> @@ -351,13 +716,13 @@ <row> <entry><literal>grantor</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>Name of the user that granted the privilege</entry> + <entry>Name of the role that granted the privilege</entry> </row> <row> <entry><literal>grantee</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>Name of the user or group that the privilege was granted to</entry> + <entry>Name of the role that the privilege was granted to</entry> </row> <row> @@ -402,14 +767,6 @@ </tbody> </tgroup> </table> - - <para> - Note that the column <literal>grantee</literal> makes no - distinction between users and groups. If you have users and groups - with the same name, there is unfortunately no way to distinguish - them. A future version of <productname>PostgreSQL</productname> - will possibly prohibit having users and groups with the same name. - </para> </sect1> <sect1 id="infoschema-column-udt-usage"> @@ -417,7 +774,7 @@ <para> The view <literal>column_udt_usage</literal> identifies all columns - that use data types owned by the current user. Note that in + that use data types owned by a currently enabled role. Note that in <productname>PostgreSQL</productname>, built-in data types behave like user-defined types, so they are included here as well. See also <xref linkend="infoschema-columns"> for details. @@ -549,10 +906,7 @@ <row> <entry><literal>column_default</literal></entry> <entry><type>character_data</type></entry> - <entry> - Default expression of the column (null if the current user is - not the owner of the table containing the column) - </entry> + <entry>Default expression of the column</entry> </row> <row> @@ -797,6 +1151,70 @@ <entry><type>character_data</type></entry> <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> </row> + + <row> + <entry><literal>is_identity</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>identity_generation</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>identity_start</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>identity_increment</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>identity_maximum</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>identity_minimum</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>identity_cycle</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>is_generated</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>generation_expression</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>is_updatable</literal></entry> + <entry><type>character_data</type></entry> + <entry> + <literal>YES</literal> if the column is updatable, + <literal>NO</literal> if not (Columns in base tables are always + updatable, columns in views not necessarily) + </entry> + </row> </tbody> </tgroup> </table> @@ -837,12 +1255,12 @@ <para> The view <literal>constraint_column_usage</literal> identifies all columns in the current database that are used by some constraint. - Only those columns are shown that are contained in a table owned - the current user. For a check constraint, this view identifies the - columns that are used in the check expression. For a foreign key - constraint, this view identifies the columns that the foreign key - references. For a unique or primary key constraint, this view - identifies the constrained columns. + Only those columns are shown that are contained in a table owned by + a currently enabled role. For a check constraint, this view + identifies the columns that are used in the check expression. For + a foreign key constraint, this view identifies the columns that the + foreign key references. For a unique or primary key constraint, + this view identifies the constrained columns. </para> <table> @@ -922,9 +1340,9 @@ <para> The view <literal>constraint_table_usage</literal> identifies all tables in the current database that are used by some constraint and - are owned by the current user. (This is different from the view - <literal>table_constraints</literal>, which identifies all table - constraints along with the table they are defined on.) For a + are owned by a currently enabled role. (This is different from the + view <literal>table_constraints</literal>, which identifies all + table constraints along with the table they are defined on.) For a foreign key constraint, this view identifies the table that the foreign key references. For a unique or primary key constraint, this view simply identifies the table the constraint belongs to. @@ -1072,7 +1490,7 @@ <para> The view <literal>domain_constraints</literal> contains all - constraints belonging to domains owned by the current user. + constraints belonging to domains defined in the current database. </para> <table> @@ -1144,10 +1562,11 @@ <title><literal>domain_udt_usage</literal></title> <para> - The view <literal>domain_udt_usage</literal> identifies all columns - that use data types owned by the current user. Note that in - <productname>PostgreSQL</productname>, built-in data types behave - like user-defined types, so they are included here as well. + The view <literal>domain_udt_usage</literal> identifies all domains + that are based on data types owned by a currently enabled role. + Note that in <productname>PostgreSQL</productname>, built-in data + types behave like user-defined types, so they are included here as + well. </para> <table> @@ -1695,15 +2114,23 @@ ORDER BY c.ordinal_position; <title><literal>enabled_roles</literal></title> <para> - The view <literal>enabled_roles</literal> identifies all groups - that the current user is a member of. (A role is the same thing as - a group.) The difference between this view and - <literal>applicable_roles</literal> is that in the future there may - be a mechanism to enable and disable groups during a session. In - that case this view identifies those groups that are currently - enabled. + The view <literal>enabled_roles</literal> identifies the currently + <quote>enabled roles</quote>. The enabled roles are recursively + defined as the current user together with all roles that have been + granted to the enabled roles with automatic inheritance. In other + words, these are all roles that the current user has direct or + indirect, automatically inheriting membership in. + <indexterm><primary>enabled role</primary></indexterm> + <indexterm><primary>role</primary><secondary>enabled</secondary></indexterm> </para> + <para> + For permission checking, the set of <quote>applicable roles</quote> + is applied, which may be broader than the set of enabled roles. So + generally, it is better to use the view + <literal>applicable_roles</literal> instead of this one; see also + there. + <table> <title><literal>enabled_roles</literal> Columns</title> @@ -1720,7 +2147,7 @@ ORDER BY c.ordinal_position; <row> <entry><literal>role_name</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>Name of a group</entry> + <entry>Name of a role</entry> </row> </tbody> </tgroup> @@ -1734,8 +2161,8 @@ ORDER BY c.ordinal_position; The view <literal>key_column_usage</literal> identifies all columns in the current database that are restricted by some unique, primary key, or foreign key constraint. Check constraints are not included - in this view. Only those columns are shown that are contained in a - table owned by the current user. + in this view. Only those columns are shown that the current user + has access to, by way of being the owner or having some privilege. </para> <table> @@ -1813,6 +2240,14 @@ ORDER BY c.ordinal_position; starts at 1) </entry> </row> + + <row> + <entry><literal>position_in_unique_constraint</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry> + Not yet implemented + </entry> + </row> </tbody> </tgroup> </table> @@ -2069,7 +2504,7 @@ ORDER BY c.ordinal_position; <para> The view <literal>referential_constraints</literal> contains all referential (foreign key) constraints in the current database that - belong to a table owned by the current user. + belong to a table owned by a currently enabled role. </para> <table> @@ -2172,8 +2607,8 @@ ORDER BY c.ordinal_position; <para> The view <literal>role_column_grants</literal> identifies all - privileges granted on columns to a group that the current user is a - member of. Further information can be found under + privileges granted on columns where the grantor or grantee is a + currently enabled role. Further information can be found under <literal>column_privileges</literal>. </para> @@ -2193,13 +2628,13 @@ ORDER BY c.ordinal_position; <row> <entry><literal>grantor</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>Name of the user that granted the privilege</entry> + <entry>Name of the role that granted the privilege</entry> </row> <row> <entry><literal>grantee</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>Name of the group that the privilege was granted to</entry> + <entry>Name of the role that the privilege was granted to</entry> </row> <row> @@ -2251,8 +2686,8 @@ ORDER BY c.ordinal_position; <para> The view <literal>role_routine_grants</literal> identifies all - privileges granted on functions to a group that the current user is - a member of. Further information can be found under + privileges granted on functions where the grantor or grantee is a + currently enabled role. Further information can be found under <literal>routine_privileges</literal>. </para> @@ -2272,13 +2707,13 @@ ORDER BY c.ordinal_position; <row> <entry><literal>grantor</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>Name of the user that granted the privilege</entry> + <entry>Name of the role that granted the privilege</entry> </row> <row> <entry><literal>grantee</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>Name of the group that the privilege was granted to</entry> + <entry>Name of the role that the privilege was granted to</entry> </row> <row> @@ -2341,9 +2776,9 @@ ORDER BY c.ordinal_position; <para> The view <literal>role_table_grants</literal> identifies all - privileges granted on tables or views to a group that the current - user is a member of. Further information can be found under - <literal>table_privileges</literal>. + privileges granted on tables or views where the grantor or grantee + is a currently enabled role. Further information can be found + under <literal>table_privileges</literal>. </para> <table> @@ -2362,13 +2797,13 @@ ORDER BY c.ordinal_position; <row> <entry><literal>grantor</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>Name of the user that granted the privilege</entry> + <entry>Name of the role that granted the privilege</entry> </row> <row> <entry><literal>grantee</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>Name of the group that the privilege was granted to</entry> + <entry>Name of the role that the privilege was granted to</entry> </row> <row> @@ -2422,12 +2857,13 @@ ORDER BY c.ordinal_position; <para> The view <literal>role_usage_grants</literal> is meant to identify <literal>USAGE</literal> privileges granted on various kinds of - objects to a group that the current user is a member of. In - <productname>PostgreSQL</productname>, this currently only applies - to domains, and since domains do not have real privileges in - <productname>PostgreSQL</productname>, this view is empty. Further - information can be found under <literal>usage_privileges</literal>. - In the future, this view may contain more useful information. + objects to a currently enabled role or by a currently enabled role. + In <productname>PostgreSQL</productname>, this currently only + applies to domains, and since domains do not have real privileges + in <productname>PostgreSQL</productname>, this view is empty. + Further information can be found under + <literal>usage_privileges</literal>. In the future, this view may + contain more useful information. </para> <table> @@ -2446,13 +2882,13 @@ ORDER BY c.ordinal_position; <row> <entry><literal>grantor</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>In the future, the name of the user that granted the privilege</entry> + <entry>In the future, the name of the role that granted the privilege</entry> </row> <row> <entry><literal>grantee</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>In the future, the name of the group that the privilege was granted to</entry> + <entry>In the future, the name of the role that the privilege was granted to</entry> </row> <row> @@ -2500,10 +2936,9 @@ ORDER BY c.ordinal_position; <para> The view <literal>routine_privileges</literal> identifies all - privileges granted on functions to the current user or by the - current user. There is one row for each combination of function, - grantor, and grantee. Privileges granted to groups are identified - in the view <literal>role_routine_grants</literal>. + privileges granted to a currently enabled role or by a currently + enabled role. There is one row for each combination of function, + grantor, and grantee. </para> <table> @@ -2522,13 +2957,13 @@ ORDER BY c.ordinal_position; <row> <entry><literal>grantor</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>Name of the user that granted the privilege</entry> + <entry>Name of the role that granted the privilege</entry> </row> <row> <entry><literal>grantee</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>Name of the user or group that the privilege was granted to</entry> + <entry>Name of the role that the privilege was granted to</entry> </row> <row> @@ -2584,14 +3019,6 @@ ORDER BY c.ordinal_position; </tbody> </tgroup> </table> - - <para> - Note that the column <literal>grantee</literal> makes no - distinction between users and groups. If you have users and groups - with the same name, there is unfortunately no way to distinguish - them. A future version of <productname>PostgreSQL</productname> - will possibly prohibit having users and groups with the same name. - </para> </sect1> <sect1 id="infoschema-routines"> @@ -2877,8 +3304,8 @@ ORDER BY c.ordinal_position; <entry><literal>routine_definition</literal></entry> <entry><type>character_data</type></entry> <entry> - The source text of the function (null if the current user is - not the owner of the function). (According to the SQL + The source text of the function (null if the function is not + owned by a currently enabled role). (According to the SQL standard, this column is only applicable if <literal>routine_body</literal> is <literal>SQL</literal>, but in <productname>PostgreSQL</productname> it will contain @@ -3012,6 +3439,174 @@ ORDER BY c.ordinal_position; <entry><type>character_data</type></entry> <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> </row> + + <row> + <entry><literal>created</literal></entry> + <entry><type>time_stamp</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>last_altered</literal></entry> + <entry><type>time_stamp</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>new_savepoint_level</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>is_udt_dependent</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_from_data_type</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_as_locator</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_char_max_length</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_char_octet_length</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_char_set_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_char_set_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_char_set_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_collation_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_collation_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_collation_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_numeric_precision</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_numeric_precision_radix</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_numeric_scale</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_datetime_precision</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_interval_type</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_interval_precision</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_type_udt_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_type_udt_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_type_udt_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_scope_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_scope_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_scope_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_maximum_cardinality</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>result_cast_dtd_identifier</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> </tbody> </tgroup> </table> @@ -3022,7 +3617,7 @@ ORDER BY c.ordinal_position; <para> The view <literal>schemata</literal> contains all schemas in the - current database that are owned by the current user. + current database that are owned by a currently enabled role. </para> <table> @@ -3084,6 +3679,121 @@ ORDER BY c.ordinal_position; </table> </sect1> + <sect1 id="infoschema-sequences"> + <title><literal>sequences</literal></title> + + <para> + The view <literal>sequences</literal> contains all sequences + defined in the current database. Only those sequences are shown + that the current user has access to (by way of being the owner or + having some privilege). + </para> + + <table> + <title><literal>sequences</literal> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Data Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>sequence_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the database that contains the sequence (always the current database)</entry> + </row> + + <row> + <entry><literal>sequence_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the schema that contains the sequence</entry> + </row> + + <row> + <entry><literal>sequence_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the sequence</entry> + </row> + + <row> + <entry><literal>data_type</literal></entry> + <entry><type>character_data</type></entry> + <entry> + The data type of the sequence. In + <productname>PostgreSQL</productname>, this is currently always + <literal>bigint</literal>. + </entry> + </row> + + <row> + <entry><literal>numeric_precision</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry> + This column contains the (declared or implicit) precision of + the sequence data type (see above). The precision indicates + the number of significant digits. It may be expressed in + decimal (base 10) or binary (base 2) terms, as specified in the + column <literal>numeric_precision_radix</literal>. + </entry> + </row> + + <row> + <entry><literal>numeric_precision_radix</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry> + This column indicates in which base the values in the columns + <literal>numeric_precision</literal> and + <literal>numeric_scale</literal> are expressed. The value is + either 2 or 10. + </entry> + </row> + + <row> + <entry><literal>numeric_scale</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry> + This column contains the (declared or implicit) scale of the + sequence data type (see above). The scale indicates the number + of significant digits to the right of the decimal point. It + may be expressed in decimal (base 10) or binary (base 2) terms, + as specified in the column + <literal>numeric_precision_radix</literal>. + </entry> + </row> + + <row> + <entry><literal>maximum_value</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Not yet implemented</entry> + </row> + + <row> + <entry><literal>minimum_value</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Not yet implemented</entry> + </row> + + <row> + <entry><literal>increment</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Not yet implemented</entry> + </row> + + <row> + <entry><literal>cycle_option</literal></entry> + <entry><type>character_data</type></entry> + <entry>Not yet implemented</entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + <sect1 id="infoschema-sql-features"> <title><literal>sql_features</literal></title> @@ -3379,6 +4089,69 @@ ORDER BY c.ordinal_position; </table> </sect1> + <sect1 id="infoschema-sql-parts"> + <title><literal>sql_parts</literal></title> + + <para> + The table <literal>sql_parts</literal> contains information about + which of the several parts of the SQL standard are supported by + <productname>PostgreSQL</productname>. + </para> + + <table> + <title><literal>sql_parts</literal> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Data Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>feature_id</literal></entry> + <entry><type>character_data</type></entry> + <entry>An identifier string containing the number of the part</entry> + </row> + + <row> + <entry><literal>feature_name</literal></entry> + <entry><type>character_data</type></entry> + <entry>Descriptive name of the part</entry> + </row> + + <row> + <entry><literal>is_supported</literal></entry> + <entry><type>character_data</type></entry> + <entry> + <literal>YES</literal> if the part is fully supported by the + current version of <productname>PostgreSQL</>, + <literal>NO</literal> if not + </entry> + </row> + + <row> + <entry><literal>is_verified_by</literal></entry> + <entry><type>character_data</type></entry> + <entry> + Always null, since the <productname>PostgreSQL</> development group does not + perform formal testing of feature conformance + </entry> + </row> + + <row> + <entry><literal>comments</literal></entry> + <entry><type>character_data</type></entry> + <entry>Possibly a comment about the supported status of the part</entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + <sect1 id="infoschema-sql-sizing"> <title><literal>sql_sizing</literal></title> @@ -3505,7 +4278,8 @@ ORDER BY c.ordinal_position; <para> The view <literal>table_constraints</literal> contains all - constraints belonging to tables owned by the current user. + constraints belonging to tables that the current user owns or has + some privilege on. </para> <table> @@ -3588,10 +4362,9 @@ ORDER BY c.ordinal_position; <para> The view <literal>table_privileges</literal> identifies all - privileges granted on tables or views to the current user or by the - current user. There is one row for each combination of table, - grantor, and grantee. Privileges granted to groups are identified - in the view <literal>role_table_grants</literal>. + privileges granted on tables or views to a currently enabled role + or by a currently enabled role. There is one row for each + combination of table, grantor, and grantee. </para> <table> @@ -3610,13 +4383,13 @@ ORDER BY c.ordinal_position; <row> <entry><literal>grantor</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>Name of the user that granted the privilege</entry> + <entry>Name of the role that granted the privilege</entry> </row> <row> <entry><literal>grantee</literal></entry> <entry><type>sql_identifier</type></entry> - <entry>Name of the user or group that the privilege was granted to</entry> + <entry>Name of the role that the privilege was granted to</entry> </row> <row> @@ -3662,14 +4435,6 @@ ORDER BY c.ordinal_position; </tbody> </tgroup> </table> - - <para> - Note that the column <literal>grantee</literal> makes no - distinction between users and groups. If you have users and groups - with the same name, there is unfortunately no way to distinguish - them. A future version of <productname>PostgreSQL</productname> - will possibly prohibit having users and groups with the same name. - </para> </sect1> <sect1 id="infoschema-tables"> @@ -3753,6 +4518,33 @@ ORDER BY c.ordinal_position; <entry><type>sql_identifier</type></entry> <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> </row> + + <row> + <entry><literal>is_insertable_into</literal></entry> + <entry><type>character_data</type></entry> + <entry> + <literal>YES</literal> if the table is insertable into, + <literal>NO</literal> if not (Base tables are always insertable + into, views not necessarily.) + </entry> + </row> + + <row> + <entry><literal>is_typed</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>commit_action</literal></entry> + <entry><type>character_data</type></entry> + <entry> + If the table is a temporary table, then + <literal>PRESERVE</literal>, else null. (The SQL standard + defines other commit actions for temporary tables, which are + not supported by <productname>PostgreSQL</>.) + </entry> + </row> </tbody> </tgroup> </table> @@ -3763,8 +4555,8 @@ ORDER BY c.ordinal_position; <para> The view <literal>triggers</literal> contains all triggers defined - in the current database that are owned by the current user. (The - owner of the table is the owner of the trigger.) + in the current database on tables that the current user owns or has + some privilege on. </para> <table> @@ -3880,6 +4672,24 @@ ORDER BY c.ordinal_position; <entry><type>sql_identifier</type></entry> <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> </row> + + <row> + <entry><literal>condition_reference_old_row</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>condition_reference_new_row</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> + + <row> + <entry><literal>created</literal></entry> + <entry><type>time_stamp</type></entry> + <entry>Applies to a feature not available in <productname>PostgreSQL</></entry> + </row> </tbody> </tgroup> </table> @@ -3917,10 +4727,10 @@ ORDER BY c.ordinal_position; <para> The view <literal>usage_privileges</literal> is meant to identify <literal>USAGE</literal> privileges granted on various kinds of - objects to the current user or by the current user. In - <productname>PostgreSQL</productname>, this currently only applies - to domains, and since domains do not have real privileges in - <productname>PostgreSQL</productname>, this view shows implicit + objects to a currently enabled role or by a currently enabled role. + In <productname>PostgreSQL</productname>, this currently only + applies to domains, and since domains do not have real privileges + in <productname>PostgreSQL</productname>, this view shows implicit <literal>USAGE</literal> privileges granted to <literal>PUBLIC</literal> for all domains. In the future, this view may contain more useful information. @@ -3998,8 +4808,8 @@ ORDER BY c.ordinal_position; The view <literal>view_column_usage</literal> identifies all columns that are used in the query expression of a view (the <command>SELECT</command> statement that defines the view). A - column is only included if the current user is the owner of the - table that contains the column. + column is only included if the table that contains the column is + owned by a currently enabled role. </para> <note> @@ -4077,6 +4887,73 @@ ORDER BY c.ordinal_position; </table> </sect1> + <sect1 id="infoschema-view-routine-usage"> + <title><literal>view_routine_usage</literal></title> + + <para> + The view <literal>view_routine_usage</literal> identifies all + routines (functions and procedures) that are used in the query + expression of a view (the <command>SELECT</command> statement that + defines the view). A routine is only included if that routine is + owned by a currently enabled role. + </para> + + <table> + <title><literal>view_routine_usage</literal> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Data Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>table_catalog</literal></entry> + <entry><literal>sql_identifier</literal></entry> + <entry>Name of the database containing the view (always the current database)</entry> + </row> + + <row> + <entry><literal>table_schema</literal></entry> + <entry><literal>sql_identifier</literal></entry> + <entry>Name of the schema containing the view</entry> + </row> + + <row> + <entry><literal>table_name</literal></entry> + <entry><literal>sql_identifier</literal></entry> + <entry>Name of the view</entry> + </row> + + <row> + <entry><literal>specific_catalog</literal></entry> + <entry><literal>sql_identifier</literal></entry> + <entry>Name of the database containing the function (always the current database)</entry> + </row> + + <row> + <entry><literal>specific_schema</literal></entry> + <entry><literal>sql_identifier</literal></entry> + <entry>Name of the schema containing the function</entry> + </row> + + <row> + <entry><literal>specific_name</literal></entry> + <entry><literal>sql_identifier</literal></entry> + <entry> + The <quote>specific name</quote> of the function. See <xref + linkend="infoschema-routines"> for more information. + </entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + <sect1 id="infoschema-view-table-usage"> <title><literal>view_table_usage</literal></title> @@ -4084,8 +4961,8 @@ ORDER BY c.ordinal_position; The view <literal>view_table_usage</literal> identifies all tables that are used in the query expression of a view (the <command>SELECT</command> statement that defines the view). A - table is only included if the current user is the owner of that - table. + table is only included if that table is owned by a currently + enabled role. </para> <note> @@ -4199,8 +5076,8 @@ ORDER BY c.ordinal_position; <entry><literal>view definition</literal></entry> <entry><type>character_data</type></entry> <entry> - Query expression defining the view (null if the current user is - not the owner of the view) + Query expression defining the view (null if the view is not + owned by a currently enabled role) </entry> </row> @@ -4213,13 +5090,20 @@ ORDER BY c.ordinal_position; <row> <entry><literal>is_updatable</literal></entry> <entry><type>character_data</type></entry> - <entry>Not yet implemented</entry> + <entry> + <literal>YES</literal> if the view is updatable (allows + <command>UPDATE</command> and <command>DELETE</command>), + <literal>NO</literal> if not + </entry> </row> <row> <entry><literal>is_insertable_into</literal></entry> <entry><type>character_data</type></entry> - <entry>Not yet implemented</entry> + <entry> + <literal>YES</literal> if the view is insertable into (allows + <command>INSERT</command>), <literal>NO</literal> if not + </entry> </row> </tbody> </tgroup> diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 9e02d8f31f3..0faa3a0cd02 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -1,10 +1,10 @@ /* * SQL Information Schema - * as defined in ISO 9075-2:1999 chapter 20 + * as defined in ISO/IEC 9075-11:2003 * * Copyright (c) 2003-2006, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.32 2006/03/05 15:58:22 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.33 2006/04/02 17:38:13 petere Exp $ */ /* @@ -18,7 +18,7 @@ /* - * 20.2 + * 5.1 * INFORMATION_SCHEMA schema */ @@ -26,6 +26,7 @@ CREATE SCHEMA information_schema; GRANT USAGE ON SCHEMA information_schema TO PUBLIC; SET search_path TO information_schema, public; + /* * A few supporting functions first ... */ @@ -155,11 +156,11 @@ $$SELECT 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 */ @@ -168,7 +169,7 @@ CREATE DOMAIN cardinal_number AS integer /* - * 20.5 + * 5.4 * CHARACTER_DATA domain */ @@ -176,7 +177,7 @@ CREATE DOMAIN character_data AS character varying; /* - * 20.6 + * 5.5 * SQL_IDENTIFIER domain */ @@ -184,7 +185,7 @@ CREATE DOMAIN sql_identifier AS character varying; /* - * 20.3 + * 5.2 * INFORMATION_SCHEMA_CATALOG_NAME view */ @@ -195,16 +196,19 @@ GRANT SELECT ON information_schema_catalog_name TO PUBLIC; /* - * 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 ADMINISTRABLE_ROLE_AUTHORIZATIONS view appears later. + + /* - * 20.9 + * 5.8 * APPLICABLE_ROLES view */ @@ -215,13 +219,156 @@ CREATE VIEW applicable_roles AS 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, 'MEMBER'); + WHERE pg_has_role(a.oid, 'USAGE'); GRANT SELECT ON applicable_roles TO PUBLIC; /* - * 20.13 + * 5.7 + * 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.9 + * ASSERTIONS view + */ + +-- feature not supported + + +/* + * 5.10 + * 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 character_data) + AS is_nullable, + + 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(null AS sql_identifier) AS collation_catalog, + CAST(null AS sql_identifier) AS collation_schema, + CAST(null 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(null AS character_data) AS interval_type, -- FIXME + CAST(null AS character_data) AS interval_precision, -- FIXME + + 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 character_data) AS is_derived_reference_attribute + + FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum), + pg_class c, pg_namespace nc, + (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) + + WHERE a.attrelid = c.oid + AND a.atttypid = t.oid + AND nc.oid = c.relnamespace + AND a.attnum > 0 AND NOT a.attisdropped + AND c.relkind in ('c'); + +GRANT SELECT ON attributes TO PUBLIC; + + +/* + * 5.11 + * CHARACTER_SETS view + */ + +-- feature not supported + + +/* + * 5.12 + * 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; + + +/* + * 5.13 * CHECK_CONSTRAINTS view */ @@ -235,14 +382,54 @@ CREATE VIEW check_constraints AS 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) - WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'MEMBER') - AND con.contype = 'c'; + 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(n.oid || '_' || r.oid || '_' || a.attnum || '_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.14 + * COLLATIONS view + */ + +-- feature not supported + +/* + * 5.15 + * COLLATION_CHARACTER_SET_APPLICABILITY view + */ + +-- feature not supported + + +/* + * 5.16 + * COLUMN_COLUMN_USAGE view + */ + +-- feature not supported + + +/* + * 5.17 * COLUMN_DOMAIN_USAGE view */ @@ -266,13 +453,13 @@ CREATE VIEW column_domain_usage AS AND c.relkind IN ('r', 'v') AND a.attnum > 0 AND NOT a.attisdropped - AND pg_has_role(t.typowner, 'MEMBER'); + AND pg_has_role(t.typowner, 'USAGE'); GRANT SELECT ON column_domain_usage TO PUBLIC; /* - * 20.16 + * 5.18 * COLUMN_PRIVILEGES */ @@ -310,15 +497,15 @@ CREATE VIEW column_privileges AS AND c.relkind IN ('r', 'v') AND aclcontains(c.relacl, makeaclitem(grantee.oid, u_grantor.oid, pr.type, false)) - AND (pg_has_role(u_grantor.oid, 'MEMBER') - OR pg_has_role(grantee.oid, 'MEMBER') + 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.19 * COLUMN_UDT_USAGE view */ @@ -340,13 +527,13 @@ CREATE VIEW column_udt_usage AS AND a.atttypid = t.oid AND nc.oid = c.relnamespace AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v') - AND pg_has_role(coalesce(bt.typowner, t.typowner), 'MEMBER'); + AND pg_has_role(coalesce(bt.typowner, t.typowner), 'USAGE'); GRANT SELECT ON column_udt_usage TO PUBLIC; /* - * 20.18 + * 5.20 * COLUMNS view */ @@ -356,11 +543,7 @@ CREATE VIEW columns AS 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 pg_has_role(c.relowner, 'MEMBER') THEN pg_get_expr(ad.adbin, ad.adrelid) - 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 is_nullable, @@ -408,8 +591,8 @@ CREATE VIEW columns AS 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(null AS character_data) AS interval_type, -- FIXME + CAST(null AS character_data) AS interval_precision, -- FIXME CAST(null AS sql_identifier) AS character_set_catalog, CAST(null AS sql_identifier) AS character_set_schema, @@ -436,7 +619,21 @@ CREATE VIEW columns AS 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 + CAST('NO' AS character_data) AS is_self_referencing, + + CAST('NO' AS character_data) 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 character_data) 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' + THEN 'YES' ELSE 'NO' END AS character_data) AS is_updatable FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum), pg_class c, pg_namespace nc, @@ -450,7 +647,7 @@ CREATE VIEW columns AS AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v') - AND (pg_has_role(c.relowner, 'MEMBER') + AND (pg_has_role(c.relowner, 'USAGE') OR has_table_privilege(c.oid, 'SELECT') OR has_table_privilege(c.oid, 'INSERT') OR has_table_privilege(c.oid, 'UPDATE') @@ -460,7 +657,7 @@ GRANT SELECT ON columns TO PUBLIC; /* - * 20.19 + * 5.21 * CONSTRAINT_COLUMN_USAGE view */ @@ -506,13 +703,13 @@ CREATE VIEW constraint_column_usage AS ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname) - WHERE pg_has_role(x.tblowner, 'MEMBER'); + WHERE pg_has_role(x.tblowner, 'USAGE'); GRANT SELECT ON constraint_column_usage TO PUBLIC; /* - * 20.20 + * 5.22 * CONSTRAINT_TABLE_USAGE view */ @@ -531,16 +728,32 @@ CREATE VIEW constraint_table_usage AS 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 pg_has_role(r.relowner, 'MEMBER'); + AND pg_has_role(r.relowner, 'USAGE'); GRANT SELECT ON constraint_table_usage TO PUBLIC; --- 20.21 DATA_TYPE_PRIVILEGES view appears later. +-- 5.23 DATA_TYPE_PRIVILEGES view appears later. /* - * 20.24 + * 5.24 + * DIRECT_SUPERTABLES view + */ + +-- feature not supported + + +/* + * 5.25 + * DIRECT_SUPERTYPES view + */ + +-- feature not supported + + +/* + * 5.26 * DOMAIN_CONSTRAINTS view */ @@ -558,15 +771,14 @@ CREATE VIEW domain_constraints AS FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t WHERE rs.oid = con.connamespace AND n.oid = t.typnamespace - AND t.oid = con.contypid - AND pg_has_role(t.typowner, 'MEMBER'); + AND t.oid = con.contypid; GRANT SELECT ON domain_constraints TO PUBLIC; /* - * 20.25 * DOMAIN_UDT_USAGE view + * apparently removed in SQL:2003 */ CREATE VIEW domain_udt_usage AS @@ -584,13 +796,13 @@ CREATE VIEW domain_udt_usage AS AND t.typbasetype = bt.oid AND bt.typnamespace = nbt.oid AND t.typtype = 'd' - AND pg_has_role(bt.typowner, 'MEMBER'); + AND pg_has_role(bt.typowner, 'USAGE'); GRANT SELECT ON domain_udt_usage TO PUBLIC; /* - * 20.26 + * 5.27 * DOMAINS view */ @@ -644,8 +856,8 @@ CREATE VIEW domains AS 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(null AS character_data) AS interval_type, -- FIXME + CAST(null AS character_data) AS interval_precision, -- FIXME CAST(t.typdefault AS character_data) AS domain_default, @@ -671,24 +883,32 @@ CREATE VIEW domains AS GRANT SELECT ON domains TO PUBLIC; --- 20.27 ELEMENT_TYPES view appears later. +-- 5.28 ELEMENT_TYPES view appears later. /* - * 20.28 + * 5.29 * ENABLED_ROLES view */ CREATE VIEW enabled_roles AS SELECT CAST(a.rolname AS sql_identifier) AS role_name FROM pg_authid a - WHERE pg_has_role(a.oid, 'MEMBER'); + WHERE pg_has_role(a.oid, 'USAGE'); GRANT SELECT ON enabled_roles TO PUBLIC; /* - * 20.30 + * 5.30 + * FIELDS view + */ + +-- feature not supported + + +/* + * 5.31 * KEY_COLUMN_USAGE view */ @@ -700,8 +920,8 @@ CREATE VIEW key_column_usage AS 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((ss.x).n AS cardinal_number) AS ordinal_position - + CAST((ss.x).n AS cardinal_number) AS ordinal_position, + CAST(null AS cardinal_number) AS position_in_unique_constraint -- FIXME FROM pg_attribute a, (SELECT r.oid, nc.nspname AS nc_nspname, c.conname, nr.nspname AS nr_nspname, r.relname, @@ -713,7 +933,11 @@ CREATE VIEW key_column_usage AS AND nc.oid = c.connamespace AND c.contype IN ('p', 'u', 'f') AND r.relkind = 'r' - AND pg_has_role(r.relowner, 'MEMBER')) AS ss + AND (pg_has_role(r.relowner, 'USAGE') + 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')) ) AS ss WHERE ss.oid = a.attrelid AND a.attnum = (ss.x).x AND NOT a.attisdropped; @@ -722,7 +946,23 @@ GRANT SELECT ON key_column_usage TO PUBLIC; /* - * 20.33 + * 5.32 + * METHOD_SPECIFICATION_PARAMETERS view + */ + +-- feature not supported + + +/* + * 5.33 + * METHOD_SPECIFICATIONS view + */ + +-- feature not supported + + +/* + * 5.34 * PARAMETERS view */ @@ -774,7 +1014,7 @@ CREATE VIEW parameters AS _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, 'MEMBER') OR + 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; @@ -782,7 +1022,15 @@ GRANT SELECT ON parameters TO PUBLIC; /* - * 20.35 + * 5.35 + * REFERENCED_TYPES view + */ + +-- feature not supported + + +/* + * 5.36 * REFERENTIAL_CONSTRAINTS view */ @@ -831,13 +1079,13 @@ CREATE VIEW referential_constraints AS WHERE c.relkind = 'r' AND con.contype = 'f' AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL) - AND pg_has_role(c.relowner, 'MEMBER'); + AND pg_has_role(c.relowner, 'USAGE'); GRANT SELECT ON referential_constraints TO PUBLIC; /* - * 20.36 + * 5.37 * ROLE_COLUMN_GRANTS view */ @@ -871,13 +1119,14 @@ CREATE VIEW role_column_grants AS AND c.relkind IN ('r', 'v') AND aclcontains(c.relacl, makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false)) - AND g_grantee.rolname IN (SELECT role_name FROM enabled_roles); + AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles) + OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles)); GRANT SELECT ON role_column_grants TO PUBLIC; /* - * 20.37 + * 5.38 * ROLE_ROUTINE_GRANTS view */ @@ -904,13 +1153,14 @@ CREATE VIEW role_routine_grants AS WHERE p.pronamespace = n.oid AND aclcontains(p.proacl, makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', false)) - AND g_grantee.rolname IN (SELECT role_name FROM enabled_roles); + AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles) + OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles)); GRANT SELECT ON role_routine_grants TO PUBLIC; /* - * 20.38 + * 5.39 * ROLE_TABLE_GRANTS view */ @@ -943,13 +1193,22 @@ CREATE VIEW role_table_grants AS AND c.relkind IN ('r', 'v') AND aclcontains(c.relacl, makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false)) - AND g_grantee.rolname IN (SELECT role_name FROM enabled_roles); + AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles) + OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles)); GRANT SELECT ON role_table_grants TO PUBLIC; /* - * 20.40 + * 5.40 + * ROLE_TABLE_METHOD_GRANTS view + */ + +-- feature not supported + + +/* + * 5.41 * ROLE_USAGE_GRANTS view */ @@ -971,7 +1230,23 @@ GRANT SELECT ON role_usage_grants TO PUBLIC; /* - * 20.43 + * 5.42 + * ROLE_UDT_GRANTS view + */ + +-- feature not supported + + +/* + * 5.43 + * ROUTINE_COLUMN_USAGE view + */ + +-- not tracked by PostgreSQL + + +/* + * 5.44 * ROUTINE_PRIVILEGES view */ @@ -1002,15 +1277,39 @@ CREATE VIEW routine_privileges AS WHERE p.pronamespace = n.oid AND aclcontains(p.proacl, makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', false)) - AND (pg_has_role(u_grantor.oid, 'MEMBER') - OR pg_has_role(grantee.oid, 'MEMBER') + 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.45 + * ROUTINE_ROUTINE_USAGE view + */ + +-- not tracked by PostgreSQL + + +/* + * 5.46 + * ROUTINE_SEQUENCE_USAGE view + */ + +-- not tracked by PostgreSQL + + +/* + * 5.47 + * ROUTINE_TABLE_USAGE view + */ + +-- not tracked by PostgreSQL + + +/* + * 5.48 * ROUTINES view */ @@ -1060,7 +1359,7 @@ CREATE VIEW routines AS CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data) AS routine_body, CAST( - CASE WHEN pg_has_role(p.proowner, 'MEMBER') 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 @@ -1080,21 +1379,50 @@ CREATE VIEW routines AS 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 + CAST('NO' AS character_data) AS as_locator, + CAST(null AS time_stamp) AS created, + CAST(null AS time_stamp) AS last_altered, + CAST(null AS character_data) AS new_savepoint_level, + CAST('YES' AS character_data) AS is_udt_dependent, -- FIXME? + + CAST(null AS character_data) AS result_cast_from_data_type, + CAST(null AS character_data) 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 character_data) 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.prorettype = t.oid AND t.typnamespace = nt.oid - AND (pg_has_role(p.proowner, 'MEMBER') + AND (pg_has_role(p.proowner, 'USAGE') OR has_function_privilege(p.oid, 'EXECUTE')); GRANT SELECT ON routines TO PUBLIC; /* - * 20.46 + * 5.49 * SCHEMATA view */ @@ -1107,13 +1435,40 @@ CREATE VIEW schemata AS CAST(null AS sql_identifier) AS default_character_set_name, CAST(null AS character_data) AS sql_path FROM pg_namespace n, pg_authid u - WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'MEMBER'); + WHERE n.nspowner = u.oid AND pg_has_role(n.nspowner, 'USAGE'); GRANT SELECT ON schemata TO PUBLIC; /* - * 20.47 + * 5.50 + * 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(null AS cardinal_number) AS maximum_value, -- FIXME + CAST(null AS cardinal_number) AS minimum_value, -- FIXME + CAST(null AS cardinal_number) AS increment, -- FIXME + CAST(null AS character_data) AS cycle_option -- FIXME + FROM pg_namespace nc, pg_class c + WHERE c.relnamespace = nc.oid + AND c.relkind = 's' + AND (pg_has_role(c.relowner, 'USAGE') + OR has_table_privilege(c.oid, 'SELECT') + OR has_table_privilege(c.oid, 'UPDATE') ); + +GRANT SELECT ON sequences TO PUBLIC; + + +/* + * 5.51 * SQL_FEATURES table */ @@ -1133,11 +1488,11 @@ GRANT SELECT ON sql_features TO PUBLIC; /* - * 20.48 + * 5.52 * SQL_IMPLEMENTATION_INFO table */ --- Note: Implementation information items are defined in ISO 9075-3:1999, +-- Note: Implementation information items are defined in ISO/IEC 9075-3:2003, -- clause 7.1. CREATE TABLE sql_implementation_info ( @@ -1165,7 +1520,7 @@ GRANT SELECT ON sql_implementation_info TO PUBLIC; /* - * 20.49 + * 5.53 * SQL_LANGUAGES table */ @@ -1179,6 +1534,8 @@ CREATE TABLE sql_languages ( sql_language_programming_language character_data ) WITHOUT OIDS; +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'); @@ -1186,7 +1543,7 @@ GRANT SELECT ON sql_languages TO PUBLIC; /* - * 20.50 + * 5.54 * SQL_PACKAGES table */ @@ -1213,11 +1570,35 @@ GRANT SELECT ON sql_packages TO PUBLIC; /* - * 20.51 + * 5.55 + * SQL_PARTS table + */ + +CREATE TABLE sql_parts ( + feature_id character_data, + feature_name character_data, + is_supported character_data, + 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)', NULL, ''); +INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', NULL, ''); +INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', NULL, ''); +INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', NULL, ''); +INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', NULL, ''); +INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', NULL, ''); + + +/* + * 5.56 * 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:2003, clause 7.2. CREATE TABLE sql_sizing ( sizing_id cardinal_number, @@ -1259,7 +1640,7 @@ GRANT SELECT ON sql_sizing TO PUBLIC; /* - * 20.52 + * 5.57 * SQL_SIZING_PROFILES table */ @@ -1279,7 +1660,7 @@ GRANT SELECT ON sql_sizing_profiles TO PUBLIC; /* - * 20.53 + * 5.58 * TABLE_CONSTRAINTS view */ @@ -1309,15 +1690,61 @@ CREATE VIEW table_constraints AS WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace AND c.conrelid = r.oid AND r.relkind = 'r' - AND pg_has_role(r.relowner, 'MEMBER'); + AND (pg_has_role(r.relowner, 'USAGE') + -- SELECT privilege omitted, per SQL standard + OR has_table_privilege(r.oid, 'INSERT') + OR has_table_privilege(r.oid, 'UPDATE') + OR has_table_privilege(r.oid, 'DELETE') + OR has_table_privilege(r.oid, 'RULE') + OR has_table_privilege(r.oid, 'REFERENCES') + OR has_table_privilege(r.oid, 'TRIGGER') ) + + UNION + + -- not-null constraints + + SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, + CAST(nr.nspname AS sql_identifier) AS constraint_schema, + CAST(nr.oid || '_' || r.oid || '_' || a.attnum || '_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 character_data) AS is_deferrable, + CAST('NO' AS character_data) AS initially_deferred --- FIXME: Not-null constraints are missing here. + 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 (pg_has_role(r.relowner, 'USAGE') + OR has_table_privilege(r.oid, 'SELECT') + OR has_table_privilege(r.oid, 'INSERT') + OR has_table_privilege(r.oid, 'UPDATE') + OR has_table_privilege(r.oid, 'DELETE') + OR has_table_privilege(r.oid, 'RULE') + OR has_table_privilege(r.oid, 'REFERENCES') + OR has_table_privilege(r.oid, 'TRIGGER') ); GRANT SELECT ON table_constraints TO PUBLIC; /* - * 20.55 + * 5.59 + * TABLE_METHOD_PRIVILEGES view + */ + +-- feature not supported + + +/* + * 5.60 * TABLE_PRIVILEGES view */ @@ -1354,15 +1781,15 @@ CREATE VIEW table_privileges AS AND c.relkind IN ('r', 'v') AND aclcontains(c.relacl, makeaclitem(grantee.oid, u_grantor.oid, pr.type, false)) - AND (pg_has_role(u_grantor.oid, 'MEMBER') - OR pg_has_role(grantee.oid, 'MEMBER') + 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.61 * TABLES view */ @@ -1383,13 +1810,21 @@ CREATE VIEW tables AS 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(null AS sql_identifier) AS user_defined_type_name, + + CAST(CASE WHEN c.relkind = 'r' + THEN 'YES' ELSE 'NO' END AS character_data) AS is_insertable_into, + CAST('NO' AS character_data) AS is_typed, + CAST( + CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'PRESERVE' + ELSE null END + AS character_data) AS commit_action FROM pg_namespace nc, pg_class c WHERE c.relnamespace = nc.oid AND c.relkind IN ('r', 'v') - AND (pg_has_role(c.relowner, 'MEMBER') + AND (pg_has_role(c.relowner, 'USAGE') OR has_table_privilege(c.oid, 'SELECT') OR has_table_privilege(c.oid, 'INSERT') OR has_table_privilege(c.oid, 'UPDATE') @@ -1402,7 +1837,23 @@ GRANT SELECT ON tables TO PUBLIC; /* - * 20.59 + * 5.62 + * TRANSFORMS view + */ + +-- feature not supported + + +/* + * 5.63 + * TRANSLATIONS view + */ + +-- feature not supported + + +/* + * 5.64 * TRIGGERED_UPDATE_COLUMNS view */ @@ -1423,7 +1874,39 @@ GRANT SELECT ON triggered_update_columns TO PUBLIC; /* - * 20.62 + * 5.65 + * TRIGGER_COLUMN_USAGE view + */ + +-- not tracked by PostgreSQL + + +/* + * 5.66 + * TRIGGER_ROUTINE_USAGE view + */ + +-- not tracked by PostgreSQL + + +/* + * 5.67 + * TRIGGER_SEQUENCE_USAGE view + */ + +-- not tracked by PostgreSQL + + +/* + * 5.68 + * TRIGGER_TABLE_USAGE view + */ + +-- not tracked by PostgreSQL + + +/* + * 5.69 * TRIGGERS view */ @@ -1448,7 +1931,10 @@ CREATE VIEW triggers AS 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 + CAST(null AS sql_identifier) AS condition_reference_new_table, + CAST(null AS sql_identifier) AS condition_reference_old_row, + CAST(null AS sql_identifier) AS condition_reference_new_row, + CAST(null AS time_stamp) AS created FROM pg_namespace n, pg_class c, pg_trigger t, (SELECT 4, 'INSERT' UNION ALL @@ -1459,13 +1945,28 @@ CREATE VIEW triggers AS AND c.oid = t.tgrelid AND t.tgtype & em.num <> 0 AND NOT t.tgisconstraint - AND pg_has_role(c.relowner, 'MEMBER'); + AND (pg_has_role(c.relowner, 'USAGE') + -- SELECT privilege omitted, per SQL standard + 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') ); GRANT SELECT ON triggers TO PUBLIC; /* - * 20.63 + * 5.70 + * UDT_PRIVILEGES view + */ + +-- feature not supported + + +/* + * 5.71 * USAGE_PRIVILEGES view */ @@ -1495,7 +1996,15 @@ GRANT SELECT ON usage_privileges TO PUBLIC; /* - * 20.65 + * 5.72 + * USER_DEFINED_TYPES view + */ + +-- feature not supported + + +/* + * 5.73 * VIEW_COLUMN_USAGE */ @@ -1528,13 +2037,46 @@ CREATE VIEW view_column_usage AS AND t.relkind IN ('r', 'v') AND t.oid = a.attrelid AND dt.refobjsubid = a.attnum - AND pg_has_role(t.relowner, 'MEMBER'); + AND pg_has_role(t.relowner, 'USAGE'); GRANT SELECT ON view_column_usage TO PUBLIC; /* - * 20.66 + * 5.74 + * 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.75 * VIEW_TABLE_USAGE */ @@ -1563,13 +2105,13 @@ CREATE VIEW view_table_usage AS AND dt.refobjid = t.oid AND t.relnamespace = nt.oid AND t.relkind IN ('r', 'v') - AND pg_has_role(t.relowner, 'MEMBER'); + AND pg_has_role(t.relowner, 'USAGE'); GRANT SELECT ON view_table_usage TO PUBLIC; /* - * 20.68 + * 5.76 * VIEWS view */ @@ -1579,20 +2121,29 @@ CREATE VIEW views AS CAST(c.relname AS sql_identifier) AS table_name, CAST( - CASE WHEN pg_has_role(c.relowner, 'MEMBER') + 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 EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = 2 AND is_instead) + AND EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = 4 AND is_instead) + THEN 'YES' ELSE 'NO' END + AS character_data) AS is_updatable, + + CAST( + CASE WHEN EXISTS (SELECT 1 FROM pg_rewrite WHERE ev_class = c.oid AND ev_type = 3 AND is_instead) + THEN 'YES' ELSE 'NO' END + AS character_data) AS is_insertable_into FROM pg_namespace nc, pg_class c WHERE c.relnamespace = nc.oid AND c.relkind = 'v' - AND (pg_has_role(c.relowner, 'MEMBER') + AND (pg_has_role(c.relowner, 'USAGE') OR has_table_privilege(c.oid, 'SELECT') OR has_table_privilege(c.oid, 'INSERT') OR has_table_privilege(c.oid, 'UPDATE') @@ -1607,7 +2158,7 @@ GRANT SELECT ON views TO PUBLIC; -- The following views have dependencies that force them to appear out of order. /* - * 20.21 + * 5.23 * DATA_TYPE_PRIVILEGES view */ @@ -1620,6 +2171,8 @@ CREATE VIEW data_type_privileges AS 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 @@ -1633,7 +2186,7 @@ GRANT SELECT ON data_type_privileges TO PUBLIC; /* - * 20.27 + * 5.28 * ELEMENT_TYPES view */ @@ -1642,7 +2195,7 @@ CREATE VIEW element_types AS 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, |