Perhaps the attached will help. It's a sample function that I wrote and
tested a few years ago on PG v8.3
It worked then, so it should be a good model for you.
On Wed, Dec 16, 2015 at 8:00 PM, Adrian Klaver <[email protected]>
wrote:
> On 12/16/2015 04:53 PM, James Sewell wrote:
>
>> No it is not.
>>
>> Just in case I tried setting it to 'postgres', logged in without -U
>> (doesn't work without PGUSER set) and tried the operation again.
>>
>
> Do you mean this:
>
> DETAIL: FATAL: role "PRDSWIDEGRID01$" does not exist
>
> or that you could not connect for another reason?
>
> If for the original reason, does the role PRDSWIDEGRID01$ actually exist
> somewhere?
>
>
>
>> Same result.
>>
>> Cheers,
>>
>>
>> James Sewell,
>> PostgreSQL Team Lead / Solutions Architect
>> ______________________________________
>>
>> Level 2, 50 Queen St, Melbourne VIC 3000
>>
>> *P *(+61) 3 8370 8000***W* www.lisasoft.com *F *(+61) 3 8370 8099
>>
>> On Wed, Dec 16, 2015 at 3:26 PM, Joe Conway <[email protected]
>> <mailto:[email protected]>> wrote:
>>
>> On 12/15/2015 06:24 PM, James Sewell wrote:
>> > I have a Windows PostgreSQL server where dblink_connect fails to
>> pick up
>> > the current user as follows:
>>
>> > ffm=# SELECT dblink_connect('master', 'dbname=ffm');
>> > ERROR: could not establish connection
>> > DETAIL: FATAL: role "PRDSWIDEGRID01$" does not exist
>>
>> > Has anyone seen this before? It seems very odd to me, I have another
>> > identical machine (except for being on 9.4.0) which this works on.
>>
>>
>> Do you happen to have a PGUSER variable defined in your environment
>> (that is, in the environment as seen by the OS user the postmaster
>> runs
>> under)?
>>
>> See: http://www.postgresql.org/docs/9.4/static/libpq-envars.html
>>
>> Joe
>>
>> --
>> Crunchy Data - http://crunchydata.com
>> PostgreSQL Support for Secure Enterprises
>> Consulting, Training, & Open Source Development
>>
>>
>>
>> ------------------------------------------------------------------------
>> The contents of this email are confidential and may be subject to legal
>> or professional privilege and copyright. No representation is made that
>> this email is free of viruses or other defects. If you have received
>> this communication in error, you may not copy or distribute any part of
>> it or otherwise disclose its contents to anyone. Please advise the
>> sender of your incorrect receipt of this correspondence.
>>
>>
>
> --
> Adrian Klaver
> [email protected]
>
>
> --
> Sent via pgsql-general mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
-- 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;
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
