Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

Thanks for the review, I have fixed the mentioned issue.
The v15 patch is ready for review.

On Thu, Jul 2, 2026 at 3:50 AM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
wrote:

> I did some more testing, I noticed one more issue with self
> referencing foreign keys:
>
> CREATE TABLE t (id int PRIMARY KEY, parent_id int REFERENCES t(id));
> SELECT pg_get_table_ddl('t'::regclass);
> -- CREATE TABLE public.t (id integer NOT NULL, parent_id integer);
> -- ALTER TABLE public.t OWNER TO postgres;
> -- ALTER TABLE public.t ADD CONSTRAINT t_parent_id_fkey FOREIGN KEY
> (parent_id) REFERENCES public.t(id);
> -- ALTER TABLE public.t ADD CONSTRAINT t_pkey PRIMARY KEY (id);
>
> It tries to add the foreign key before the primary, and fails with
> `ERROR: there is no unique constraint matching given keys for
> referenced table "t"`
>
> There's also another issue in schema_qualified false, with partitions
> in different schemas:
>
> CREATE SCHEMA s;
> CREATE SCHEMA other;
> CREATE TABLE s.pt (id int, val int) PARTITION BY RANGE (id);
> CREATE TABLE other.pt_c PARTITION OF s.pt FOR VALUES FROM (0) TO (100);
> SELECT pg_get_table_ddl('s.pt'::regclass, schema_qualified => false);
> -- CREATE TABLE pt (id integer, val integer) PARTITION BY RANGE (id);
> -- ALTER TABLE pt OWNER TO postgres;
> -- CREATE TABLE pt_c PARTITION OF s.pt FOR VALUES FROM (0) TO (100);
> -- ALTER TABLE pt_c OWNER TO postgres;
>
> The second create table statement references pt as s.pt, which seems
> incorrect.
> It is also missing its own schema qualification, which I'm unsure if
> it is wrong or not. If I interpret the documentation strictly, it
> isn't the target table, so it should appear with its schema
> qualification?
>
>
>