/* ********** *
http://sourceforge.jp/projects/pgshowcreatetbl/

 * This SQL function returns a CREATE TABLE statement for the table assigned.
 * PostgreSQL にて CREATE TABLE 文を取得するための SQL 関数です。
   PostgreSQL では pg_dump --schema-only コマンドで CREATE TABLE 文を取得できますが、
   DB 接続や psql コンソールなどから取得することができません。
   本 SQL は、それを実現するためのものです。


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


= Usage =
{{{ code sql
  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 の設定変更なしに利用することができます。
   * '''PHP, Java, Ruby 等、お好きな言語に組み込むことができます。'''
     1. $1, $2 を言語ごとの変数にする
     2. ダブルクオーテーションをエスケープする (" -> \")
 * This function is written in SQL. This brings the features that:
   * You do not need to re-configure your PostgreSQL settings.
   * '''You can embed this function in your preferred languages: PHP, Java, Ruby, etc...'''
     1. replace $1 and $2 into variables of the language you are going to use.
     2. escape the double-quotations (" -> \")


= 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


= known bugs =
 * see http://sourceforge.jp/projects/pgshowcreatetbl/ticket/?type[]=19647&type_mode[]=eq&status[]=1&view=type

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

CREATE OR REPLACE FUNCTION show_create_table(varchar /*table*/, varchar /*schema*/) returns text as $$
/*
 * show create table for PostgreSQL v0.2
 *   2010.9.2 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 CASE
  WHEN tb.relkind='r' THEN /*0*/(
SELECT 'CREATE TABLE ' || $1 || ' (' || chr(10) || array_to_string/*1*/(ARRAY/*2*/(
SELECT '  "'||"Column" ||'" '|| "Type" || "Modifiers" || "Index" FROM /*3*/(
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_attribute at
--
-- for Type, Modifiers
--
 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
--
-- 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 tb.oid = at.attrelid
   AND at.attnum >= 1
)/*3*/ as columns ORDER BY attnum)/*2*/, ','||chr(10))/*1*/
--
--
-- multi-columns Index
--
--
|| /*1*/(SELECT
CASE WHEN count(*) = 0 THEN ''
     ELSE ','||chr(10)||'  '||
  array_to_string/*2*/(ARRAY/*3*/(
  SELECT CASE WHEN indisprimary THEN 'PRIMARY KEY ' ELSE 'UNIQUE ' END
      || substr(indexdef, strpos(indexdef, '('), strpos(indexdef, ')') - strpos(indexdef, '(') + 1)
      || ' /* '||index||' */'
   FROM /*4*/(
  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_index ix
   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
   WHERE ix.indrelid = tb.oid
     AND ix.indnatts > 1
     AND (ix.indisprimary OR ix.indisunique)
  )/*4*/ as def ORDER BY indisprimary desc, index)/*3*/, ','||chr(10))/*2*/
 END
-- copy from the above conditions
  FROM pg_index ix
 WHERE ix.indrelid = tb.oid
   AND ix.indnatts > 1
   AND (ix.indisprimary OR ix.indisunique)
)/*1*/ || chr(10) || ')'
)/*0*/
  WHEN tb.relkind='v' THEN
       'CREATE VIEW ' || $1 || ' AS (' || chr(10)
       || trim(trailing ';' from pg_get_viewdef(tb.oid)) || chr(10) || ')'
  ELSE '/* pg_class.relkind='''||tb.relkind||''' */'
   END
 as show_create_table
  FROM pg_class tb
 INNER JOIN pg_namespace ns ON tb.relnamespace = ns.oid
 WHERE tb.relname = $1
   AND ($2 IS NULL OR ns.nspname = $2)
$$ language sql;