postgresql function not raising exception

Question

Im trying to test out pg_input_is_valid & pg_input_error_info and it doesnt seem to work.

From the below example i expect it to raise a message that 56.7899999 does not meet the criteria. Any ideas why there is no raised exception?

CREATE OR REPLACE FUNCTION input_check(t int[])
RETURNS int AS $$
DECLARE
    current int; 
    ok int := 0;  
    e text;
BEGIN
    FOREACH current IN ARRAY t LOOP
        IF pg_input_is_valid(current, 'numeric(5,2)') THEN
            ok := ok + 1;
        ELSE
            SELECT message, detail
            INTO e
            FROM pg_input_error_info(current, 'numeric(5,2)');
            RAISE NOTICE 'Skipping [%] because it is not valid: %', current, e;
        END IF;
    END LOOP;

    RETURN ok;
END
$$ LANGUAGE plpgsql;
CREATE FUNCTION

postgres=#  SELECT input_check(ARRAY['12.34', '56.7899999']);
-[ RECORD 1 ]--
input_check | 2

raise exception for input value '56.7899999'

Answer 1

To get the function to run I changed it to:

CREATE OR REPLACE FUNCTION public.input_check(t text[])
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
    current text;
    ok int := 0;
    e text;
BEGIN
    FOREACH current IN ARRAY t LOOP
        IF pg_input_is_valid(current, 'numeric(5,2)') THEN
            ok := ok + 1;
        ELSE
            SELECT message, detail
            INTO e
            FROM pg_input_error_info(current, 'numeric(5,2)');
            RAISE NOTICE 'Skipping [%] because it is not valid: %', current, e;
        END IF;
    END LOOP;

    RETURN ok;
END
$function$

Running the above still yielded:

SELECT input_check(ARRAY['12.34', '56.7899999']);
 input_check 
-------------
           2

The reason is that per Functions info:

pg_input_is_valid ( string text, type text ) → boolean Tests whether the given string is valid input for the specified data type; ...

As this shows:

select 56.7899999::numeric(5,2);
 numeric 
---------
   56.79

56.7899999 is a valid input versus something like:

select '56.7899999'::integer;
ERROR:  invalid input syntax for type integer: "56.7899999"
LINE 1: select '56.7899999'::integer;