PL/pgSQL function to validate UPC and EAN barcodes - works! Improvements?

Поиск
Список
Период
Сортировка
От Miles Keaton
Тема PL/pgSQL function to validate UPC and EAN barcodes - works! Improvements?
Дата
Msg-id 59b2d39b05062422444ede36a8@mail.gmail.com
обсуждение исходный текст
Ответы Re: PL/pgSQL function to validate UPC and EAN barcodes
Список pgsql-general
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;

В списке pgsql-general по дате отправления:

Предыдущее
От: "Marc G. Fournier"
Дата:
Сообщение: Re: PostgreSQL Certification
Следующее
От: Sven Willenberger
Дата:
Сообщение: Re: PL/pgSQL function to validate UPC and EAN barcodes