SET CLIENT_ENCODING TO 'euc_jp';

CREATE TYPE postgresforest.ConstraintData AS (
	conname			name,	--制約名
	concategory		smallint,	--制約種別番号
	attnum			smallint,	--対象カラム数
	targetcolumn	smallint[],	--対象カラム番号
	description		text	--制約詳細
);

/* --------------------------------------------------------------
 * 
 * CopyColumn(name, name)
 * 
 * param $1 : コピー元テーブル名
 * param $2 : コピー先テーブル名
 * 
 * return : RET_SUCCESS := 0;
 *          RET_FAIL := 1;
 *          RET_SRCTBLNOTFOUND := 2;
 *          RET_DESTTBLEXIST := 3;
 *
 * To call this func:
 *     SELECT ret FROM postgresforest('srcTbl','destTbl') AS ret;
 * --------------------------------------------------------------
 */
CREATE OR REPLACE FUNCTION postgresforest.CopyColumn(name,name) RETURNS integer as '
DECLARE
	RET_SUCCESS constant integer := 0;
	RET_FAIL constant integer := 1;
	
	srcTbl ALIAS FOR $1;
	destTbl ALIAS FOR $2;
BEGIN
	--publicスキーマ内のテーブルのみが対象
	EXECUTE ''SET search_path TO public,pg_catalog'';
	
	DECLARE
		rule bool;
		subclass bool;
	BEGIN
		SELECT INTO rule, subclass relhasrules, relhassubclass FROM pg_class WHERE relname = srcTbl;
		
		--テーブルにサブクラスが定義されている場合は処理を中断
		IF subclass = ''t''
		THEN
			--EXCEPTIONで返してしまうと、戻り値も返せなくなってしまう
			--RAISE EXCEPTION ''There are inheritances on table "%". Please drop them.'', srcTbl;

			RAISE WARNING ''There are inheritances on table "%". Please drop them.'', srcTbl;
			return RET_FAIL;

		--テーブルにルールが定義されている場合は警告を発行し処理を継続
		ELSE
			IF rule = ''t''
			THEN
				RAISE WARNING ''There are rules on table "%".'', srcTbl;
			END IF;
		END IF;
	END;
	
	--何も問題が無ければテーブルをコピー(srcTbl -> destTbl)
	EXECUTE ''CREATE TABLE public.'' ||destTbl ||'' ( LIKE '' ||srcTbl ||'' )'';
	
	--最終的にカラム数をチェック
	--カラム数がコピー元とコピー先で異なっていたら処理を中断
	DECLARE
		srcCols pg_class.relnatts%TYPE;
		destCols pg_class.relnatts%TYPE;
	BEGIN
		SELECT INTO srcCols relnatts FROM pg_class WHERE relname = srcTbl;
		SELECT INTO destCols relnatts FROM pg_class WHERE relname = destTbl;
		
		IF srcCols <> destCols
		THEN
			--destTblは不要
			EXECUTE ''DROP TABLE public.'' ||destTbl;
			
			--EXCEPTIONで返してしまうと、戻り値も返せなくなってしまう
			--RAISE EXCEPTION ''COPY TABLE % TO % FAILED'', srcTbl,destTbl;
			
			RAISE WARNING ''COPY TABLE % TO % FAILED'', srcTbl,destTbl;
			RAISE WARNING ''srcTbl has % columns but destTbl has %. '', srcCols,destCols;
			
			RETURN RET_FAIL;
		END IF;
	END;

	RETURN RET_SUCCESS;
END;
' LANGUAGE plpgsql;




/* --------------------------------------------------------------
 * 
 * ExtractConstraint(name)
 * 
 * param $1 : 制約情報取り出し元テーブル名
 * 
 * return : SETOF postgresforest.ConstraintData;
 *
 * To call this func:
 *     SELECT conname, concategory, targetcolomn, description
 *     FROM postgresforest.ExtractConstraint('srcTbl');
 * --------------------------------------------------------------
 */
CREATE OR REPLACE FUNCTION postgresforest.ExtractConstraint(name) RETURNS SETOF postgresforest.ConstraintData as '
DECLARE
	--return
	conData postgresforest.ConstraintData;

	catPk constant int2 := 1; --Primary Key
	catFk constant int2 := 2; --Foreign Key
	catChk constant int2 := 3; --Check
	catInd constant int2 := 4; --Index
	catUni constant int2 := 5; --Unique
	catDef constant int2 := 6; --Default
	catComm constant int2 := 7; --Comment	7

	srcTbl ALIAS FOR $1;
	relOid oid;
BEGIN
	--publicスキーマ内のテーブルのみが対象
	EXECUTE ''SET search_path TO public,pg_catalog'';
	
	--srcTblのOID取り出し
	SELECT INTO relOid oid FROM pg_class
	WHERE relname = srcTbl AND 
		  relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = ''public'') ;
	
	
	--インデクスの取り出し(同時にプライマリキー、ユニーク制約も取り出す)
	DECLARE
		rec RECORD;
	BEGIN
	--indkey:インデクスを張るカラム。複数カラムに渡る場合は配列要素にすべて入る
	--indnkeys:複数カラムに渡るインデクスを構築した際の対象カラム数
		FOR rec IN EXECUTE ''SELECT ''
		                     ||''coalesce(c.conname, t.relname) as indexrelname, ''
		                     ||''i.indkey, ''
		                     ||''t.relnatts as indnkeys, ''
		                     ||''coalesce(c.contype, ''''0'''') as contype, ''
		                     ||''pg_catalog.pg_get_indexdef(i.indexrelid) as usingdef ''
		                 ||''FROM pg_catalog.pg_index i JOIN pg_catalog.pg_class t ON (t.oid = i.indexrelid) ''
		                     ||''LEFT JOIN pg_catalog.pg_depend d ON (d.classid = t.tableoid AND d.objid = t.oid AND d.deptype = ''''i'''') ''
		                     ||''LEFT JOIN pg_catalog.pg_constraint c ON (d.refclassid = c.tableoid AND d.refobjid = c.oid) ''
		                 ||''WHERE i.indrelid = '' || relOid ||''::pg_catalog.oid ''
		                 ||''ORDER BY indexrelname''
		LOOP
			conData.conname := rec.indexrelname;

			--制約種別の判別(PK | UNIQUE | 単純INDEX)
			IF rec.contype = ''p''
			THEN
				conData.concategory := catPk;
			ELSIF rec.contype = ''u''
			THEN
				conData.concategory := catUni;
			ELSIF rec.contype = ''0''
			THEN
				conData.concategory := catInd;
			END IF;
			
			--USING句以降の取り出し
			conData.description = rec.usingdef;
			
			--対象カラムの取り出し
			
			DECLARE
				cnt integer := 0;
				elem smallint;
			BEGIN
				WHILE cnt < rec.indnkeys
				LOOP
					elem := rec.indkey[cnt];
					IF cnt = 0 THEN
						conData.attnum := 1;
						conData.targetcolumn := ''{''||elem||''}'';
					ELSE
						conData.attnum := conData.attnum + 1;
						conData.targetcolumn[cnt+1] := elem;
					END IF;
					cnt := cnt + 1;
				END LOOP;
			END;
	
			RETURN NEXT conData;
		END LOOP;
	END;
	
	--FKの取り出し
	--  TODO:
	--  ・1:1の参照のみが取り出し対象(PostgreSQLでは複数カラムに対して一つのFKを作れる)
	--    EX)
	--  ・MATCH句はデフォルトの"MATCH SIMPLE"のみが取り出し対象
	--  ・ON DELETE/ON UPDATE句はデフォルトの"NO ACTION"のみが取り出し対象
	DECLARE
		rec RECORD;
	BEGIN
		FOR rec IN EXECUTE ''SELECT conname, conkey, pg_catalog.pg_get_constraintdef(oid) as condef ''
		                   ||''FROM pg_catalog.pg_constraint ''
		                   ||''WHERE conrelid = '' || reloid || ''::pg_catalog.oid AND ''
		                   ||''contype = ''''f'''' ''
		LOOP
			conData.conname := rec.conname;
			conData.concategory = catFk;
			conData.attnum := 1;
			conData.targetcolumn = rec.conkey;
			conData.description = rec.condef;
			
			RETURN NEXT conData;
		END LOOP;
	END;
	
	--CHECK制約
	DECLARE
		rec RECORD;
	BEGIN
		--制約定義文をそのまま取り出す
		--定義文内にシングルクォートがある場合、これをエスケイプさせる(リプレイス)
		FOR rec IN EXECUTE ''SELECT conname, conkey, ''
		                   ||''replace(pg_catalog.pg_get_constraintdef(c1.oid),''''\\\\'''''''',''''\\\\\\\\\\\\'''''''') AS consrc ''
		                   ||''FROM pg_catalog.pg_constraint c1 ''
		                   ||''WHERE conrelid = '' || reloid || ''::pg_catalog.oid and contype = ''''c'''' ''
		                   ||''ORDER BY conname'' 
		LOOP
			conData.conname := rec.conname;
			conData.concategory = catChk;
			conData.attnum := 1;
			conData.targetcolumn = rec.conkey;
			conData.description = rec.consrc;
			
			RETURN NEXT conData;
		END LOOP;

	END;
	
	--DEFAULT制約
	DECLARE
		rec RECORD;
	BEGIN
		FOR rec IN EXECUTE ''SELECT adnum, ''
		                   ||''replace(pg_catalog.pg_get_expr(adbin, adrelid),''''\\\\'''''''',''''\\\\\\\\\\\\'''''''') AS adsrc ''
		                   ||''FROM pg_catalog.pg_attrdef ''
		                   ||''WHERE adrelid = ''
		                   ||reloid
		                   ||''::pg_catalog.oid''
		LOOP
			--DEFAULTは制約名が付かないのでDEFAULTを制約名として入れる(実際には使用しない)
			conData.conname := ''DEFAULT'';
			conData.concategory := catDef;
			conData.attnum := 1;
			conData.targetcolumn := ''{''|| rec.adnum ||''}'';
			conData.description := rec.adsrc;
			RETURN NEXT conData;
		END LOOP;
	END;
	
	--COMMENT取り出し
	--  各カラムに対するコメントのみ
	DECLARE
		rec RECORD;
	BEGIN
		FOR rec IN EXECUTE ''SELECT replace(description,''''\\\\'''''''',''''\\\\\\\\\\\\'''''''') as description, objsubid ''
		                   ||''FROM pg_catalog.pg_description ''
		                   ||''WHERE objoid = ''||reloid||''::pg_catalog.oid and ''
		                   ||''objsubid != 0 and classoid = ''''pg_catalog.pg_class''''::pg_catalog.regclass ''
		                   ||''ORDER BY objoid, classoid, objsubid''
		LOOP
			--COMMENTは制約名が付かないのでCOMMENTを制約名として入れる(実際には使用しない)
			conData.conname := ''COMMENT'';
			conData.concategory := catComm;
			conData.attnum := 1;
			conData.targetcolumn := ''{''|| rec.objsubid ||''}'';
			conData.description := rec.description;
			RETURN NEXT conData;
		END LOOP;
	END;

	
	RETURN;
END;
' LANGUAGE plpgsql;

/* --------------------------------------------------------------
 * 
 * AddaptConstraint(name)
 * 
 * param $1 : 制約名:name
 * param $2 : 制約種別番号:smallint
 * param $3 : 対象カラム数:smallint
 * param $4 : 対象カラム名:smallint[]
 * param $5 : 制約詳細:text
 * param $6 : 制約付加先テーブル名:name
 * 
 * return : RET_SUCCESS := 0;
 *          RET_FAIL := 1;
 *          RET_DESTTBLNOTFOUND := 3;
 *
 * To call this func:
 *     SELECT ret FROM postgresforest.AddaptConstraint('ConName',1,1,'{2}','description','desttbl');
 *     (各カラムはキャストの必要がある場合があるので注意)
 * --------------------------------------------------------------
 */
CREATE OR REPLACE FUNCTION postgresforest.AddaptConstraint(name, smallint, smallint, smallint[], text, name) RETURNS integer as '
DECLARE
	--return
	RET_SUCCESS constant integer := 0;
	RET_FAIL constant integer := 1;
	
	conname ALIAS FOR $1;
	concategory ALIAS FOR $2;
	attnum ALIAS FOR $3;
	targetcolumn ALIAS FOR $4;
	description ALIAS FOR $5;
	destTbl  ALIAS FOR $6;
	
	catPk constant int2 := 1; --Primary Key
	catFk constant int2 := 2; --Foreign Key
	catChk constant int2 := 3; --Check
	catInd constant int2 := 4; --Index
	catUni constant int2 := 5; --Unique
	catDef constant int2 := 6; --Default
	catComm constant int2 := 7; --Comment
	
	cnt integer := 1;
	attr name[];
	inattr name;
	relOid oid;
	stmt text;
BEGIN
	--publicスキーマ内のテーブルのみが対象
	EXECUTE ''SET search_path TO public,pg_catalog'';
	
	--destTblのOID取り出し
	SELECT INTO relOid oid FROM pg_class
	WHERE relname = destTbl AND 
		  relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = ''public'') ;
	
	
	-- descriptionの値を使用するもの(FK, CHECK, INDEX)
	-- INDEXは対象テーブル、カラムを変更する
	-- ------> targetcolumnは使用しない 
	IF concategory = catFk OR concategory = catChk THEN
	
		stmt := ''ALTER TABLE '' || destTbl;
		stmt := stmt ||'' ADD CONSTRAINT "'' || conname ||''" ''||description;

	ELSIF concategory = catInd THEN
		DECLARE
			uni int;
			using text;
		BEGIN
			-- CREATE UNIQUE INDEX文で作られた制約はここで設定
			RAISE NOTICE ''desc -> %'',description;
			SELECT INTO uni position(''unique'' in lower(description));
			SELECT INTO using substring(description from position(''using'' in lower(description)));
			RAISE NOTICE ''uni -> %'',uni;
			RAISE NOTICE ''using -> %'',using;
			
			IF uni = 0 THEN
				--UNIQUE INDEXでは無い
				stmt := ''CREATE INDEX ''|| conname ||'' ON ''||destTbl||'' ''||using;
			ELSE
				--UNIQUE INDEX
				stmt := ''CREATE UNIQUE INDEX ''|| conname ||'' ON ''||destTbl||'' ''||using;
			END IF;
		END;
		
	-- targetcolumnからカラム名を割り出してDDLを生成するもの(PK, UNIQUE, DEFAULT, COMMENT)
	-- ------> descriptionは使用しない 

	--Primary Key
	ELSIF concategory = catPk THEN
	
		--PKの制約名はなぜかダブルクォートでないとシンタクスエラーとなる
		stmt := ''ALTER TABLE '' || destTbl ||'' ADD CONSTRAINT "''|| conname ||''" PRIMARY KEY ( '';
RAISE NOTICE ''stmt -> %'',stmt;
		
		-- カラム名取り出し(複数カラム)
		cnt := 1;
		WHILE cnt <= attnum
		LOOP
			SELECT INTO inattr a.attname FROM pg_attribute a
				WHERE a.attrelid = relOid::pg_catalog.oid AND a.attnum = targetcolumn[cnt]::pg_catalog.int2;
			
			IF cnt = 1
			THEN
				stmt := stmt || inattr;
			ELSE
				stmt := stmt || '', '' || inattr;
			END IF;
			cnt := cnt + 1;
		END LOOP;
RAISE NOTICE ''stmt -> %'',stmt;
		
		
		stmt := stmt || '' )'';
RAISE NOTICE ''stmt -> %'',stmt;
		

	--UNIQUE
	ELSIF concategory = catUni THEN
		stmt := ''ALTER TABLE '' || destTbl || '' ADD CONSTRAINT '' || conname ||'' UNIQUE ( '';
		
		-- カラム名取り出し(複数カラム)
		cnt := 1;
		WHILE cnt <= attnum
		LOOP
			SELECT INTO inattr a.attname FROM pg_attribute a
				WHERE a.attrelid = relOid::pg_catalog.oid AND a.attnum = targetcolumn[cnt]::pg_catalog.int2;
			
			IF cnt = 1
			THEN
				stmt := stmt || inattr;
			ELSE
				stmt := stmt || '', '' || inattr;
			END IF;
			cnt := cnt + 1;
		END LOOP;
		
		stmt := stmt || '' )'';
		
	ELSIF concategory = catDef THEN
		-- カラム名取り出し(単一カラム)
		SELECT INTO inattr a.attname FROM pg_attribute a
			WHERE a.attrelid = relOid::pg_catalog.oid AND a.attnum = targetcolumn[1]::pg_catalog.int2;
		stmt := ''ALTER TABLE ''||destTbl||'' ALTER COLUMN ''||inattr||'' SET DEFAULT '' ||description;
		
	ELSIF concategory = catComm THEN
		-- カラム名取り出し(単一カラム)
		SELECT INTO inattr a.attname FROM pg_attribute a
			WHERE a.attrelid = relOid::pg_catalog.oid AND a.attnum = targetcolumn[1]::pg_catalog.int2;
		stmt := ''COMMENT ON COLUMN ''||destTbl||''.''||inattr||'' IS ''''''||description||'''''''';
	END IF;
	
	RAISE LOG ''PostgresForest stmt -> %'',stmt;
	
	EXECUTE stmt;
	
	RETURN RET_SUCCESS;
	
END;
' language plpgsql;
