Sum all ascii values for every character of varchar in PostgreSQL-CodePudding

I have a table I want to partition based on HASH. This table has a column with varchar, which is the key I want to use to partition.

Ofc. I can't partition based on HASH with varchar, therefore I will SUM all the ASCII values of each character in the varchar.

I hope to get some help to stitch together a function, which takes a varchar parameter and returns the SUM as an INTEGER.

I have tried several variations - some of them commented out -, this is how it looks so far:

CREATE OR REPLACE FUNCTION sum_string_ascii_values(theString varchar)
    RETURNS INTEGER
    LANGUAGE plpgsql
AS
    $$
    DECLARE
        theSum INTEGER;
    BEGIN
        -- Sum on all ascii values coming from the every single char from the input varchar.
        SELECT SUM( val )
            FROM LATERAL ( SELECT ASCII( UNNEST( STRING_TO_ARRAY( LOWER(theString), null) ) ) ) AS val
        INTO theSum;

        --SELECT SUM(val) FROM ASCII( UNNEST( STRING_TO_ARRAY( LOWER(theString), null) ) ) AS val INTO theSUM;
        --RETURN SUM( ASCII( UNNEST( STRING_TO_ARRAY( LOWER(theString), null) ) ) );

        RETURN theSUM;
    END;
    $$;

I hope someone will be able to write and explain a solution to this problem.

CodePudding user response：

Instead of using SELECT to sum the characters, you can loop through the string instead

CREATE OR REPLACE FUNCTION sum_string_ascii_values(input text) RETURNS int LANGUAGE plpgsql AS $$
DECLARE
  hash int = 0;
  pos int = 0;
BEGIN
  WHILE pos <= length(input) LOOP
    hash =  hash   ascii(upper(substr(input, pos, 1)));
    pos = pos   1;
  END LOOP;
 RETURN hash;
END;
$$;

Here is a link to a dbfiddle to demonstrate https://dbfiddle.uk/yfhpHyT1