-- Function: test_archive(text, text, text, text, date)

-- DROP FUNCTION test_archive(text, text, text, text, date);

CREATE OR REPLACE FUNCTION test_archive(text, text, text, text, date)
  RETURNS bigint AS
$BODY$
-- CREATED BY: Melvin Davidson
-- DATE: 2008-08-11
-- 
-- Execute this function from archive db
-- General flow
-- 1. Open connection to production db 
-- 2. Insert into archive db as select from with criteria
-- 3. Delete from production db with same criteria
-- 4. Close connection.
-- 5. Return the number of TXMaster (Total) records archived.
--
-- ERROR HANDLING
-- If dblink connection fails, -1 is returned
-- If archive date is >= Current Date, -10 is returned
-- If Total number of transactions inserted does not = TXMaster inserts, 
-- rollback is done and -100 returned
-- If Total number of remote transactions deleted does not = remote TXMaster deletes, 
-- rollback is done and -200 returned

DECLARE
-- Parameter(s)
	p_host_ip			ALIAS FOR $1;
	p_dbname			ALIAS FOR $2;
	p_user			ALIAS FOR $3;
	p_passwd			ALIAS FOR $4;
	p_date			ALIAS FOR $5;

-- Variable(s)
	v_ctr					BIGINT DEFAULT 0;
	v_txmaster_ctr			BIGINT DEFAULT 0;
	v_txassetcycle_ctr		BIGINT DEFAULT 0;
	v_txassetdamaged_ctr		BIGINT DEFAULT 0;
	v_txassetfilledemptied_ctr	BIGINT DEFAULT 0;
	v_txassetfound_ctr		BIGINT DEFAULT 0;
	v_txassetlost_ctr			BIGINT DEFAULT 0;
	v_txassetmoved_ctr		BIGINT DEFAULT 0;
	v_txassetOwnerChanged_ctr	BIGINT DEFAULT 0;
	v_txassetprodasscheck_ctr 	BIGINT DEFAULT 0;
	v_txassetrepaired_ctr		BIGINT DEFAULT 0;
	v_txassettagassigned_ctr	BIGINT DEFAULT 0;
	v_txbillingaction_ctr		BIGINT DEFAULT 0;
	v_txexception_ctr			BIGINT DEFAULT 0;
	v_txorderdetailfilled_ctr	BIGINT DEFAULT 0;
	v_txassettagremoved_ctr		BIGINT DEFAULT 0;
	v_txpendingmovesetup_ctr	BIGINT DEFAULT 0;

	v_id 				bigint;
	v_displayname 		character varying(100);
	v_fullname 			character varying(100);
	v_gmtoffset 		integer;

	v_connect			TEXT;
	v_sql				TEXT;
	v_msg				TEXT;
	v_flag			TEXT;
BEGIN

	BEGIN
	v_connect := 'hostaddr=' || p_host_ip || ' port=5432 dbname=' || p_dbname || ' user=' || p_user || ' password=' || p_passwd ;
	SELECT dblink_connect('db_connect', v_connect) INTO v_msg;

-- Check that archive date is valid
	IF p_date >= CURRENT_DATE THEN
		RETURN -10;
	END IF;
	
-- NOTE: IF STATEMENT below is ineffective, as a failed dblink_connect will abort function.
	IF v_msg != 'OK' THEN
		v_msg := 'Remote Connect failed. Check connection parameters';
		RAISE NOTICE '%', v_msg;
		RETURN -1;
	END IF;

	v_sql = 'SELECT "ID", 
			"TXDate", 
			"TXOperatorID"
		   FROM "TXMaster"
		  WHERE "TXDate"::date <= ' || quote_literal(p_date) || 
                    'AND "ID" NOT IN 
                       ( SELECT "TXMasterID" 
                           FROM "TXAssetCycle"
                          WHERE "CycleEnd" IS NULL
                             OR "CycleEnd" > '|| quote_literal(p_date) || ') ;' ;

			
	INSERT INTO "TXMaster"
        select * FROM dblink('db_connect', v_sql )
           as t1(id bigint,
                 txdate timestamp without time zone, 
                 txoperatorid bigint
                 ); 

	GET DIAGNOSTICS v_txmaster_ctr = ROW_COUNT;

	v_sql = 'SELECT "TXMasterID", 
			"AssetID", 
			"ParentTx",
			"CycleStart", 
			"CycleEnd", 
			"ResponsibleOrganizationID", 
			"CycleLength"
		  FROM "TXAssetCycle" t
		  JOIN "TXMaster" m ON (m."ID" = t."TXMasterID")
		 WHERE m."TXDate"::date <= ' || quote_literal(p_date) ||
		  'AND t."CycleEnd" IS NOT NULL 
                   AND t."CycleEnd" <= ' || quote_literal(p_date) || ';' ;


	INSERT INTO "TXAssetCycle"
	select * FROM dblink('db_connect', v_sql )
            as t1(txmasterid bigint, 
                  assetid bigint, 
                  parenttx bigint, 
                  cyclestart date,
                  cycleEnd date,
                  responsibleorganizationid bigint,
                  cyclelength bigint
                  ); 

	GET DIAGNOSTICS v_txassetcycle_ctr = ROW_COUNT;
	
	v_sql = 'SELECT "TXMasterID",
		        "AssetID",
		        "DamagedAtLocationID",
		        "Notes",
		        "DamageTypeID"
		   FROM "TXAssetDamaged" t
		   JOIN "TXMaster" m ON (m."ID" = t."TXMasterID")
		  WHERE "TXDate"::date <= ' || quote_literal(p_date) || ';' ;

	INSERT INTO "TXAssetDamaged"
	select * FROM dblink('db_connect', v_sql )
            as t1(txmasterid bigint, 
                  assetid bigint, 
                  damagedatlocationid bigint, 
                  notes text,
                  damagetypeid bigint
                  ); 
	
	GET DIAGNOSTICS v_txassetdamaged_ctr = ROW_COUNT;

	v_sql = 'SELECT "TXMasterID",
		        "AssetID",
		        "OrganizationID",
		        "ProductID",
		        "FillChange",
		        "FillUnitID",
		        "LocationID",
		        "ProductBatchNumber"
		   FROM "TXAssetFilledEmptied" t
		   JOIN "TXMaster" m ON (m."ID" = t."TXMasterID")
		  WHERE "TXDate"::date <= ' || quote_literal(p_date) || ';' ;

	INSERT INTO "TXAssetFilledEmptied"
	select * FROM dblink('db_connect', v_sql )
            as t1(txmasterid bigint, 
                  assetid bigint, 
                  organizationid bigint, 
                  productid bigint,
                  fillchange real,
                  fillunitid bigint,
                  locationid bigint,
                  productbatchnumber character varying(25)
                  ); 

	GET DIAGNOSTICS v_txassetfilledemptied_ctr = ROW_COUNT;

	v_sql = 'SELECT "TXMasterID",
		        "AssetID",
		        "FoundAtLocationID",
		        "Notes",
		        "LostTXID"
	           FROM "TXAssetFound" t
		   JOIN "TXMaster" m ON (m."ID" = t."TXMasterID")
		  WHERE "TXDate"::date <= ' || quote_literal(p_date) || ';' ;

	INSERT INTO "TXAssetFound"
	select * FROM dblink('db_connect', v_sql )
            as t1(txmasterid bigint, 
		  assetid bigint,
		  foundatlocationid bigint,
		  notes text,
		  losttxid bigint
		  );

  	GET DIAGNOSTICS v_txassetfound_ctr = ROW_COUNT;

	v_sql = 'SELECT "TXMasterID",
			"AssetID",
			"LostAtLocationID",
			"Notes"
 		   FROM "TXAssetLost" t
		   JOIN "TXMaster" m ON (m."ID" = t."TXMasterID")
		  WHERE "TXDate"::date <= ' || quote_literal(p_date) || ';' ;

	INSERT INTO "TXAssetLost"
	select * FROM dblink('db_connect', v_sql )
            as t1(txmasterid bigint, 
		  assetid bigint,
		  lostatlocationid bigint,
		  notes text
		  );

  	GET DIAGNOSTICS v_txassetlost_ctr = ROW_COUNT;
  	
	v_sql = 'SELECT "TXMasterID", 
			"AssetID",
			"FromLocationID",
			"ToLocationID",
			"Quantity",
			"BillOfLadingID",
			"ProductID",
			"FillUnitID",
			"CurrentFill",
			"ParentAssetTXID"
		   FROM "TXAssetMoved" t
		   JOIN "TXMaster" m ON (m."ID" = t."TXMasterID")
		  WHERE "TXDate"::date <= ' || quote_literal(p_date) || ';' ;

	INSERT INTO "TXAssetMoved"
	select * FROM dblink('db_connect', v_sql )
            as t1(txmasterid bigint, 
                  assetid bigint, 
		  fromLocationid bigint,
		  tolocationid bigint,
		  quantity integer,
		  billOfladingid bigint,
		  productID bigint,
		  fillunitid bigint,
		  currentfill real,
		  parentassettxid bigint
                  ); 

	GET DIAGNOSTICS v_txassetmoved_ctr = ROW_COUNT;

	v_sql = 'SELECT "TXMasterID",
		        "AssetID",
		        "FromOwnerOrganizationID",
		        "ToOwnerOrganizationID",
		        "FromLesseeOrganizationID",
		        "ToLesseeOrganizationID"
		   FROM "TXAssetOwnerChanged" t
		   JOIN "TXMaster" m ON (m."ID" = t."TXMasterID")
		  WHERE "TXDate"::date <= ' || quote_literal(p_date) || ';' ;

	INSERT INTO "TXAssetOwnerChanged"
	select * FROM dblink('db_connect', v_sql )
            as t1(txmasterid bigint,
                  assetid bigint,
                  fromownerorganizationid bigint,
                  toownerorganizationid bigint,
                  fromlesseeorganizationid bigint,
                  tolesseeorganizationid bigint
                  );
                  
	GET DIAGNOSTICS v_txassetOwnerChanged_ctr = ROW_COUNT;

	v_sql = 'SELECT "TXMasterID",
	                "AssetID",
	                "ProductID",
	                "ValidAssociation",
	                "RequestingIP",
	                "CurrentCount"
		   FROM "TXAssetProductAssociationCheck" t
		   JOIN "TXMaster" m ON (m."ID" = t."TXMasterID")
		  WHERE "TXDate"::date <= ' || quote_literal(p_date) || ';' ;

	INSERT INTO "TXAssetProductAssociationCheck"
	select * FROM dblink('db_connect', v_sql )
            as t1(txmasterid bigint,
                  assetid bigint,
                  productid bigint,
                  validassociation boolean,
                  requestingip character varying(50),
                  currentcount integer
                  );

	GET DIAGNOSTICS v_txassetprodasscheck_ctr = ROW_COUNT;
	
	v_sql = 'SELECT "TXMasterID",
                        "AssetID",
                        "RepairedAtLocationID",
                        "Notes",
                        "MaintComponentID",
                        "TXDamagedID"
	           FROM "TXAssetRepaired" t
		   JOIN "TXMaster" m ON (m."ID" = t."TXMasterID")
		  WHERE "TXDate"::date <= ' || quote_literal(p_date) || ';' ;

	INSERT INTO "TXAssetRepaired"
	select * FROM dblink('db_connect', v_sql )
            as t1(txmasterid bigint,
                  assetid bigint,
                  repairedatlocationid bigint,
                  notes text,
                  maintcomponentid bigint,
                  txdamagedid bigint
                  );

	GET DIAGNOSTICS v_txassetrepaired_ctr = ROW_COUNT;

	v_sql = 'SELECT "TXMasterID",
                        "AssetID",
                        "TagID"
		   FROM "TXAssetTagAssigned" t
		   JOIN "TXMaster" m ON (m."ID" = t."TXMasterID")
		  WHERE "TXDate"::date <= ' || quote_literal(p_date) || ';' ;

	INSERT INTO "TXAssetTagAssigned"
	select * FROM dblink('db_connect', v_sql )
            as t1(txmasterid bigint,
                  assetid bigint,
                  tagid bigint
                  );

	GET DIAGNOSTICS v_txassettagassigned_ctr = ROW_COUNT;

	v_sql = 'SELECT "TXMasterID",
                        "AssetID",
                        "ResponsibleOrganizationID",
                        "ParentTx",
                        "ErpCode"
                   FROM "TXBillingAction" t
                   JOIN "TXMaster" m ON (m."ID" = t."TXMasterID")
		  WHERE "TXDate"::date <= ' || quote_literal(p_date) || ';' ;

	INSERT INTO "TXBillingAction"
	select * FROM dblink('db_connect', v_sql )
            as t1(txmasterid bigint,
                  assetid bigint,
                  responsibleorganizationid bigint,
                  parenttx bigint,
                  erpcode character varying
                  );
	
	GET DIAGNOSTICS v_txbillingaction_ctr = ROW_COUNT;

	v_sql = 'SELECT "TXMasterID",
			"Message"
		   FROM "TXException" t
                   JOIN "TXMaster" m ON (m."ID" = t."TXMasterID")
		  WHERE "TXDate"::date <= ' || quote_literal(p_date) || ';' ;

	INSERT INTO "TXException"
	select * FROM dblink('db_connect', v_sql )
            as t1(txmasterid bigint,
		  message text
		  );

	GET DIAGNOSTICS v_txexception_ctr = ROW_COUNT;

	v_sql = 'SELECT "TXMasterID",
			"OrderDetailID",
			"FilledAtLocationID"
		   FROM "TXOrderDetailFilled" t
                   JOIN "TXMaster" m ON (m."ID" = t."TXMasterID")
		  WHERE "TXDate"::date <= ' || quote_literal(p_date) || ';' ;

	INSERT INTO "TXOrderDetailFilled"
	select * FROM dblink('db_connect', v_sql )
            as t1(txmasterid bigint,
                  orderdetailid bigint,
                  filledatlocationd bigint
                  );

	GET DIAGNOSTICS v_txorderdetailfilled_ctr = ROW_COUNT;
	
	v_sql = 'SELECT "TXMasterID",
	                "AssetID",
	                "TagID"
		   FROM "TxAssetTagRemoved" t
                   JOIN "TXMaster" m ON (m."ID" = t."TXMasterID")
		  WHERE "TXDate"::date <= ' || quote_literal(p_date) || ';' ;

	INSERT INTO "TxAssetTagRemoved"
	select * FROM dblink('db_connect', v_sql )
            as t1(txmasterid bigint,
                  assetid bigint,
                  tagid bigint
                  );

	GET DIAGNOSTICS v_txassettagremoved_ctr = ROW_COUNT;

	v_sql = 'SELECT "TxMasterID",
	                "MoveDocType",
	                "MoveDocID",
	                "MoveCompleted"
		   FROM "TxPendingMoveSetup" t
                   JOIN "TXMaster" m ON (m."ID" = t."TxMasterID")
		  WHERE "TXDate"::date <= ' || quote_literal(p_date) || ';' ;

	INSERT INTO "TxPendingMoveSetup"
	select * FROM dblink('db_connect', v_sql )
            as t1(txmasterid bigint,
                  movedoctype character varying(10),
                  movedocid bigint,
                  movecompleted boolean
                  );

	GET DIAGNOSTICS v_txpendingmovesetup_ctr = ROW_COUNT;

-- Get total tx's inserted

	v_ctr := v_txassetcycle_ctr
	       + v_txassetdamaged_ctr
	       + v_txassetfilledemptied_ctr
	       + v_txassetfound_ctr
	       + v_txassetlost_ctr
	       + v_txassetmoved_ctr
	       + v_txassetOwnerChanged_ctr
	       + v_txassetprodasscheck_ctr
	       + v_txassetrepaired_ctr
	       + v_txassettagassigned_ctr
	       + v_txbillingaction_ctr
	       + v_txexception_ctr
	       + v_txorderdetailfilled_ctr
	       + v_txassettagremoved_ctr
	       + v_txpendingmovesetup_ctr;

-- Check that total tx's inserted = total from master
	IF v_ctr <> v_txmaster_ctr THEN
		v_flag = 'INSERT';
		v_msg := 'ERROR total transactions archived ' 
		         || v_ctr::text 
		         || ' does not equal TXMaster transactions ' 
		         || v_txmaster_ctr::text
		         || ' -> Aborting and rolling back.';
		RAISE EXCEPTION '%', v_msg;
	END IF;	

-- Delete the rows inserted from productions db
-- ON remote db, we need to define a transaction so a ROLLBACK can be done in event of problems
	PERFORM dblink_exec('db_connect', 'BEGIN;');

	v_sql = 'DELETE FROM "TXAssetCycle"
                  WHERE "TXMasterID" IN 
                       (SELECT "ID" FROM "TXMaster"
                         WHERE "TXDate"::date <= ' || quote_literal(p_date) || 
		   'AND "CycleEnd" IS NOT NULL 
                    AND "CycleEnd" <= ' || quote_literal(p_date) || ');' ;

-- Note: As dblink_exec returns a text of # deleted eg: DELETE 134, we need to get count as below
	SELECT dblink_exec('db_connect', v_sql) INTO v_msg;
	v_txassetcycle_ctr := overlay(v_msg placing '' from 1 for 6)::bigint;

	v_sql = 'DELETE FROM "TXAssetDamaged" t
                  WHERE "TXMasterID" IN 
                       (SELECT "ID" FROM "TXMaster"
                         WHERE "TXDate"::date <= ' || quote_literal(p_date) || ');' ;

	SELECT dblink_exec('db_connect', v_sql) INTO v_msg;
	v_txassetdamaged_ctr := overlay(v_msg placing '' from 1 for 6)::bigint;	

	v_sql = 'DELETE FROM "TXAssetFilledEmptied" t
                  WHERE "TXMasterID" IN 
                       (SELECT "ID" FROM "TXMaster"
                         WHERE "TXDate"::date <= ' || quote_literal(p_date) || ');' ;

	SELECT dblink_exec('db_connect', v_sql) INTO v_msg;
	v_txassetfilledemptied_ctr := overlay(v_msg placing '' from 1 for 6)::bigint;	

	v_sql = 'DELETE FROM "TXAssetFound" t
                  WHERE "TXMasterID" IN 
                       (SELECT "ID" FROM "TXMaster"
                         WHERE "TXDate"::date <= ' || quote_literal(p_date) || ');' ;

	SELECT dblink_exec('db_connect', v_sql) INTO v_msg;
	v_txassetfound_ctr := overlay(v_msg placing '' from 1 for 6)::bigint;	

	v_sql = 'DELETE FROM "TXAssetLost" t
                  WHERE "TXMasterID" IN 
                       (SELECT "ID" FROM "TXMaster"
                         WHERE "TXDate"::date <= ' || quote_literal(p_date) || ');' ;

	SELECT dblink_exec('db_connect', v_sql) INTO v_msg;
	v_txassetlost_ctr := overlay(v_msg placing '' from 1 for 6)::bigint;	

	v_sql = 'DELETE FROM "TXAssetMoved" t
                  WHERE "TXMasterID" IN 
                       (SELECT "ID" FROM "TXMaster"
                         WHERE "TXDate"::date <= ' || quote_literal(p_date) || ');' ;

	SELECT dblink_exec('db_connect', v_sql) INTO v_msg;
	v_txassetmoved_ctr := overlay(v_msg placing '' from 1 for 6)::bigint;	

	v_sql = 'DELETE FROM "TXAssetOwnerChanged" t
                  WHERE "TXMasterID" IN 
                       (SELECT "ID" FROM "TXMaster"
                         WHERE "TXDate"::date <= ' || quote_literal(p_date) || ');' ;

	SELECT dblink_exec('db_connect', v_sql) INTO v_msg;
	v_txassetOwnerChanged_ctr := overlay(v_msg placing '' from 1 for 6)::bigint;	

	v_sql = 'DELETE FROM "TXAssetProductAssociationCheck" t
                  WHERE "TXMasterID" IN 
                       (SELECT "ID" FROM "TXMaster"
                         WHERE "TXDate"::date <= ' || quote_literal(p_date) || ');' ;

	SELECT dblink_exec('db_connect', v_sql) INTO v_msg;
	v_txassetprodasscheck_ctr := overlay(v_msg placing '' from 1 for 6)::bigint;	

	v_sql = 'DELETE FROM "TXAssetRepaired" t
                  WHERE "TXMasterID" IN 
                       (SELECT "ID" FROM "TXMaster"
                         WHERE "TXDate"::date <= ' || quote_literal(p_date) || ');' ;

	SELECT dblink_exec('db_connect', v_sql) INTO v_msg;
	v_txassetrepaired_ctr := overlay(v_msg placing '' from 1 for 6)::bigint;	

	v_sql = 'DELETE FROM "TXAssetTagAssigned" t
                  WHERE "TXMasterID" IN 
                       (SELECT "ID" FROM "TXMaster"
                         WHERE "TXDate"::date <= ' || quote_literal(p_date) || ');' ;

	SELECT dblink_exec('db_connect', v_sql) INTO v_msg;
	v_txassettagassigned_ctr := overlay(v_msg placing '' from 1 for 6)::bigint;	

	v_sql = 'DELETE FROM "TXBillingAction" t
                  WHERE "TXMasterID" IN 
                       (SELECT "ID" FROM "TXMaster"
                         WHERE "TXDate"::date <= ' || quote_literal(p_date) || ');' ;

	SELECT dblink_exec('db_connect', v_sql) INTO v_msg;
	v_txbillingaction_ctr := overlay(v_msg placing '' from 1 for 6)::bigint;	

	v_sql = 'DELETE FROM "TXException" t
                  WHERE "TXMasterID" IN 
                       (SELECT "ID" FROM "TXMaster"
                         WHERE "TXDate"::date <= ' || quote_literal(p_date) || ');' ;

	SELECT dblink_exec('db_connect', v_sql) INTO v_msg;
	v_txexception_ctr := overlay(v_msg placing '' from 1 for 6)::bigint;	

	v_sql = 'DELETE FROM "TXOrderDetailFilled" t
                  WHERE "TXMasterID" IN 
                       (SELECT "ID" FROM "TXMaster"
                         WHERE "TXDate"::date <= ' || quote_literal(p_date) || ');' ;

	SELECT dblink_exec('db_connect', v_sql) INTO v_msg;
	v_txorderdetailfilled_ctr := overlay(v_msg placing '' from 1 for 6)::bigint;	

	v_sql = 'DELETE FROM "TxAssetTagRemoved" t
                  WHERE "TXMasterID" IN 
                       (SELECT "ID" FROM "TXMaster"
                         WHERE "TXDate"::date <= ' || quote_literal(p_date) || ');' ;

	SELECT dblink_exec('db_connect', v_sql) INTO v_msg;
	v_txassettagremoved_ctr := overlay(v_msg placing '' from 1 for 6)::bigint;	

	v_sql = 'DELETE FROM "TxPendingMoveSetup" t
                  WHERE "TxMasterID" IN 
                       (SELECT "ID" FROM "TXMaster"
                         WHERE "TXDate"::date <= ' || quote_literal(p_date) || ');' ;

	SELECT dblink_exec('db_connect', v_sql) INTO v_msg;
	v_txpendingmovesetup_ctr := overlay(v_msg placing '' from 1 for 6)::bigint;	

	v_sql = 'DELETE FROM "TXMaster" m
		  WHERE m."TXDate"::date <= ' || quote_literal(p_date) ||
                    'AND "ID" NOT IN 
                       ( SELECT "TXMasterID" 
                           FROM "TXAssetCycle"
                          WHERE "CycleEnd" IS NULL
                             OR "CycleEnd" > ' || quote_literal(p_date) || ') ;' ;

	SELECT dblink_exec('db_connect', v_sql) INTO v_msg;
	v_txmaster_ctr := overlay(v_msg placing '' from 1 for 6)::bigint;	


-- Get total tx's deleted

	v_ctr := v_txassetcycle_ctr
	       + v_txassetdamaged_ctr
	       + v_txassetfilledemptied_ctr
	       + v_txassetfound_ctr
	       + v_txassetlost_ctr
	       + v_txassetmoved_ctr
	       + v_txassetOwnerChanged_ctr
	       + v_txassetprodasscheck_ctr
	       + v_txassetrepaired_ctr
	       + v_txassettagassigned_ctr
	       + v_txbillingaction_ctr
	       + v_txexception_ctr
	       + v_txorderdetailfilled_ctr
	       + v_txassettagremoved_ctr
	       + v_txpendingmovesetup_ctr;

-- Check that total tx's deleted = total from master
	IF v_ctr <> v_txmaster_ctr THEN
		v_flag = 'DELETE';
		v_msg := 'ERROR total transactions deleted ' 
	         || v_ctr::text 
	         || ' does not equal TXMaster transactions ' 
	         || v_txmaster_ctr::text
	         || ' -> Aborting and rolling back.';
		RAISE EXCEPTION '%', v_msg;
	END IF;

--	COMMIT all transactions on remote db
	SELECT dblink_exec('db_connect', 'COMMIT;') INTO v_msg;
	
	PERFORM dblink_disconnect('db_connect');

  RETURN v_txmaster_ctr;

	EXCEPTION 
		WHEN RAISE_EXCEPTION THEN
		IF v_flag = 'INSERT' THEN
			PERFORM dblink_disconnect('db_connect');
			RETURN -100;
		ELSE 
			PERFORM dblink_exec('db_connect', 'ROLLBACK;');
			PERFORM dblink_disconnect('db_connect');
			RETURN -200;
		END IF;
		WHEN connection_exception THEN
		RETURN -1;
	END;

-- SAMPLE FUNCTION CALL
-- SELECT test_archive('10.2.0.36', 'test_archive_test', 'postgres', '<password_for_postgres>', '2008-01-16');

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
ALTER FUNCTION test_archive(text, text, text, text, date) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION test_archive(text, text, text, text, date) TO postgres;