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'
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;