From 1f67771533ea58a8f6304bb14b9f0839482b902b Mon Sep 17 00:00:00 2001
From: Alan Knowles <alan@roojs.com>
Date: Tue, 7 Oct 2014 18:46:19 +0800
Subject: [PATCH] pgsql/schema_create_table_statement.sql

---
 pgsql/schema_create_table_statement.sql | 72 +++++++++++++++++++++++++
 1 file changed, 72 insertions(+)
 create mode 100644 pgsql/schema_create_table_statement.sql

diff --git a/pgsql/schema_create_table_statement.sql b/pgsql/schema_create_table_statement.sql
new file mode 100644
index 00000000..228f73f3
--- /dev/null
+++ b/pgsql/schema_create_table_statement.sql
@@ -0,0 +1,72 @@
+CREATE OR REPLACE FUNCTION generate_create_table_statement(p_table_name varchar)
+  RETURNS text AS
+$BODY$
+DECLARE
+    v_table_ddl   text;
+    column_record record;
+BEGIN
+    FOR column_record IN 
+        SELECT 
+            b.nspname as schema_name,
+            b.relname as table_name,
+            a.attname as column_name,
+            pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
+            CASE WHEN 
+                (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
+                 FROM pg_catalog.pg_attrdef d
+                 WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
+                'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
+                              FROM pg_catalog.pg_attrdef d
+                              WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
+            ELSE
+                ''
+            END as column_default_value,
+            CASE WHEN a.attnotnull = true THEN 
+                'NOT NULL'
+            ELSE
+                'NULL'
+            END as column_not_null,
+            a.attnum as attnum,
+            e.max_attnum as max_attnum
+        FROM 
+            pg_catalog.pg_attribute a
+            INNER JOIN 
+             (SELECT c.oid,
+                n.nspname,
+                c.relname
+              FROM pg_catalog.pg_class c
+                   LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
+              WHERE c.relname ~ ('^('||p_table_name||')$')
+                AND pg_catalog.pg_table_is_visible(c.oid)
+              ORDER BY 2, 3) b
+            ON a.attrelid = b.oid
+            INNER JOIN 
+             (SELECT 
+                  a.attrelid,
+                  max(a.attnum) as max_attnum
+              FROM pg_catalog.pg_attribute a
+              WHERE a.attnum > 0 
+                AND NOT a.attisdropped
+              GROUP BY a.attrelid) e
+            ON a.attrelid=e.attrelid
+        WHERE a.attnum > 0 
+          AND NOT a.attisdropped
+        ORDER BY a.attnum
+    LOOP
+        IF column_record.attnum = 1 THEN
+            v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' (';
+        ELSE
+            v_table_ddl:=v_table_ddl||',';
+        END IF;
+
+        IF column_record.attnum <= column_record.max_attnum THEN
+            v_table_ddl:=v_table_ddl||chr(10)||
+                     '    '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null;
+        END IF;
+    END LOOP;
+
+    v_table_ddl:=v_table_ddl||');';
+    RETURN v_table_ddl;
+END;
+$BODY$
+  LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;
\ No newline at end of file
-- 
2.39.2