Re: syntax error in function - Mailing list pgsql-novice

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