+
+
+-- SQL/MED views; these use section numbers from part 9 of the standard.
+-- (still SQL:2008; there is no SQL:2011 SQL/MED)
+
+/* Base view for foreign table columns */
+CREATE VIEW _pg_foreign_table_columns AS
+ SELECT n.nspname,
+ c.relname,
+ a.attname,
+ a.attfdwoptions
+ FROM pg_foreign_table t, pg_authid u, pg_namespace n, pg_class c,
+ pg_attribute a
+ WHERE u.oid = c.relowner
+ AND (pg_has_role(c.relowner, 'USAGE')
+ OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES'))
+ AND n.oid = c.relnamespace
+ AND c.oid = t.ftrelid
+ AND c.relkind = 'f'
+ AND a.attrelid = c.oid
+ AND a.attnum > 0;
+
+/*
+ * 24.2
+ * COLUMN_OPTIONS view
+ */
+CREATE VIEW column_options AS
+ SELECT CAST(current_database() AS sql_identifier) AS table_catalog,
+ c.nspname AS table_schema,
+ c.relname AS table_name,
+ c.attname AS column_name,
+ CAST((pg_options_to_table(c.attfdwoptions)).option_name AS sql_identifier) AS option_name,
+ CAST((pg_options_to_table(c.attfdwoptions)).option_value AS character_data) AS option_value
+ FROM _pg_foreign_table_columns c;
+
+GRANT SELECT ON column_options TO PUBLIC;
+
+
+/* Base view for foreign-data wrappers */
+CREATE VIEW _pg_foreign_data_wrappers AS
+ SELECT w.oid,
+ w.fdwowner,
+ w.fdwoptions,
+ CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
+ CAST(fdwname AS sql_identifier) AS foreign_data_wrapper_name,
+ CAST(u.rolname AS sql_identifier) AS authorization_identifier,
+ CAST('c' AS character_data) AS foreign_data_wrapper_language
+ FROM pg_foreign_data_wrapper w, pg_authid u
+ WHERE u.oid = w.fdwowner
+ AND (pg_has_role(fdwowner, 'USAGE')
+ OR has_foreign_data_wrapper_privilege(w.oid, 'USAGE'));
+
+
+/*
+ * 24.4
+ * FOREIGN_DATA_WRAPPER_OPTIONS view
+ */
+CREATE VIEW foreign_data_wrapper_options AS
+ SELECT foreign_data_wrapper_catalog,
+ foreign_data_wrapper_name,
+ CAST((pg_options_to_table(w.fdwoptions)).option_name AS sql_identifier) AS option_name,
+ CAST((pg_options_to_table(w.fdwoptions)).option_value AS character_data) AS option_value
+ FROM _pg_foreign_data_wrappers w;
+
+GRANT SELECT ON foreign_data_wrapper_options TO PUBLIC;
+
+
+/*
+ * 24.5
+ * FOREIGN_DATA_WRAPPERS view
+ */
+CREATE VIEW foreign_data_wrappers AS
+ SELECT foreign_data_wrapper_catalog,
+ foreign_data_wrapper_name,
+ authorization_identifier,
+ CAST(NULL AS character_data) AS library_name,
+ foreign_data_wrapper_language
+ FROM _pg_foreign_data_wrappers w;
+
+GRANT SELECT ON foreign_data_wrappers TO PUBLIC;
+
+
+/* Base view for foreign servers */
+CREATE VIEW _pg_foreign_servers AS
+ SELECT s.oid,
+ s.srvoptions,
+ CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
+ CAST(srvname AS sql_identifier) AS foreign_server_name,
+ CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog,
+ CAST(w.fdwname AS sql_identifier) AS foreign_data_wrapper_name,
+ CAST(srvtype AS character_data) AS foreign_server_type,
+ CAST(srvversion AS character_data) AS foreign_server_version,
+ CAST(u.rolname AS sql_identifier) AS authorization_identifier
+ FROM pg_foreign_server s, pg_foreign_data_wrapper w, pg_authid u
+ WHERE w.oid = s.srvfdw
+ AND u.oid = s.srvowner
+ AND (pg_has_role(s.srvowner, 'USAGE')
+ OR has_server_privilege(s.oid, 'USAGE'));
+
+
+/*
+ * 24.6
+ * FOREIGN_SERVER_OPTIONS view
+ */
+CREATE VIEW foreign_server_options AS
+ SELECT foreign_server_catalog,
+ foreign_server_name,
+ CAST((pg_options_to_table(s.srvoptions)).option_name AS sql_identifier) AS option_name,
+ CAST((pg_options_to_table(s.srvoptions)).option_value AS character_data) AS option_value
+ FROM _pg_foreign_servers s;
+
+GRANT SELECT ON TABLE foreign_server_options TO PUBLIC;
+
+
+/*
+ * 24.7
+ * FOREIGN_SERVERS view
+ */
+CREATE VIEW foreign_servers AS
+ SELECT foreign_server_catalog,
+ foreign_server_name,
+ foreign_data_wrapper_catalog,
+ foreign_data_wrapper_name,
+ foreign_server_type,
+ foreign_server_version,
+ authorization_identifier
+ FROM _pg_foreign_servers;
+
+GRANT SELECT ON foreign_servers TO PUBLIC;
+
+
+/* Base view for foreign tables */
+CREATE VIEW _pg_foreign_tables AS
+ SELECT
+ CAST(current_database() AS sql_identifier) AS foreign_table_catalog,
+ n.nspname AS foreign_table_schema,
+ c.relname AS foreign_table_name,
+ t.ftoptions AS ftoptions,
+ CAST(current_database() AS sql_identifier) AS foreign_server_catalog,
+ CAST(srvname AS sql_identifier) AS foreign_server_name,
+ CAST(u.rolname AS sql_identifier) AS authorization_identifier
+ FROM pg_foreign_table t, pg_foreign_server s, pg_foreign_data_wrapper w,
+ pg_authid u, pg_namespace n, pg_class c
+ WHERE w.oid = s.srvfdw
+ AND u.oid = c.relowner
+ AND (pg_has_role(c.relowner, 'USAGE')
+ OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER')
+ OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES'))
+ AND n.oid = c.relnamespace
+ AND c.oid = t.ftrelid
+ AND c.relkind = 'f'
+ AND s.oid = t.ftserver;
+
+
+/*
+ * 24.8
+ * FOREIGN_TABLE_OPTIONS view
+ */
+CREATE VIEW foreign_table_options AS
+ SELECT foreign_table_catalog,
+ foreign_table_schema,
+ foreign_table_name,
+ CAST((pg_options_to_table(t.ftoptions)).option_name AS sql_identifier) AS option_name,
+ CAST((pg_options_to_table(t.ftoptions)).option_value AS character_data) AS option_value
+ FROM _pg_foreign_tables t;
+
+GRANT SELECT ON TABLE foreign_table_options TO PUBLIC;
+
+
+/*
+ * 24.9
+ * FOREIGN_TABLES view
+ */
+CREATE VIEW foreign_tables AS
+ SELECT foreign_table_catalog,
+ foreign_table_schema,
+ foreign_table_name,
+ foreign_server_catalog,
+ foreign_server_name
+ FROM _pg_foreign_tables;
+
+GRANT SELECT ON foreign_tables TO PUBLIC;
+
+
+
+/* Base view for user mappings */
+CREATE VIEW _pg_user_mappings AS
+ SELECT um.oid,
+ um.umoptions,
+ um.umuser,
+ CAST(COALESCE(u.rolname,'PUBLIC') AS sql_identifier ) AS authorization_identifier,
+ s.foreign_server_catalog,
+ s.foreign_server_name,
+ s.authorization_identifier AS srvowner
+ FROM pg_user_mapping um LEFT JOIN pg_authid u ON (u.oid = um.umuser),
+ _pg_foreign_servers s
+ WHERE s.oid = um.umserver;
+
+
+/*
+ * 24.12
+ * USER_MAPPING_OPTIONS view
+ */
+CREATE VIEW user_mapping_options AS
+ SELECT authorization_identifier,
+ foreign_server_catalog,
+ foreign_server_name,
+ CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name,
+ CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user)
+ OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE'))
+ OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) THEN (pg_options_to_table(um.umoptions)).option_value
+ ELSE NULL END AS character_data) AS option_value
+ FROM _pg_user_mappings um;
+
+GRANT SELECT ON user_mapping_options TO PUBLIC;
+
+
+/*
+ * 24.13
+ * USER_MAPPINGS view
+ */
+CREATE VIEW user_mappings AS
+ SELECT authorization_identifier,
+ foreign_server_catalog,
+ foreign_server_name
+ FROM _pg_user_mappings;
+
+GRANT SELECT ON user_mappings TO PUBLIC;