agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
Subject: [PATCH v3 1/1] Introduce a new function pg_get_publication_ddl() t=
Date: Sun, 5 Oct 2025 18:11:56 +0200
The function accepts a publication name or OID indistinctively,
providing an easy way to call the function as a user from
inside the Postgres code, or any SQL statement.
Comprehensive regression tests are included covering various
possible situations for a given publication.
Reviewed-by: H=C3=BCseyin Demir <huseyin.d3r@gmail.com>
Reviewed-by: Cary Huang <cary.huang@highgo.ca>
Reviewed-by: Peter Smith <smithpb2250@gmail.com>
Reviewed-by: Man Zeng <zengman@halodbtech.com>
Reviewed-by: Japin Li <japinli@hotmail.com>
Reviewed-by: Solai v <solai.cdac@gmail.com>
Signed-off-by: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
---
doc/src/sgml/func/func-info.sgml | 32 +
src/backend/utils/adt/ddlutils.c | 381 ++++++++-
src/include/catalog/pg_proc.dat | 16 +
src/test/regress/expected/publication_ddl.out | 747 ++++++++++++++++++
src/test/regress/parallel_schedule | 5 +-
src/test/regress/sql/publication_ddl.sql | 239 ++++++
6 files changed, 1418 insertions(+), 2 deletions(-)
create mode 100644 src/test/regress/expected/publication_ddl.out
create mode 100644 src/test/regress/sql/publication_ddl.sql
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info=
.sgml
index 00f64f50ceb..9d570b50822 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3961,6 +3961,38 @@ acl | {postgres=3DarwdDxtm/postgres,foo=3Dr/pos=
tgres}
<literal>TABLESPACE</literal>.
</para></entry>
</row>
+ <row>
+ <entry role=3D"func_table_entry"><para role=3D"func_signature">
+ <indexterm>
+ <primary>pg_get_publication_ddl</primary>
+ </indexterm>
+ <function>pg_get_publication_ddl</function>
+ ( <parameter>publication</parameter> <type>oid</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parame=
ter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para role=3D"func_signature">
+ <function>pg_get_publication_ddl</function>
+ ( <parameter>publication</parameter> <type>text</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parame=
ter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <link linkend=3D"sql-createpublication"><command>=
CREATE PUBLICATION</command></link> statement for
+ the specified publication (by OID or name), followed by an
+ <command>ALTER PUBLICATION ... OWNER TO</command> statement (the
+ <command>CREATE PUBLICATION</command> grammar has no
+ <literal>OWNER</literal> clause). Each statement is returned as a
+ separate row. An error is raised if no publication with the suppl=
ied
+ OID or name exists.
+ The following options are supported:
+ <literal>pretty</literal> (boolean) for formatted output and
+ <literal>owner</literal> (boolean) to include
+ <literal>OWNER</literal>.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlut=
ils.c
index f32fcd453ef..2994f7a21f2 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -20,12 +20,14 @@
=20
#include "access/genam.h"
#include "access/htup_details.h"
+#include "access/relation.h"
#include "access/table.h"
#include "catalog/pg_auth_members.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_collation.h"
#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
+#include "catalog/pg_publication_rel.h"
#include "catalog/pg_tablespace.h"
#include "commands/tablespace.h"
#include "common/relpath.h"
@@ -86,7 +88,10 @@ static List *pg_get_tablespace_ddl_internal(Oid tsid, bo=
ol pretty, bool no_owner
static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, =
bool isnull);
static List *pg_get_database_ddl_internal(Oid dbid, bool pretty,
bool no_owner, bool no_tablespace);
-
+static Datum pg_get_publication_ddl_srf(FunctionCallInfo fcinfo,
+ Oid puboid, bool isnull);
+static List *pg_get_publication_ddl_internal(Oid puboid, bool pretty,
+ bool no_owner);
=20
/*
* parse_ddl_options
@@ -1185,3 +1190,377 @@ pg_get_database_ddl(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funcctx);
}
}
+
+/*
+ * pg_get_publication_ddl_srf - common SRF logic for publication DDL
+ */
+static Datum
+pg_get_publication_ddl_srf(FunctionCallInfo fcinfo, Oid puboid, bool isnul=
l)
+{
+ FuncCallContext *funcctx;
+ List *statements;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ DdlOption opts[] =3D {
+ {"pretty", DDL_OPT_BOOL},
+ {"owner", DDL_OPT_BOOL},
+ };
+
+ funcctx =3D SRF_FIRSTCALL_INIT();
+ oldcontext =3D MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ if (isnull)
+ {
+ MemoryContextSwitchTo(oldcontext);
+ SRF_RETURN_DONE(funcctx);
+ }
+
+ parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
+
+ statements =3D pg_get_publication_ddl_internal(puboid,
+ opts[0].isset && opts[0].boolval,
+ opts[1].isset && !opts[1].boolval);
+
+ funcctx->user_fctx =3D statements;
+ funcctx->max_calls =3D list_length(statements);
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx =3D SRF_PERCALL_SETUP();
+ statements =3D (List *) funcctx->user_fctx;
+
+ if (funcctx->call_cntr < funcctx->max_calls)
+ {
+ char *stmt;
+
+ stmt =3D (char *) list_nth(statements, funcctx->call_cntr);
+
+ SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
+ }
+ else
+ {
+ list_free_deep(statements);
+ SRF_RETURN_DONE(funcctx);
+ }
+}
+
+/*
+ * pg_get_publication_ddl_oid
+ * Return DDL to recreate a publication, taking OID.
+ */
+Datum
+pg_get_publication_ddl_oid(PG_FUNCTION_ARGS)
+{
+ Oid puboid =3D InvalidOid;
+ bool isnull;
+
+ isnull =3D PG_ARGISNULL(0);
+ if (!isnull)
+ puboid =3D PG_GETARG_OID(0);
+
+ return pg_get_publication_ddl_srf(fcinfo, puboid, isnull);
+}
+
+/*
+ * pg_get_publication_ddl_name
+ * Return DDL to recreate a publication, taking name.
+ */
+Datum
+pg_get_publication_ddl_name(PG_FUNCTION_ARGS)
+{
+ Oid puboid =3D InvalidOid;
+ bool isnull;
+
+ isnull =3D PG_ARGISNULL(0);
+ if (!isnull)
+ {
+ char *pubname =3D text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+ puboid =3D get_publication_oid(pubname, false);
+ pfree(pubname);
+ }
+
+ return pg_get_publication_ddl_srf(fcinfo, puboid, isnull);
+}
+
+/*
+ * pg_get_publication_ddl_internal
+ * Common code for pg_get_publication_ddl_oid and
+ * pg_get_publication_ddl_name.
+ *
+ * Returns a List of palloc'd strings. The first element is the
+ * CREATE PUBLICATION statement; if no_owner is false a second element
+ * carries an ALTER PUBLICATION ... OWNER TO statement (the CREATE
+ * PUBLICATION grammar has no OWNER clause, so ownership must be applied
+ * as a follow-on statement).
+ */
+static List *
+pg_get_publication_ddl_internal(Oid puboid, bool pretty, bool no_owner)
+{
+ Publication *pub;
+ StringInfoData buf;
+ List *statements =3D NIL;
+ List *pub_incl_relids =3D NIL;
+ List *pub_excl_relids =3D NIL;
+ List *pub_schemas =3D NIL;
+ bool first_perm =3D true;
+
+ if (!SearchSysCacheExists1(PUBLICATIONOID, ObjectIdGetDatum(puboid)))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("publication with OID %u does not exist", puboid)));
+
+ pub =3D GetPublication(puboid);
+
+ initStringInfo(&buf);
+
+ appendStringInfo(&buf, "CREATE PUBLICATION %s", quote_identifier(pub->nam=
e));
+
+ /*
+ * Having all tables or all sequences means that there are no per-table
+ * publications
+ */
+ if (pub->alltables || pub->allsequences)
+ {
+ append_ddl_option(&buf, pretty, 4, "FOR ");
+
+ if (pub->alltables)
+ {
+ appendStringInfoString(&buf, "ALL TABLES");
+ pub_excl_relids =3D GetExcludedPublicationTables(
+ pub->oid, PUBLICATION_PART_ROOT);
+
+ if (pub_excl_relids !=3D NIL)
+ {
+ ListCell *excl_cell;
+ char *schemaname =3D NULL;
+
+ appendStringInfoString(&buf, " EXCEPT (TABLE ");
+
+ foreach(excl_cell, pub_excl_relids)
+ {
+ HeapTuple tp =3D SearchSysCache1(RELOID, ObjectIdGetDatum(excl_cell->=
oid_value));
+ Form_pg_class reltup;
+
+ if (!HeapTupleIsValid(tp))
+ elog(ERROR, "cache lookup failed for relation %u", excl_cell->oid_va=
lue);
+
+ reltup =3D (Form_pg_class) GETSTRUCT(tp);
+ schemaname =3D get_namespace_name(reltup->relnamespace);
+
+ appendStringInfo(&buf, "%s%s",
+ foreach_current_index(excl_cell) > 0 ? ", " : "",
+ quote_qualified_identifier(schemaname, NameStr(reltup->relname))=
);
+
+ pfree(schemaname);
+ ReleaseSysCache(tp);
+ }
+
+ appendStringInfoChar(&buf, ')');
+ }
+ }
+ if (pub->allsequences)
+ appendStringInfo(&buf,
+ "%sALL SEQUENCES",
+ pub->alltables ? ", " : "");
+ }
+ else
+ {
+ pub_incl_relids =3D GetIncludedPublicationRelations(pub->oid, PUBLICATIO=
N_PART_ROOT);
+ pub_schemas =3D GetPublicationSchemas(pub->oid);
+ }
+
+ /*
+ * Publication can have table relations
+ */
+ if (pub_incl_relids !=3D NIL)
+ {
+ ListCell *pub_cell;
+ char *schemaname =3D NULL;
+ char *tablename;
+
+ append_ddl_option(&buf, pretty, 4, "FOR TABLE ");
+
+ foreach(pub_cell, pub_incl_relids)
+ {
+ HeapTuple pubtuple =3D NULL;
+ HeapTuple reltup;
+ Form_pg_class relform;
+ Datum columns,
+ rowfilter;
+ Oid relid =3D pub_cell->oid_value;
+ bool cols_nulls,
+ condition_nulls;
+
+ reltup =3D SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(reltup))
+ elog(ERROR,
+ "cache lookup failed for relation %u",
+ relid);
+
+ relform =3D (Form_pg_class) GETSTRUCT(reltup);
+ tablename =3D NameStr(relform->relname);
+ schemaname =3D get_namespace_name(relform->relnamespace);
+
+ appendStringInfo(&buf, "%s%s",
+ foreach_current_index(pub_cell) > 0 ? ", " : "",
+ quote_qualified_identifier(schemaname, tablename));
+
+ pfree(schemaname);
+
+ pubtuple =3D SearchSysCache2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pub->oid));
+
+ if (!HeapTupleIsValid(pubtuple))
+ elog(ERROR,
+ "cache lookup failed for relation %u in publication %u",
+ relid, pub->oid);
+
+ columns =3D SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
+ Anum_pg_publication_rel_prattrs,
+ &cols_nulls);
+
+ rowfilter =3D SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
+ Anum_pg_publication_rel_prqual,
+ &condition_nulls);
+
+ /* If non-null, we have a list of columns to publish */
+ if (!cols_nulls)
+ {
+ Bitmapset *attmap;
+ int attnum =3D -1;
+
+ attmap =3D pub_collist_to_bitmapset(NULL, columns, NULL);
+
+ appendStringInfoChar(&buf, '(');
+ while ((attnum =3D bms_next_member(attmap, attnum)) >=3D 0)
+ {
+ appendStringInfo(&buf, "%s%s",
+ bms_member_index(attmap, attnum) ? ", " : "",
+ quote_identifier(get_attname(relid, attnum, false)));
+ }
+ appendStringInfoChar(&buf, ')');
+
+ bms_free(attmap);
+ }
+
+ /*
+ * If there is a condition it goes after the columns. We can have
+ * conditions without columns as well.
+ */
+ if (!condition_nulls)
+ {
+ Node *node;
+ List *context;
+ char *str;
+
+ node =3D stringToNode(TextDatumGetCString(rowfilter));
+ context =3D deparse_context_for(tablename, relid);
+ str =3D deparse_expression(node, context, false, false);
+ appendStringInfo(&buf, " WHERE (%s)", str);
+ }
+
+ ReleaseSysCache(pubtuple);
+ ReleaseSysCache(reltup);
+ }
+ }
+
+ /* If we have schemas, they will go right before the EXCEPT and/or WITH */
+ if (pub_schemas !=3D NIL)
+ {
+ ListCell *schema_cell;
+
+ /*
+ * Schemas can be preceded by a list of tables. When they are, the
+ * "TABLES IN SCHEMA" stays inline as a continuation of the existing
+ * FOR clause; otherwise it starts the FOR clause on its own line in
+ * pretty mode.
+ */
+ if (pub_incl_relids =3D=3D NIL)
+ append_ddl_option(&buf, pretty, 4, "FOR TABLES IN SCHEMA");
+ else
+ appendStringInfoString(&buf, ", TABLES IN SCHEMA");
+
+ foreach(schema_cell, pub_schemas)
+ {
+ char *nspname =3D get_namespace_name(schema_cell->oid_value);
+
+ appendStringInfo(&buf, "%s %s",
+ foreach_current_index(schema_cell) > 0 ? "," : "",
+ quote_identifier(nspname));
+ pfree(nspname);
+ }
+ }
+
+ /* Always add the WITH options */
+ append_ddl_option(&buf, pretty, 4, "WITH (");
+
+ /* Publish string */
+ appendStringInfoString(&buf, "publish=3D'");
+
+
+ if (pub->pubactions.pubinsert)
+ {
+ /*
+ * By precedence we know that the insert will always be first, no need
+ * to check previous values
+ */
+ appendStringInfoString(&buf, "insert");
+ first_perm =3D false;
+ }
+ if (pub->pubactions.pubupdate)
+ {
+ appendStringInfo(&buf, "%supdate", first_perm ? "" : ", ");
+ first_perm =3D false;
+ }
+ if (pub->pubactions.pubdelete)
+ {
+ appendStringInfo(&buf, "%sdelete", first_perm ? "" : ", ");
+ first_perm =3D false;
+ }
+ if (pub->pubactions.pubtruncate)
+ {
+ appendStringInfo(&buf, "%struncate", first_perm ? "" : ", ");
+ }
+
+ appendStringInfoString(&buf, "', ");
+
+ /* publish_generated_columns string */
+ appendStringInfo(&buf, "publish_generated_columns=3D%s, ",
+ pub->pubgencols_type =3D=3D PUBLISH_GENCOLS_NONE ? "none" : "stored"=
);
+
+ /* publish_via_partition_root value */
+ appendStringInfo(&buf, "publish_via_partition_root=3D%s)",
+ pub->pubviaroot ? "true" : "false");
+
+ appendStringInfoChar(&buf, ';');
+ statements =3D lappend(statements, pstrdup(buf.data));
+ pfree(buf.data);
+
+ /* OWNER */
+ if (!no_owner)
+ {
+ HeapTuple tup;
+ Form_pg_publication pubform;
+ char *owner;
+
+ tup =3D SearchSysCache1(PUBLICATIONOID, ObjectIdGetDatum(puboid));
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for publication %u", puboid);
+ pubform =3D (Form_pg_publication) GETSTRUCT(tup);
+ owner =3D GetUserNameFromId(pubform->pubowner, false);
+ ReleaseSysCache(tup);
+
+ initStringInfo(&buf);
+ appendStringInfo(&buf, "ALTER PUBLICATION %s OWNER TO %s;",
+ quote_identifier(pub->name), quote_identifier(owner));
+ statements =3D lappend(statements, pstrdup(buf.data));
+ pfree(buf.data);
+ pfree(owner);
+ }
+
+ return statements;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.=
dat
index be157a5fbe9..2579b530e28 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12317,6 +12317,22 @@
proname =3D> 'pg_relation_is_publishable', provolatile =3D> 's',
prorettype =3D> 'bool', proargtypes =3D> 'regclass',
prosrc =3D> 'pg_relation_is_publishable' },
+{ oid =3D> '6122', descr =3D> 'get DDL to recreate a publication',
+ proname =3D> 'pg_get_publication_ddl', provariadic =3D> 'text', proisstr=
ict =3D> 'f',
+ provolatile =3D> 's', proretset =3D> 't', prorows =3D> '2', prorettype =
=3D> 'text',
+ proargtypes =3D> 'oid text',
+ proargmodes =3D> '{i,v}',
+ proallargtypes =3D> '{oid,text}',
+ pronargdefaults =3D> '1', proargdefaults =3D> '{NULL}',
+ prosrc =3D> 'pg_get_publication_ddl_oid' },
+{ oid =3D> '6123', descr =3D> 'get DDL to recreate a publication',
+ proname =3D> 'pg_get_publication_ddl', provariadic =3D> 'text', proisstr=
ict =3D> 'f',
+ provolatile =3D> 's', proretset =3D> 't', prorows =3D> '2', prorettype =
=3D> 'text',
+ proargtypes =3D> 'text text',
+ proargmodes =3D> '{i,v}',
+ proallargtypes =3D> '{text,text}',
+ pronargdefaults =3D> '1', proargdefaults =3D> '{NULL}',
+ prosrc =3D> 'pg_get_publication_ddl_name' },
=20
# rls
{ oid =3D> '3298',
diff --git a/src/test/regress/expected/publication_ddl.out b/src/test/regre=
ss/expected/publication_ddl.out
new file mode 100644
index 00000000000..4e76624ae39
--- /dev/null
+++ b/src/test/regress/expected/publication_ddl.out
@@ -0,0 +1,747 @@
+--
+-- Test for DDL statement from:
+-- - pg_get_publication_ddl
+--
+-- suppress warning that depends on wal_level
+SET client_min_messages =3D 'ERROR';
+-- Run the body under a stable role so the ALTER PUBLICATION ... OWNER TO
+-- output is deterministic across environments.
+CREATE ROLE regress_publication_ddl_user LOGIN SUPERUSER;
+SET SESSION AUTHORIZATION 'regress_publication_ddl_user';
+-- test with a non-existing publication
+SELECT pg_get_publication_ddl('non-existing');
+ERROR: publication "non-existing" does not exist
+SELECT pg_get_publication_ddl(0::oid);
+ERROR: publication with OID 0 does not exist
+-- empty publication is possible and allowed
+CREATE PUBLICATION testpub_ddl_1;
+SELECT pg_get_publication_ddl('testpub_ddl_1');
+ pg_get_pub=
lication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--
+ CREATE PUBLICATION testpub_ddl_1 WITH (publish=3D'insert, update, delete,=
truncate', publish_generated_columns=3Dnone, publish_via_partition_root=3D=
false);
+ ALTER PUBLICATION testpub_ddl_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_1'));
+ pg_get_pub=
lication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--
+ CREATE PUBLICATION testpub_ddl_1 WITH (publish=3D'insert, update, delete,=
truncate', publish_generated_columns=3Dnone, publish_via_partition_root=3D=
false);
+ ALTER PUBLICATION testpub_ddl_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_1', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_1 =
+
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- NULL input should produce an empty result set
+SELECT count(*) =3D 0 AS is_null FROM pg_get_publication_ddl(NULL::oid);
+ is_null=20
+---------
+ t
+(1 row)
+
+SELECT count(*) =3D 0 AS is_null FROM pg_get_publication_ddl(NULL::text);
+ is_null=20
+---------
+ t
+(1 row)
+
+-- create set of tables for publications
+CREATE TABLE testpub_ddl_tbl1 (foo int, bar int);
+CREATE TABLE testpub_ddl_tbl2 (foo int, bar int);
+CREATE TABLE testpub_ddl_tbl3 (foo int, bar int, beque int, baz int);
+CREATE TABLE testpub_ddl_tbl4 (foo int, bar int, beque bool);
+CREATE TABLE testpub_ddl_tbl5 (foo int, "bar beque" int);
+CREATE PUBLICATION testpub_ddl_2 FOR TABLE testpub_ddl_tbl1, testpub_ddl_t=
bl2, testpub_ddl_tbl3 WITH (publish=3D'delete', publish_generated_columns=
=3D'stored', publish_via_partition_root=3D'true');
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, publi=
c.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish=3D'delete', publi=
sh_generated_columns=3Dstored, publish_via_partition_root=3Dtrue);
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_2'));
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, publi=
c.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish=3D'delete', publi=
sh_generated_columns=3Dstored, publish_via_partition_root=3Dtrue);
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_2', 'pretty', 'true');
+ pg_get_publication_ddl=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
-----------------------
+ CREATE PUBLICATION testpub_ddl_2 =
+
+ FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.te=
stpub_ddl_tbl3 +
+ WITH (publish=3D'delete', publish_generated_columns=3Dstored, publish=
_via_partition_root=3Dtrue);
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+ALTER PUBLICATION testpub_ddl_2 SET (publish =3D 'delete, update');
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, publi=
c.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish=3D'update, delete=
', publish_generated_columns=3Dstored, publish_via_partition_root=3Dtrue);
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_2'));
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, publi=
c.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish=3D'update, delete=
', publish_generated_columns=3Dstored, publish_via_partition_root=3Dtrue);
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_2', 'pretty', 'true');
+ pg_get_publication_ddl=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
-------------------------------
+ CREATE PUBLICATION testpub_ddl_2 =
+
+ FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.te=
stpub_ddl_tbl3 +
+ WITH (publish=3D'update, delete', publish_generated_columns=3Dstored,=
publish_via_partition_root=3Dtrue);
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for one table
+CREATE PUBLICATION testpub_ddl_3 FOR TABLE ONLY testpub_ddl_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_3');
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
------------------------------------
+ CREATE PUBLICATION testpub_ddl_3 FOR TABLE public.testpub_ddl_tbl1 WITH (=
publish=3D'insert, update, delete, truncate', publish_generated_columns=3Dn=
one, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_3'));
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
------------------------------------
+ CREATE PUBLICATION testpub_ddl_3 FOR TABLE public.testpub_ddl_tbl1 WITH (=
publish=3D'insert, update, delete, truncate', publish_generated_columns=3Dn=
one, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_3', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_3 =
+
+ FOR TABLE public.testpub_ddl_tbl1 =
+
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for one table with two columns and a rowfilter
+CREATE PUBLICATION testpub_ddl_4 FOR TABLE ONLY testpub_ddl_tbl3 (bar,baz)=
WHERE (bar =3D baz);
+SELECT pg_get_publication_ddl('testpub_ddl_4');
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_4 FOR TABLE public.testpub_ddl_tbl3(bar, b=
az) WHERE ((bar =3D baz)) WITH (publish=3D'insert, update, delete, truncate=
', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_4'));
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_4 FOR TABLE public.testpub_ddl_tbl3(bar, b=
az) WHERE ((bar =3D baz)) WITH (publish=3D'insert, update, delete, truncate=
', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_4', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_4 =
+
+ FOR TABLE public.testpub_ddl_tbl3(bar, baz) WHERE ((bar =3D baz)) =
+
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for one table with two columns and a rowfilter
+CREATE PUBLICATION testpub_ddl_5 FOR TABLE ONLY testpub_ddl_tbl4 (bar,bequ=
e) WHERE (beque IS TRUE);
+SELECT pg_get_publication_ddl('testpub_ddl_5');
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_5 FOR TABLE public.testpub_ddl_tbl4(bar, b=
eque) WHERE ((beque IS TRUE)) WITH (publish=3D'insert, update, delete, trun=
cate', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse=
);
+ ALTER PUBLICATION testpub_ddl_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_5'));
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_5 FOR TABLE public.testpub_ddl_tbl4(bar, b=
eque) WHERE ((beque IS TRUE)) WITH (publish=3D'insert, update, delete, trun=
cate', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse=
);
+ ALTER PUBLICATION testpub_ddl_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_5', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_5 =
+
+ FOR TABLE public.testpub_ddl_tbl4(bar, beque) WHERE ((beque IS TRUE))=
+
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for all tables
+CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES;
+SELECT pg_get_publication_ddl('testpub_ddl_6');
+ pg=
_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------
+ CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES WITH (publish=3D'insert, =
update, delete, truncate', publish_generated_columns=3Dnone, publish_via_pa=
rtition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_6 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_6'));
+ pg=
_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------
+ CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES WITH (publish=3D'insert, =
update, delete, truncate', publish_generated_columns=3Dnone, publish_via_pa=
rtition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_6 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_6', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_6 =
+
+ FOR ALL TABLES =
+
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_6 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for all sequences
+CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES;
+SELECT pg_get_publication_ddl('testpub_ddl_7');
+ p=
g_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------
+ CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES WITH (publish=3D'inser=
t, update, delete, truncate', publish_generated_columns=3Dnone, publish_via=
_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_7 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_7'));
+ p=
g_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------
+ CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES WITH (publish=3D'inser=
t, update, delete, truncate', publish_generated_columns=3Dnone, publish_via=
_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_7 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_7', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_7 =
+
+ FOR ALL SEQUENCES =
+
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_7 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for all tables and all sequences
+CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES;
+SELECT pg_get_publication_ddl('testpub_ddl_8');
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------------
+ CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES WITH (publ=
ish=3D'insert, update, delete, truncate', publish_generated_columns=3Dnone,=
publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_8 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_8'));
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------------
+ CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES WITH (publ=
ish=3D'insert, update, delete, truncate', publish_generated_columns=3Dnone,=
publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_8 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_8', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_8 =
+
+ FOR ALL TABLES, ALL SEQUENCES =
+
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_8 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- explicit publish_generated_columns=3D'none'
+CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish_generated_co=
lumns=3D'none');
+SELECT pg_get_publication_ddl('testpub_ddl_9');
+ pg=
_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------
+ CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish=3D'insert, =
update, delete, truncate', publish_generated_columns=3Dnone, publish_via_pa=
rtition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_9 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_9'));
+ pg=
_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------
+ CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish=3D'insert, =
update, delete, truncate', publish_generated_columns=3Dnone, publish_via_pa=
rtition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_9 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_9', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_9 =
+
+ FOR ALL TABLES =
+
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_9 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create a publication with a bare bolean in the row filter
+CREATE PUBLICATION testpub_ddl_10 FOR TABLE testpub_ddl_tbl4 WHERE (beque);
+SELECT pg_get_publication_ddl('testpub_ddl_10');
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_10 FOR TABLE public.testpub_ddl_tbl4 WHERE=
(beque) WITH (publish=3D'insert, update, delete, truncate', publish_genera=
ted_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_10 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_10'));
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_10 FOR TABLE public.testpub_ddl_tbl4 WHERE=
(beque) WITH (publish=3D'insert, update, delete, truncate', publish_genera=
ted_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_10 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_10', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_10 =
+
+ FOR TABLE public.testpub_ddl_tbl4 WHERE (beque) =
+
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_10 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create schema for schema publication
+CREATE SCHEMA pub_schema_test_ddl;
+CREATE TABLE pub_schema_test_ddl.schema_tbl1 (foo int, bar int);
+CREATE TABLE pub_schema_test_ddl.schema_tbl2 (foo int, bar int);
+CREATE TABLE pub_schema_test_ddl.schema_tbl3 (foo int, bar int, baz int);
+-- create a publication for a list of tables and schema
+CREATE PUBLICATION testpub_ddl_schema_1 FOR TABLE pub_schema_test_ddl.sche=
ma_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test_=
ddl;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_1');
+ =
pg_get_publication_ddl=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_1 FOR TABLE pub_schema_test_ddl.sch=
ema_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test=
_ddl WITH (publish=3D'insert, update, delete, truncate', publish_generated_=
columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_schema_1'));
+ =
pg_get_publication_ddl=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_1 FOR TABLE pub_schema_test_ddl.sch=
ema_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test=
_ddl WITH (publish=3D'insert, update, delete, truncate', publish_generated_=
columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_1', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_1 =
+
+ FOR TABLE pub_schema_test_ddl.schema_tbl1, pub_schema_test_ddl.schema=
_tbl2, TABLES IN SCHEMA pub_schema_test_ddl +
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+-- create publication in schema only for table
+CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLES IN SCHEMA pub_schema_te=
st_ddl, TABLE pub_schema_test_ddl.schema_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_2');
+ =
pg_get_publication_ddl=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------
+ CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLE pub_schema_test_ddl.sch=
ema_tbl1, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish=3D'insert, upd=
ate, delete, truncate', publish_generated_columns=3Dnone, publish_via_parti=
tion_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_schema_2'));
+ =
pg_get_publication_ddl=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------
+ CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLE pub_schema_test_ddl.sch=
ema_tbl1, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish=3D'insert, upd=
ate, delete, truncate', publish_generated_columns=3Dnone, publish_via_parti=
tion_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_2', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_2 =
+
+ FOR TABLE pub_schema_test_ddl.schema_tbl1, TABLES IN SCHEMA pub_schem=
a_test_ddl +
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+-- create publication for all tables in schema
+CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_te=
st_ddl;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_3');
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_t=
est_ddl WITH (publish=3D'insert, update, delete, truncate', publish_generat=
ed_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_schema_3'));
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_t=
est_ddl WITH (publish=3D'insert, update, delete, truncate', publish_generat=
ed_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_3', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_3 =
+
+ FOR TABLES IN SCHEMA pub_schema_test_ddl =
+
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+-- a new schema for multiple schemas
+CREATE SCHEMA pub_schema_test_ddl_2;
+CREATE TABLE pub_schema_test_ddl_2.schema_tbl1 (foo int, bar int);
+-- create a publication for a list of schemas
+CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_te=
st_ddl, pub_schema_test_ddl_2;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_4');
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_t=
est_ddl, pub_schema_test_ddl_2 WITH (publish=3D'insert, update, delete, tru=
ncate', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfals=
e);
+ ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_schema_4'));
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_t=
est_ddl, pub_schema_test_ddl_2 WITH (publish=3D'insert, update, delete, tru=
ncate', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfals=
e);
+ ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_4', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_4 =
+
+ FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2 =
+
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+-- create a publication for a specific schema and a table in public schema
+-- both with the same name
+CREATE TABLE schema_tbl1 (foo int, bar int);
+CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.sch=
ema_tbl1, schema_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_5');
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.sch=
ema_tbl1, public.schema_tbl1 WITH (publish=3D'insert, update, delete, trunc=
ate', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_schema_5'));
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.sch=
ema_tbl1, public.schema_tbl1 WITH (publish=3D'insert, update, delete, trunc=
ate', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_5', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_5 =
+
+ FOR TABLE pub_schema_test_ddl.schema_tbl1, public.schema_tbl1 =
+
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+-- create tables for partition test
+CREATE TABLE testpub_ddl_part (foo int, bar int) PARTITION BY RANGE (foo);
+CREATE TABLE testpub_ddl_part_p1 PARTITION OF testpub_ddl_part FOR VALUES =
FROM (0) TO (10);
+CREATE TABLE testpub_ddl_part_p2 PARTITION OF testpub_ddl_part FOR VALUES =
FROM (10) TO (20);
+CREATE PUBLICATION testpub_ddl_part1 FOR TABLE testpub_ddl_part;
+SELECT pg_get_publication_ddl('testpub_ddl_part1');
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
----------------------------------------
+ CREATE PUBLICATION testpub_ddl_part1 FOR TABLE public.testpub_ddl_part WI=
TH (publish=3D'insert, update, delete, truncate', publish_generated_columns=
=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_part1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_part1'));
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
----------------------------------------
+ CREATE PUBLICATION testpub_ddl_part1 FOR TABLE public.testpub_ddl_part WI=
TH (publish=3D'insert, update, delete, truncate', publish_generated_columns=
=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_part1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_part1', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part1 =
+
+ FOR TABLE public.testpub_ddl_part =
+
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_part1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+CREATE PUBLICATION testpub_ddl_part2 FOR TABLE testpub_ddl_part WITH (publ=
ish_via_partition_root=3D'false');
+SELECT pg_get_publication_ddl('testpub_ddl_part2');
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
----------------------------------------
+ CREATE PUBLICATION testpub_ddl_part2 FOR TABLE public.testpub_ddl_part WI=
TH (publish=3D'insert, update, delete, truncate', publish_generated_columns=
=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_part2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_part2'));
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
----------------------------------------
+ CREATE PUBLICATION testpub_ddl_part2 FOR TABLE public.testpub_ddl_part WI=
TH (publish=3D'insert, update, delete, truncate', publish_generated_columns=
=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_part2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_part2', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part2 =
+
+ FOR TABLE public.testpub_ddl_part =
+
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_part2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+CREATE PUBLICATION testpub_ddl_part3 FOR TABLE testpub_ddl_part WITH (publ=
ish_via_partition_root=3D'true');
+SELECT pg_get_publication_ddl('testpub_ddl_part3');
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------
+ CREATE PUBLICATION testpub_ddl_part3 FOR TABLE public.testpub_ddl_part WI=
TH (publish=3D'insert, update, delete, truncate', publish_generated_columns=
=3Dnone, publish_via_partition_root=3Dtrue);
+ ALTER PUBLICATION testpub_ddl_part3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_part3'));
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------
+ CREATE PUBLICATION testpub_ddl_part3 FOR TABLE public.testpub_ddl_part WI=
TH (publish=3D'insert, update, delete, truncate', publish_generated_columns=
=3Dnone, publish_via_partition_root=3Dtrue);
+ ALTER PUBLICATION testpub_ddl_part3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_part3', 'pretty', 'true');
+ pg_get_publication_ddl=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
-----------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part3 =
+
+ FOR TABLE public.testpub_ddl_part =
+
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dtrue);
+ ALTER PUBLICATION testpub_ddl_part3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+CREATE PUBLICATION testpub_ddl_part4 FOR TABLE testpub_ddl_part_p1;
+SELECT pg_get_publication_ddl('testpub_ddl_part4');
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part4 FOR TABLE public.testpub_ddl_part_p1=
WITH (publish=3D'insert, update, delete, truncate', publish_generated_colu=
mns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_part4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_part4'));
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part4 FOR TABLE public.testpub_ddl_part_p1=
WITH (publish=3D'insert, update, delete, truncate', publish_generated_colu=
mns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_part4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_part4', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part4 =
+
+ FOR TABLE public.testpub_ddl_part_p1 =
+
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_part4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- identifiers that require quoting: publication, schema, table and column
+CREATE SCHEMA "Pub Schema";
+CREATE TABLE "Pub Schema"."Quoted Table" ("Col One" int, "select" int);
+CREATE PUBLICATION "testpub Quoted Pub" FOR TABLE "Pub Schema"."Quoted Tab=
le" ("Col One", "select") WHERE ("Col One" > 0);
+SELECT pg_get_publication_ddl('testpub Quoted Pub');
+ =
pg_get_publication_ddl=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------
+ CREATE PUBLICATION "testpub Quoted Pub" FOR TABLE "Pub Schema"."Quoted Ta=
ble"("Col One", "select") WHERE (("Col One" > 0)) WITH (publish=3D'insert, =
update, delete, truncate', publish_generated_columns=3Dnone, publish_via_pa=
rtition_root=3Dfalse);
+ ALTER PUBLICATION "testpub Quoted Pub" OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub Quoted Pub'));
+ =
pg_get_publication_ddl=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------
+ CREATE PUBLICATION "testpub Quoted Pub" FOR TABLE "Pub Schema"."Quoted Ta=
ble"("Col One", "select") WHERE (("Col One" > 0)) WITH (publish=3D'insert, =
update, delete, truncate', publish_generated_columns=3Dnone, publish_via_pa=
rtition_root=3Dfalse);
+ ALTER PUBLICATION "testpub Quoted Pub" OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub Quoted Pub', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION "testpub Quoted Pub" =
+
+ FOR TABLE "Pub Schema"."Quoted Table"("Col One", "select") WHERE (("C=
ol One" > 0)) +
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION "testpub Quoted Pub" OWNER TO regress_publication_ddl_u=
ser;
+(2 rows)
+
+-- tables for EXCEPT
+CREATE TABLE testpub_ddl_except1 (foo int, bar int);
+CREATE TABLE testpub_ddl_except2 (foo int, bar int);
+-- create publication for all tables except one
+CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE testpu=
b_ddl_except1);
+SELECT pg_get_publication_ddl('testpub_ddl_except1');
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE publi=
c.testpub_ddl_except1) WITH (publish=3D'insert, update, delete, truncate', =
publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_us=
er;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_except1'));
+ =
pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-----------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE publi=
c.testpub_ddl_except1) WITH (publish=3D'insert, update, delete, truncate', =
publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_us=
er;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_except1', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except1 =
+
+ FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1) =
+
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_us=
er;
+(2 rows)
+
+-- create publication for all sequences and all tables except two tables
+CREATE PUBLICATION testpub_ddl_except2 FOR ALL SEQUENCES, ALL TABLES EXCEP=
T (TABLE testpub_ddl_except1, testpub_ddl_except2);
+SELECT pg_get_publication_ddl('testpub_ddl_except2');
+ =
pg_get_publication_ddl=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------
+ CREATE PUBLICATION testpub_ddl_except2 FOR ALL TABLES EXCEPT (TABLE publi=
c.testpub_ddl_except1, public.testpub_ddl_except2), ALL SEQUENCES WITH (pub=
lish=3D'insert, update, delete, truncate', publish_generated_columns=3Dnone=
, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_us=
er;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_except2'));
+ =
pg_get_publication_ddl=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
---------------------------------
+ CREATE PUBLICATION testpub_ddl_except2 FOR ALL TABLES EXCEPT (TABLE publi=
c.testpub_ddl_except1, public.testpub_ddl_except2), ALL SEQUENCES WITH (pub=
lish=3D'insert, update, delete, truncate', publish_generated_columns=3Dnone=
, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_us=
er;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_except2', 'pretty', 'true');
+ pg_get_publication_ddl=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
+--------------------------------------------------------------------------=
------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except2 =
+
+ FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1, public.testp=
ub_ddl_except2), ALL SEQUENCES +
+ WITH (publish=3D'insert, update, delete, truncate', publish_generated=
_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_us=
er;
+(2 rows)
+
+-- get all the created publications DDL into the table
+CREATE TEMP TABLE pub_ddl AS
+SELECT p.pubname, t.n, t.stmt
+FROM pg_publication p,
+LATERAL pg_get_publication_ddl(p.pubname) WITH ORDINALITY AS t(stmt, n)
+WHERE p.pubname LIKE 'testpub%';
+-- drop the publications to be recreated
+SELECT format('DROP PUBLICATION %I', pubname)
+FROM (SELECT DISTINCT pubname FROM pub_ddl) ORDER BY pubname \gexec
+DROP PUBLICATION "testpub Quoted Pub"
+DROP PUBLICATION testpub_ddl_1
+DROP PUBLICATION testpub_ddl_10
+DROP PUBLICATION testpub_ddl_2
+DROP PUBLICATION testpub_ddl_3
+DROP PUBLICATION testpub_ddl_4
+DROP PUBLICATION testpub_ddl_5
+DROP PUBLICATION testpub_ddl_6
+DROP PUBLICATION testpub_ddl_7
+DROP PUBLICATION testpub_ddl_8
+DROP PUBLICATION testpub_ddl_9
+DROP PUBLICATION testpub_ddl_except1
+DROP PUBLICATION testpub_ddl_except2
+DROP PUBLICATION testpub_ddl_part1
+DROP PUBLICATION testpub_ddl_part2
+DROP PUBLICATION testpub_ddl_part3
+DROP PUBLICATION testpub_ddl_part4
+DROP PUBLICATION testpub_ddl_schema_1
+DROP PUBLICATION testpub_ddl_schema_2
+DROP PUBLICATION testpub_ddl_schema_3
+DROP PUBLICATION testpub_ddl_schema_4
+DROP PUBLICATION testpub_ddl_schema_5
+-- recreate all the publications using the ddl from pg_get_publication_ddl=
()
+SELECT stmt FROM pub_ddl ORDER BY pubname, n \gexec
+CREATE PUBLICATION "testpub Quoted Pub" FOR TABLE "Pub Schema"."Quoted Tab=
le"("Col One", "select") WHERE (("Col One" > 0)) WITH (publish=3D'insert, u=
pdate, delete, truncate', publish_generated_columns=3Dnone, publish_via_par=
tition_root=3Dfalse);
+ALTER PUBLICATION "testpub Quoted Pub" OWNER TO regress_publication_ddl_us=
er;
+CREATE PUBLICATION testpub_ddl_1 WITH (publish=3D'insert, update, delete, =
truncate', publish_generated_columns=3Dnone, publish_via_partition_root=3Df=
alse);
+ALTER PUBLICATION testpub_ddl_1 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_10 FOR TABLE public.testpub_ddl_tbl4 WHERE =
(beque) WITH (publish=3D'insert, update, delete, truncate', publish_generat=
ed_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ALTER PUBLICATION testpub_ddl_10 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, public=
.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish=3D'update, delete'=
, publish_generated_columns=3Dstored, publish_via_partition_root=3Dtrue);
+ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_3 FOR TABLE public.testpub_ddl_tbl1 WITH (p=
ublish=3D'insert, update, delete, truncate', publish_generated_columns=3Dno=
ne, publish_via_partition_root=3Dfalse);
+ALTER PUBLICATION testpub_ddl_3 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_4 FOR TABLE public.testpub_ddl_tbl3(bar, ba=
z) WHERE ((bar =3D baz)) WITH (publish=3D'insert, update, delete, truncate'=
, publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ALTER PUBLICATION testpub_ddl_4 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_5 FOR TABLE public.testpub_ddl_tbl4(bar, be=
que) WHERE ((beque IS TRUE)) WITH (publish=3D'insert, update, delete, trunc=
ate', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ALTER PUBLICATION testpub_ddl_5 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES WITH (publish=3D'insert, u=
pdate, delete, truncate', publish_generated_columns=3Dnone, publish_via_par=
tition_root=3Dfalse);
+ALTER PUBLICATION testpub_ddl_6 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES WITH (publish=3D'insert=
, update, delete, truncate', publish_generated_columns=3Dnone, publish_via_=
partition_root=3Dfalse);
+ALTER PUBLICATION testpub_ddl_7 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES WITH (publi=
sh=3D'insert, update, delete, truncate', publish_generated_columns=3Dnone, =
publish_via_partition_root=3Dfalse);
+ALTER PUBLICATION testpub_ddl_8 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish=3D'insert, u=
pdate, delete, truncate', publish_generated_columns=3Dnone, publish_via_par=
tition_root=3Dfalse);
+ALTER PUBLICATION testpub_ddl_9 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE public=
.testpub_ddl_except1) WITH (publish=3D'insert, update, delete, truncate', p=
ublish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_use=
r;
+CREATE PUBLICATION testpub_ddl_except2 FOR ALL TABLES EXCEPT (TABLE public=
.testpub_ddl_except1, public.testpub_ddl_except2), ALL SEQUENCES WITH (publ=
ish=3D'insert, update, delete, truncate', publish_generated_columns=3Dnone,=
publish_via_partition_root=3Dfalse);
+ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_use=
r;
+CREATE PUBLICATION testpub_ddl_part1 FOR TABLE public.testpub_ddl_part WIT=
H (publish=3D'insert, update, delete, truncate', publish_generated_columns=
=3Dnone, publish_via_partition_root=3Dfalse);
+ALTER PUBLICATION testpub_ddl_part1 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_part2 FOR TABLE public.testpub_ddl_part WIT=
H (publish=3D'insert, update, delete, truncate', publish_generated_columns=
=3Dnone, publish_via_partition_root=3Dfalse);
+ALTER PUBLICATION testpub_ddl_part2 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_part3 FOR TABLE public.testpub_ddl_part WIT=
H (publish=3D'insert, update, delete, truncate', publish_generated_columns=
=3Dnone, publish_via_partition_root=3Dtrue);
+ALTER PUBLICATION testpub_ddl_part3 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_part4 FOR TABLE public.testpub_ddl_part_p1 =
WITH (publish=3D'insert, update, delete, truncate', publish_generated_colum=
ns=3Dnone, publish_via_partition_root=3Dfalse);
+ALTER PUBLICATION testpub_ddl_part4 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_schema_1 FOR TABLE pub_schema_test_ddl.sche=
ma_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test_=
ddl WITH (publish=3D'insert, update, delete, truncate', publish_generated_c=
olumns=3Dnone, publish_via_partition_root=3Dfalse);
+ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_us=
er;
+CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLE pub_schema_test_ddl.sche=
ma_tbl1, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish=3D'insert, upda=
te, delete, truncate', publish_generated_columns=3Dnone, publish_via_partit=
ion_root=3Dfalse);
+ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_us=
er;
+CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_te=
st_ddl WITH (publish=3D'insert, update, delete, truncate', publish_generate=
d_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_us=
er;
+CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_te=
st_ddl, pub_schema_test_ddl_2 WITH (publish=3D'insert, update, delete, trun=
cate', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse=
);
+ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_us=
er;
+CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.sche=
ma_tbl1, public.schema_tbl1 WITH (publish=3D'insert, update, delete, trunca=
te', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse);
+ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_us=
er;
+-- cleanup publications
+SELECT format('DROP PUBLICATION %I', pubname)
+FROM (SELECT DISTINCT pubname FROM pub_ddl) ORDER BY pubname \gexec
+DROP PUBLICATION "testpub Quoted Pub"
+DROP PUBLICATION testpub_ddl_1
+DROP PUBLICATION testpub_ddl_10
+DROP PUBLICATION testpub_ddl_2
+DROP PUBLICATION testpub_ddl_3
+DROP PUBLICATION testpub_ddl_4
+DROP PUBLICATION testpub_ddl_5
+DROP PUBLICATION testpub_ddl_6
+DROP PUBLICATION testpub_ddl_7
+DROP PUBLICATION testpub_ddl_8
+DROP PUBLICATION testpub_ddl_9
+DROP PUBLICATION testpub_ddl_except1
+DROP PUBLICATION testpub_ddl_except2
+DROP PUBLICATION testpub_ddl_part1
+DROP PUBLICATION testpub_ddl_part2
+DROP PUBLICATION testpub_ddl_part3
+DROP PUBLICATION testpub_ddl_part4
+DROP PUBLICATION testpub_ddl_schema_1
+DROP PUBLICATION testpub_ddl_schema_2
+DROP PUBLICATION testpub_ddl_schema_3
+DROP PUBLICATION testpub_ddl_schema_4
+DROP PUBLICATION testpub_ddl_schema_5
+-- cleanup tables
+DROP TABLE testpub_ddl_tbl1;
+DROP TABLE testpub_ddl_tbl2;
+DROP TABLE testpub_ddl_tbl3;
+DROP TABLE testpub_ddl_tbl4;
+DROP TABLE testpub_ddl_tbl5;
+DROP TABLE pub_ddl;
+--- cleanup tables for schema tests
+DROP TABLE schema_tbl1;
+-- cleanup tables for partitions
+DROP TABLE testpub_ddl_part;
+-- cleanup tables for quoted names
+DROP TABLE "Pub Schema"."Quoted Table";
+-- cleanup tables for except
+DROP TABLE testpub_ddl_except1;
+DROP TABLE testpub_ddl_except2;
+-- cleanup schemas
+DROP SCHEMA pub_schema_test_ddl CASCADE;
+DROP SCHEMA pub_schema_test_ddl_2 CASCADE;
+DROP SCHEMA "Pub Schema";
+-- cleanup role
+RESET SESSION AUTHORIZATION;
+DROP ROLE regress_publication_ddl_user;
+RESET client_min_messages;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel=
_schedule
index 8fa0a6c47fb..3d3a6c57af5 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -135,7 +135,6 @@ test: compression compression_lz4 compression_pglz clus=
ter
# oidjoins is read-only, though, and should run late for best coverage
test: oidjoins event_trigger
=20
-
# event_trigger_login cannot run concurrently with any other tests because
# on-login event handling could catch connection of a concurrent test.
test: event_trigger_login
@@ -143,6 +142,10 @@ test: event_trigger_login
# this test also uses event triggers, so likewise run it by itself
test: fast_default
=20
+# run retail DDL tests last to avoid object name collisions and
+# interference with previous tests.
+test: publication_ddl
+
# run tablespace test at the end because it drops the tablespace created d=
uring
# setup that other tests may use.
test: tablespace
diff --git a/src/test/regress/sql/publication_ddl.sql b/src/test/regress/sq=
l/publication_ddl.sql
new file mode 100644
index 00000000000..3543447e2c5
--- /dev/null
+++ b/src/test/regress/sql/publication_ddl.sql
@@ -0,0 +1,239 @@
+--
+-- Test for DDL statement from:
+-- - pg_get_publication_ddl
+--
+
+-- suppress warning that depends on wal_level
+SET client_min_messages =3D 'ERROR';
+
+-- Run the body under a stable role so the ALTER PUBLICATION ... OWNER TO
+-- output is deterministic across environments.
+CREATE ROLE regress_publication_ddl_user LOGIN SUPERUSER;
+SET SESSION AUTHORIZATION 'regress_publication_ddl_user';
+
+-- test with a non-existing publication
+SELECT pg_get_publication_ddl('non-existing');
+SELECT pg_get_publication_ddl(0::oid);
+
+-- empty publication is possible and allowed
+CREATE PUBLICATION testpub_ddl_1;
+SELECT pg_get_publication_ddl('testpub_ddl_1');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_1'));
+SELECT pg_get_publication_ddl('testpub_ddl_1', 'pretty', 'true');
+
+-- NULL input should produce an empty result set
+SELECT count(*) =3D 0 AS is_null FROM pg_get_publication_ddl(NULL::oid);
+SELECT count(*) =3D 0 AS is_null FROM pg_get_publication_ddl(NULL::text);
+
+-- create set of tables for publications
+CREATE TABLE testpub_ddl_tbl1 (foo int, bar int);
+CREATE TABLE testpub_ddl_tbl2 (foo int, bar int);
+CREATE TABLE testpub_ddl_tbl3 (foo int, bar int, beque int, baz int);
+CREATE TABLE testpub_ddl_tbl4 (foo int, bar int, beque bool);
+CREATE TABLE testpub_ddl_tbl5 (foo int, "bar beque" int);
+
+CREATE PUBLICATION testpub_ddl_2 FOR TABLE testpub_ddl_tbl1, testpub_ddl_t=
bl2, testpub_ddl_tbl3 WITH (publish=3D'delete', publish_generated_columns=
=3D'stored', publish_via_partition_root=3D'true');
+
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_2'));
+SELECT pg_get_publication_ddl('testpub_ddl_2', 'pretty', 'true');
+
+ALTER PUBLICATION testpub_ddl_2 SET (publish =3D 'delete, update');
+
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_2'));
+SELECT pg_get_publication_ddl('testpub_ddl_2', 'pretty', 'true');
+
+-- create publication for one table
+CREATE PUBLICATION testpub_ddl_3 FOR TABLE ONLY testpub_ddl_tbl1;
+
+SELECT pg_get_publication_ddl('testpub_ddl_3');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_3'));
+SELECT pg_get_publication_ddl('testpub_ddl_3', 'pretty', 'true');
+
+-- create publication for one table with two columns and a rowfilter
+CREATE PUBLICATION testpub_ddl_4 FOR TABLE ONLY testpub_ddl_tbl3 (bar,baz)=
WHERE (bar =3D baz);
+
+SELECT pg_get_publication_ddl('testpub_ddl_4');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_4'));
+SELECT pg_get_publication_ddl('testpub_ddl_4', 'pretty', 'true');
+
+-- create publication for one table with two columns and a rowfilter
+CREATE PUBLICATION testpub_ddl_5 FOR TABLE ONLY testpub_ddl_tbl4 (bar,bequ=
e) WHERE (beque IS TRUE);
+
+SELECT pg_get_publication_ddl('testpub_ddl_5');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_5'));
+SELECT pg_get_publication_ddl('testpub_ddl_5', 'pretty', 'true');
+
+-- create publication for all tables
+CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES;
+
+SELECT pg_get_publication_ddl('testpub_ddl_6');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_6'));
+SELECT pg_get_publication_ddl('testpub_ddl_6', 'pretty', 'true');
+
+-- create publication for all sequences
+CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES;
+SELECT pg_get_publication_ddl('testpub_ddl_7');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_7'));
+SELECT pg_get_publication_ddl('testpub_ddl_7', 'pretty', 'true');
+
+-- create publication for all tables and all sequences
+CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES;
+
+SELECT pg_get_publication_ddl('testpub_ddl_8');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_8'));
+SELECT pg_get_publication_ddl('testpub_ddl_8', 'pretty', 'true');
+
+-- explicit publish_generated_columns=3D'none'
+CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish_generated_co=
lumns=3D'none');
+SELECT pg_get_publication_ddl('testpub_ddl_9');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_9'));
+SELECT pg_get_publication_ddl('testpub_ddl_9', 'pretty', 'true');
+
+-- create a publication with a bare bolean in the row filter
+CREATE PUBLICATION testpub_ddl_10 FOR TABLE testpub_ddl_tbl4 WHERE (beque);
+SELECT pg_get_publication_ddl('testpub_ddl_10');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_10'));
+SELECT pg_get_publication_ddl('testpub_ddl_10', 'pretty', 'true');
+
+-- create schema for schema publication
+CREATE SCHEMA pub_schema_test_ddl;
+CREATE TABLE pub_schema_test_ddl.schema_tbl1 (foo int, bar int);
+CREATE TABLE pub_schema_test_ddl.schema_tbl2 (foo int, bar int);
+CREATE TABLE pub_schema_test_ddl.schema_tbl3 (foo int, bar int, baz int);
+
+-- create a publication for a list of tables and schema
+CREATE PUBLICATION testpub_ddl_schema_1 FOR TABLE pub_schema_test_ddl.sche=
ma_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test_=
ddl;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_1');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_schema_1'));
+SELECT pg_get_publication_ddl('testpub_ddl_schema_1', 'pretty', 'true');
+
+-- create publication in schema only for table
+CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLES IN SCHEMA pub_schema_te=
st_ddl, TABLE pub_schema_test_ddl.schema_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_schema_2'));
+SELECT pg_get_publication_ddl('testpub_ddl_schema_2', 'pretty', 'true');
+
+-- create publication for all tables in schema
+CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_te=
st_ddl;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_3');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_schema_3'));
+SELECT pg_get_publication_ddl('testpub_ddl_schema_3', 'pretty', 'true');
+
+-- a new schema for multiple schemas
+CREATE SCHEMA pub_schema_test_ddl_2;
+CREATE TABLE pub_schema_test_ddl_2.schema_tbl1 (foo int, bar int);
+
+-- create a publication for a list of schemas
+CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_te=
st_ddl, pub_schema_test_ddl_2;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_4');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_schema_4'));
+SELECT pg_get_publication_ddl('testpub_ddl_schema_4', 'pretty', 'true');
+
+-- create a publication for a specific schema and a table in public schema
+-- both with the same name
+CREATE TABLE schema_tbl1 (foo int, bar int);
+CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.sch=
ema_tbl1, schema_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_5');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_schema_5'));
+SELECT pg_get_publication_ddl('testpub_ddl_schema_5', 'pretty', 'true');
+
+-- create tables for partition test
+CREATE TABLE testpub_ddl_part (foo int, bar int) PARTITION BY RANGE (foo);
+CREATE TABLE testpub_ddl_part_p1 PARTITION OF testpub_ddl_part FOR VALUES =
FROM (0) TO (10);
+CREATE TABLE testpub_ddl_part_p2 PARTITION OF testpub_ddl_part FOR VALUES =
FROM (10) TO (20);
+
+CREATE PUBLICATION testpub_ddl_part1 FOR TABLE testpub_ddl_part;
+SELECT pg_get_publication_ddl('testpub_ddl_part1');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_part1'));
+SELECT pg_get_publication_ddl('testpub_ddl_part1', 'pretty', 'true');
+
+CREATE PUBLICATION testpub_ddl_part2 FOR TABLE testpub_ddl_part WITH (publ=
ish_via_partition_root=3D'false');
+SELECT pg_get_publication_ddl('testpub_ddl_part2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_part2'));
+SELECT pg_get_publication_ddl('testpub_ddl_part2', 'pretty', 'true');
+
+CREATE PUBLICATION testpub_ddl_part3 FOR TABLE testpub_ddl_part WITH (publ=
ish_via_partition_root=3D'true');
+SELECT pg_get_publication_ddl('testpub_ddl_part3');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_part3'));
+SELECT pg_get_publication_ddl('testpub_ddl_part3', 'pretty', 'true');
+
+CREATE PUBLICATION testpub_ddl_part4 FOR TABLE testpub_ddl_part_p1;
+SELECT pg_get_publication_ddl('testpub_ddl_part4');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_part4'));
+SELECT pg_get_publication_ddl('testpub_ddl_part4', 'pretty', 'true');
+
+-- identifiers that require quoting: publication, schema, table and column
+CREATE SCHEMA "Pub Schema";
+CREATE TABLE "Pub Schema"."Quoted Table" ("Col One" int, "select" int);
+CREATE PUBLICATION "testpub Quoted Pub" FOR TABLE "Pub Schema"."Quoted Tab=
le" ("Col One", "select") WHERE ("Col One" > 0);
+SELECT pg_get_publication_ddl('testpub Quoted Pub');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub Quoted Pub'));
+SELECT pg_get_publication_ddl('testpub Quoted Pub', 'pretty', 'true');
+
+-- tables for EXCEPT
+CREATE TABLE testpub_ddl_except1 (foo int, bar int);
+CREATE TABLE testpub_ddl_except2 (foo int, bar int);
+
+-- create publication for all tables except one
+CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE testpu=
b_ddl_except1);
+SELECT pg_get_publication_ddl('testpub_ddl_except1');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_except1'));
+SELECT pg_get_publication_ddl('testpub_ddl_except1', 'pretty', 'true');
+
+-- create publication for all sequences and all tables except two tables
+CREATE PUBLICATION testpub_ddl_except2 FOR ALL SEQUENCES, ALL TABLES EXCEP=
T (TABLE testpub_ddl_except1, testpub_ddl_except2);
+SELECT pg_get_publication_ddl('testpub_ddl_except2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam=
e=3D'testpub_ddl_except2'));
+SELECT pg_get_publication_ddl('testpub_ddl_except2', 'pretty', 'true');
+
+-- get all the created publications DDL into the table
+CREATE TEMP TABLE pub_ddl AS
+SELECT p.pubname, t.n, t.stmt
+FROM pg_publication p,
+LATERAL pg_get_publication_ddl(p.pubname) WITH ORDINALITY AS t(stmt, n)
+WHERE p.pubname LIKE 'testpub%';
+
+-- drop the publications to be recreated
+SELECT format('DROP PUBLICATION %I', pubname)
+FROM (SELECT DISTINCT pubname FROM pub_ddl) ORDER BY pubname \gexec
+
+-- recreate all the publications using the ddl from pg_get_publication_ddl=
()
+SELECT stmt FROM pub_ddl ORDER BY pubname, n \gexec
+
+-- cleanup publications
+SELECT format('DROP PUBLICATION %I', pubname)
+FROM (SELECT DISTINCT pubname FROM pub_ddl) ORDER BY pubname \gexec
+
+-- cleanup tables
+DROP TABLE testpub_ddl_tbl1;
+DROP TABLE testpub_ddl_tbl2;
+DROP TABLE testpub_ddl_tbl3;
+DROP TABLE testpub_ddl_tbl4;
+DROP TABLE testpub_ddl_tbl5;
+DROP TABLE pub_ddl;
+
+--- cleanup tables for schema tests
+DROP TABLE schema_tbl1;
+
+-- cleanup tables for partitions
+DROP TABLE testpub_ddl_part;
+
+-- cleanup tables for quoted names
+DROP TABLE "Pub Schema"."Quoted Table";
+
+-- cleanup tables for except
+DROP TABLE testpub_ddl_except1;
+DROP TABLE testpub_ddl_except2;
+
+-- cleanup schemas
+DROP SCHEMA pub_schema_test_ddl CASCADE;
+DROP SCHEMA pub_schema_test_ddl_2 CASCADE;
+DROP SCHEMA "Pub Schema";
+
+-- cleanup role
+RESET SESSION AUTHORIZATION;
+DROP ROLE regress_publication_ddl_user;
+
+RESET client_min_messages;
--=20
2.53.0
--=-=-=--
view thread (667+ messages) latest in thread
Message-ID: <no-message-id-889629@localhost>
Permalink: ../../no-message-id-889629@localhost/
Also on: postgresql.org/message-id/no-message-id-889629@localhost
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: jonathan.abdiel@gmail.com
Subject: Re: [PATCH v3 1/1] Introduce a new function pg_get_publication_ddl() t=
In-Reply-To: <no-message-id-889629@localhost>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox