step1:
psql -h127.0.0.1 -p 5432 -U liuhy -W -d dts < ./pg_get_tabledef.sql

step2:
dts=# \df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+-----------------+------------------+-------------------------------------------------------------------------------------------------------------------------------+------
public | pg_get_coldef | text | in_schema character varying, in_table character varying, in_column character varying | func
public | pg_get_tabledef | text | in_schema character varying, in_table character varying, _verbose boolean, VARIADIC arr tabledefs[] DEFAULT '{}'::tabledefs[] | func
(2 rows)

step3:
dts=# \d
List of relations
Schema | Name | Type | Owner
--------+-----------------+----------+-------
public | persons0 | table | liuhy
public | persons0_id_seq | sequence | liuhy
(2 rows)

dts=# select * from public.pg_get_tabledef('public','persons0', false);
ERROR: line=PL/pgSQL function pg_get_tabledef(character varying,character varying,boolean,tabledefs[]) line 203 at assignment. 42883. function pg_get_coldef(character varying, character varying, information_schema.sql_identifier) does not exist
CONTEXT: PL/pgSQL function pg_get_tabledef(character varying,character varying,boolean,tabledefs[]) line 364 at RAISE
dts=#

dts=# select version()
;
version

PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.1.0, 64-bit
(1 row)

dts=#

identified via pull request#17

Hi Michael,

I'm seeing NOT NULL being appended twice for GENERATED ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS IDENTITY columns.

I think this is due to the code here and here, as NOT NULL is then appended again here.

Thanks!

If the table definition has indexes defined within it as constraints, then do not add the CREATE INDEX statements for them.

You cannot specify an index tablespace AFTER the WHERE CLAUSE for a partial index. It must occur beforehand.

Valid Table definition:

CREATE TABLE myschema.mytable (
    id uuid NOT NULL,
    email public.citext NOT NULL,
    searchable tsvector GENERATED ALWAYS AS (to_tsvector('simple'::regconfig, COALESCE(public.translate(email, '@.-'::public.citext, ' '::text), ''::text))) STORED,
    searchable2 tsvector DEFAULT to_tsvector('simple'::regconfig, ''::text)
);

Invalid output:

CREATE  TABLE myschema.mytable (
   id uuid NOT NULL, 
   email public.citext NOT NULL,
   searchable tsvector NULL
 ) TABLESPACE pg_default;

Output misses an entire column ("searchable2") and wrongly defines the other vector column ("searchable").

Currently, the source schema prepends the datatype for USER-DEFINED datatypes. But if the type is from an extension like PostGIS then do not presume the schema since it probably defaults to "public", so leave column not schema-qualified.

create table：
create table tbpgdts_0412173049(c1 int primary key, c2 int not null, c3 int);
create table unionkey_tbpgdts_0412173049 (id1 int, id2 int, id3 int, id4 numeric, id5 numeric, col1 int[], primary key (id2, id4, id5));
create table _ref_tbpgdts_0412173049(c1 int primary key, c2 int REFERENCES tbpgdts_0412173049(c1));
create view vpgdts_0412173049 as select * from tbpgdts_0412173049 limit 100;

call func:
dts2=> select * from public.pg_get_tabledef('public','unionkey_tbpgdts_0412173049', false)
dts2-> ;
pg_get_tabledef

CREATE TABLE public.unionkey_tbpgdts_0412173049 ( +
id1 integer NULL, +
id2 integer NOT NULL, +
id3 integer NULL, +
id4 numeric NOT NULL, +
id5 numeric NOT NULL, +
col1 ARRAY NULL, +
CONSTRAINT unionkey_tbpgdts_0412173049_pkey PRIMARY KEY (id2, id4, id5)+
) TABLESPACE pg_default; +
+

unionkey_tbpgdts_0412173049 col col1 int[] ===> col1 ARRAY

If a table is partitioned with foreign keys, an implied foreign key is done for partitioned tables if defined on the parent, so don't define explicitly on the partitions.

1> test case：
CREATE SCHEMA a;
create table a.test_parent_23210642 ( i serial );
CREATE SCHEMA b;
create table b.test_child_23210642 () inherits (a.test_parent_23210642);

2> pg_get_tabledef show result:
select * from public.pg_get_tabledef('b','test_child_23210642', false);
pg_get_tabledef

CREATE TABLE b.test_child_23210642( +
) INHERITS (b.test_parent_23210642) +
TABLESPACE pg_default; +
+

The actual parent table test_parent_23210642 is under a schema

This is parent table ddl：

create table measurement
(
measurement_id integer default nextval('test.measurement_measurement_id_seq'::regclass) not null,
sensor_id integer not null,
reading double precision not null,
recorded_at timestamp not null,
constraint measurement_pkey
primary key (measurement_id, recorded_at)
)
partition by RANGE (recorded_at);

This is child table ddl：

CREATE TABLE test.measurement_y2024m01 PARTITION OF test.measurement FOR VALUES FROM ('2024-01-01 00:00:00') TO ('2024-02-01 00:00:00') TABLESPACE pg_default;
CREATE UNIQUE INDEX measurement_y2024m01_pkey ON test.measurement_y2024m01 USING btree (measurement_id, recorded_at) TABLESPACE pg_default;

If I run this SQL statement, I will receive a prompt saying that measurement_y2024m01_pkey already exists

There were 2 places where comments about fixes were shown. Consolidated them into one place under the function prototype heading.

The default as it works now is to define the PKEY internally as part of table definition.

CREATE SCHEMA a;
create table a.test_parent_a (i serial primary key);

CREATE TABLE a.test_child_b(
CONSTRAINT test_child_b_id_fkey FOREIGN KEY (id) REFERENCES a.test_parent_a(i)
) INHERITS (a.test_parent_a) TABLESPACE pg_default;
CREATE UNIQUE INDEX test_child_b_pkey ON a.test_child_b USING btree (id) TABLESPACE pg_default;

err
dts1=> CREATE TABLE a.test_child_b(
dts1(> CONSTRAINT test_child_b_id_fkey FOREIGN KEY (id) REFERENCES a.test_parent_a(i)
dts1(> ) INHERITS (a.test_parent_a) TABLESPACE pg_default;
ERROR: column "id" referenced in foreign key constraint does not exist

Thanks @phamhieu for finding this bug and providing a merge request to fix it, which I tested and approved.
cast v_constraintrec.constraint_name to text

Hey Michael,
Thanks for fixing the last bug. Found another small one:

With the following SQL:

create extension citext schema extensions;

create table public.example (
  id uuid not null primary key default gen_random_uuid(),
  name extensions.citext not null
);

The definition is showing as:

create table public.example (
    id uuid not null default gen_random_uuid (),
    name public.citext not null,
    constraint example_pk primary key (id)
  ) tablespace pg_default;

As you can see, the definition should show the type of name as extensions.citext, but it's showing as public.citext.

Hey Michael,
Me again, sorry 😅

When running select * from public.pg_get_tabledef('public','myTable', false); I get the following error:

Failed to run sql query: line=SQL statement "SELECT CASE WHEN pg_get_serial_sequence(v_qualified, v_colrec.column_name) IS NOT NULL THEN True ELSE False END" PL/pgSQL function pg_get_tabledef(character varying,character varying,boolean,tabledefs[]) line 230 at SQL statement. 42P01. relation "public.camelcase" does not exist

Thank you 🙏🏻

If I create a view with this query:

CREATE VIEW myView WITH (security_invoker=true) AS SELECT 'Hello World';

when I try to get its definition by running select pg_get_viewdef('public.myView', true) as definition I get:

[{"definition":" SELECT 'Hello World'::text AS \"?column?\";"}]

Now that I look at it more closely, the pg_get_viewdef function doesn't support views very well :(.

If you run the following SQL:

create table public.my_table (
  id bigint generated by default as identity,
  constraint my_table_pkey primary key (id)
);

create trigger my_trigger_one
after insert on my_table for each row
execute function my_func_one();

create trigger my_trigger_two
after insert on my_table for each row
execute function my_func_two();

The definition shown is:

create table public.my_table (
  id bigint generated by default as identity,
  constraint my_table_pkey primary key (id)
) tablespace pg_default;

create trigger my_trigger_one
after insert on my_table for each row
execute function my_func_one ();

my_trigger_two is missing here.

Thanks for your work on this function 🙌🏻

1> test case：
CREATE SCHEMA a;
create table a.test_parent_23210642 ( i serial );
CREATE SCHEMA b;
create table b.test_child_23210642 () inherits (a.test_parent_23210642);

2> pg_get_tabledef show result:
select * from public.pg_get_tabledef('b','test_child_23210642', false);
pg_get_tabledef

CREATE TABLE b.test_child_23210642( +
) INHERITS (b.test_parent_23210642) +
TABLESPACE pg_default; +
+

The actual parent table test_parent_23210642 is under a schema

I have a table defined like so:

CREATE TABLE IF NOT EXISTS public.achievements
(
   achievements_id bigserial ,
   ...
  apps_id bigint NULL,
  communities_id bigint NULL,
  ...
)
...
CONSTRAINT achievements_achievement_identifier_apps_id_key UNIQUE (achievement_identifier, apps_id),
CONSTRAINT chk_only_one_not_null CHECK (num_nonnulls(apps_id, communities_id) = 1)  

The other constraints are returned, however these two are not.
The ConType for the first one is 'u' and the second one is 'c'
and pg_get_constraintdef does return the proper definition for these constraints.

I'm calling the pg_get_tabledef with all triggers, external pkey, external foreign keys as I was trying to programatically be able to rebuild the table in a new database...

Currently, if you specify FKEYS_NONE, it still displays foreign keys in the table definition. Fix so that the option is respected and no foreign keys are output when so directed.

Add owner info so it really acts like pg_dump output which always includes the owner of the objects as shown by your example output using pg_dump:
ALTER TABLE sample.emp OWNER TO postgres;