Pivotal Knowledge Base

Follow

Get Object SQL Definition (DDL)

Goal

Need to retrieve object definition in SQL for:

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

Solution

Use pg_dump schema capabilities.

Example

lpetrovmac:pg_log lpetrov$ 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: lpetrov; Tablespace: 
--

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


ALTER TABLE public.tt1 OWNER TO lpetrov;

--
-- Greenplum Database database dump complete
--

This also works on other types of objects

lpetrovmac:pg_log lpetrov$ 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: lpetrov
--

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


ALTER TABLE public.v1 OWNER TO lpetrov;

--
-- 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: lpetrov
--

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


ALTER TABLE public.s1 OWNER TO lpetrov;

--
-- Greenplum Database database dump complete
--

Comments

Powered by Zendesk