/* ********** *

[Install]
  psql シェルでコピー＆ペーストするなどしてください。
  copy & paste in psql shell.

[Usage]
  SELECT show_create_table('table_name', 'schema_name');
    or
  SELECT show_create_table('table_name', null);
    schema_name を null にすると、全スキーマが検索の対象になります。
    If schema_name = null, all of the schema will be searched.

[Note]
  このファンクションは、SQL文1つから構成されます。
  postgreSQL の設定変更なしに利用することができます。
  This function is written in SQL.
  You do not need to re-configure your postgreSQL settings.

[Available versions]
  v8.3.8
  v8.x (maybe...)

  上記以外のバージョンで動作した場合、ご報告頂けると有り難いです。
  If this function successfully runs on your postgreSQL, please tell me your version!
    http://sourceforge.jp/forum/forum.php?forum_id=23996

 * ********** */
CREATE OR REPLACE FUNCTION show_create_table(varchar /*table*/, varchar /*schema*/) returns text as $$
/*
 * show create table for PostgreSQL v0.1
 *   2010.8.16 Tadashi Murakami
 *
 * Bug reports are welcome:
 *   http://sourceforge.jp/projects/pgshowcreatetbl/
 *
 * Licensed under the Apache License, Version 2.0.
 * You may obtain a copy of the License at
 *   http://www.apache.org/licenses/LICENSE-2.0
 */
SELECT 'CREATE TABLE ' || $1 || ' (' || chr(10) || array_to_string(ARRAY(
SELECT '  "'||"Column" ||'" '|| "Type" || "Modifiers" || "Index" FROM (
SELECT at.attnum, ns.nspname as schema, tb.relname as table, at.attname as "Column",
       /* Type */
       CASE WHEN at.attinhcount<>0 OR at.attisdropped THEN null
            ELSE
            CASE WHEN tp.typname = 'int2' THEN 'smallint'
                 WHEN tp.typname = 'int4' THEN 'integer'
                 WHEN tp.typname = 'int8' THEN 'bigint'
                 WHEN tp.typname = 'float4' THEN 'real'
                 WHEN tp.typname = 'float8' THEN 'double precision'
                 WHEN tp.typname = 'bpchar' THEN 'char'
                 ELSE tp.typname
             END ||
            CASE WHEN at.attlen >= 0        THEN ''
                 WHEN at.atttypmod < 4      THEN ''
                 WHEN tp.typname<>'numeric' THEN '('|| at.atttypmod-4 ||')'
                 WHEN (at.atttypmod & 65535) = 4 THEN '('|| (at.atttypmod >> 16) ||')'
                 ELSE '('|| (at.atttypmod >> 16) ||','|| (at.atttypmod & 65535)-4 ||')'
             END
        END as "Type",
       /* Modifiers */
       CASE WHEN at.attnotnull THEN ' not null' ELSE ''
        END ||
       CASE WHEN ad.adbin IS NULL THEN ''
            ELSE ' default ' || pg_get_expr(ad.adbin, tb.oid)
        END as "Modifiers",
       /* one-column Index */
       CASE WHEN ix.indexrelid IS NULL THEN ''
            ELSE
            CASE WHEN ix.indisprimary THEN ' PRIMARY KEY'
                 WHEN ix.indisunique  THEN ' UNIQUE'
                 ELSE ' /* index */'
             END ||
            CASE WHEN am.amname <> 'btree' THEN ' /*'||am.amname||'*/' ELSE ''
             END
            || ' /* '|| ic.relname ||' */'
        END as "Index"
--     , tb.*, at.*, tp.*
  FROM pg_class tb
--
-- for Type, Modifiers
--
 INNER JOIN pg_attribute at ON tb.oid = at.attrelid
 INNER JOIN pg_type      tp ON at.atttypid = tp.oid
  LEFT OUTER JOIN pg_attrdef ad ON ad.adrelid = tb.oid AND ad.adnum = at.attnum
 INNER JOIN pg_namespace ns ON tb.relnamespace = ns.oid
--
-- for one-column Index
--
  LEFT OUTER JOIN pg_index ix ON ix.indrelid = tb.oid AND ix.indnatts = 1 AND at.attnum = ix.indkey[0]
  LEFT OUTER JOIN pg_class ic ON ix.indexrelid = ic.oid
  LEFT OUTER JOIN pg_am    am ON ic.relam = am.oid
--
 WHERE at.attnum >= 1
   AND tb.relname = $1
   AND ($2 IS NULL OR ns.nspname = $2)
) as columns ORDER BY attnum), ','||chr(10))
--
--
-- multi-columns Index
--
--
|| (SELECT
CASE WHEN count(*) = 0 THEN ''
     ELSE ','||chr(10)||'  '||
-- SELECT
  array_to_string(ARRAY(
  SELECT CASE WHEN indisprimary THEN 'PRIMARY KEY ' ELSE 'UNIQUE ' END
      || substr(indexdef, strpos(indexdef, '('), strpos(indexdef, ')') - strpos(indexdef, '(') + 1)
      || ' /* '||index||' */'
   FROM (
  SELECT ic.relname as index, ns.nspname as schema, tb.relname as table -- , ia.attname, ia.attnum
       , ix.indnatts, ix.indisunique, ix.indisprimary, am.amname
       , ix.indkey
       , pg_get_indexdef(ic.oid) as indexdef
  --     , pg_get_expr(ix.indexprs, ix.indrelid), pg_get_expr(ix.indpred, ix.indrelid)
    FROM pg_class tb
   INNER JOIN pg_index ix ON ix.indrelid = tb.oid
   INNER JOIN pg_class ic ON ix.indexrelid = ic.oid
   INNER JOIN pg_am    am ON ic.relam = am.oid
  --  LEFT OUTER JOIN pg_attribute ia ON ia.attnum = any(ix.indkey) AND ia.attrelid = tb.oid
   INNER JOIN pg_namespace ns ON tb.relnamespace = ns.oid
   WHERE ix.indnatts > 1
     AND (ix.indisprimary OR ix.indisunique)
     AND tb.relname = $1
     AND ($2 IS NULL OR ns.nspname = $2)
  ) as def ORDER BY indisprimary desc, index), ','||chr(10))
 END
-- copy from the above conditions
  FROM pg_class tb
 INNER JOIN pg_index ix ON ix.indrelid = tb.oid
 INNER JOIN pg_namespace ns ON tb.relnamespace = ns.oid
 WHERE ix.indnatts > 1
   AND (ix.indisprimary OR ix.indisunique)
   AND tb.relname = $1
   AND ($2 IS NULL OR ns.nspname = $2)
)||
chr(10) || ')'
$$ language sql;