Seeing system objects as DDL

Dump specific objects

Tables

See GET_DDL for more information.

Examples

Getting the DDL for a table

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

Exporting table DDL to a file

COPY (SELECT GET_DDL('cool_animals')) TO '/home/rhendricks/animals.ddl';

Views

See GET_VIEW_DDL for more information.

Examples

Listing all views

farm=> SELECT view_name FROM sqream_catalog.views;
view_name
----------------------
angry_animals
only_agressive_animals

Getting the DDL for a view

farm=> SELECT GET_VIEW_DDL('angry_animals');
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 view DDL to a file

COPY (SELECT GET_VIEW_DDL('angry_animals')) TO '/home/rhendricks/angry_animals.sql';

User defined functions

See GET_FUNCTION_DDL for more information.

Examples

Listing all UDFs

master=> SELECT * FROM sqream_catalog.user_defined_functions;
database_name | function_id | function_name
--------------+-------------+--------------
master        |           1 | my_distance

Getting the DDL for a function

master=> SELECT GET_FUNCTION_DDL('my_distance');
create function "my_distance" (x1 float,
                            y1 float,
                            x2 float,
                            y2 float) returns float as
   $$
   import  math
   if  y1  <  x1:
       return  0.0
   else:
       return  math.sqrt((y2  -  y1)  **  2  +  (x2  -  x1)  **  2)
   $$
   language python volatile;

Exporting function DDL to a file

COPY (SELECT GET_FUNCTION_DDL('my_distance')) TO '/home/rhendricks/my_distance.sql';

Saved queries

See LIST_SAVED_QUERIES, SHOW_SAVED_QUERY for more information.

Dump entire database DDLs

Dumping the database DDL includes tables and views, but not UDFs and saved queries.

See DUMP_DATABASE_DDL for more information.

Examples

Exporting database DDL to a client

farm=> SELECT DUMP_DATABASE_DDL();
create table "public"."cool_animals" (
  "id" int not null,
  "name" varchar(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';

Note

To export data in tables, see COPY TO.