
CREATE OR REPLACE PROCEDURE p_AlterColumn
(
    TableName    IN VARCHAR,
    ColumnName   IN VARCHAR,
    ColumnType   IN VARCHAR,
    ColumnIsNull IN VARCHAR,
    ModifyFlag   IN VARCHAR,
    DefaultValue IN VARCHAR = 'NULL'
)
AS
$BODY$
DECLARE
  v_str           varchar(2000);
  v_defaultvalue  varchar(100);
  v_defaultsql    varchar(110);
  v_NewColumnName varchar(30);
  v_ColumnType varchar(30);
  v_existColumn bigint;
  v_DbColIsNull		varchar(10) := 'NULL';
  num   bigint:=1;
  idx_type RECORD;
   v_idxName varchar(200)[];
   v_idxUnique varchar(200)[];
   v_idxCols varchar(1000)[];
   pk varchar(200);
   clustered varchar(200);
   createIdxSubscript integer[];
   suffix  integer;
   i integer;
   j integer;
   x varchar(200);
   y varchar(200);
   existsIdx bool;
   idx_row RECORD;

BEGIN
	IF ColumnType = 'DATETIME' THEN
		v_ColumnType := 'TIMESTAMP';
	ELSIF ColumnType = 'INT' THEN
		v_ColumnType := 'INTEGER';
	ELSIF ColumnType = 'IMAGE' THEN
		v_ColumnType := 'BYTEA';
	ELSIF ColumnType = 'NTEXT' THEN
		v_ColumnType := 'TEXT';
	ELSIF ColumnType = 'BLOB' THEN
		v_ColumnType := 'BYTEA';
	ELSIF ColumnType = 'NCLOB' THEN
		v_ColumnType := 'TEXT';
	ELSE
		v_ColumnType := ColumnType;
	END IF;
    v_ColumnType := REPLACE(v_ColumnType, 'NVARCHAR', 'VARCHAR');

  TableName = lower(TableName);
  ColumnName = lower(ColumnName);
    IF DefaultValue = 'NULL' OR DefaultValue is null THEN
        v_defaultsql := ' ';
        v_defaultvalue = 'NULL';
    ELSE
        IF DefaultValue = 'GETDATE()' THEN
            v_defaultvalue := ' LOCALTIMESTAMP ';
        ELSIF DefaultValue = 'NEWID()' THEN
            v_defaultvalue := ' uuid_generate_v4() ';
        ELSE
            IF SUBSTRING(DefaultValue from 1 for 3) = '{ts' THEN
                v_defaultvalue := REPLACE(REPLACE(DefaultValue, '{ts', ''), '}', '');
            ELSE
                v_defaultvalue := DefaultValue;
            END IF;
        END IF;
		IF v_defaultvalue IS NOT NULL AND char_length(v_defaultvalue) > 0 THEN
	        v_defaultsql := ' DEFAULT ' || v_defaultvalue;
		ELSE
	        v_defaultsql := ' ';
		END IF;
    END IF;
	IF SUBSTR(ModifyFlag,1,1) = '1' THEN
		IF v_ColumnType = 'NULL' THEN
			EXECUTE 'SELECT count(1) FROM information_schema.columns WHERE table_name = $1 AND column_name = $2' INTO v_existColumn USING TableName, ColumnName;
			if v_existColumn = 1 THEN
				v_str := ' alter table ' || TableName || ' drop column ' || ColumnName || ' ';
				EXECUTE v_str;
			END IF;
		ELSE
			v_NewColumnName := REPLACE(ColumnName, 'f', 'n');
			if v_NewColumnName = ColumnName then
				if length(ColumnName) = 30 then
					v_NewColumnName := 'f' || substr(ColumnName, 2, 30);
				else
					v_NewColumnName := 'f' || ColumnName;
				end if;
			end if;

			suffix := 0;

			j:=0;
			v_idxName[0]:=null;
      createIdxSubscript[0]:=null;

			for idx_row in select b.relname tabname, ic.relname idxname, b.indisprimary, b.indisunique, b.indisclustered, c.attname from pg_attribute c
	inner join (select i.indrelid, t.relname, i.indexrelid, i.indisprimary, i.indisunique, i.indisclustered, unnest(i.indkey) colid from pg_index i
			inner join pg_class t on i.indrelid = t.oid where t.relname = TableName) b on c.attrelid = b.indrelid and c.attnum = colid
	inner join pg_class ic on ic.oid = b.indexrelid  loop

				i := 0;
				IF idx_row.indisprimary = true THEN
					pk = idx_row.idxname;
				END IF;
				IF idx_row.indisclustered = true THEN
					clustered = idx_row.idxname;
				END IF;

				existsIdx := false;

        IF v_idxName[0] is not null THEN
				FOREACH x IN ARRAY v_idxName
				LOOP

					IF x = idx_row.idxname THEN
						v_idxCols[i] = v_idxCols[i] || ', ' || idx_row.attname;
						existsIdx = true;
					END IF;
					EXIT WHEN existsIdx;
					i := i + 1;
				END LOOP;
				END IF;

				IF existsIdx = false THEN
					v_idxName[suffix] = idx_row.idxname;
					v_idxCols[suffix] = idx_row.attname;
					IF idx_row.indisunique = true THEN
					  v_idxUnique[suffix] = idx_row.idxname;
				  END IF;
					suffix := suffix + 1;
				END IF;

				IF idx_row.attname = ColumnName THEN
					createIdxSubscript[j] = i;
					j := j+1;
				END IF;
			end loop;

			v_str := ' alter table ' || TableName || ' add ' || v_NewColumnName || ' ' || v_ColumnType || v_defaultsql || ' ';
			execute v_str;
			v_str := ' update ' || TableName || ' set ' || v_NewColumnName || ' = ' || ColumnName || '::' || v_ColumnType || ' ';
			execute v_str;
			v_str := ' alter table ' || TableName || ' drop column ' || ColumnName || ' ';
			execute v_str;
			v_str := ' alter table ' || TableName || ' rename column ' || v_NewColumnName || ' to ' || ColumnName || ' ';
			execute v_str;

      IF createIdxSubscript[0] is not null then
			FOREACH i IN ARRAY createIdxSubscript
			LOOP
				IF v_idxName[i] = pk THEN
					v_str := ' ALTER TABLE ' || TableName || ' ADD CONSTRAINT ' || v_idxName[i] || ' PRIMARY KEY (' || v_idxCols[i] || ')' ;
				ELSE
					existsIdx := false;
					IF v_idxUnique[0] is not null THEN
                        FOREACH x IN ARRAY v_idxUnique
                        LOOP
                            IF v_idxName[i] = x THEN
                                existsIdx := true;
                            END IF;
                        END LOOP;
	                END IF;
					IF existsIdx THEN
						v_str := ' CREATE UNIQUE INDEX ' || v_idxName[i] || ' ON ' || TableName || ' (' || v_idxCols[i] || ') ' ;
					else
						v_str := ' CREATE INDEX ' || v_idxName[i] || ' ON ' || TableName || ' (' || v_idxCols[i] || ') ' ;
					end if;

				END IF;
				execute v_str;

				IF v_idxName[i] = clustered THEN
					execute ' CLUSTER ' || TableName || ' USING ' || v_idxName[i] || ' ';
					raise info ' CLUSTER % USING %', TableName, v_idxName[i];
				END IF;
			end loop;
		  END IF;
		END IF;
	ELSE
		IF SUBSTR(ModifyFlag,2,1) = '1' THEN
		v_str := ' alter table ' || TableName || ' alter column ' || ColumnName || ' TYPE ' || v_ColumnType || ' ';
			execute v_str;
		END IF;
		IF SUBSTR(ModifyFlag,4,1) = '1' THEN
			IF v_defaultsql IS NOT NULL AND v_defaultsql <> ' ' THEN
				v_str := ' alter table ' || TableName || ' alter column ' || ColumnName || ' SET ' || v_defaultsql || ' ';
			ELSE
				v_str := ' alter table ' || TableName || ' alter column ' || ColumnName || ' DROP DEFAULT ';
			END IF;
    		execute  v_str;
		END IF;
	END IF;
	IF SUBSTR(ModifyFlag,3,1) = '1' AND ColumnIsNull = 'NOT NULL' THEN
		IF v_defaultvalue <> 'NULL' AND char_length(v_defaultvalue) > 0 THEN
            v_str := 'UPDATE ' || TableName || ' SET ' || ColumnName || ' = ' || v_defaultvalue || ' WHERE ' || ColumnName || ' IS NULL ';
            execute  v_str;
        END IF;
        v_str := ' alter table ' || TableName || ' alter column ' || ColumnName || ' SET NOT NULL ';
        execute  v_str;
    ELSIF SUBSTR(ModifyFlag,3,1) = '1' THEN
        v_str := ' alter table ' || TableName || ' alter column ' || ColumnName || ' DROP NOT NULL ';
        execute v_str;
    END IF;
END;
$BODY$ LANGUAGE plpgsql;
GO


CREATE OR REPLACE PROCEDURE p_AlterPK
(
PkName    IN  VARCHAR,
TableName  IN  VARCHAR,
FieldName  IN  VARCHAR,
IsClustered  IN  integer = 0
)
AS
$BODY$
DECLARE
v_str         VARCHAR(2000);
v_indexname  varchar;
consname varchar;

cursor1 cursor for
select pg_constraint.conname from pg_constraint  inner join pg_class  on pg_constraint.conrelid = pg_class.oid
 where pg_class.relname = lower(TableName) and pg_constraint.contype='p';

BEGIN
open cursor1;
fetch cursor1 into consname;
close cursor1;

if FieldName is not null and FieldName <> 'NULL' then
    IF consname IS NOT NULL THEN
        v_str := ' ALTER TABLE ' || TableName || ' DROP CONSTRAINT ' || consname ;
        execute v_str;
    END IF;

	v_str := ' ALTER TABLE ' || TableName || ' ADD CONSTRAINT ' || PkName || ' PRIMARY KEY (' || FieldName || ')';
execute v_str;
end if;
END;
$BODY$ LANGUAGE plpgsql;
GO


CREATE OR REPLACE PROCEDURE p_dropidx
(
    IdxName text,
    TabName text
)
AS
$BODY$
DECLARE
  v_str varchar(2000);
  v_indexname varchar(200);
  consname    varchar(200);

  cursor1     CURSOR FOR
    SELECT conname
      from pg_class a,pg_constraint b
     WHERE a.oid = b.conrelid
		   and a.relname = lower(TabName)
       and b.conname = lower(IdxName)
       and (contype = 'p' OR contype = 'u');

  cur2        CURSOR FOR
    SELECT indexname
      FROM pg_indexes
     WHERE tablename = lower(TabName)
       AND indexname = lower(IdxName);

BEGIN
  open cursor1;
  loop
    fetch cursor1
      into consname;
    EXIT WHEN NOT FOUND;
    IF consname IS NOT NULL THEN
      v_str := ' ALTER TABLE ' || TabName || ' DROP CONSTRAINT ' ||
               consname;
      EXECUTE v_str;
    END IF;
  end loop;
  close cursor1;
  open cur2;
  fetch cur2
    into v_indexname;
  close cur2;
  if v_indexname IS NOT NULL then
    v_str := ' drop index ' || v_indexname;
    EXECUTE v_str;
  end if;
END;
$BODY$
  LANGUAGE PLPGSQL;
GO

DROP FUNCTION IF EXISTS udt_inttable(ids integer[]);
GO
CREATE OR REPLACE FUNCTION udt_inttable(ids bigint[]) RETURNS table(fid bigint) as
$BODY$
BEGIN
    RETURN query select unnest(ids);
END
$BODY$
  LANGUAGE plpgsql VOLATILE;
GO
