Przejdź do treści

pg_input_is_valid i pg_input_error_info

  • przez

W tym poście chciałbym przedstawić dwie funkcje jakie zostały wprowadzone do PostgreSQL 16: pg_input_is_valid oraz pg_input_error_info Moim zdaniem ułatwiają pracę z walidacją parametrów a zwłaszcza takich, które chcemy konwertować.

Dokumentację tych funkcji znajdziesz tutaj. Tak pokrótce napiszę Ci, że obie służą do tego samego: sprawdzają czy wartość zmiennej jest wymaganego typu. pg_input_is_valid zwraca true (czyli boolean 😉) jeśli wartość zmiennej jest danego typu a pg_input_error_info zwraca record (message text, detail text, hint text, sql_error_code text) czyli trochę więcej szczegółów 😉 Oczywiście to samo można zrobić w inny sposób i każdy z nas zna przynajmniej milion takich sposobów ale lepiej mieć wybór i to nie tylko w bazach danych 😉

Aby zaprezentować Ci jak można ich użyć napiszę prostą funkcję get_interval_hours, która będzie przyjmować tablicę stringów z interwałami a zwracać będzie ilość godzin po zsumowaniu tych interwałów. Czyli dla dwóch interwałów po 1 dzień powinna zwrócić 48 godzin. Natomiast jeśli jeden z elementów przesłanej tablicy nie jest typu INTERVAL to rzuci wyjątkiem.

CREATE OR REPLACE FUNCTION get_interval_hours( intervals TEXT[] )RETURNS int
AS $CODE$
DECLARE
  interval_text TEXT;
  hours INT;
  e TEXT;
BEGIN
  hours = 0;
  FOREACH interval_text IN ARRAY intervals LOOP
    IF pg_input_is_valid(interval_text, 'INTERVAL') THEN
	   SELECT EXTRACT(epoch FROM interval_text::INTERVAL)::INT/3600 + hours
	   INTO hours;
    ELSE
       SELECT message
       INTO e
       FROM pg_input_error_info(interval_text, 'date');
       RAISE EXCEPTION '[%] is not valid: %', interval_text, e;
   END IF;
  END LOOP;

  RETURN hours;
END
$CODE$
LANGUAGE plpgsql;

Ok to teraz sprawdźmy czy dobrze działa:

SELECT public.get_interval_hours(ARRAY['1 day','1 day', '1 week'])

Zwraca 216 czyli działa dobrze 🙂No to teraz sprawdźmy co się stanie jak wywołamy funkcję z błędną wartością aaaa:

SELECT public.get_interval_hours(ARRAY['1 day','1 day', '1 week','aaa'])

Funkcja zwróci nam:

ERROR:  [aaa] is not valid: invalid input syntax for type date: "aaa"
CONTEXT:  PL/pgSQL function get_interval_hours(text[]) line 16 at RAISE 

SQL state: P0001

Mam nadzieję, że ten krótki post przyda Ci się w przyszłości.

Tagi: