v28-0006-Implemented-pg_publication_objects-view.patch

From 335cd5329546f62252914caa080f5f894a80e1fc Mon Sep 17 00:00:00 2001
From: Vignesh C <vignesh21@gmail.com>
Date: Tue, 31 Aug 2021 18:25:11 +0530
Subject: [PATCH v28 6/6] Implemented pg_publication_objects view.

Implemented pg_publication_objects view which displays "FOR TABLE" and
"FOR ALL TABLES IN SCHEMA" publications and the objects they contain.
---
 doc/src/sgml/catalogs.sgml           | 70 ++++++++++++++++++++++++++++
 src/backend/catalog/system_views.sql | 20 ++++++++
 src/test/regress/expected/rules.out  | 16 +++++++
 3 files changed, 106 insertions(+)

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index c18a90a691..38293cbdba 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -9501,6 +9501,11 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
       <entry>publications and their associated tables</entry>
      </row>
 
+     <row>
+      <entry><link linkend="view-pg-publication-objects"><structname>pg_publication_objects</structname></link></entry>
+      <entry>publications and their associated objects</entry>
+     </row>
+
      <row>
       <entry><link linkend="view-pg-replication-origin-status"><structname>pg_replication_origin_status</structname></link></entry>
       <entry>information about replication origins, including replication progress</entry>
@@ -11330,6 +11335,71 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
 
  </sect1>
 
+<sect1 id="view-pg-publication-objects">
+  <title><structname>pg_publication_objects</structname></title>
+
+  <indexterm zone="view-pg-publication-objects">
+   <primary>pg_publication_objects</primary>
+  </indexterm>
+
+  <para>
+   The view <structname>pg_publication_objects</structname> provides
+   information about the mapping between publications and the objects they
+   contain.  Unlike the underlying catalog
+   <link linkend="catalog-pg-publication-rel"><structname>pg_publication_rel</structname></link>,
+   this view expands publications defined as <literal>FOR TABLE</literal>
+   and <literal>FOR ALL TABLES IN SCHEMA</literal>, so for such publications
+   there will be a row for each eligible object.
+  </para>
+
+  <table>
+   <title><structname>pg_publication_objects</structname> Columns</title>
+   <tgroup cols="1">
+    <thead>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       Column Type
+      </para>
+      <para>
+       Description
+      </para></entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>pubname</structfield> <type>name</type>
+       (references <link linkend="catalog-pg-publication"><structname>pg_publication</structname></link>.<structfield>pubname</structfield>)
+      </para>
+      <para>
+       Name of publication
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>objname</structfield> <type>name</type>
+       (references <link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.<structfield>nspname</structfield> or <link linkend="catalog-pg-class"><structname>pg_class</structname></link>.<structfield>relname</structfield>)
+      </para>
+      <para>
+       Name of schema or Name of table
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>objtype</structfield> <type>name</type>
+      </para>
+      <para>
+       The object type: <literal>schema</literal> or <literal>table</literal>
+      </para></entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+ </sect1>
+ 
  <sect1 id="view-pg-publication-tables">
   <title><structname>pg_publication_tables</structname></title>
 
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 55f6e3711d..079148a364 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -362,6 +362,26 @@ CREATE VIEW pg_stats_ext_exprs WITH (security_barrier) AS
 -- unprivileged users may read pg_statistic_ext but not pg_statistic_ext_data
 REVOKE ALL ON pg_statistic_ext_data FROM public;
 
+CREATE VIEW pg_publication_objects AS
+SELECT
+    P.pubname,
+    N.nspname AS objname,
+    'schema'::text AS objtype
+FROM pg_catalog.pg_publication P
+    JOIN pg_catalog.pg_publication_namespace S ON P.oid = S.pnpubid
+    JOIN pg_catalog.pg_class C ON C.relnamespace = S.pnnspid
+    JOIN pg_catalog.pg_namespace N on N.oid = S.pnnspid
+UNION
+SELECT
+    P.pubname,
+    quote_ident(N.nspname) || '.' || quote_ident(C.relname) AS objname,
+    'table'::text AS objtype
+FROM pg_catalog.pg_publication P
+    JOIN pg_catalog.pg_publication_rel R ON P.oid = R.prpubid
+    JOIN pg_catalog.pg_class C ON C.oid = R.prrelid
+    JOIN pg_catalog.pg_namespace N ON N.oid = C.relnamespace
+ORDER BY pubname;
+
 CREATE VIEW pg_publication_tables AS
     SELECT
         P.pubname AS pubname,
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 2fa00a3c29..1af7c53abd 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1451,6 +1451,22 @@ pg_prepared_xacts| SELECT p.transaction,
    FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid)
      LEFT JOIN pg_authid u ON ((p.ownerid = u.oid)))
      LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
+pg_publication_objects| SELECT p.pubname,
+    n.nspname AS objname,
+    'schema'::text AS objtype
+   FROM (((pg_publication p
+     JOIN pg_publication_namespace s ON ((p.oid = s.pnpubid)))
+     JOIN pg_class c ON ((c.relnamespace = s.pnnspid)))
+     JOIN pg_namespace n ON ((n.oid = s.pnnspid)))
+UNION
+ SELECT p.pubname,
+    ((quote_ident((n.nspname)::text) || '.'::text) || quote_ident((c.relname)::text)) AS objname,
+    'table'::text AS objtype
+   FROM (((pg_publication p
+     JOIN pg_publication_rel r ON ((p.oid = r.prpubid)))
+     JOIN pg_class c ON ((c.oid = r.prrelid)))
+     JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
+  ORDER BY 1;
 pg_publication_tables| SELECT p.pubname,
     n.nspname AS schemaname,
     c.relname AS tablename
-- 
2.30.2