CREATE or REPLACE FUNCTION "newid"() 
RETURNS "pg_catalog"."varchar" AS 
$BODY$ 
DECLARE 
  v_seed_value varchar(32); 
BEGIN 
select 
md5( 
inet_client_addr()::varchar || 
timeofday() || 
inet_server_addr()::varchar || 
to_hex(inet_client_port()) 
) 
into v_seed_value; 

return (substr(v_seed_value,1,8) || '-' || 
        substr(v_seed_value,9,4) || '-' || 
        substr(v_seed_value,13,4) || '-' || 
        substr(v_seed_value,17,4) || '-' || 
        substr(v_seed_value,21,12)); 
END; 
$BODY$ 
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER; 


CREATE or REPLACE FUNCTION exec(text) returns text language plpgsql volatile
  AS $f$
    BEGIN
      EXECUTE $1;
      RETURN $1;
    END;
$f$;

CREATE OR REPLACE PROCEDURE p_AlterColumn
(
    TableName    IN VARCHAR,
    ColumnName   IN VARCHAR,
    ColumnType   IN VARCHAR,  --ModifyFlag='1000' ColumnType = 'NULL' 表示删除
    ColumnIsNull IN VARCHAR,  --'NULL'或'NOT NULL'
    ModifyFlag   IN VARCHAR,  --数据类型，精度，为空，缺省值分别占1位。改了缺省值和精度该值为：'0101'
    DefaultValue IN VARCHAR = 'NULL'  --字符加'号 '''ABC'''   整数'0'
) 
AS $$
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;
 /* for idx_type in 
  (INDEX_NAME User_Indexes.index_name%TYPE,
   TABLE_NAME user_Indexes.table_name%TYPE,
   COLUMN_NAMES VARCHAR2(2000),
   UNIQUENESS User_Indexes.uniqueness%TYPE,
   CONSTRAINT_TYPE user_constraints.CONSTRAINT_TYPE%TYPE
   );
   TYPE idx_tabType is TABLE of idx_type INDEX BY BINARY_INTEGER;
   */
   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 is not null AND DefaultValue <> 'NULL' AND DefaultValue <> '' THEN
		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', 'TO_DATE('), '}', ', ''YYYY-MM-DD HH24:MI:SS'')');
				v_defaultvalue := REPLACE(REPLACE(DefaultValue, '{ts', ''), '}', '');
			ELSE
			    v_defaultvalue := DefaultValue;
			END IF;
		END IF;
		v_defaultsql := ' DEFAULT ' || v_defaultvalue;
	ELSE
		v_defaultsql := '';
	  DefaultValue := '';
	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		--修改字段类型、修改字段长度、修改为空、修改缺省值
		-- TODO 判断兼容类型可以直接转换
			v_NewColumnName := REPLACE(ColumnName, 'f', 'n');	-- varchar转integer
			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;
					-- v_idxUnique[suffix] = idx_row.idxname;
					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
					-- createIdxName[suffix] = idx_row.relname;
					createIdxSubscript[j] = i;
					j := j+1;
				END IF;
			
				-- raise notice 'v_idxUnique%',v_idxUnique ;

/*				
				v_idxName[] = idx_row.relname; || ' ON ' || TableName || ' (' || idx_row.attname
	
				IF idx_tab(i).CONSTRAINT_TYPE = 'P' THEN
					v_str := ' ALTER TABLE ' || TableName || ' DROP CONSTRAINT ' || idx_tab(i).INDEX_NAME || ' DROP INDEX ';
				ELSE
					v_str := ' DROP INDEX ' || idx_tab(i).INDEX_NAME;
				END IF;
				execute immediate v_str;
*/
			end loop;

      raise notice 'TableName=%,v_NewColumnName=%,v_ColumnType=%,v_defaultsql=%',TableName,v_NewColumnName,v_ColumnType,v_defaultsql;
			v_str := ' alter table ' || TableName || ' add ' || v_NewColumnName || ' ' || v_ColumnType || v_defaultsql || ' ';
			execute v_str;
			-- v_str := ' update ' || TableName || ' set ' || v_NewColumnName || ' = ' || ColumnName || ' ';
			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
      -- raise notice 'TableName=%, v_idxName[%]=%, v_idxCols[%]=%', TableName, i,v_idxName[i], i,v_idxCols[i] ;
				IF v_idxName[i] = pk THEN 
					v_str := ' ALTER TABLE ' || TableName || ' ADD CONSTRAINT ' || v_idxName[i] || ' PRIMARY KEY (' || v_idxCols[i] || ')' ;
				ELSE
					existsIdx := false;
					FOREACH x IN ARRAY v_idxUnique
					LOOP
						IF v_idxName[i] = x THEN
							existsIdx := true;
						END IF;
					END LOOP;
					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;

				-- ALTER TABLE public.t_1 CLUSTER ON idx_t_1_f2;
				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
	----修改精度缺省值
    --v_str := ' alter table ' || TableName || ' modify ' || ColumnName || ' default ' || v_value || ' ';
		v_str := ' alter table ' || TableName || ' alter column ' || ColumnName || ' TYPE ' || v_ColumnType || ' ';
		IF v_defaultsql <> '' THEN
			v_str := v_str || ', alter column ' || ColumnName || ' SET ' || v_defaultsql || ' ';
		END IF;
		execute  v_str;
	END IF;
	
    -- raise notice 'TableName=%,ColumnName=%,v_defaultvalue=%,ColumnIsNull=%',TableName,ColumnName,v_defaultvalue,ColumnIsNull;
		-- 修改非空约束
		IF SUBSTR(ModifyFlag,3,1) = '1' AND ColumnIsNull = 'NOT NULL' THEN	--修改 null 为 not null
			v_str := 'UPDATE ' || TableName || ' SET ' || ColumnName || ' = ' || v_defaultvalue || ' WHERE ' || ColumnName || ' IS NULL ';
			execute  v_str;
			v_str := ' alter table ' || TableName || ' alter column ' || ColumnName || ' SET NOT NULL ';
			-- raise notice 'v_str=%',v_str ;
			execute  v_str;
		ELSEIF SUBSTR(ModifyFlag,3,1) = '1' THEN
			v_str := ' alter table ' || TableName || ' alter column ' || ColumnName || ' DROP NOT NULL ';
			-- raise notice 'v_str=%',v_str ;
			execute v_str;
		END IF;

END;
$$ LANGUAGE plpgsql;



CREATE OR REPLACE PROCEDURE p_AlterPK
(
PkName    IN  VARCHAR,
TableName  IN  VARCHAR,
FieldName  IN  VARCHAR,
IsClustered  IN  integer = 0  --0非聚集   1聚集
)
AS $$
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 consname IS NOT NULL THEN
v_str := ' ALTER TABLE ' || TableName || ' DROP CONSTRAINT ' || consname ;
execute v_str;
END IF;

if FieldName is not null and FieldName <> 'NULL' and FieldName <> '' then
	v_str := ' ALTER TABLE ' || TableName || ' ADD CONSTRAINT ' || PkName || ' PRIMARY KEY (' || FieldName || ')';
execute v_str;
end if;
END;
$$ LANGUAGE plpgsql;



CREATE OR REPLACE PROCEDURE p_ModifyObjectName(
    tablename character varying,
    objectname character varying,
    newobjectname character varying,
    objecttype character varying,
    fieldtype character varying DEFAULT ''::character varying)
AS $$
DECLARE
  v_str           varchar(2000);
  v_cnt           integer;
BEGIN
  
  IF (ObjectType =  'OBJECT') THEN
    select 1 into v_cnt from information_schema.tables where TABLE_NAME = ObjectName;
    IF(v_cnt > 0) THEN
        v_str :=  ' ALTER TABLE ' || ObjectName || ' RENAME TO ' || NewObjectName || ' ';
        execute v_str;
    END IF;
  ELSIF (ObjectType =  'COLUMN') THEN
    SELECT 1 INTO v_cnt FROM information_schema.columns WHERE TABLE_NAME = TableName AND COLUMN_NAME = ObjectName;
    IF(v_cnt > 0) THEN
        v_cnt := ' ALTER TABLE ' || TableName || ' RENAME ' || ObjectName || ' TO ' || NewObjectName || ' ';
		execute v_str;
    END IF;
  END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE p_dropidx
(
    IdxName text, 
    TabName text
) 
AS $$      
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 = TabName
       and b.conname = IdxName
       and (contype = 'p' OR contype = 'u');

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

BEGIN
  open cursor1;
  loop
    fetch cursor1
      into consname;
    EXIT WHEN NOT FOUND; /* apply on cursor1 */
    IF (consname IS NOT NULL AND consname ::text <> '') 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 AND v_indexname ::text <> '') then
    v_str := ' drop index ' || v_indexname;
    EXECUTE v_str;
  end if;
END; $$
  LANGUAGE PLPGSQL;

--求两个数的最大公约数
CREATE OR REPLACE FUNCTION FN_GCD(
    num1 numeric,
    num2 numeric)
  RETURNS numeric AS
$BODY$
DECLARE
    v_m decimal(23,10);
    v_i decimal(23,10);
    v_ret decimal(23,10);
begin
    --先化整后再求余，否则oracle与sqlserver的mod函数对小数的算法是不一样的
    if(num1<=0 or num2<=0) then
	return 1;
    end if;
    
    if(num1<num2) then
	v_m = num2;
	num2 = num1;
	num1 = v_m;
    end if;
    
    v_i=num1%num2;
    WHILE v_i>0 LOOP
	num1=num2;
	num2=v_i;
	v_i=num1%num2;
    END LOOP;
    return num2;
end
$BODY$
  LANGUAGE plpgsql VOLATILE;


--求两个数的最小公倍数（调用了最大公约数函数)
CREATE OR REPLACE FUNCTION FN_LCM(
    v_num1 numeric,
    v_num2 numeric)
  RETURNS numeric AS
$BODY$
declare
    v_a decimal(23,10);
    v_b decimal(23,10);
    v_c decimal(23,10);
    v_d decimal(23,10);
BEGIN    
    v_a = v_num1;
    v_b = v_num2;
    v_c = "FN_GCD"(v_num1,v_num2);
    v_d = v_a*v_b/v_c;

    RETURN v_d;
END
$BODY$
  LANGUAGE plpgsql VOLATILE;


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

CREATE OR REPLACE FUNCTION udt_varchartable(ids varchar[]) RETURNS table(fid varchar) as
$BODY$
BEGIN
    
    RETURN query select unnest(ids);
END
$BODY$
  LANGUAGE plpgsql VOLATILE;

CREATE TYPE udt_nvarchartable AS (id varchar(450));
