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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
