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.