Thanks to Zsolt and Marcos for the review.
Added schema_qualified (boolean, default true). When false, the target table is emitted unqualified everywhere (e.g., CREATE TABLE, ALTER TABLE, INHERITS, PARTITION OF, identity SEQUENCE NAME, etc.); same-schema sibling references follow suit; cross-schema references (e.g. FK targets in a different schema) remain qualified for correctness. Output from the always-qualified ruleutils helpers (pg_get_indexdef_string, pg_get_constraintdef_command, pg_get_ruledef, pg_get_statisticsobjdef_string) is post-processed to strip the base-schema prefix.
1) CHECK constraints on partition children are now emitted as ALTER TABLE … ADD CONSTRAINT … CHECK (…). They had been silently dropped because the PARTITION OF form has no column list to inline them into.
2) Inherited generated columns no longer emit a spurious ALTER COLUMN … SET DEFAULT, which would fail at replay.
3) User-named NOT NULL constraints are now emitted inline as CONSTRAINT <name> NOT NULL. Auto-named NOT NULLs keep the existing inline-NOT NULL + ALTER TABLE dedup behaviour so the common-case output is unchanged.
4) Added regression coverage for the three bug fixes plus the schema_qualified=false paths (same-schema vs cross-schema, INHERITS/PARTITION OF parents, custom identity sequence name, replay into a different target schema)
Attached is the v3 patch, ready for review.
Hello!
I did some basic testing with the new functions, and found a few bugs:
1. Seems like check constraints on partitions are ignored:
CREATE TABLE p (id int, val int) PARTITION BY RANGE (id);
CREATE TABLE p_child PARTITION OF p (CONSTRAINT chk_inline CHECK (val > 0))
FOR VALUES FROM (0) TO (100);
SELECT * FROM pg_get_table_ddl('p_child', 'owner','false');
2. inherited stored generated columns can't be replayed:
CREATE TABLE par_s (
id int,
g int GENERATED ALWAYS AS (id * 2) STORED
);
CREATE TABLE ch_s () INHERITS (par_s);
SELECT * FROM pg_get_table_ddl('ch_s', 'owner','false');
-- CREATE TABLE public.ch_s () INHERITS (public.par_s);
-- ALTER TABLE public.ch_s ALTER COLUMN g SET DEFAULT (id * 2);
Dropping ch_s, executing the returned statements:
ERROR: column "g" of relation "ch_s" is a generated column
HINT: Use ALTER TABLE ... ALTER COLUMN ... SET EXPRESSION instead.
3. named not null constraints can't be replayed:
CREATE TABLE t (a int CONSTRAINT my_nn NOT NULL);
SELECT * FROM pg_get_table_ddl('t'::regclass,'owner','false');
-- CREATE TABLE public.t ( a integer NOT NULL);
-- ALTER TABLE public.t ADD CONSTRAINT my_nn NOT NULL a;
Dropping t, executing the statements:
ERROR: cannot create not-null constraint "my_nn" on column "a" of table "t"
DETAIL: A not-null constraint named "t_a_not_null" already exists for
this column.
