Re: PL/pgSQL function to validate UPC and EAN barcodes

> > I've made a PL/pgSQL function to validate UPC and EAN barcodes.
> > It works correctly, but is a little ugly.
> > Wondering if any PL/pgSQL experts can offer some suggestions. (I'm
> > new to PL/pgSQL.)

For what it's worth, here's a function I'm using to calculate the checksum of
an EAN barcode, it shows an alternative approach.

CREATE OR REPLACE FUNCTION checksum_ean(numeric(12,0))
RETURNS integer
LANGUAGE 'plpgsql'
IMMUTABLE
STRICT
SECURITY INVOKER
AS ' DECLARE
article_id ALIAS FOR $1;
ean12 TEXT;
chksm INTEGER := 0;
BEGIN
-- check article id range
IF (article_id < 0) OR (article_id > 1E12 - 1) THEN
RAISE EXCEPTION ''WARNING: Illegal article id !'';
END IF;

-- textual representation, prepend ean base when necessary
IF (art_id < 1E5) THEN
ean12 = ''8714075'' || to_char(art_id, ''FM00000'');
ELSE
ean12 = to_char(art_id, ''FM000000000000'');
END IF;

-- loop over the digits and calculate the checksum
FOR i IN 1..12 LOOP
IF (i % 2) THEN
chksm = (chksm + int4(substr( ean12, i, 1))) % 10;
ELSE
chksm = (chksm + 3 * int4(substr( ean12, i, 1))) % 10;
END IF;
END LOOP;
IF (chksm <> 0) THEN
RETURN (10 - chksm);
ELSE
RETURN chksm;
END IF;
END;';

--
Best,

Frank.