Thread: syntax error in function

syntax error in function

From
"Keith Worthington"
Date:
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


Re: syntax error in function

From
Tom Lane
Date:
"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

Re: syntax error in function

From
"Keith Worthington"
Date:
> "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


Re: syntax error in function

From
"Mike G."
Date:
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