Re: Re: Mapping output from a SEQUENCE into something non-repeating/colliding but random-looking?

[Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx>]

Just to follow up on this with a look at check digit generation and checkin:

Luhn's algorithm should do for the check digit, I think. It need not be
anything complex given the chances for collision in the sample space.
Additionally, it's commonly used, easily implemented and widely
understood since it's used in credit card numbers among many other things.

For anyone who later needs it, here's a handy verifier for the Luhn's
Algorithm check digit, written as plain SQL functions with all
integer-based computation (no string decomposition), along with a
corresponding check digit generator and associated utility functions:


CREATE OR REPLACE FUNCTION luhn_verify(int8) RETURNS boolean AS $$
SELECT
    -- Add the digits, doubling odd-numbered digits (counting left with
    -- least significant as zero), and see if the sum is evenly
    -- divisible by zero.
    MOD(SUM(
       -- Extract digit `n' counting left from least significant as zero
       MOD( ( $1::int8 / (10^n)::int8 ), 10::int8)
       -- Double odd-numbered digits
       * (MOD(n,2) + 1)
    ), 10) = 0
FROM generate_series(0, ceil(log( $1 ))::integer - 1) AS n;
$$ LANGUAGE 'SQL'
IMMUTABLE
STRICT;

COMMENT ON FUNCTION luhn_verify(int8) IS 'Return true iff the last digit
of the input is a correct check digit for the rest of the input
according to Luhn''s algorithm.'

CREATE OR REPLACE FUNCTION luhn_generate_checkdigit(int8) RETURNS int8 AS $$
SELECT
    -- Add the digits, doubling even-numbered digits (counting left
    -- with least-significant as zero). Subtract the remainder of
    -- dividing the sum by 10 from 10, and take the remainder
    -- of dividing that by 10 in turn.
    MOD(10 - MOD(SUM(
      MOD( ($1::int8 / (10^n)::int8), 10::int8 )
      * (2 - MOD(n,2)) -- double even digits
    ),10),10)::int8
FROM generate_series(0, ceil(log($1))::integer - 1) AS n;
$$ LANGUAGE 'SQL'
IMMUTABLE
STRICT;

COMMENT ON FUNCTION luhn_generate_checkdigit(int8) IS 'For the input
value, generate a check digit according to Luhn''s algorithm';

CREATE OR REPLACE FUNCTION luhn_generate(int8) RETURNS int8 AS $$
SELECT 10 * $1 + luhn_generate_checkdigit($1);
$$ LANGUAGE 'SQL'
IMMUTABLE
STRICT;

COMMENT ON FUNCTION luhn_generate(int8) IS 'Append a check digit
generated according to Luhn''s algorithm to the input value. The
input value must be no greater than (maxbigint/10).';

CREATE OR REPLACE FUNCTION luhn_strip(int8) RETURNS int8 AS $$
SELECT $1 / 10;
$$ LANGUAGE 'SQL'
IMMUTABLE
STRICT;

COMMENT ON FUNCTION luhn_strip(int8) IS 'Strip the least significant
digit from the input value. Intended for use when stripping the check
digit from a number including a Luhn''s algorithm check digit.';

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general