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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

"Keith Worthington" <keithw@narrowpathinc.com> writes:
> I am afraid I cannot see the
> forest through the trees. Can someone point out the error of my ways?

I think you're missing a semicolon at the end of the PERFORM.

plpgsql's syntax error reporting kinda sucks :-(. There are some
proposals afoot to improve it in 8.1 ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

> "Keith Worthington" <keithw@narrowpathinc.com> writes:
> > I am afraid I cannot see the
> > forest through the trees. Can someone point out the error of my ways?
>
> I think you're missing a semicolon at the end of the PERFORM.
>
> plpgsql's syntax error reporting kinda sucks :-(. There are some
> proposals afoot to improve it in 8.1 ...
>
> regards, tom lane

Thanks Tom.

As it turns out I was missing two semicolons and had three extra commas and
two underspecified values. Sheesh!

Kind Regards,
Keith

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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Usually when you enter an ' in a function it has to be escaped (\'\'). Depending on what you are creating the function in (pgadmin) they might not be displayed when viewing other functions.

mike

On Tue, Dec 21, 2004 at 12:22:49PM -0500, Keith Worthington wrote:
> 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;
a> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org