v2-0001-Introduce-a-new-function-pg_get_publication_ddl-t.patch
text/x-patch
Filename: v2-0001-Introduce-a-new-function-pg_get_publication_ddl-t.patch
Type: text/x-patch
Part: 0
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v2-0001
Subject: Introduce a new function pg_get_publication_ddl() that returns the CREATE ddl statement for a given PUBLICATION.
| File | + | − |
|---|---|---|
| doc/src/sgml/func/func-info.sgml | 35 | 0 |
| src/backend/utils/adt/ddlutils.c | 382 | 1 |
| src/include/catalog/pg_proc.dat | 16 | 0 |
| src/test/regress/expected/publication_ddl.out | 671 | 0 |
| src/test/regress/parallel_schedule | 4 | 1 |
| src/test/regress/sql/publication_ddl.sql | 248 | 0 |
From 626f15915820da1053c89f546caf4c5ae3419b0d Mon Sep 17 00:00:00 2001
From: "Jonathan Gonzalez V." <jonathan.abdiel@gmail.com>
Date: Sun, 5 Oct 2025 18:11:56 +0200
Subject: [PATCH v2 1/1] Introduce a new function pg_get_publication_ddl() that
returns the CREATE ddl statement for a given PUBLICATION.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
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üseyin 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>
Signed-off-by: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
---
doc/src/sgml/func/func-info.sgml | 35 +
src/backend/utils/adt/ddlutils.c | 383 +++++++++-
src/include/catalog/pg_proc.dat | 16 +
src/test/regress/expected/publication_ddl.out | 671 ++++++++++++++++++
src/test/regress/parallel_schedule | 5 +-
src/test/regress/sql/publication_ddl.sql | 248 +++++++
6 files changed, 1356 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..705bf32a17f 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3961,6 +3961,41 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
<literal>TABLESPACE</literal>.
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="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</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para role="func_signature">
+ <function>pg_get_publication_ddl</function>
+ ( <parameter>publication</parameter> <type>text</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE PUBLICATION</command> 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 supplied
+ OID or name exists. When the publication was created with
+ <literal>FOR ALL TABLES, ALL SEQUENCES</literal>, the emitted
+ statement always lists <literal>ALL TABLES</literal> before
+ <literal>ALL SEQUENCES</literal> regardless of the original order.
+ 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/ddlutils.c
index f32fcd453ef..39cdcdf5a63 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -20,12 +20,14 @@
#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, bool 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);
/*
* parse_ddl_options
@@ -1185,3 +1190,379 @@ 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 isnull)
+{
+ FuncCallContext *funcctx;
+ List *statements;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ DdlOption opts[] = {
+ {"pretty", DDL_OPT_BOOL},
+ {"owner", DDL_OPT_BOOL},
+ };
+
+ funcctx = SRF_FIRSTCALL_INIT();
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ if (isnull)
+ {
+ MemoryContextSwitchTo(oldcontext);
+ SRF_RETURN_DONE(funcctx);
+ }
+
+ parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
+
+ statements = pg_get_publication_ddl_internal(puboid,
+ opts[0].isset && opts[0].boolval,
+ opts[1].isset && !opts[1].boolval);
+
+ funcctx->user_fctx = statements;
+ funcctx->max_calls = list_length(statements);
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+ statements = (List *) funcctx->user_fctx;
+
+ if (funcctx->call_cntr < funcctx->max_calls)
+ {
+ char *stmt;
+
+ stmt = (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 = InvalidOid;
+ bool isnull;
+
+ isnull = PG_ARGISNULL(0);
+ if (!isnull)
+ puboid = 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 = InvalidOid;
+ bool isnull;
+
+ isnull = PG_ARGISNULL(0);
+ if (!isnull)
+ {
+ char *pubname = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+ puboid = 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;
+ StringInfo buf;
+ List *statements = NIL;
+ List *pub_incl_relids = NIL;
+ List *pub_excl_relids = NIL;
+ List *pub_schemas = NIL;
+ bool first_perm = true;
+
+ if (!SearchSysCacheExists1(PUBLICATIONOID, ObjectIdGetDatum(puboid)))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("publication with OID %u does not exist", puboid)));
+
+ pub = GetPublication(puboid);
+
+ buf = makeStringInfo();
+
+ appendStringInfo(buf, "CREATE PUBLICATION %s", quote_identifier(pub->name));
+
+ /*
+ * 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 = GetExcludedPublicationTables(pub->oid, PUBLICATION_PART_ROOT);
+ }
+ if (pub->allsequences)
+ appendStringInfo(buf,
+ "%sALL SEQUENCES",
+ pub->alltables ? ", " : "");
+ }
+ else
+ {
+ pub_incl_relids = GetIncludedPublicationRelations(pub->oid, PUBLICATION_PART_ROOT);
+ pub_schemas = GetPublicationSchemas(pub->oid);
+ }
+
+ if (pub_incl_relids != NIL)
+ {
+ ListCell *pub_cell;
+ char *schemaname = NULL;
+ char *tablename;
+
+ append_ddl_option(buf, pretty, 4, "FOR TABLE ");
+
+ /*
+ * Publication can have table relations
+ */
+ foreach(pub_cell, pub_incl_relids)
+ {
+ HeapTuple pubtuple = NULL;
+ HeapTuple reltup;
+ Form_pg_class relform;
+ Datum columns,
+ conditions;
+ Oid relid = pub_cell->oid_value;
+ bool cols_nulls,
+ condition_nulls;
+
+ reltup = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(reltup))
+ elog(ERROR,
+ "cache lookup failed for relation %u",
+ relid);
+
+ relform = (Form_pg_class) GETSTRUCT(reltup);
+ tablename = NameStr(relform->relname);
+ schemaname = get_namespace_name(relform->relnamespace);
+
+ appendStringInfo(buf, "%s%s",
+ foreach_current_index(pub_cell) > 0 ? ", " : "",
+ quote_qualified_identifier(schemaname, tablename));
+
+ pfree(schemaname);
+
+ pubtuple = SearchSysCache2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pub->oid));
+
+ if (!HeapTupleIsValid(pubtuple))
+ elog(ERROR,
+ "cache lookup failed for publication relation %u in publication %u",
+ relid, pub->oid);
+
+ columns = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
+ Anum_pg_publication_rel_prattrs,
+ &cols_nulls);
+
+ conditions = 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 = -1;
+
+ attmap = pub_collist_to_bitmapset(NULL, columns, NULL);
+
+ appendStringInfoChar(buf, '(');
+ while ((attnum = bms_next_member(attmap, attnum)) >= 0)
+ {
+ appendStringInfo(buf, "%s%s",
+ bms_member_index(attmap, attnum) ? ", " : "",
+ quote_identifier(get_attname(relid, attnum, true)));
+ }
+ 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 = stringToNode(TextDatumGetCString(conditions));
+ context = deparse_context_for(tablename, relid);
+ str = 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 WITH */
+ if (pub_schemas != 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 == NIL)
+ append_ddl_option(buf, pretty, 4, "FOR TABLES IN SCHEMA");
+ else
+ appendStringInfoString(buf, ", TABLES IN SCHEMA");
+
+ foreach(schema_cell, pub_schemas)
+ {
+ char *nspname = get_namespace_name(schema_cell->oid_value);
+
+ appendStringInfo(buf, "%s %s",
+ foreach_current_index(schema_cell) > 0 ? "," : "",
+ quote_identifier(nspname));
+ pfree(nspname);
+ }
+ }
+
+ if (pub_excl_relids != NIL)
+ {
+ ListCell *excl_cell;
+ char *schemaname = NULL;
+
+ appendStringInfoString(buf, " EXCEPT (TABLE ");
+
+ foreach(excl_cell, pub_excl_relids)
+ {
+ HeapTuple tp = 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_value);
+
+ reltup = (Form_pg_class) GETSTRUCT(tp);
+ schemaname = 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, ')');
+ }
+
+ /* Always add the WITH options */
+ append_ddl_option(buf, pretty, 4, "WITH (");
+
+ /* Publish string */
+ appendStringInfoString(buf, "publish='");
+
+ /*
+ * We need to know if we're the second permission added to prefix with a
+ * ", " string
+ */
+ 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 = false;
+ }
+
+ if (pub->pubactions.pubupdate)
+ {
+ appendStringInfo(buf, "%supdate", first_perm ? "" : ", ");
+ first_perm = false;
+ }
+ if (pub->pubactions.pubdelete)
+ {
+ appendStringInfo(buf, "%sdelete", first_perm ? "" : ", ");
+ first_perm = false;
+ }
+
+ if (pub->pubactions.pubtruncate)
+ {
+ appendStringInfo(buf, "%struncate", first_perm ? "" : ", ");
+ }
+
+ appendStringInfoString(buf, "', ");
+
+ /* publish_generated_columns string */
+ appendStringInfo(buf, "publish_generated_columns='%s', ",
+ pub->pubgencols_type == PUBLISH_GENCOLS_NONE ? "none" : "stored");
+
+ /* publish_via_partition_root value */
+ appendStringInfo(buf, "publish_via_partition_root='%s')",
+ pub->pubviaroot ? "true" : "false");
+
+ appendStringInfoChar(buf, ';');
+ statements = lappend(statements, pstrdup(buf->data));
+
+ /* OWNER */
+ if (!no_owner)
+ {
+ HeapTuple tup;
+ Form_pg_publication pubform;
+ char *owner;
+
+ tup = SearchSysCache1(PUBLICATIONOID, ObjectIdGetDatum(puboid));
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for publication %u", puboid);
+ pubform = (Form_pg_publication) GETSTRUCT(tup);
+ owner = GetUserNameFromId(pubform->pubowner, false);
+ ReleaseSysCache(tup);
+
+ resetStringInfo(buf);
+ appendStringInfo(buf, "ALTER PUBLICATION %s OWNER TO %s;",
+ quote_identifier(pub->name), quote_identifier(owner));
+ pfree(owner);
+ statements = lappend(statements, pstrdup(buf->data));
+ }
+
+ 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 => 'pg_relation_is_publishable', provolatile => 's',
prorettype => 'bool', proargtypes => 'regclass',
prosrc => 'pg_relation_is_publishable' },
+{ oid => '6122', descr => 'get DDL to recreate a publication',
+ proname => 'pg_get_publication_ddl', provariadic => 'text', proisstrict => 'f',
+ provolatile => 's', proretset => 't', prorows => '2', prorettype => 'text',
+ proargtypes => 'oid text',
+ proargmodes => '{i,v}',
+ proallargtypes => '{oid,text}',
+ pronargdefaults => '1', proargdefaults => '{NULL}',
+ prosrc => 'pg_get_publication_ddl_oid' },
+{ oid => '6123', descr => 'get DDL to recreate a publication',
+ proname => 'pg_get_publication_ddl', provariadic => 'text', proisstrict => 'f',
+ provolatile => 's', proretset => 't', prorows => '2', prorettype => 'text',
+ proargtypes => 'text text',
+ proargmodes => '{i,v}',
+ proallargtypes => '{text,text}',
+ pronargdefaults => '1', proargdefaults => '{NULL}',
+ prosrc => 'pg_get_publication_ddl_name' },
# rls
{ oid => '3298',
diff --git a/src/test/regress/expected/publication_ddl.out b/src/test/regress/expected/publication_ddl.out
new file mode 100644
index 00000000000..9fcb6582205
--- /dev/null
+++ b/src/test/regress/expected/publication_ddl.out
@@ -0,0 +1,671 @@
+--
+-- Test for DDL statement from:
+-- - pg_get_publication_ddl
+--
+-- suppress warning that depends on wal_level
+SET client_min_messages = '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_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='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 pubname='testpub_ddl_1'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='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
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_1 +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- NULL input should produce an empty result set
+SELECT count(*) = 0 AS is_null FROM pg_get_publication_ddl(NULL::oid);
+ is_null
+---------
+ t
+(1 row)
+
+SELECT count(*) = 0 AS is_null FROM pg_get_publication_ddl(NULL::text);
+ is_null
+---------
+ t
+(1 row)
+
+-- create base table to test basic table publication
+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_tbl2, testpub_ddl_tbl3 WITH (publish='delete', publish_generated_columns='stored', publish_via_partition_root='true');
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish='delete', publish_generated_columns='stored', publish_via_partition_root='true');
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_2'));
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish='delete', publish_generated_columns='stored', publish_via_partition_root='true');
+ 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
+-----------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 +
+ FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 +
+ WITH (publish='delete', publish_generated_columns='stored', publish_via_partition_root='true');
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+ALTER PUBLICATION testpub_ddl_2 SET (publish = 'delete, update');
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish='update, delete', publish_generated_columns='stored', publish_via_partition_root='true');
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_2'));
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish='update, delete', publish_generated_columns='stored', publish_via_partition_root='true');
+ 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
+-------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 +
+ FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 +
+ WITH (publish='update, delete', publish_generated_columns='stored', publish_via_partition_root='true');
+ 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
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_3 FOR TABLE public.testpub_ddl_tbl1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_3'));
+ pg_get_publication_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_3 FOR TABLE public.testpub_ddl_tbl1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ 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
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_3 +
+ FOR TABLE public.testpub_ddl_tbl1 +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for one table with two columns and a condition
+CREATE PUBLICATION testpub_ddl_4 FOR TABLE ONLY testpub_ddl_tbl3 (bar,baz) WHERE (bar = baz);
+SELECT pg_get_publication_ddl('testpub_ddl_4');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_4 FOR TABLE public.testpub_ddl_tbl3(bar, baz) WHERE (bar = baz) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_4'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_4 FOR TABLE public.testpub_ddl_tbl3(bar, baz) WHERE (bar = baz) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ 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
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_4 +
+ FOR TABLE public.testpub_ddl_tbl3(bar, baz) WHERE (bar = baz) +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for one table with two columns and a condition with an expression
+CREATE PUBLICATION testpub_ddl_5 FOR TABLE ONLY testpub_ddl_tbl4 (bar,beque) WHERE (beque IS TRUE);
+SELECT pg_get_publication_ddl('testpub_ddl_5');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_5 FOR TABLE public.testpub_ddl_tbl4(bar, beque) WHERE (beque IS TRUE) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_5'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_5 FOR TABLE public.testpub_ddl_tbl4(bar, beque) WHERE (beque IS TRUE) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ 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
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_5 +
+ FOR TABLE public.testpub_ddl_tbl4(bar, beque) WHERE (beque IS TRUE) +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ 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
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_6 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_6'));
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ 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
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_6 +
+ FOR ALL TABLES +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ 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');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_7 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_7'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ 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
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_7 +
+ FOR ALL SEQUENCES +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ 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
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_8 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_8'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ 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
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_8 +
+ FOR ALL TABLES, ALL SEQUENCES +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_8 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- explicit publish_generated_columns='none'
+CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish_generated_columns='none');
+SELECT pg_get_publication_ddl('testpub_ddl_9');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_9 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_9'));
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ 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
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_9 +
+ FOR ALL TABLES +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_9 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- columns in publication must be quoted
+CREATE PUBLICATION testpub_ddl_10 FOR TABLE testpub_ddl_tbl5("bar beque");
+SELECT pg_get_publication_ddl('testpub_ddl_10');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_10 FOR TABLE public.testpub_ddl_tbl5("bar beque") WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_10 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_10'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_10 FOR TABLE public.testpub_ddl_tbl5("bar beque") WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ 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
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_10 +
+ FOR TABLE public.testpub_ddl_tbl5("bar beque") +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ 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
+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;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_1');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 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='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_1'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 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='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_1', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ 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='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication in schema only for table
+CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLES IN SCHEMA pub_schema_test_ddl, TABLE pub_schema_test_ddl.schema_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_2');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLE pub_schema_test_ddl.schema_tbl1, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_2'));
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLE pub_schema_test_ddl.schema_tbl1, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_2', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_2 +
+ FOR TABLE pub_schema_test_ddl.schema_tbl1, TABLES IN SCHEMA pub_schema_test_ddl +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for all tables in schema
+CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_test_ddl;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_3');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_3'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_3', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_3 +
+ FOR TABLES IN SCHEMA pub_schema_test_ddl +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_user;
+(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_test_ddl, pub_schema_test_ddl_2;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_4');
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_4'));
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_4', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_4 +
+ FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2 +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_user;
+(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.schema_tbl1, schema_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_5');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.schema_tbl1, public.schema_tbl1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_5'));
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.schema_tbl1, public.schema_tbl1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_5', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_5 +
+ FOR TABLE pub_schema_test_ddl.schema_tbl1, public.schema_tbl1 +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_user;
+(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
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part1 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_part1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part1'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part1 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ 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
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part1 +
+ FOR TABLE public.testpub_ddl_part +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_part1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+CREATE PUBLICATION testpub_ddl_part2 FOR TABLE testpub_ddl_part WITH (publish_via_partition_root='false');
+SELECT pg_get_publication_ddl('testpub_ddl_part2');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part2 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_part2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part2'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part2 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ 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
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part2 +
+ FOR TABLE public.testpub_ddl_part +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_part2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+CREATE PUBLICATION testpub_ddl_part3 FOR TABLE testpub_ddl_part WITH (publish_via_partition_root='true');
+SELECT pg_get_publication_ddl('testpub_ddl_part3');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part3 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='true');
+ ALTER PUBLICATION testpub_ddl_part3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part3'));
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part3 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='true');
+ 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
+-----------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part3 +
+ FOR TABLE public.testpub_ddl_part +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='true');
+ 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
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part4 FOR TABLE public.testpub_ddl_part_p1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_part4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part4'));
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part4 FOR TABLE public.testpub_ddl_part_p1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ 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
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part4 +
+ FOR TABLE public.testpub_ddl_part_p1 +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ 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 "Quoted Pub" FOR TABLE "Pub Schema"."Quoted Table" ("Col One", "select") WHERE ("Col One" > 0);
+SELECT pg_get_publication_ddl('Quoted Pub');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION "Quoted Pub" FOR TABLE "Pub Schema"."Quoted Table"("Col One", "select") WHERE ("Col One" > 0) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION "Quoted Pub" OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='Quoted Pub'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION "Quoted Pub" FOR TABLE "Pub Schema"."Quoted Table"("Col One", "select") WHERE ("Col One" > 0) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION "Quoted Pub" OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('Quoted Pub', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION "Quoted Pub" +
+ FOR TABLE "Pub Schema"."Quoted Table"("Col One", "select") WHERE ("Col One" > 0) +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION "Quoted Pub" OWNER TO regress_publication_ddl_user;
+(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 testpub_ddl_except1);
+SELECT pg_get_publication_ddl('testpub_ddl_except1');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_except1'));
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_except1', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except1 +
+ FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1) +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for all tables except two tables
+CREATE PUBLICATION testpub_ddl_except2 FOR ALL SEQUENCES, ALL TABLES EXCEPT (TABLE testpub_ddl_except1, testpub_ddl_except2);
+SELECT pg_get_publication_ddl('testpub_ddl_except2');
+ pg_get_publication_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except2 FOR ALL TABLES, ALL SEQUENCES EXCEPT (TABLE public.testpub_ddl_except1, public.testpub_ddl_except2) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_except2'));
+ pg_get_publication_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except2 FOR ALL TABLES, ALL SEQUENCES EXCEPT (TABLE public.testpub_ddl_except1, public.testpub_ddl_except2) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_except2', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except2 +
+ FOR ALL TABLES, ALL SEQUENCES EXCEPT (TABLE public.testpub_ddl_except1, public.testpub_ddl_except2) +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- cleanup publications
+DROP PUBLICATION testpub_ddl_1;
+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_10;
+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;
+DROP PUBLICATION testpub_ddl_part1;
+DROP PUBLICATION testpub_ddl_part2;
+DROP PUBLICATION testpub_ddl_part3;
+DROP PUBLICATION testpub_ddl_part4;
+DROP PUBLICATION "Quoted Pub";
+DROP PUBLICATION testpub_ddl_except1;
+DROP PUBLICATION testpub_ddl_except2;
+-- 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;
+-- cleanup tables in schemas
+DROP TABLE pub_schema_test_ddl.schema_tbl1;
+DROP TABLE pub_schema_test_ddl.schema_tbl2;
+DROP TABLE pub_schema_test_ddl.schema_tbl3;
+DROP TABLE pub_schema_test_ddl_2.schema_tbl1;
+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;
+DROP SCHEMA pub_schema_test_ddl_2;
+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 cluster
# oidjoins is read-only, though, and should run late for best coverage
test: oidjoins event_trigger
-
# 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
+# 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 during
# setup that other tests may use.
test: tablespace
diff --git a/src/test/regress/sql/publication_ddl.sql b/src/test/regress/sql/publication_ddl.sql
new file mode 100644
index 00000000000..9ec41bc0ae0
--- /dev/null
+++ b/src/test/regress/sql/publication_ddl.sql
@@ -0,0 +1,248 @@
+--
+-- Test for DDL statement from:
+-- - pg_get_publication_ddl
+--
+
+-- suppress warning that depends on wal_level
+SET client_min_messages = '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 pubname='testpub_ddl_1'));
+SELECT pg_get_publication_ddl('testpub_ddl_1', 'pretty', 'true');
+
+-- NULL input should produce an empty result set
+SELECT count(*) = 0 AS is_null FROM pg_get_publication_ddl(NULL::oid);
+SELECT count(*) = 0 AS is_null FROM pg_get_publication_ddl(NULL::text);
+
+-- create base table to test basic table publication
+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_tbl2, testpub_ddl_tbl3 WITH (publish='delete', publish_generated_columns='stored', publish_via_partition_root='true');
+
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_2'));
+SELECT pg_get_publication_ddl('testpub_ddl_2', 'pretty', 'true');
+
+ALTER PUBLICATION testpub_ddl_2 SET (publish = 'delete, update');
+
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='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 pubname='testpub_ddl_3'));
+SELECT pg_get_publication_ddl('testpub_ddl_3', 'pretty', 'true');
+
+-- create publication for one table with two columns and a condition
+CREATE PUBLICATION testpub_ddl_4 FOR TABLE ONLY testpub_ddl_tbl3 (bar,baz) WHERE (bar = baz);
+
+SELECT pg_get_publication_ddl('testpub_ddl_4');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_4'));
+SELECT pg_get_publication_ddl('testpub_ddl_4', 'pretty', 'true');
+
+-- create publication for one table with two columns and a condition with an expression
+CREATE PUBLICATION testpub_ddl_5 FOR TABLE ONLY testpub_ddl_tbl4 (bar,beque) WHERE (beque IS TRUE);
+
+SELECT pg_get_publication_ddl('testpub_ddl_5');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='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 pubname='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 pubname='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 pubname='testpub_ddl_8'));
+SELECT pg_get_publication_ddl('testpub_ddl_8', 'pretty', 'true');
+
+-- explicit publish_generated_columns='none'
+CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish_generated_columns='none');
+SELECT pg_get_publication_ddl('testpub_ddl_9');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_9'));
+SELECT pg_get_publication_ddl('testpub_ddl_9', 'pretty', 'true');
+
+-- columns in publication must be quoted
+CREATE PUBLICATION testpub_ddl_10 FOR TABLE testpub_ddl_tbl5("bar beque");
+SELECT pg_get_publication_ddl('testpub_ddl_10');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='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
+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;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_1');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='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_test_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 pubname='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_test_ddl;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_3');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='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_test_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 pubname='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.schema_tbl1, schema_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_5');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='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 pubname='testpub_ddl_part1'));
+SELECT pg_get_publication_ddl('testpub_ddl_part1', 'pretty', 'true');
+
+CREATE PUBLICATION testpub_ddl_part2 FOR TABLE testpub_ddl_part WITH (publish_via_partition_root='false');
+SELECT pg_get_publication_ddl('testpub_ddl_part2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part2'));
+SELECT pg_get_publication_ddl('testpub_ddl_part2', 'pretty', 'true');
+
+CREATE PUBLICATION testpub_ddl_part3 FOR TABLE testpub_ddl_part WITH (publish_via_partition_root='true');
+SELECT pg_get_publication_ddl('testpub_ddl_part3');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='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 pubname='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 "Quoted Pub" FOR TABLE "Pub Schema"."Quoted Table" ("Col One", "select") WHERE ("Col One" > 0);
+SELECT pg_get_publication_ddl('Quoted Pub');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='Quoted Pub'));
+SELECT pg_get_publication_ddl('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 testpub_ddl_except1);
+SELECT pg_get_publication_ddl('testpub_ddl_except1');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_except1'));
+SELECT pg_get_publication_ddl('testpub_ddl_except1', 'pretty', 'true');
+
+-- create publication for all tables except two tables
+CREATE PUBLICATION testpub_ddl_except2 FOR ALL SEQUENCES, ALL TABLES EXCEPT (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 pubname='testpub_ddl_except2'));
+SELECT pg_get_publication_ddl('testpub_ddl_except2', 'pretty', 'true');
+
+-- cleanup publications
+DROP PUBLICATION testpub_ddl_1;
+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_10;
+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;
+DROP PUBLICATION testpub_ddl_part1;
+DROP PUBLICATION testpub_ddl_part2;
+DROP PUBLICATION testpub_ddl_part3;
+DROP PUBLICATION testpub_ddl_part4;
+DROP PUBLICATION "Quoted Pub";
+DROP PUBLICATION testpub_ddl_except1;
+DROP PUBLICATION testpub_ddl_except2;
+
+-- 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;
+
+-- cleanup tables in schemas
+DROP TABLE pub_schema_test_ddl.schema_tbl1;
+DROP TABLE pub_schema_test_ddl.schema_tbl2;
+DROP TABLE pub_schema_test_ddl.schema_tbl3;
+DROP TABLE pub_schema_test_ddl_2.schema_tbl1;
+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;
+DROP SCHEMA pub_schema_test_ddl_2;
+DROP SCHEMA "Pub Schema";
+
+-- cleanup role
+RESET SESSION AUTHORIZATION;
+DROP ROLE regress_publication_ddl_user;
+
+RESET client_min_messages;
--
2.53.0