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

= show create table for PostgreSQL =
 * 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 は、それを実現するためのものです。


= Features =
 * このファンクションは、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...'''
     * example： 1) replace $1 and $2 into variables of the language you are going to use, 2) escape the double-quotations (" -> \")


= 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.


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


= Available versions =
 * v8.3.8
 * v8.3.x (probably...)
 * 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.6
 *   2010.9.3 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,2*/ARRAY(
    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
    ), ','||chr(10))/*2,1*/
  --
  --
  -- multi-columns Index
  --
  --
  ||(/*1*/
    SELECT
    CASE WHEN count(*) = 0 THEN ''
         ELSE ','||chr(10)||'  '|| array_to_string
      (/*2,3*/ARRAY(
      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), ','||chr(10)
      )/*3,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
  (/*0*/
    ($2 IS NOT NULL AND ns.nspname = $2)
      OR
    (/*1,2*/$2 IS NULL AND ns.nspname = (
    SELECT ns.nspname
    --  FROM pg_class tb, pg_namespace ns
    -- WHERE tb.relname = $1
    --   AND tb.relnamespace = ns.oid
     WHERE strpos(replace(current_setting('search_path'), '"$user"', current_user), ns.nspname) =
      (/*3*/
      SELECT MIN(strpos(replace(current_setting('search_path'), '"$user"', current_user), ns2.nspname))
        FROM pg_class tb2, pg_namespace ns2
       WHERE tb2.relname = $1
         AND tb2.relnamespace = ns2.oid
      )/*3*/
    ))/*2,1*/
  )/*0*/
$$ language sql;