[Redshift] 既存テーブルのDDLを生成する

- gen.sh
``` bash
#!/bin/sh
#
# [Usage]
#   args
#    1. User ID
#    2. Password
#    3. Host
#    4. Database Name
#    5. Schema Name
#    6. Table Name
#
#   ex
#    $ ./gen.sh user01 Password xxx.ap-northeast-1.redshift.amazonaws.com dwh schema01 table01
#

echo "-- 注意!--------------------------"
echo "-- 外部キーのDDLは出力されません!"
echo "----------------------------------"

export PGPASSWORD=$2
SCHEMA_NAME="$(tr '[:upper:]' '[:lower:]' <<< $5)"
TABLE_NAME="$(tr '[:upper:]' '[:lower:]' <<< $6)"


cat ./sql/gen_create_table.sql | sed "s/<スキーマ名>/$SCHEMA_NAME/g" | sed "s/<テーブル名>/$TABLE_NAME/g" > ./tmp/gen_create_table.sql.$$
psql -t -q -h $3 -U $1 -d $4 -p 5439 -f ./tmp/gen_create_table.sql.$$ > ./tmp/gen_create_table.sql.$$.log
if [ `wc -c < ./tmp/gen_create_table.sql.$$.log` -gt 51 ]; then
  psql -t -q -h $3 -U $1 -d $4 -p 5439 -f ./tmp/gen_create_table.sql.$$ | perl -pe 's/\n/|/g' | sed 's/SORTKEY (| )|//' | sed 's/,| )/| )/g' | sed 's/|/\n/g' | sed 's/^\s//g' | sed '/^$/d'
  echo ";"
  echo ""
fi

cat ./sql/gen_add_primary_key.sql | sed "s/<スキーマ名>/$SCHEMA_NAME/g" | sed "s/<テーブル名>/$TABLE_NAME/g" > ./tmp/gen_add_primary_key.sql.$$
psql -t -q -h $3 -U $1 -d $4 -p 5439 -f ./tmp/gen_add_primary_key.sql.$$ > ./tmp/gen_add_primary_key.sql.$$.log
if [ `wc -c < ./tmp/gen_add_primary_key.sql.$$.log` -gt 1 ]; then
  echo "ALTER TABLE <スキーマ名>.<テーブル名>" | sed "s/<スキーマ名>/$SCHEMA_NAME/g" | sed "s/<テーブル名>/$TABLE_NAME/g"
  echo "  ADD CONSTRAINT <テーブル名>_pk" | sed "s/<テーブル名>/$TABLE_NAME/g"
  echo "  PRIMARY KEY ("
  cat ./tmp/gen_add_primary_key.sql.$$.log | perl -pe 's/\n/|/g' | sed 's/,||/|/g' | sed 's/|/\n/g'
  echo ")"
  echo ";"
fi

cat ./sql/gen_add_unique_key.sql | sed "s/<スキーマ名>/$SCHEMA_NAME/g" | sed "s/<テーブル名>/$TABLE_NAME/g" > ./tmp/gen_add_unique_key.sql.$$
psql -t -q -h $3 -U $1 -d $4 -p 5439 -f ./tmp/gen_add_unique_key.sql.$$ > ./tmp/gen_add_unique_key.sql.$$.log
if [ `wc -c < ./tmp/gen_add_unique_key.sql.$$.log` -gt 1 ]; then
  cat ./tmp/gen_add_unique_key.sql.$$.log | sed 's/|//g' | sed 's/^\s*//g' | sed 's/\s*$//g' | sed 's/\s\s//g' | tr '\n' ' ' | sed 's/ALTER TABLE/\nALTER TABLE/g' | sed 's/,\s*$/);\n/g'
fi
```

- gen_create_table.sql
``` sql
SET SEARCH_PATH TO <スキーマ名>;

SELECT
  sqlstmt
FROM (

  /** CREATE TABLE文: 作成テーブル名の宣言 */
  (SELECT 0 AS attnum, 'CREATE TABLE ' || '<スキーマ名>.<テーブル名> (' AS sqlstmt)
  UNION ALL

  /** CREATE TABLE文: テーブル内各項目定義 */
  (
  SELECT
    def_comment.attnum AS attnum,
    '  '
    || pg_table_def.column
    || ' '
    || UPPER(pg_table_def.type)
    || ' ENCODE ' || CASE pg_table_def.encoding WHEN 'none' THEN 'raw' ELSE pg_table_def.encoding END
    || CASE pg_table_def.notnull WHEN TRUE THEN ' NOT NULL' ELSE '' END
    || ',' AS sqlstmt
  FROM
    pg_table_def,
    (
    SELECT
      pg_stat_user_tables.schemaname AS schema,
      pg_stat_user_tables.relname    AS tablename,
      tablecom.description           AS table_comment,
      pg_attribute.attname           AS columnname,
      colcom.description             AS column_comment,
      pg_attribute.attnum,
      pg_attrdef.adsrc               AS default
    FROM
      pg_stat_user_tables, pg_type, pg_attribute
      LEFT JOIN pg_description tablecom ON pg_attribute.attrelid = tablecom.objoid AND tablecom.objsubid = 0
      LEFT JOIN pg_description colcom ON pg_attribute.attnum = colcom.objsubid AND pg_attribute.attrelid = colcom.objoid
      LEFT JOIN pg_attrdef ON pg_attribute.attrelid = pg_attrdef.adrelid AND pg_attribute.attnum = pg_attrdef.adnum
    WHERE
      pg_attribute.attrelid = pg_stat_user_tables.relid
      AND pg_attribute.atttypid = pg_type.oid
      AND pg_attribute.attnum > 0
      AND pg_stat_user_tables.relname IN (
        SELECT pg_class.relname
        FROM
          pg_namespace
          JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
        WHERE
          pg_class.relkind = 'r'
          AND pg_class.relname NOT LIKE 'pg_%'
          AND pg_class.relname NOT LIKE 'sql_%')
      AND pg_stat_user_tables.schemaname = '<スキーマ名>'
      AND pg_stat_user_tables.relname = '<テーブル名>'
    ORDER BY
      pg_stat_user_tables.schemaname,
      pg_stat_user_tables.relname,
      pg_attribute.attnum
   ) def_comment
  WHERE
    pg_table_def.tablename = def_comment.tablename
    AND pg_table_def.column = def_comment.columnname
    AND pg_table_def.schemaname = '<スキーマ名>'
    AND pg_table_def.tablename = '<テーブル名>'
  ORDER BY
    def_comment.schema,
    def_comment.tablename,
    def_comment.attnum
  )

  /** CREATE TABLE文: 各種項目定義終わり */
  UNION ALL
  (SELECT 1000 AS attnum, ')' AS sqlstmt)

  /** CREATE TABLE文: 分散スタイル指定 */
  UNION ALL
  (
  SELECT DISTINCT
    2000 AS attnum,
    CASE pg_class.reldiststyle
    /** 1:KEY(分散キー指定) */WHEN 1 THEN 'DISTKEY (' || pg_table_def.column || ')'
    /** 0:EVEN */WHEN 0 THEN 'DISTSTYLE EVEN' /** OR '' */
    END AS sqlstmt
  FROM
    pg_table_def,
    pg_class
  WHERE
    pg_table_def.schemaname = '<スキーマ名>'
    AND pg_table_def.tablename = '<テーブル名>'
    AND pg_table_def.distkey = TRUE
    AND pg_table_def.tablename = pg_class.relname
  )
  UNION ALL
  (
  SELECT DISTINCT attnum, sqlstmt
    FROM
    (SELECT
      2000 AS attnum,
      CASE pg_class.reldiststyle
        /** 8:ALL */WHEN 8 THEN 'DISTSTYLE ALL'
      END AS sqlstmt
    FROM
      pg_table_def,
      pg_class
    WHERE
      pg_table_def.schemaname = '<スキーマ名>'
      AND pg_table_def.tablename = '<テーブル名>'
      AND pg_table_def.distkey = FALSE
      AND pg_table_def.tablename = pg_class.relname)
  )
  /** CREATE TABLE文: ソートキー指定 */
  UNION ALL
  (
  SELECT sortkey AS attnum, sortitemtable.sortitem AS sqlstmt FROM
    (SELECT sortkey, sortitem FROM
      (SELECT 20000 AS sortkey, 'SORTKEY (' AS sortitem)
      UNION ALL
      (
      SELECT
        20100 + pg_table_def.sortkey,
        '  ' || pg_table_def.column || ',' AS sortitem
      FROM
        pg_table_def
      WHERE
        pg_table_def.schemaname = '<スキーマ名>'
        AND pg_table_def.tablename = '<テーブル名>'
        AND pg_table_def.sortkey != 0
      ORDER BY
        pg_table_def.sortkey
      )
      UNION ALL
      (SELECT 21000 AS sortkey, ')'AS sortitem)
    ) sortitemtable)
  )
ORDER BY attnum;
```

- gen_add_primary_key.sql
``` sql
SET SEARCH_PATH TO <スキーマ名>;

SELECT
  '   ' || attname || ','
FROM
  pg_constraint r1
  ,pg_namespace r2
  ,pg_class r3
  ,pg_attribute r4
WHERE
  contype = 'p'
  AND conrelid = r3.oid
  AND conrelid = r4.attrelid
  AND r3.relnamespace = r2.oid
  AND r4.attnum = ANY(r1.conkey)
  AND nspname = '<スキーマ名>'
  AND relname = '<テーブル名>'
ORDER BY
  CHARINDEX('|' || attnum || '|', '|' || array_to_string(conkey, '|') || '|')
```

- gen_add_unique_key.sql
``` sql
SET SEARCH_PATH TO <スキーマ名>;

SELECT
  CASE WHEN CHARINDEX('|' || attnum || '|', '|' || array_to_string(conkey, '|') || '|') = 1 THEN 'ALTER TABLE <スキーマ名>.<テーブル名> ADD UNIQUE (' ELSE '' END
  ,attname || ','
FROM
  pg_constraint r1
  ,pg_namespace r2
  ,pg_class r3
  ,pg_attribute r4
WHERE
  contype = 'u'
  AND conrelid = r3.oid
  AND conrelid = r4.attrelid
  AND r3.relnamespace = r2.oid
  AND r4.attnum = ANY(r1.conkey)
  AND nspname = '<スキーマ名>'
  AND relname = '<テーブル名>'
ORDER BY
  conname
  ,CHARINDEX('|' || attnum || '|', '|' || array_to_string(conkey, '|') || '|')
```