DUMP_DATABASE_DDL

DUMP_DATABASE_DDL() is a function that shows the CREATE statements for database objects including views and tables. Begining with 2020.3.1, DUMP_DATABASE_DDL includes foreign tables in the output.

Warning

This function does not currently show UDFs. To list available UDFs, use the catalog:

farm=> SELECT * FROM sqream_catalog.user_defined_functions;
farm,1,my_distance

Then, export UDFs one-by-one using GET_FUNCTION_DDL.

Tip

For just tables, see GET_DDL.
For just views, see GET_VIEW_DDL.
For UDFs, see GET_FUNCTION_DDL.

Permissions

The role must have the CONNECT permission at the database level.

Syntax

dump_database_ddl_statement ::=
    SELECT DUMP_DATABASE_DDL()
    ;

Parameters

This function accepts no parameters.

Examples

Getting the DDL for a database

farm=> SELECT DUMP_DATABASE_DDL();
create table "public"."cool_animals" (
  "id" int not null,
  "name" text(30) not null,
  "weight" double null,
  "is_agressive" bool default false not null
)
;

create view "public".angry_animals as
  select
      "cool_animals"."id" as "id",
      "cool_animals"."name" as "name",
      "cool_animals"."weight" as "weight",
      "cool_animals"."is_agressive" as "is_agressive"
    from
      "public".cool_animals as cool_animals
    where
      "cool_animals"."is_agressive" = false;

Exporting database DDL to a file

COPY (SELECT DUMP_DATABASE_DDL()) TO '/home/rhendricks/database.ddl';