Re: PL/pgSQL function to validate UPC and EAN barcodes

[Frank van Vugt <ftm.van.vugt@xxxxxxx>]

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq