Обсуждение: PL/pgSQL function to validate UPC and EAN barcodes - works! Improvements?

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.)

Main questions:
#1 - I wanted to add a "0" to the front of the barcode if it was only
12 characters long.  Seems that the incoming "barcode" variable was
immutable, so I had to create a new variable ("b") to hold the
possibly-new version.   Any more elegant way to do this?

#2 - The big ugly : having to cast every digit in the substring into
an integer so I could add them together.   Any shorter way to do this?

For details on how it's validated, see "COMPUTING THE CHECKSUM DIGIT", here:
http://www.barcodeisland.com/ean13.phtml

Thanks!


------------
CREATE OR REPLACE FUNCTION valid_barcode(barcode text) RETURNS boolean
AS $function$
DECLARE
  b text;
  odd int;
  even int;
  s int;
BEGIN
  IF LENGTH(barcode) < 12 OR LENGTH(barcode) > 13 THEN
    return false;
  END IF;
  -- normalize UPC and EAN to both be 13 digits
  IF LENGTH(barcode) = 12 THEN
    b = '0' || barcode;
  ELSE
    b = barcode;
  END IF;
  -- sum of odd digits times 3, plus sum of even digits
  even = CAST(SUBSTR(b, 1, 1) AS int) + CAST(SUBSTR(b, 3, 1) AS int) +
CAST(SUBSTR(b, 5, 1) AS int) + CAST(SUBSTR(b, 7, 1) AS int) +
CAST(SUBSTR(b, 9, 1) AS int) + CAST(SUBSTR(b, 11, 1) AS int);
  odd = CAST(SUBSTR(b, 2, 1) AS int) + CAST(SUBSTR(b, 4, 1) AS int) +
CAST(SUBSTR(b, 6, 1) AS int) + CAST(SUBSTR(b, 8, 1) AS int) +
CAST(SUBSTR(b, 10, 1) AS int) + CAST(SUBSTR(b, 12, 1) AS int);
  s = (3 * odd) + even;
  -- remainder to nearest 10 should be same as last check digit
  IF (CAST((CEIL(CAST(s AS float8) / 10) * 10) AS int) % s) =
CAST(SUBSTR(b, 13, 1) AS int) THEN
    return true;
  ELSE
    return false;
  END IF;
END;
$function$ LANGUAGE plpgsql;

Miles Keaton presumably uttered the following on 06/25/05 01:44:
> 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.)
>
> Main questions:
> #1 - I wanted to add a "0" to the front of the barcode if it was only
> 12 characters long.  Seems that the incoming "barcode" variable was
> immutable, so I had to create a new variable ("b") to hold the
> possibly-new version.   Any more elegant way to do this?
>
> #2 - The big ugly : having to cast every digit in the substring into
> an integer so I could add them together.   Any shorter way to do this?
>
> For details on how it's validated, see "COMPUTING THE CHECKSUM DIGIT", here:
> http://www.barcodeisland.com/ean13.phtml
>
> Thanks!
>
>
> ------------
> CREATE OR REPLACE FUNCTION valid_barcode(barcode text) RETURNS boolean
> AS $function$
> DECLARE
>   b text;
>   odd int;
>   even int;
>   s int;
> BEGIN
>   IF LENGTH(barcode) < 12 OR LENGTH(barcode) > 13 THEN
>     return false;
>   END IF;
>   -- normalize UPC and EAN to both be 13 digits
>   IF LENGTH(barcode) = 12 THEN
>     b = '0' || barcode;
>   ELSE
>     b = barcode;
>   END IF;
>   -- sum of odd digits times 3, plus sum of even digits
>   even = CAST(SUBSTR(b, 1, 1) AS int) + CAST(SUBSTR(b, 3, 1) AS int) +
> CAST(SUBSTR(b, 5, 1) AS int) + CAST(SUBSTR(b, 7, 1) AS int) +
> CAST(SUBSTR(b, 9, 1) AS int) + CAST(SUBSTR(b, 11, 1) AS int);
>   odd = CAST(SUBSTR(b, 2, 1) AS int) + CAST(SUBSTR(b, 4, 1) AS int) +
> CAST(SUBSTR(b, 6, 1) AS int) + CAST(SUBSTR(b, 8, 1) AS int) +
> CAST(SUBSTR(b, 10, 1) AS int) + CAST(SUBSTR(b, 12, 1) AS int);
>   s = (3 * odd) + even;
>   -- remainder to nearest 10 should be same as last check digit
>   IF (CAST((CEIL(CAST(s AS float8) / 10) * 10) AS int) % s) =
> CAST(SUBSTR(b, 13, 1) AS int) THEN
>     return true;
>   ELSE
>     return false;
>   END IF;
> END;
> $function$ LANGUAGE plpgsql;
>

Perhaps it would be faster to accept the UPC code as a bigint in your
function then your initial tests would be by value (if barcode <
10,000,000,000,000,000 ... etc); you would only have to cast once in the
case of a short barcode (b = '0' || barcode)::bigint

Then you could create a loop, with a counter i starting at
1,000,000,000,000;
i := 1000000000000
WHILE i > 0 LOOP
    odd := odd + (b/i); b := b - ((b/i) *i); i = i/10;
    even := even + (b/i); b := b - ((b/i)*i); i = i/10;
END LOOP;
-- and so on

The math may be a bit off there, but the idea is to play with the number
as a number instead of a lot of casting. Just a thought.

Sven

> > 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.