X-Git-Url: https://granicus.if.org/sourcecode?a=blobdiff_plain;f=src%2Fbackend%2Fcatalog%2Finformation_schema.sql;h=659d7cb5a49b465eb68c7c034fe067014f3fdadf;hb=ee943004466418595363d567f18c053bae407792;hp=230758654cc84eced4d3ee8cdf1d231f9566f58e;hpb=bd61a623ace3f3db8da1c7936416706968ae6ec2;p=postgresql diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 230758654c..659d7cb5a4 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -2,9 +2,17 @@ * SQL Information Schema * as defined in ISO/IEC 9075-11:2011 * - * Copyright (c) 2003-2013, PostgreSQL Global Development Group + * Copyright (c) 2003-2016, PostgreSQL Global Development Group * * src/backend/catalog/information_schema.sql + * + * Note: this file is read in single-user -j mode, which means that the + * command terminator is semicolon-newline-newline; whenever the backend + * sees that, it stops and executes what it's got. If you write a lot of + * statements without empty lines between, they'll all get quoted to you + * in any error message about one of them, so don't do that. Also, you + * cannot write a semicolon immediately followed by an empty line in a + * string literal (including a function body!) or a multiline comment. */ /* @@ -731,7 +739,8 @@ CREATE VIEW columns AS CAST(null AS character_data) AS generation_expression, CAST(CASE WHEN c.relkind = 'r' OR - (c.relkind = 'v' AND pg_view_is_updatable(c.oid)) + (c.relkind IN ('v', 'f') AND + pg_column_is_updatable(c.oid, a.attnum, false)) THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum) @@ -1132,10 +1141,15 @@ CREATE VIEW parameters AS 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((ss.x).n AS sql_identifier) AS dtd_identifier + CAST((ss.x).n AS sql_identifier) AS dtd_identifier, + CAST( + CASE WHEN pg_has_role(proowner, 'USAGE') + THEN pg_get_function_arg_default(p_oid, (ss.x).n) + ELSE NULL END + AS character_data) AS parameter_default FROM pg_type t, pg_namespace nt, - (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid, + (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid, p.proowner, p.proargnames, p.proargmodes, _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x FROM pg_namespace n, pg_proc p @@ -1501,7 +1515,9 @@ 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, 'USAGE'); + WHERE n.nspowner = u.oid + AND (pg_has_role(n.nspowner, 'USAGE') + OR has_schema_privilege(n.oid, 'CREATE, USAGE')); GRANT SELECT ON schemata TO PUBLIC; @@ -1895,7 +1911,9 @@ CREATE VIEW tables AS CAST(t.typname AS sql_identifier) AS user_defined_type_name, CAST(CASE WHEN c.relkind = 'r' OR - (c.relkind = 'v' AND pg_view_is_insertable(c.oid)) + (c.relkind IN ('v', 'f') AND + -- 1 << CMD_INSERT + pg_relation_is_updatable(c.oid, false) & 8 = 8) THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into, CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed, @@ -1918,7 +1936,39 @@ GRANT SELECT ON tables TO PUBLIC; * TRANSFORMS view */ --- feature not supported +CREATE VIEW transforms AS + SELECT CAST(current_database() AS sql_identifier) AS udt_catalog, + CAST(nt.nspname AS sql_identifier) AS udt_schema, + CAST(t.typname AS sql_identifier) AS udt_name, + CAST(current_database() AS sql_identifier) AS specific_catalog, + CAST(np.nspname AS sql_identifier) AS specific_schema, + CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name, + CAST(l.lanname AS sql_identifier) AS group_name, + CAST('FROM SQL' AS character_data) AS transform_type + FROM pg_type t JOIN pg_transform x ON t.oid = x.trftype + JOIN pg_language l ON x.trflang = l.oid + JOIN pg_proc p ON x.trffromsql = p.oid + JOIN pg_namespace nt ON t.typnamespace = nt.oid + JOIN pg_namespace np ON p.pronamespace = np.oid + + UNION + + SELECT CAST(current_database() AS sql_identifier) AS udt_catalog, + CAST(nt.nspname AS sql_identifier) AS udt_schema, + CAST(t.typname AS sql_identifier) AS udt_name, + CAST(current_database() AS sql_identifier) AS specific_catalog, + CAST(np.nspname AS sql_identifier) AS specific_schema, + CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name, + CAST(l.lanname AS sql_identifier) AS group_name, + CAST('TO SQL' AS character_data) AS transform_type + FROM pg_type t JOIN pg_transform x ON t.oid = x.trftype + JOIN pg_language l ON x.trflang = l.oid + JOIN pg_proc p ON x.trftosql = p.oid + JOIN pg_namespace nt ON t.typnamespace = nt.oid + JOIN pg_namespace np ON p.pronamespace = np.oid + + ORDER BY udt_catalog, udt_schema, udt_name, group_name, transform_type -- some sensible grouping for interactive use +; /* @@ -2491,14 +2541,24 @@ CREATE VIEW views AS ELSE null END AS character_data) AS view_definition, - CAST('NONE' AS character_data) AS check_option, + CAST( + CASE WHEN 'check_option=cascaded' = ANY (c.reloptions) + THEN 'CASCADED' + WHEN 'check_option=local' = ANY (c.reloptions) + THEN 'LOCAL' + ELSE 'NONE' END + AS character_data) AS check_option, CAST( - CASE WHEN pg_view_is_updatable(c.oid) THEN 'YES' ELSE 'NO' END + -- (1 << CMD_UPDATE) + (1 << CMD_DELETE) + CASE WHEN pg_relation_is_updatable(c.oid, false) & 20 = 20 + THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable, CAST( - CASE WHEN pg_view_is_insertable(c.oid) THEN 'YES' ELSE 'NO' END + -- 1 << CMD_INSERT + CASE WHEN pg_relation_is_updatable(c.oid, false) & 8 = 8 + THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into, CAST(