syntax error in function - Mailing list pgsql-novice

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