public inbox for [email protected]  

help / color / mirror / Atom feed

From: Zsolt Parragi <[email protected]>
To: [email protected]
Subject: Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements
Date: Tue, 9 Jun 2026 14:17:11 -0500
Message-ID: <CAN4CZFP5-fAGwxMCyKGePgp3N8NCpoFXkruO=5xjTC=gPwqYxw@mail.gmail.com> (raw)
In-Reply-To: <CANxoLDfL1AxL=k9SsRNReKQ-sJUSy6RvCqeog5HWckFxff=0Pg@mail.gmail.com>
References: <CANxoLDfjQnhM=E6JSyYo9s9OdjqoN8s_3wE5yL=kaDu_X8j-dA@mail.gmail.com>
	<CANxoLDcxmXNHNyPt5v+LaBJVJmyt=j95D7TDE9SepoM7y15t-w@mail.gmail.com>
	<CAN4CZFNqf41TCg1ENccq6DAYQpArCbnKUSidHnRKaVMWGj5pBA@mail.gmail.com>
	<CANxoLDfL1AxL=k9SsRNReKQ-sJUSy6RvCqeog5HWckFxff=0Pg@mail.gmail.com>

Thanks, I can confirm that the previous bugs were fixed, however the
bugfixes also introduce a new issue, where inherited not null
constraints are missing:

CREATE TABLE par (a int);
CREATE TABLE ch (b int) INHERITS (par);
ALTER TABLE ch ADD CONSTRAINT my_nn NOT NULL a;
SELECT * FROM pg_get_table_ddl('ch','owner','false');
--  CREATE TABLE public.ch ( b integer) INHERITS (public.par);

The schema_qualified => false part also doesn't work as described:

+        <command>CREATE STATISTICS</command> statement.  References to
+        objects in the same schema as the target table (inheritance
+        parents, partition parents, identity sequences, and any
+        same-schema object the deparse helpers happen to mention) are
+        also emitted unqualified, so the script can be replayed under a
+        different <varname>search_path</varname> to recreate the table
+        in another schema.

but:

CREATE SCHEMA s1;
CREATE SEQUENCE s1.myseq;
CREATE FUNCTION s1.f(int) RETURNS int LANGUAGE sql IMMUTABLE AS 'SELECT $1';
CREATE TABLE s1.t (
    id  int DEFAULT nextval('s1.myseq'),
    val int,
    CONSTRAINT chk CHECK (s1.f(val) > 0)
);
SELECT * FROM pg_get_table_ddl('s1.t', 'owner','false',
'schema_qualified','false');
--  CREATE TABLE t ( id integer DEFAULT nextval('s1.myseq'::regclass),
val integer, CONSTRAINT chk CHECK ((s1.f(val) > 0)));

s1 appears twice in the output.

It also has an issue with strings containing the schema:

CREATE SCHEMA myschema;
CREATE TABLE myschema.p (id int, note text) PARTITION BY RANGE (id);
CREATE TABLE myschema.pc PARTITION OF myschema.p
    (CONSTRAINT chk CHECK (note <> 'myschema.secret')) FOR VALUES FROM
(0) TO (100);
SELECT * FROM pg_get_table_ddl('myschema.pc', 'owner','false',
'schema_qualified','false');
-- CREATE TABLE pc PARTITION OF p FOR VALUES FROM (0) TO (100);
-- ALTER TABLE pc ADD CONSTRAINT chk CHECK ((note <> 'secret'::text));

view thread (54+ messages)  latest in thread

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: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements
  In-Reply-To: <CAN4CZFP5-fAGwxMCyKGePgp3N8NCpoFXkruO=5xjTC=gPwqYxw@mail.gmail.com>

* 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