diff options
| -rw-r--r-- | doc/src/sgml/information_schema.sgml | 742 | ||||
| -rw-r--r-- | src/backend/catalog/information_schema.sql | 178 | ||||
| -rw-r--r-- | src/backend/catalog/sql_features.txt | 4 |
3 files changed, 918 insertions, 6 deletions
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index f866cf72789..82760ecdc44 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -1,4 +1,4 @@ -<!-- $Header: /cvsroot/pgsql/doc/src/sgml/information_schema.sgml,v 1.2 2003/05/25 09:36:09 petere Exp $ --> +<!-- $Header: /cvsroot/pgsql/doc/src/sgml/information_schema.sgml,v 1.3 2003/06/05 16:08:47 petere Exp $ --> <chapter id="information-schema"> <title>The Information Schema</title> @@ -956,8 +956,246 @@ <row> <entry><literal>dtd_identifier</literal></entry> <entry><type>sql_identifier</type></entry> + <entry> + A unique identifier of the data type of the domain (The + specific format of the identifier is not defined and not + guaranteed to remain the same in future versions.) + </entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + + <sect1 id="infoschema-parameters"> + <title><literal>parameters</literal></title> + + <para> + The view <literal>parameters</literal> contains information about + the parameters (arguments) all functions in the current database. + Only those functions are shown that the current user has access to + (by way of being the owner or having some privilege). + </para> + + <table> + <title><literal>parameters</literal> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Data Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>specific_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the database containing the function (always the current database)</entry> + </row> + + <row> + <entry><literal>specific_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the schema containing the function</entry> + </row> + + <row> + <entry><literal>specific_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + The <quote>specific name</quote> of the function. See <xref + linkend="infoschema-routines"> for more information. + </entry> + </row> + + <row> + <entry><literal>ordinal_position</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry> + Ordinal position of the parameter in the argument list of the + function (count starts at 1) + </entry> + </row> + + <row> + <entry><literal>parameter_mode</literal></entry> + <entry><type>character_data</type></entry> + <entry> + Always <literal>IN</literal>, meaning input parameter (In the + future there might be other parameter modes.) + </entry> + </row> + + <row> + <entry><literal>is_result</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>as_locator</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>parameter_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Always null, since PostgreSQL does not support named parameters</entry> + </row> + + <row> + <entry><literal>data_type</literal></entry> + <entry><type>character_data</type></entry> + <entry>Data type of the parameter</entry> + </row> + + <row> + <entry><literal>character_maximum_length</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Always null, since this information is not applied to parameter data types in PostgreSQL</entry> + </row> + + <row> + <entry><literal>character_octet_length</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Always null, since this information is not applied to parameter data types in PostgreSQL</entry> + </row> + + <row> + <entry><literal>character_set_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>character_set_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>character_set_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>collation_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>collation_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>collation_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>numeric_precision</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Always null, since this information is not applied to parameter data types in PostgreSQL</entry> + </row> + + <row> + <entry><literal>numeric_precision_radix</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Always null, since this information is not applied to parameter data types in PostgreSQL</entry> + </row> + + <row> + <entry><literal>numeric_scale</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Always null, since this information is not applied to parameter data types in PostgreSQL</entry> + </row> + + <row> + <entry><literal>datetime_precision</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Always null, since this information is not applied to parameter data types in PostgreSQL</entry> + </row> + + <row> + <entry><literal>interval_type</literal></entry> + <entry><type>character_data</type></entry> + <entry>Always null, since this information is not applied to parameter data types in PostgreSQL</entry> + </row> + + <row> + <entry><literal>interval_precision</literal></entry> + <entry><type>character_data</type></entry> + <entry>Always null, since this information is not applied to parameter data types in PostgreSQL</entry> + </row> + + <row> + <entry><literal>udt_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the database that the data type of the parameter is + defined in (always the current database) + </entry> + </row> + + <row> + <entry><literal>udt_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the schema that the data type of the parameter is + defined in + </entry> + </row> + + <row> + <entry><literal>udt_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the data type of the parameter + </entry> + </row> + + <row> + <entry><literal>scope_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>scope_schema</literal></entry> + <entry><type>sql_identifier</type></entry> <entry>Applies to a feature not available in PostgreSQL</entry> </row> + + <row> + <entry><literal>scope_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>maximum_cardinality</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>dtd_identifier</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + A unique identifier of the data type of the parameter (The + specific format of the identifier is not defined and not + guaranteed to remain the same in future versions.) + </entry> + </row> </tbody> </tgroup> </table> @@ -1067,6 +1305,508 @@ </table> </sect1> + <sect1 id="infoschema-routine-privileges"> + <title><literal>routine_privileges</literal></title> + + <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. + </para> + + <table> + <title><literal>routine_privileges</literal> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Data Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>grantor</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the user that granted the privileges</entry> + </row> + + <row> + <entry><literal>grantee</literal</entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the user that the privilege was granted to</entry> + </row> + + <row> + <entry><literal>specific_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the database containing the function (always the current database)</entry> + </row> + + <row> + <entry><literal>specific_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the schema containing the function</entry> + </row> + + <row> + <entry><literal>specific_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + The <quote>specific name</quote> of the function. See <xref + linkend="infoschema-routines"> for more information. + </entry> + </row> + + <row> + <entry><literal>routine_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the database containing the function (always the current database)</entry> + </row> + + <row> + <entry><literal>routine_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the schema containing the function</entry> + </row> + + <row> + <entry><literal>routine_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the function (may be duplicated in case of overloading)</entry> + </row> + + <row> + <entry><literal>privilege_type</literal</entry> + <entry><type>character_data</type></entry> + <entry>Always <literal>EXECUTE</literal> (the only privilege type for functions)</entry> + </row> + + <row> + <entry><literal>is_grantable</literal></entry> + <entry><type>character_data</type></entry> + <entry><literal>YES</literal> if the privilege is grantable, <literal>NO</literal> if not</entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + + <sect1 id="infoschema-routines"> + <title><literal>routines</literal></title> + + <para> + The view <literal>routines</literal> contains all functions in the + current database. Only those functions are shown that the current + user has access to (by way of being the owner or having some + privilege). + </para> + + <table> + <title><literal>routines</literal> Columns</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Name</entry> + <entry>Data Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><literal>specific_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the database containing the function (always the current database)</entry> + </row> + + <row> + <entry><literal>specific_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the schema containing the function</entry> + </row> + + <row> + <entry><literal>specific_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + The <quote>specific name</quote> of the function. This is a + name that uniquely identifies the function in the schema, even + if the real name of the function is overloaded. The format of + the specific name is not defined, it should only be used to + compare it to other instances of specific routine names. + </entry> + </row> + + <row> + <entry><literal>routine_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the database containing the function (always the current database)</entry> + </row> + + <row> + <entry><literal>routine_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the schema containing the function</entry> + </row> + + <row> + <entry><literal>routine_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Name of the function (may be duplicated in case of overloading)</entry> + </row> + + <row> + <entry><literal>routine_type</literal></entry> + <entry><type>character_data</type></entry> + <entry> + Always <literal>FUNCTION</literal> (In the future there might + be other types of routines.) + </entry> + </row> + + <row> + <entry><literal>module_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>module_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>module_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>udt_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>udt_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>udt_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>data_type</literal></entry> + <entry><type>character_data</type></entry> + <entry>Return data type of the function</entry> + </row> + + <row> + <entry><literal>character_maximum_length</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Always null, since this information is not applied to return data types in PostgreSQL</entry> + </row> + + <row> + <entry><literal>character_octet_length</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Always null, since this information is not applied to return data types in PostgreSQL</entry> + </row> + + <row> + <entry><literal>character_set_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>character_set_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>character_set_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>collation_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>collation_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>collation_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>numeric_precision</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Always null, since this information is not applied to return data types in PostgreSQL</entry> + </row> + + <row> + <entry><literal>numeric_precision_radix</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Always null, since this information is not applied to return data types in PostgreSQL</entry> + </row> + + <row> + <entry><literal>numeric_scale</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Always null, since this information is not applied to return data types in PostgreSQL</entry> + </row> + + <row> + <entry><literal>datetime_precision</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Always null, since this information is not applied to return data types in PostgreSQL</entry> + </row> + + <row> + <entry><literal>interval_type</literal></entry> + <entry><type>character_data</type></entry> + <entry>Always null, since this information is not applied to return data types in PostgreSQL</entry> + </row> + + <row> + <entry><literal>interval_precision</literal></entry> + <entry><type>character_data</type></entry> + <entry>Always null, since this information is not applied to return data types in PostgreSQL</entry> + </row> + + <row> + <entry><literal>type_udt_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the database that the return data type of the function + is defined in (always the current database) + </entry> + </row> + + <row> + <entry><literal>type_udt_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the schema that the return data type of the function is + defined in + </entry> + </row> + + <row> + <entry><literal>type_udt_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + Name of the return data type of the function + </entry> + </row> + + <row> + <entry><literal>scope_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>scope_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>scope_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>maximum_cardinality</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>dtd_identifier</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry> + A unique identifier of the return data type of the function + (The specific format of the identifier is not defined and not + guaranteed to remain the same in future versions.) + </entry> + </row> + + <row> + <entry><literal>routine_body</literal></entry> + <entry><type>character_data</type></entry> + <entry> + If the function is an SQL function, then + <literal>SQL</literal>, else <literal>EXTERNAL</literal>. + </entry> + </row> + + <row> + <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 + standard, this column is only applicable if + <literal>routine_body</literal> is <literal>SQL</literal>, but + in PostgreSQL it will contain whatever source text was + specified when the function was created.) + </entry> + </row> + + <row> + <entry><literal>external_name</literal></entry> + <entry><type>character_data</type></entry> + <entry> + If this function is a C function, then the external name (link + symbol) of the function; else null. (This works out to be the + same value that is shown in + <literal>routine_definition</literal>.) + </entry> + </row> + + <row> + <entry><literal>external_language</literal></entry> + <entry><type>character_data</type></entry> + <entry>The language the function is written in</entry> + </row> + + <row> + <entry><literal>parameter_style</literal></entry> + <entry><type>character_data</type></entry> + <entry> + Always <literal>GENERAL</literal> (The SQL standard defines + other parameter styles, which are not available in PostgreSQL.) + </entry> + </row> + + <row> + <entry><literal>is_deterministic</literal></entry> + <entry><type>character_data</type></entry> + <entry> + If the function is declared immutable (called deterministic in + the SQL standard), then <literal>YES</literal>, else + <literal>NO</literal>. (You cannot query the other volatility + levels available in PostgreSQL through the information schema.) + </entry> + </row> + + <row> + <entry><literal>sql_data_access</literal></entry> + <entry><type>character_data</type></entry> + <entry> + Always <literal>MODIFIES</literal>, meaning that the function + possibly modifies SQL data. This information is not useful for + PostgreSQL. + </entry> + </row> + + <row> + <entry><literal>is_null_call</literal></entry> + <entry><type>character_data</type></entry> + <entry> + If the function automatically returns null if any of its + arguments are null, then <literal>YES</literal>, else + <literal>NO</literal>. + </entry> + </row> + + <row> + <entry><literal>sql_path</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>schema_level_routine</literal></entry> + <entry><type>character_data</type></entry> + <entry> + Always <literal>YES</literal> (The opposite would be a method + of a user-defined type, which is a feature not available in + PostgreSQL.) + </entry> + </row> + + <row> + <entry><literal>max_dynamic_result_sets</literal></entry> + <entry><type>cardinal_number</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>is_user_defined_cast</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>is_implicitly_invocable</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>security_type</literal></entry> + <entry><type>character_data</type></entry> + <entry> + If the function runs with the privileges of the current user, + then <literal>INVOKER</literal>, if the function runs with the + privileges of the user who defined it, then + <literal>DEFINER</literal>. + </entry> + </row> + + <row> + <entry><literal>to_sql_specific_catalog</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>to_sql_specific_schema</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>to_sql_specific_name</literal></entry> + <entry><type>sql_identifier</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + + <row> + <entry><literal>as_locator</literal></entry> + <entry><type>character_data</type></entry> + <entry>Applies to a feature not available in PostgreSQL</entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> + <sect1 id="infoschema-schemata"> <title><literal>schemata</literal></title> diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 6dc3ac7b297..c1ca85ce8d7 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -4,7 +4,7 @@ * * Copyright 2002, PostgreSQL Global Development Group * - * $Id: information_schema.sql,v 1.6 2003/05/25 09:36:09 petere Exp $ + * $Id: information_schema.sql,v 1.7 2003/06/05 16:08:47 petere Exp $ */ @@ -272,7 +272,7 @@ CREATE VIEW columns AS CAST(null AS sql_identifier) AS scope_name, CAST(null AS cardinal_number) AS maximum_cardinality, - CAST(CAST(t.oid AS varchar) AS sql_identifier) AS dtd_identifier, + CAST(t.oid 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, @@ -474,7 +474,7 @@ CREATE VIEW domains AS CAST(null AS sql_identifier) AS scope_name, CAST(null AS cardinal_number) AS maximum_cardinality, - CAST(CAST(t.oid AS varchar) AS sql_identifier) AS dtd_identifier + CAST(t.oid AS sql_identifier) AS dtd_identifier FROM pg_type t, pg_namespace nt, pg_type bt, pg_namespace nbt @@ -488,6 +488,65 @@ GRANT SELECT ON domains TO PUBLIC; /* + * 20.33 + * 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(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name, + CAST(n + 1 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 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 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 character_data) 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_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(t.oid AS sql_identifier) AS dtd_identifier + + FROM pg_namespace n, pg_proc p, pg_type t, pg_namespace nt, pg_user u, + (select 0 union select 1 union select 2 union select 3 union select 4 union + select 5 union select 6 union select 7 union select 8 union select 9 union + select 10 union select 11 union select 12 union select 13 union select 14 union + select 15 union select 16 union select 17 union select 18 union select 19 union + select 20 union select 21 union select 22 union select 23 union select 24 union + select 25 union select 26 union select 27 union select 28 union select 29 union + select 30 union select 31) AS pos(n) + + WHERE n.oid = p.pronamespace AND p.pronargs > pos.n + AND p.proargtypes[n] = t.oid AND t.typnamespace = nt.oid + AND p.proowner = u.usesysid + AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE')); + +GRANT SELECT ON parameters TO PUBLIC; + + +/* * 20.35 * REFERENTIAL_CONSTRAINTS view */ @@ -540,6 +599,119 @@ GRANT SELECT ON referential_constraints TO PUBLIC; /* + * 20.43 + * ROUTINE_PRIVILEGES view + */ + +CREATE VIEW routine_privileges AS + SELECT CAST(u_owner.usename AS sql_identifier) AS grantor, + CAST(u_grantee.usename 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('NO' AS character_data) AS is_grantable + + FROM pg_user u_owner, + pg_user u_grantee, + pg_namespace n, + pg_proc p + + WHERE u_owner.usesysid = p.proowner + AND p.pronamespace = n.oid + AND has_function_privilege(u_grantee.usename, p.oid, 'EXECUTE') + AND (u_owner.usename = current_user OR u_grantee.usename = current_user); + +GRANT SELECT ON routine_privileges TO PUBLIC; + + +/* + * 20.45 + * ROUTINES view + */ + +CREATE VIEW routines 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(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('FUNCTION' AS character_data) AS routine_type, + CAST(null AS sql_identifier) AS module_catalog, + CAST(null AS sql_identifier) AS module_schema, + CAST(null AS sql_identifier) AS module_name, + CAST(null AS sql_identifier) AS udt_catalog, + CAST(null AS sql_identifier) AS udt_schema, + CAST(null AS sql_identifier) AS udt_name, + + CAST( + CASE 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 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 character_data) 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(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(t.oid AS sql_identifier) AS dtd_identifier, + + 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 + AS character_data) AS routine_definition, + CAST( + CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END + AS character_data) AS external_name, + 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('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(null AS character_data) AS sql_path, + CAST('YES' AS character_data) 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(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, + pg_type t, pg_namespace nt + + WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.proowner = u.usesysid + AND p.prorettype = t.oid AND t.typnamespace = nt.oid + AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE')); + +GRANT SELECT ON routines TO PUBLIC; + + +/* * 20.46 * SCHEMATA view */ diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 33c80918f58..3beaae1ecc0 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -335,8 +335,8 @@ T321 Basic SQL-invoked routines 02 User-defined stored procedures with no overlo T321 Basic SQL-invoked routines 03 Function invocation YES T321 Basic SQL-invoked routines 04 CALL statement NO T321 Basic SQL-invoked routines 05 RETURN statement NO -T321 Basic SQL-invoked routines 06 ROUTINES view NO -T321 Basic SQL-invoked routines 07 PARAMETERS view NO +T321 Basic SQL-invoked routines 06 ROUTINES view YES +T321 Basic SQL-invoked routines 07 PARAMETERS view YES T322 Overloading of SQL-invoked functions and procedures YES T323 Explicit security for external routines YES T331 Basic roles NO |