参见https://www.postgresql.org/docs/13/functions-matching.html
如果愿意，您可以使用~文本操作符，或者使用regexp_matches函数。下面是使用regexp_matches的示例。我还更正了您的正则表达式。您缺少终止匹配$

create or replace function sample_regex_match() returns void as $$

    declare
        sample_strings text[];
    BEGIN
    sample_strings:=array['abc:skp:293043204','abc:skp:456','abc:skp:789','dontmatch','abc:skp:23hj29490'];
    for i in 1..array_length(sample_strings,1) loop
        if cardinality(regexp_matches(sample_strings[i],'abc:skp:([\d]+)$'))>0 then
            raise notice 'The string "%" matches the regex', sample_strings[i];
        else
            raise notice 'The string "%" does not match the regex', sample_strings[i];
        end if;
    end loop;
END $$ language plpgsql;
select sample_regex_match();

这将输出

The string "abc:skp:293043204" matches the regex
The string "abc:skp:456" matches the regex
The string "abc:skp:789" matches the regex
The string "dontmatch" does not match the regex
The string "abc:skp:23hj29490" does not match the regex