Why are TRIM and SUBSTRING not working in this PL/pgSQL function?

user_4475290 0
Viewed 29

I wrote this very simple PL/pgSQL function in order to manage date formats in my database (datetuple is a custom type being a tuple of int's):

CREATE FUNCTION date2datetuple (_date text)
RETURNS datetuple AS
$BODY$
BEGIN
    IF _date ~ '[0-9]{4}' THEN
        RETURN (_date::int,
                _date::int);
    ELSIF _date ~ '[0-9]{4}ca' THEN
        RETURN (trim(trailing 'ca' from _date)::int, 
                trim(trailing 'ca' from _date)::int);
    ELSE
        RETURN (substring(_date from '^[0-9]{4}')::int - substring(_date from '[0-9]{2}$')::int,
                substring(_date from '^[0-9]{4}')::int + substring(_date from '[0-9]{2}$')::int);
    END IF;
END;
$BODY$
LANGUAGE plpgsql;

The function gets defined without any error, but when I input e.g. '1980ca' then it throws me this error:

ERROR:  invalid input syntax for type integer: "1950ca"
CONTEXT:  PL/pgSQL function date2datetuple(text) line 4 at RETURN
SQL state: 22P02

Upon further investigation it seems that any argument just gets passed on without being modified, which throws an error since '1950ca' cannot be cast as int. Why are the functions not doing anything?

plpgsql postgresql
1 Answers

According to the documentation:

Unlike LIKE patterns, a regular expression is allowed to match anywhere within a string, unless the regular expression is explicitly anchored to the beginning or end of the string.

You should anchor the pattern in the first and (probably) second comparison:

    ...
    IF _date ~ '^[0-9]{4}$' THEN
    ...
    ELSIF _date ~ '^[0-9]{4}ca$' THEN
    ...
user_19957380
Related