SELECT
ddl
FROM
 (
 SELECT
  table_id
  ,REGEXP_REPLACE (schemaname, '^zzzzzzzz', '') AS schemaname
  ,REGEXP_REPLACE (tablename, '^zzzzzzzz', '') AS tablename
  ,seq
  ,ddl
 FROM
  (
  SELECT
   table_id
   ,schemaname
   ,tablename
   ,seq
   ,ddl
  FROM
   (
   --DROP TABLE
   SELECT
    c.oid::bigint as table_id
    ,n.nspname AS schemaname
    ,c.relname AS tablename
    ,0 AS seq
    ,'--DROP TABLE ' + QUOTE_IDENT(n.nspname) + '.' + QUOTE_IDENT(c.relname) + ';' AS ddl
   FROM pg_namespace AS n
   INNER JOIN pg_class AS c ON n.oid = c.relnamespace
   WHERE c.relkind = 'r'
   --CREATE TABLE
   UNION SELECT
    c.oid::bigint as table_id
    ,n.nspname AS schemaname
    ,c.relname AS tablename
    ,2 AS seq
    ,'CREATE TABLE IF NOT EXISTS ' + QUOTE_IDENT(n.nspname) + '.' + QUOTE_IDENT(c.relname) + '' AS ddl
   FROM pg_namespace AS n
   INNER JOIN pg_class AS c ON n.oid = c.relnamespace
   WHERE c.relkind = 'r'
   --OPEN PAREN COLUMN LIST
   UNION SELECT c.oid::bigint as table_id,n.nspname AS schemaname, c.relname AS tablename, 5 AS seq, '(' AS ddl
   FROM pg_namespace AS n
   INNER JOIN pg_class AS c ON n.oid = c.relnamespace
   WHERE c.relkind = 'r'
   --COLUMN LIST
   UNION SELECT
    table_id
    ,schemaname
    ,tablename
    ,seq
    ,'\t' + col_delim + col_name + ' ' + col_datatype + ' ' + col_nullable + ' ' + col_default + ' ' + col_encoding AS ddl
   FROM
    (
    SELECT
     c.oid::bigint as table_id
    ,n.nspname AS schemaname
     ,c.relname AS tablename
     ,100000000 + a.attnum AS seq
     ,CASE WHEN a.attnum > 1 THEN ',' ELSE '' END AS col_delim
     ,QUOTE_IDENT(a.attname) AS col_name
     ,CASE WHEN STRPOS(UPPER(format_type(a.atttypid, a.atttypmod)), 'CHARACTER VARYING') > 0
       THEN REPLACE(UPPER(format_type(a.atttypid, a.atttypmod)), 'CHARACTER VARYING', 'VARCHAR')
      WHEN STRPOS(UPPER(format_type(a.atttypid, a.atttypmod)), 'CHARACTER') > 0
       THEN REPLACE(UPPER(format_type(a.atttypid, a.atttypmod)), 'CHARACTER', 'CHAR')
      ELSE UPPER(format_type(a.atttypid, a.atttypmod))
      END AS col_datatype
     ,CASE WHEN format_encoding((a.attencodingtype)::integer) = 'none'
      THEN 'ENCODE RAW'
      ELSE 'ENCODE ' + format_encoding((a.attencodingtype)::integer)
      END AS col_encoding
     ,CASE WHEN a.atthasdef IS TRUE THEN 'DEFAULT ' + adef.adsrc ELSE '' END AS col_default
     ,CASE WHEN a.attnotnull IS TRUE THEN 'NOT NULL' ELSE '' END AS col_nullable
    FROM pg_namespace AS n
    INNER JOIN pg_class AS c ON n.oid = c.relnamespace
    INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
    LEFT OUTER JOIN pg_attrdef AS adef ON a.attrelid = adef.adrelid AND a.attnum = adef.adnum
    WHERE c.relkind = 'r'
      AND a.attnum > 0
    ORDER BY a.attnum
    )
   --CONSTRAINT LIST
   UNION (SELECT
    c.oid::bigint as table_id
    ,n.nspname AS schemaname
    ,c.relname AS tablename
    ,200000000 + CAST(con.oid AS INT) AS seq
    ,'\t,' + pg_get_constraintdef(con.oid) AS ddl
   FROM pg_constraint AS con
   INNER JOIN pg_class AS c ON c.relnamespace = con.connamespace AND c.oid = con.conrelid
   INNER JOIN pg_namespace AS n ON n.oid = c.relnamespace
   WHERE c.relkind = 'r' AND pg_get_constraintdef(con.oid) NOT LIKE 'FOREIGN KEY%%'
   ORDER BY seq)
   --CLOSE PAREN COLUMN LIST
   UNION SELECT c.oid::bigint as table_id,n.nspname AS schemaname, c.relname AS tablename, 299999999 AS seq, ')' AS ddl
   FROM pg_namespace AS n
   INNER JOIN pg_class AS c ON n.oid = c.relnamespace
   WHERE c.relkind = 'r'
   --BACKUP
   UNION SELECT
   c.oid::bigint as table_id
    ,n.nspname AS schemaname
    ,c.relname AS tablename
    ,300000000 AS seq
    ,'BACKUP NO' as ddl
 FROM pg_namespace AS n
   INNER JOIN pg_class AS c ON n.oid = c.relnamespace
   INNER JOIN (SELECT
     SPLIT_PART(key,'_',5) id
     FROM pg_conf
     WHERE key LIKE 'pg_class_backup_%%'
     AND SPLIT_PART(key,'_',4) = (SELECT
       oid
       FROM pg_database
       WHERE datname = current_database())) t ON t.id=c.oid
   WHERE c.relkind = 'r'
   --BACKUP WARNING
   UNION SELECT
   c.oid::bigint as table_id
    ,n.nspname AS schemaname
    ,c.relname AS tablename
    ,1 AS seq
    ,'--WARNING: This DDL inherited the BACKUP NO property from the source table' as ddl
 FROM pg_namespace AS n
   INNER JOIN pg_class AS c ON n.oid = c.relnamespace
   INNER JOIN (SELECT
     SPLIT_PART(key,'_',5) id
     FROM pg_conf
     WHERE key LIKE 'pg_class_backup_%%'
     AND SPLIT_PART(key,'_',4) = (SELECT
       oid
       FROM pg_database
       WHERE datname = current_database())) t ON t.id=c.oid
   WHERE c.relkind = 'r'
   --DISTSTYLE
   UNION SELECT
    c.oid::bigint as table_id
    ,n.nspname AS schemaname
    ,c.relname AS tablename
    ,300000001 AS seq
    ,CASE WHEN c.reldiststyle = 0 THEN 'DISTSTYLE EVEN'
     WHEN c.reldiststyle = 1 THEN 'DISTSTYLE KEY'
     WHEN c.reldiststyle = 8 THEN 'DISTSTYLE ALL'
     WHEN c.reldiststyle = 9 THEN 'DISTSTYLE AUTO'
     ELSE '<<Error - UNKNOWN DISTSTYLE>>'
     END AS ddl
   FROM pg_namespace AS n
   INNER JOIN pg_class AS c ON n.oid = c.relnamespace
   WHERE c.relkind = 'r'
   --DISTKEY COLUMNS
   UNION SELECT
    c.oid::bigint as table_id
    ,n.nspname AS schemaname
    ,c.relname AS tablename
    ,400000000 + a.attnum AS seq
    ,' DISTKEY (' + QUOTE_IDENT(a.attname) + ')' AS ddl
   FROM pg_namespace AS n
   INNER JOIN pg_class AS c ON n.oid = c.relnamespace
   INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
   WHERE c.relkind = 'r'
     AND a.attisdistkey IS TRUE
     AND a.attnum > 0
   --SORTKEY COLUMNS
   UNION select table_id,schemaname, tablename, seq,
        case when min_sort <0 then 'INTERLEAVED SORTKEY (' else ' SORTKEY (' end as ddl
 from (SELECT
    c.oid::bigint as table_id
    ,n.nspname AS schemaname
    ,c.relname AS tablename
    ,499999999 AS seq
    ,min(attsortkeyord) min_sort FROM pg_namespace AS n
   INNER JOIN  pg_class AS c ON n.oid = c.relnamespace
   INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
   WHERE c.relkind = 'r'
   AND abs(a.attsortkeyord) > 0
   AND a.attnum > 0
   group by 1,2,3,4 )
   UNION (SELECT
    c.oid::bigint as table_id
    ,n.nspname AS schemaname
    ,c.relname AS tablename
    ,500000000 + abs(a.attsortkeyord) AS seq
    ,CASE WHEN abs(a.attsortkeyord) = 1
     THEN '\t' + QUOTE_IDENT(a.attname)
     ELSE '\t, ' + QUOTE_IDENT(a.attname)
     END AS ddl
   FROM  pg_namespace AS n
   INNER JOIN pg_class AS c ON n.oid = c.relnamespace
   INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
   WHERE c.relkind = 'r'
     AND abs(a.attsortkeyord) > 0
     AND a.attnum > 0
   ORDER BY abs(a.attsortkeyord))
   UNION SELECT
    c.oid::bigint as table_id
    ,n.nspname AS schemaname
    ,c.relname AS tablename
    ,599999999 AS seq
    ,'\t)' AS ddl
   FROM pg_namespace AS n
   INNER JOIN  pg_class AS c ON n.oid = c.relnamespace
   INNER JOIN  pg_attribute AS a ON c.oid = a.attrelid
   WHERE c.relkind = 'r'
     AND abs(a.attsortkeyord) > 0
     AND a.attnum > 0
   --END SEMICOLON
   UNION SELECT c.oid::bigint as table_id ,n.nspname AS schemaname, c.relname AS tablename, 600000000 AS seq, ';' AS ddl
   FROM  pg_namespace AS n
   INNER JOIN pg_class AS c ON n.oid = c.relnamespace
   WHERE c.relkind = 'r' 
   --COMMENT
   UNION
   SELECT c.oid::bigint AS table_id,
        n.nspname     AS schemaname,
        c.relname     AS tablename,
        600250000     AS seq,
        ('COMMENT ON '::text + nvl2(cl.column_name, 'column '::text, 'table '::text) + quote_ident(n.nspname::text) + '.'::text + quote_ident(c.relname::text) + nvl2(cl.column_name, '.'::text + cl.column_name::text, ''::text) + ' IS \''::text + quote_ident(des.description) + '\'; '::text)::character VARYING AS ddl
   FROM pg_description des
   JOIN pg_class c ON c.oid = des.objoid
   JOIN pg_namespace n ON n.oid = c.relnamespace
   LEFT JOIN information_schema."columns" cl
   ON cl.ordinal_position::integer = des.objsubid AND cl.table_name::NAME = c.relname
   WHERE c.relkind = 'r'
 
   UNION
   --TABLE OWNERSHIP AS AN ALTER TABLE STATMENT
   SELECT c.oid::bigint as table_id ,n.nspname AS schemaname, c.relname AS tablename, 600500000 AS seq, 
   'ALTER TABLE ' + QUOTE_IDENT(n.nspname) + '.' + QUOTE_IDENT(c.relname) + ' owner to '+  QUOTE_IDENT(u.usename) +';' AS ddl
   FROM  pg_namespace AS n
   INNER JOIN pg_class AS c ON n.oid = c.relnamespace
   INNER JOIN pg_user AS u ON c.relowner = u.usesysid
   WHERE c.relkind = 'r'
   
   )
   UNION (
     SELECT c.oid::bigint as table_id,'zzzzzzzz' || n.nspname AS schemaname,
        'zzzzzzzz' || c.relname AS tablename,
        700000000 + CAST(con.oid AS INT) AS seq,
        'ALTER TABLE ' + QUOTE_IDENT(n.nspname) + '.' + QUOTE_IDENT(c.relname) + ' ADD ' + pg_get_constraintdef(con.oid)::VARCHAR(1024) + ';' AS ddl
     FROM pg_constraint AS con
       INNER JOIN pg_class AS c
              ON c.relnamespace = con.connamespace
              AND c.oid = con.conrelid
       INNER JOIN pg_namespace AS n ON n.oid = c.relnamespace
     WHERE c.relkind = 'r'
     AND con.contype = 'f'
     ORDER BY seq
   )
  ORDER BY table_id,schemaname, tablename, seq
  )
  WHERE schemaname = %(schemaname)s
 );