syntax error in function

Hi All,

I am trying to create a trigger function that moves data from one table to
another. This is patterned after several working functions. After I create
the function I attempt to COPY data into the source table and I get an error.
I have commented out the entire ELSE section only to have postgres complain
about the IF statement. Then I created my own BOOLEAN variable to use instead
of the FOUND vraible and that didn't work. I am afraid I cannot see the
forest through the trees. Can someone point out the error of my ways?

IPADB=# COPY data_transfer.tbl_inventory_scanner FROM '/tmp/scanner.out';
WARNING: plpgsql: ERROR during compile of tf_xfr_scanner_data near line 31
ERROR: syntax error at or near "ELSE"
IPADB=#

DROP FUNCTION data_transfer.tf_xfr_scanner_data() CASCADE;

CREATE OR REPLACE FUNCTION data_transfer.tf_xfr_scanner_data() RETURNS TRIGGER
AS '
DECLARE
rcrd_scanner RECORD;
BEGIN
FOR rcrd_scanner IN SELECT data_transfer.tbl_inventory_scanner.employee_id,
data_transfer.tbl_inventory_scanner.item_id,
data_transfer.tbl_inventory_scanner.quantity,
data_transfer.tbl_inventory_scanner.scan_date,
data_transfer.tbl_inventory_scanner.scan_time
FROM data_transfer.tbl_inventory_scanner
ORDER BY data_transfer.tbl_inventory_scanner.scan_date,
data_transfer.tbl_inventory_scanner.scan_time,
data_transfer.tbl_inventory_scanner.item_id

LOOP
-- Attempt to retrieve a matching record from the target table.
PERFORM inventory.tbl_scanner.item_id
FROM inventory.tbl_scanner
WHERE inventory.tbl_scanner.scan_timestamp = CAST( CAST(
rcrd_scanner.scan_date || '' '' || rcrd_scanner.scan_time AS text) AS timestamp)
AND inventory.tbl_scanner.item_id = rcrd_scanner.item_id
IF NOT FOUND THEN
-- A matching record was not found. Insert the record.
INSERT INTO inventory.tbl_scanner
( scan_timestamp,
item_id,
quantity,
employee_id )
VALUES ( CAST( CAST( scan_date || '' '' || scan_time AS text
) AS timestamp ),
rcrd_scanner.item_id,
rcrd_scanner.quantity,
rcrd_scanner.employee_id );
ELSE
-- A matching record was found. This is an error.
FOUND := FALSE;
END IF;
IF NOT FOUND THEN
-- The record was not inserted nor updated properly. Write it to the
load_error table.
INSERT INTO load_error.tbl_inventory_scanner
( employee_id,
item_id,
quantity,
scan_date,
scan_time )
VALUES ( rcrd_scanner.employee_id,
rcrd_scanner.item_id,
rcrd_scanner.quantity,
rcrd_scanner.scan_date,
rcrd_scanner.scan_time );
END IF;
-- The record has been processed. Remove it from the transfer table.
DELETE
FROM data_transfer.tbl_inventory_scanner
WHERE data_transfer.tbl_inventory_scanner.scan_date =
rcrd_scanner.scan_date,
AND data_transfer.tbl_inventory_scanner.scan_time =
rcrd_scanner.scan_time,
AND data_transfer.tbl_inventory_scanner.item_id = rcrd_scanner.item_id
END LOOP;
RETURN NULL;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER tgr_xfr_scanner_data
AFTER INSERT
ON data_transfer.tbl_inventory_scanner
FOR EACH ROW EXECUTE PROCEDURE data_transfer.tf_xfr_scanner_data();

Kind Regards,
Keith

______________________________________________
99main Internet Services http://www.99main.com