pgsql/schema_create_table_statement.sql

[Pman.Core] / pgsql / schema_create_table_statement.sql

diff --git a/pgsql/schema_create_table_statement.sql b/pgsql/schema_create_table_statement.sql
index 1fc8006..0d40f1d 100644 (file)
--- a/pgsql/schema_create_table_statement.sql
+++ b/pgsql/schema_create_table_statement.sql
@@ -4,6 +4,7 @@ $BODY$
 DECLARE
     v_table_ddl   text;
     column_record record;
+    v_schema text;
 BEGIN
     FOR column_record IN 
         SELECT 
@@ -54,19 +55,68 @@ BEGIN
         ORDER BY a.attnum
     LOOP
         IF column_record.attnum = 1 THEN
-            v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' ();';
+            v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' ();'||chr(10)||chr(10);
         ELSE
-            v_table_ddl:= 'ALTER TABLE '||column_record.schema_name||'.'||column_record.table_name|| ' ADD COLUMN ' || v_table_ddl||';';
+            
         END IF;
-
+        -- what does this do?
+        v_schema := column_record.schema_name;
+         
         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;
+            v_table_ddl:= v_table_ddl||chr(10)||
+                     'ALTER TABLE  '||column_record.schema_name||'.'||column_record.table_name||
+                     ' ADD COLUMN ' ||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null || ';';
+        END IF;
+    END LOOP;
+    
+    FOR column_record IN 
+        
+        c.relname as iname,
+        CASE c.relkind
+            WHEN 'r' THEN 'table'
+            WHEN 'v' THEN 'view'
+            WHEN 'i' THEN 'index'
+            WHEN 'S' THEN 'sequence'
+            WHEN 's' THEN 'special' END as itype,
+          
+            c2.relname as itable
+        FROM pg_catalog.pg_class c
+             JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
+             JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
+             LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
+             LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
+        WHERE c.relkind IN ('i','')
+            AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
+            AND pg_catalog.pg_table_is_visible(c.oid)
+            AND c2.relname  = p_table_name
+        
+    LOOP
+         
+            v_table_ddl:= v_table_ddl||chr(10)||
+                     'CREATE INDEX ' ||  column_record.iname || 'ON '||v_schema||'.'||p_table_name|
+                     ' ADD COLUMN ' ||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||');';
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+    --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
+  LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;
+  
+SELECT schema_create_table_statement('item');
+  
\ No newline at end of file