Create CHECK constraint based on complex algorithm

Function

You could fix your function (errors marked with !!! ):

CREATE OR REPLACE FUNCTION pg_temp.org(volunteer_id VARCHAR)
RETURNS CHAR AS $$
DECLARE
    dob_individualized VARCHAR(9);
    dob VARCHAR(9);  --  VARCHAR(6) was too short !!!
    mid_character CHAR(1);
    control_character CHAR(1);
    remainder INT;
BEGIN
    -- Extract date of birth and individualized string from the volunteer ID
    dob := SUBSTRING(volunteer_id FROM 1 FOR 6);
    dob_individualized := SUBSTRING(volunteer_id FROM 8 FOR 3);  -- offset 7 was wrong !!!

    -- Determine the mid-character based on the year of birth
    CASE 
        WHEN dob BETWEEN '000001' AND '999999' THEN mid_character := '+';
        WHEN dob BETWEEN '00A001' AND '99F999' THEN mid_character := '-';
        ELSE mid_character := '';
    END CASE;

    -- Concatenate the date of birth and individualized string
    dob := dob || dob_individualized;

    -- Calculate the remainder
    remainder := CAST(dob AS INT) % 31;

    -- Determine the control character
    CASE remainder
        WHEN 10 THEN control_character := 'A';
        WHEN 11 THEN control_character := 'B';
        WHEN 12 THEN control_character := 'C';
        WHEN 13 THEN control_character := 'D';
        WHEN 14 THEN control_character := 'E';
        WHEN 15 THEN control_character := 'F';
        WHEN 16 THEN control_character := 'H';
        WHEN 17 THEN control_character := 'J';
        WHEN 18 THEN control_character := 'K';
        WHEN 19 THEN control_character := 'L';
        WHEN 20 THEN control_character := 'M';
        WHEN 21 THEN control_character := 'N';
        WHEN 22 THEN control_character := 'P';
        WHEN 23 THEN control_character := 'R';
        WHEN 24 THEN control_character := 'S';
        WHEN 25 THEN control_character := 'T';
        WHEN 26 THEN control_character := 'U';
        WHEN 27 THEN control_character := 'V';
        WHEN 28 THEN control_character := 'W';
        WHEN 29 THEN control_character := 'X';
        WHEN 30 THEN control_character := 'Y';
        ELSE control_character := CAST(remainder AS CHAR(1));
    END CASE;

    RETURN control_character;
END;
$$ LANGUAGE plpgsql;

But most of it is just noise. Boils down to:

CREATE OR REPLACE FUNCTION calculate_control_character(volunteer_id text)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT
RETURN ('[0:30]={0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,H,J,K,L,M,N,P,R,S,T,U,V,W,X,Y}'::text[])
       [(left($1, 6) || substring($1, 8, 3))::int % 31];

Basically picks an array element according to the position computed with your formula.

Using the short syntax of standard SQL functions. See:

• What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?

Add appropriate function labels. In this case: IMMUTABLE, PARALLEL SAFE, STRICT. The first two are crucial for performance! See:

• How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?
• https://dba.stackexchange.com/questions/251274/when-to-mark-functions-as-parallel-restricted-vs-parallel-safe
• Function executes faster without STRICT modifier?

Postgres arrays are 1-based by default. The modulo operator % returns 0-based. You can either add 1, or work with a custom-index array (cheaper). See:

• Normalize array subscripts so they start with 1

Don't use the data type char(n). See:

• Any downsides of using data type "text" for storing strings?

CHECK constraint

Also a bit cheaper and shorter:

ALTER TABLE volunteer
ADD CONSTRAINT chk_validvolunteerid CHECK (
   length(id) = 11
   AND substring(id, 7, 1) = ANY ('{+,-,A,B,C,D,E,F,X,Y,W,V,U}')
   AND right(id, 1) = calculate_control_character(id)
);

You could even just use the expression from my condensed function directly without creating a function at all.

Just remember to document what you are doing and why, either way.

-- Create a function to calculate the control character

CREATE OR REPLACE FUNCTION calculate_control_character(volunteer_id VARCHAR)
RETURNS CHAR AS $$
DECLARE
    dob_individualized VARCHAR(9);
    dob VARCHAR(6);
    mid_character CHAR(1);
    control_character CHAR(1);
    remainder INT;
BEGIN
    -- Extract date of birth and individualized string from the volunteer ID
    dob := SUBSTRING(volunteer_id FROM 1 FOR 6);
    dob_individualized := SUBSTRING(volunteer_id FROM 7 FOR 3);

    -- Determine the mid-character based on the year of birth
    CASE 
        WHEN dob BETWEEN '000001' AND '999999' THEN mid_character := '+';
        WHEN dob BETWEEN '00A001' AND '99F999' THEN mid_character := '-';
        ELSE mid_character := '';
    END CASE;

    -- Concatenate the date of birth and individualized string
    dob := dob || dob_individualized;

    -- Calculate the remainder
    remainder := CAST(dob AS INT) % 31;

    -- Determine the control character
    CASE remainder
        WHEN 10 THEN control_character := 'A';
        WHEN 11 THEN control_character := 'B';
        WHEN 12 THEN control_character := 'C';
        WHEN 13 THEN control_character := 'D';
        WHEN 14 THEN control_character := 'E';
        WHEN 15 THEN control_character := 'F';
        WHEN 16 THEN control_character := 'H';
        WHEN 17 THEN control_character := 'J';
        WHEN 18 THEN control_character := 'K';
        WHEN 19 THEN control_character := 'L';
        WHEN 20 THEN control_character := 'M';
        WHEN 21 THEN control_character := 'N';
        WHEN 22 THEN control_character := 'P';
        WHEN 23 THEN control_character := 'R';
        WHEN 24 THEN control_character := 'S';
        WHEN 25 THEN control_character := 'T';
        WHEN 26 THEN control_character := 'U';
        WHEN 27 THEN control_character := 'V';
        WHEN 28 THEN control_character := 'W';
        WHEN 29 THEN control_character := 'X';
        WHEN 30 THEN control_character := 'Y';
        ELSE control_character := CAST(remainder AS CHAR(1));
    END CASE;

    RETURN control_character;
END;
$$ LANGUAGE plpgsql;

-- Add a check constraint to the Volunteer table

    ALTER TABLE Volunteer
    ADD CONSTRAINT CHK_ValidVolunteerID CHECK (
        LENGTH(ID) = 11 AND
        SUBSTRING(ID FROM 7 FOR 1) IN ('+', '-', 'A', 'B', 'C', 'D', 'E', 'F', 'X', 'Y', 'W', 'V', 'U') AND
        SUBSTRING(ID FROM 11 FOR 1) = calculate_control_character(ID)
    );

What you are asking for validating a column value before insert, and if it fails than return some sort of error, in this case you can create a check constraint which calls a custom function to validate volunteer_id: Your table can look somewhat look like:

CREATE TABLE volunteer (
    id SERIAL PRIMARY KEY,
    volunteer_id TEXT NOT NULL,
    CONSTRAINT chk_volunteer_id CHECK (validate_volunteer_id(volunteer_id))
);

if table already exists than:

ALTER TABLE volunteer
ADD CONSTRAINT chk_volunteer_id CHECK (validate_volunteer_id(volunteer_id));

before running this you will have to check all existing data meets the criteria already or the alter statement will fail.

Now regarding the custom function:

CREATE OR REPLACE FUNCTION validate_volunteer_id(vol_id TEXT) 
RETURNS BOOLEAN AS $$
DECLARE
    dob TEXT;
    individualized_string TEXT;
    control_character TEXT;
    separator CHAR;
    nine_digit_string BIGINT;
    remainder INTEGER;
    valid_control_characters CHAR[];
BEGIN
    IF LENGTH(vol_id) <> 11 THEN
        RETURN FALSE;
    END IF;

    dob := SUBSTRING(vol_id FROM 1 FOR 6);
    separator := SUBSTRING(vol_id FROM 7 FOR 1);
    individualized_string := SUBSTRING(vol_id FROM 8 FOR 3);
    control_character := SUBSTRING(vol_id FROM 11 FOR 1);

    IF separator NOT IN ('+', '-', 'A', 'B', 'C', 'D', 'E', 'F', 'X', 'Y', 'W', 'V', 'U') THEN
        RETURN FALSE;
    END IF;

    nine_digit_string := (dob || individualized_string)::BIGINT;

    remainder := nine_digit_string % 31;

    valid_control_characters := ARRAY['0', '1', '2', '3', '4', '5', '6', '7', '8', '9',
                                      'A', 'B', 'C', 'D', 'E', 'F', 'H', 'J', 'K', 'L',
                                      'M', 'N', 'P', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y'];
    IF control_character <> valid_control_characters[remainder + 1] THEN
        RETURN FALSE;
    END IF;

    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

Now when you will try to insert a new row it will be validated by the validate_volunteer_id function. If the volunteer_id does not meet the criteria, the insert will fail with a constraint violation error.

Hope this is what you were looking for

You don't need a function here, you can use a single CHECK constraint, using Regex, some string manipulation, conversions and array indexing.

  code char(11) CHECK (
    code SIMILAR TO '[0-9]{6}[+\-ABCDEFXYWVU][0-9]{3}[0-9A-FHJ-NPR-Y]'
    AND
      to_date(
        LEFT(code, 4) ||
        CASE
          WHEN SUBSTR(code, 7, 1) IN ('U','V','W','X','Y','-') THEN '19'
          WHEN SUBSTR(code, 7, 1) = '+' THEN '18'
          ELSE '20'
        END ||
        SUBSTR(code, 5, 2), 'DDMMYYYY') IS NOT NULL
    AND RIGHT(code, 1) =
      ( '{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, H, J, K, L, M, N, P, R, S, T, U, V, W, X, Y}'::char(1)[] )
      [ LEFT(code, 6)::int % 31 + 1 ]
  )

• The Regex ensures the string is exactly 6 digits, then one of the convention characters, 3 digit, and a control character.
• We use SUBSTRING and a CASE to get a DDMMYYYY date string, which we convert to ensure it actually works as a date.
• We ensure the control character matches the value from an array of control characters, where the index to that value comes from converting the first 6 digits to an int and Mod-ing by 31.

db<>fiddle