Get the object SQL definition (DDL)

Products

• VMware Tanzu Greenplum

Issue/Introduction

How to retrieve object definition in SQL for:

• DDL inspection
• DDL backup
• object re-creation
• providing DDL for inspection by GPDB Support

Resolution

Use the pg_dump schema capabilities.

Example

####mac:pg_log #####$ pg_dump -s -t tt1
--
-- Greenplum Database database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: tt1; Type: TABLE; Schema: public; Owner: ######; Tablespace: 
--

CREATE TABLE tt1 (
    a integer,
    b text
) DISTRIBUTED BY (a);


ALTER TABLE public.tt1 OWNER TO ######;

--
-- Greenplum Database database dump complete
--

This also works on other types of objects.

######mac:pg_log ######$ pg_dump -s -t v1
--
-- Greenplum Database database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_with_oids = false;

--
-- Name: v1; Type: VIEW; Schema: public; Owner: ######
--

CREATE VIEW v1 AS
    SELECT tt1.a, tt1.b FROM tt1;


ALTER TABLE public.v1 OWNER TO #####;

--
-- Greenplum Database database dump complete
--
lpetrovmac:pg_log lpetrov$ pg_dump -s -t s1
--
-- Greenplum Database database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_with_oids = false;

--
-- Name: s1; Type: SEQUENCE; Schema: public; Owner: #######
--

CREATE SEQUENCE s1
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


ALTER TABLE public.s1 OWNER TO ######;

--
-- Greenplum Database database dump complete
--

Additional Information