Verhoeff Checksum function for PostgresSQL

Question

I've found a Verhoeff Checksum function for PostgresSQL at: https://github.com/HIISORG/SNOMED-CT-PostgreSQL/blob/master/Verhoeff.sql

CREATE OR REPLACE FUNCTION verhoeff_generate (
  input numeric = NULL::numeric
)
RETURNS smallint AS $$
DECLARE
  _c SMALLINT := 0;
  _m SMALLINT;
  _i SMALLINT := 0;
  _n VARCHAR(255);
  -- Delcare array
  _d CHAR(100) := '0123456789123406789523401789563401289567401239567859876043216598710432765982104387659321049876543210';
  _p CHAR(80) := '01234567891576283094580379614289160435279453126870428657390127938064157046913258';
  _v CHAR(10) := '0432156789';
BEGIN
  _n := REVERSE(input::TEXT);
  WHILE _i<length(_n) LOOP
    _m := CAST(SUBSTRING(_p,(((_i + 1)%8)*10) + CAST(SUBSTRING(_n, _i+1, 1) AS SMALLINT) + 1, 1) AS SMALLINT);

    _c := CAST (substring(_d, (_c *10 + _m + 1), 1) AS SMALLINT);
    _i := _i + 1;
  END LOOP;

  RETURN CONCAT(input, CAST(substring(_v,_c+1,1) as SMALLINT));
END; $$
LANGUAGE 'plpgsql'
IMMUTABLE
RETURNS NULL ON NULL INPUT;

I've modified the RETURN, so that it would concatenate the INPUT with the Checksum digit:

RETURN CONCAT(input, CAST(substring(_v,_c+1,1) as SMALLINT));

And I get the error:

[2020-02-20 11:53:19] [22003] ERROR: value "331010000014" is out of range for type smallint
[2020-02-20 11:53:19] Where: PL/pgSQL function verhoeff_generate(numeric) while casting return value to function's return type

---

I've tried:

 RETURN CONCAT(input, CAST(substring(_v,_c+1,1) as BIGINT));

Still getting the same error.

Answer 1

You've modified the code that gives the return value, away from the original smallint it was returning, to now be a string. (CONCAT function outputs a string - you can cast numbers as many times as you like before you feed them into concat, but they will be converted into strings and then concatenated, and concat outputs a string, no matter what you feed into it).

CONCAT is now returning you a string containing too many digits (it is too numerically large) to fit into a smallint - a conversion that PG is attempting to carry out implicitly for you. This represents the core problem:

CREATE OR REPLACE FUNCTION return_big_number ()
RETURNS smallint AS $$
  RETURN '32769'; --string of a number that is too big for a smallint
END; $$

'32769' is a string that cannot be converted to a smallint, because it's simply too numerically great - smallint caps out at 32767. Similarly by using concat, you're generating a string that contains digits representing a number too numerically large for a smallint

---

Either change the function declaration at the top so that it returns a suitable string:

RETURNS smallint AS $$
        ^^^^^^^^
       change this to perhaps "RETURNS text AS $$"

Or if having the output as numeric would suit you better, change the function so it declares to return a numeric datatype that can represent more digits than a smallint, and change the return value calculation to keep it numeric (multiply the input by some power of 10, and add the checksum, rather than changing the input to string and concatenating the checksum)