* SQL Information Schema
* as defined in ISO/IEC 9075-11:2011
*
- * Copyright (c) 2003-2012, 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.
*/
/*
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)
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
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;
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,
* 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
+;
/*
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(
projects / postgresql / blobdiff