git.roojs.org Git - Pman.Core/blob - pgsql/schema_create_table_statement.sql

   1 CREATE OR REPLACE FUNCTION schema_create_table_statement(p_table_name varchar)
   2   RETURNS text AS
   3 $BODY$
   4 DECLARE
   5     v_table_ddl   text;
   6     column_record record;
   7     v_schema text;
   8 BEGIN
   9     FOR column_record IN
  10         SELECT
  11             b.nspname as schema_name,
  12             b.relname as table_name,
  13             a.attname as column_name,
  14             pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
  15             CASE WHEN
  16                 (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
  17                  FROM pg_catalog.pg_attrdef d
  18                  WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
  19                 'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
  20                               FROM pg_catalog.pg_attrdef d
  21                               WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
  22             ELSE
  23                 ''
  24             END as column_default_value,
  25             CASE WHEN a.attnotnull = true THEN
  26                 'NOT NULL'
  27             ELSE
  28                 'NULL'
  29             END as column_not_null,
  30             a.attnum as attnum,
  31             e.max_attnum as max_attnum
  32         FROM
  33             pg_catalog.pg_attribute a
  34             INNER JOIN
  35              (SELECT c.oid,
  36                 n.nspname,
  37                 c.relname
  38               FROM pg_catalog.pg_class c
  39                    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  40               WHERE c.relname ~ ('^('||p_table_name||')$')
  41                 AND pg_catalog.pg_table_is_visible(c.oid)
  42               ORDER BY 2, 3) b
  43             ON a.attrelid = b.oid
  44             INNER JOIN
  45              (SELECT
  46                   a.attrelid,
  47                   max(a.attnum) as max_attnum
  48               FROM pg_catalog.pg_attribute a
  49               WHERE a.attnum > 0
  50                 AND NOT a.attisdropped
  51               GROUP BY a.attrelid) e
  52             ON a.attrelid=e.attrelid
  53         WHERE a.attnum > 0
  54           AND NOT a.attisdropped
  55         ORDER BY a.attnum
  56     LOOP
  57         IF column_record.attnum = 1 THEN
  58             v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' ();'||chr(10)||chr(10);
  59         ELSE
  60
  61         END IF;
  62         -- what does this do?
  63         v_schema := column_record.schema_name;
  64
  65         IF column_record.attnum <= column_record.max_attnum THEN
  66             v_table_ddl:= v_table_ddl||chr(10)||
  67                      'ALTER TABLE  '||column_record.schema_name||'.'||column_record.table_name||
  68                      ' ADD COLUMN ' ||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null || ';';
  69         END IF;
  70     END LOOP;
  71       v_table_ddl:= v_table_ddl||chr(10);
  72     FOR column_record IN
  73         SELECT
  74                 c.oid,
  75                 c.relname,
  76                 a.attname,
  77                 a.attnum,
  78                 CASE
  79                     WHEN i.indisprimary THEN 'ALTER TABLE '||v_schema||'.'||p_table_name||' ADD CONSTRAINT ' || c.relname || ' PRIMARY KEY (' || a.attname || ');'
  80
  81                     WHEN i.indisunique THEN 'CREATE UNIQUE INDEX '|| c.relname || ' ON ' ||v_schema||'.'||p_table_name||' USING btree (' || a.attname || ');'
  82                     ELSE
  83                     'CREATE INDEX '|| c.relname || ' ON ' ||v_schema||'.'||p_table_name||' USING btree (' || a.attname || ' );'
  84                 END as iline
  85             FROM pg_index AS i, pg_class AS c, pg_attribute AS a
  86             WHERE i.indexrelid = c.oid AND i.indexrelid = a.attrelid AND i.indrelid = (v_schema|| '.' || p_table_name)::regclass
  87
  88     LOOP
  89
  90             v_table_ddl:= v_table_ddl||chr(10)||
  91                       column_record.iline;
  92
  93     END LOOP;
  94   v_table_ddl:= v_table_ddl||chr(10);
  95     -- constraints...
  96      FOR column_record IN
  97
  98         SELECT
  99             tc.constraint_name, tc.table_name, kcu.column_name,
 100             ccu.table_name AS foreign_table_name,
 101             ccu.column_name AS foreign_column_name
 102         FROM
 103             information_schema.table_constraints AS tc
 104             JOIN information_schema.key_column_usage AS kcu
 105               ON tc.constraint_name = kcu.constraint_name
 106             JOIN information_schema.constraint_column_usage AS ccu
 107               ON ccu.constraint_name = tc.constraint_name
 108         WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name=p_table_name
 109
 110     LOOP
 111
 112             v_table_ddl:= v_table_ddl||chr(10)||
 113                       'ALTER TABLE ' ||v_schema||'.'||p_table_name||' ADD CONSTRAINT ' || column_record.constraint_name ||
 114                           ' FOREIGN KEY (' || column_record.column_name || ') REFERENCES ' || column_record.foreign_table_name || '(' || column_record.foreign_column_name || ') MATCH SIMPLE;';
 115
 116     END LOOP;
 117
 118
 119
 120
 121
 122
 123
 124     v_table_ddl:= v_table_ddl||chr(10);
 125
 126
 127     --v_table_ddl:=v_table_ddl||');';
 128     RETURN v_table_ddl;
 129 END;
 130 $BODY$
 131   LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;
 132
 133 SELECT schema_create_table_statement('item');
 134