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 <adrian.klaver@xxxxxxxxxxx> 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 <mail@xxxxxxxxxxxxx
<mailto:mail@xxxxxxxxxxxxx>> 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
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
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.![]()
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 (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
