Just a Theory: Validating UPCs with PL/pgSQL

Validating UPCs with PL/pgSQL

So I wanted to write a PL/pgSQL function to validate UPC codes. The rules for validation are:

The UPC must consist of 12 or 13 numerals
The last numeral is a checksum for the previous 11 numerals
The checksum is calculated as follows:
- Add the digits in the odd-numbered positions in the string and multiply by three
- Add in the digits in the even-numbered positions
- Subtract the result from the next-higher multiple of ten.

It took me a few minutes to whip up this implementation in Perl:

use List::Util qw(sum);

sub validate_upc {
    my $upc = shift;
    my @nums = split $upc;
    shift @nums if @nums == 13; # Support EAN codes.
    die "$upc is not a valid UPC" if @upc != 12;
    10 - (  sum( @nums[0,2,4,6,8,10] ) * 3
          + sum( @nums[1,3,5,7,9] )
    ) % 10 == $nums[11];
}

Trying to do the same thing in PL/pgSQL was harder, mainly because I couldn’t find an easy way to split a string up into its individual characters. string_to_array() seems ideal, but don’t follow the same rules as Perl when it comes to the empty string:

try=% select string_to_array('123', '');
 string_to_array
 -----------------
 {123}
(1 row)

Bummer. So I had to fall back on individual calls to substring(), instead:

CREATE OR REPLACE FUNCTION validate_upc (
upc text
) RETURNS boolean AS $$
DECLARE
    offset integer := 0;
BEGIN
    IF char_length(upc) = 13 THEN
        offset := 1;
    ELSIF char_length(upc) <> 12 THEN
        RAISE EXCEPTION '% is not a valid UPC', upc;
    END IF;

    IF 10 - (
        (
            substring(upc,  1 + offset, 1)::integer
          + substring(upc,  3 + offset, 1)::integer
          + substring(upc,  5 + offset, 1)::integer
          + substring(upc,  7 + offset, 1)::integer
          + substring(upc,  9 + offset, 1)::integer
          + substring(upc, 11 + offset, 1)::integer
         ) * 3
         + substring(upc,  2 + offset, 1)::integer
         + substring(upc,  4 + offset, 1)::integer
         + substring(upc,  6 + offset, 1)::integer
         + substring(upc,  8 + offset, 1)::integer
         + substring(upc, 10 + offset, 1)::integer
         ) % 10  = substring(upc, 12 + offset, 1)::integer
    THEN
        RETURN true;
    ELSE
        RETURN false;
    END IF;
END;
$$ LANGUAGE plpgsql;

This works, and seems pretty fast, but I’m wondering if there isn’t an easier way to do this in PL/pgSQL. Do you know of one? Leave me a comment.

Tags:

Comments & Trackbacks

Adrian Klaver wrote:

Try again with formatting. Here is how I calculated for UPCA. Don't know if it any easier.

CREATE OR REPLACE FUNCTION public.upc_check_digit(bpchar)
  RETURNS int4 AS
'
	DECLARE
	odd_number      INTEGER;
	odd_sum		INTEGER :=0;
	even_number	INTEGER;
	even_sum	INTEGER :=0;
	total		INTEGER;
	total_plus	INTEGER;
	check_sum 	INTEGER;
	BEGIN
	FOR i IN 1..11 LOOP
	odd_number:=substring($1,i,1)::INTEGER;
	odd_sum:=odd_sum + odd_number;
	i=i + 1;
	END LOOP;
	FOR i IN 2..10 LOOP
	even_number:=substring($1,i,1)::INTEGER;
	even_sum:=even_sum + even_number;
	i=i + 1;
	END LOOP;
	total:=(odd_sum*3) + even_sum;
	IF substring(total,length(total),1)::INTEGER=0 THEN
		check_sum:=0;
	ELSE
	total_plus:=total-substring(total,length(total),1)::INTEGER + 10;
	check_sum:=mod(total_plus,total);
	END IF;
	RETURN check_sum;
	END;
	'
  LANGUAGE 'plpgsql' VOLATILE;

Josh Berkus wrote:

Use PL/perl?

David,

Why not just use PL/Perl? PL/pgSQL is possibly the worst language in the universe (save Visual Basic) for string manipulation.

Theory wrote:

Re: Use PL/Perl

Josh

Since I can't load List::Util in PL/Perl, it's not a whole lot better:

CREATE OR REPLACE FUNCTION validate_upc_perl (
upc text
) RETURNS boolean AS $_$
    my @nums = split '', shift;
    shift @nums if @nums == 13; # Support EAN codes.
    10 - (
        ((  $nums[0] + $nums[2] + $nums[4] + $nums[6]
          + $nums[8] + $nums[10]) * 3)
        + $nums[1] + $nums[3] + $nums[5] + $nums[7] + $nums[9]
    ) % 10 == $nums[11] ? 'true' : 'false';
$_$ LANGUAGE plperl;

It does seem to be just about as fast, though.

—Theory

Ovid wrote:

The Perl is Fine

Frankly, I much prefer the Perl version. If it's just as fast, I think it's much clearer. No problem with leveraging one's existing knowledge. I do find it disappointing that one can't load libraries in PL/Perl, but it's a small price to pay.

Theory wrote:

The Perl is Fine

Ovid

In fact, you can load modules if you install plperlu, which is how you install an untrusted Perl. But as for this example, I'll stick with the PL/pgSQL for as long as I can, since it's just as fast and much easier for someone to install.

—Theory

Andreas Kretschmer wrote:

a simple str2array

--
-- this works like the perl's string_to_array()
--

create or replace function str2array(text) returns text[] as $$
declare i int;
        r text[];
begin
        for i in 1 .. length($1) loop
                r[i]=substring($1,i,1);
        end loop;
        return r;
end;
$$ language plpgsql immutable;

Discussion is now closed.

Just a Theory

Validating UPCs with PL/pgSQL

Comments & Trackbacks

Use PL/perl?

Re: Use PL/Perl

The Perl is Fine

The Perl is Fine

a simple str2array

Syndicate

Where I work

Twitterstream

Stuff I Read

PGXN Code

CPAN Code

vCard