Około rok temu została wypuszczona wersja 8.3 systemu bazodanowego postgreSQL, teraz po roku nadszedł wreszcie czas kiedy to firmy hostingowe zaczynają upgrate’ować bazy do tej wersji.
W wersji 8.3 zostało prowadzonych wiele przydatnych funkcjonalności jak chociażby wyszukiwanie pełnotekstowe TSearch2, jednak nie o tym miałem pisać…
Oprócz dodatkowych funkcjonalności w postgres 8.3 została wprowadzona kontrola zgodności typów danych. Po przejściu providera hostingu z pg 8.x.x na pg 8.3.x istnieje realne zagrożenie błędnego działania naszych aplikacji w przypadku gdy nie zachowywaliśmy zgodości typów danych.
W postgreSQL operatory (np. =, > etc..) i funkcje (np. substr(), regexp_replace() etc…) posiadaja zadeklarowane typy danych na których działają np. INT=INT, substr(TEXT, INT, INT).
Problem
W wersjach przed postgreSQL 8.3 jeżeli operator lub funkcja, która przyjmowała wartości tekstowe została wywołana z wartościami o typie nie tekstowym to wartość parametru był automatycznie przekształcania do typu tekstowego TEXT. Od wersji postgreSQL 8.3 automatyczna konwersja typów została zlikwidowana gdyż jej idea nie jest zgodna z poprawnymi zasadami programowania. To my programiści powinniśmy kontrolować przepływ, wartości i typy danych. Pozostawiona została jedynie automatyczna konwersja w obrębie typów tekstowych (CHAR(X), VARCHAR(X) są CAST’owane do TEXT).
Zatem w najnowszej wersji postgreSQL zapytanie:
SELECT substr(id, 1, 1 ) FROM ...;
gdzie id jest typu integer zwróci błąd „function does not exist” gdyż nie ma wbudowanej funkcji substr(INT, INT, INT)
Analogiczny błąd zwróci operator =
SELECT ... WHERE id = foo;
gdzie id jest typu INTEGER, a foo typu CHAR zwróci błąd „operator does not exist” gdyż nie ma wbudowanego operatora INT = CHAR.
Rozwiązanie
Możliwe są trzy rozwiązania:
- Poprawimy zły typ kolumn, które powodują błędy
- W każdym zapytaniu zrzutujemy zmienną na dobry typ
- Napiszemy własne funkcje, operatory obsługujące te niestandardowe operacje.
1. Poprawa typu kolumny
Możemy zmodyfikować typ kolumny np. z CHAR na INT i wtedy zapytanie wykorzystujące operator INT = INT zadziała poprawnie.
ALTER TABLE sample ALTER COLUMN foo TYPE INTEGER;
Jednak operacja ta nie zawsze może być możliwa. W przypadku źle zaaprojektowanej aplikacji, konwersja taka może okazać się niemożliwa. Co w wypadku gdy pole 'foo’ zawiera ciąg liczb zaczynających się od zera? Przed konwersją przykładowy ciąg może wyglądać '0012388′ po konwersji na typ liczbowy ayutomatycznie zmieni się na '12388′ gdyż INTEGER nie może posiadać zer na przedzie. Ta zmiana może spododować, że kolejne zapytania pomimo prawidłowej składni syntaktycznej zwrócą nieprawidłowe wyniki, pozatym możemy stracić w bazie pewne informacje (zera na przedzie).
2. Castowanie typów w zapytaniu
Gdy zmiana typu kolumny jest niemożliwa pozostaje nam dostosować naszą aplikację do wymagań postgresa. W zapytaniu możemy zrzutować wartość zmiennej na żądany typ
SELECT ... WHERE id = foo::integer;
lub odpowiednik
SELECT ... WHERE id = CAST(foo AS integer);
Minusem takiego rozwiązania jest to, że kolumne 'foo’ należy zrzutować w każdym zapytaniu.
3. Własna funkcje lub operator
Zamiast poprawiać wszystkie zapytania, możemy napisać funkcje/operator który obsłuży żądaną operację.
Tworzenie operatora składa się z dwóch etapów: stworzenie funkcji w języku proceduralnym pl/pgSQL, która obsługuje operację i definicję samego operatora.
Funkcja INTEGER = CHAR
CREATE OR REPLACE FUNCTION equal_integer_vs_char(integer, char) RETURNS bool AS $$
DECLARE
left_arg ALIAS FOR $1;
right_arg ALIAS FOR $2;
BEGIN
IF left_arg = CAST( right_arg AS INTEGER ) THEN
RETURN true;
ELSE
RETURN false;
END IF;
END;
$$ LANGUAGE plpgsql;
Definicja operatora INTEGER = CHAR, przeczytaj także w manualu.
CREATE OPERATOR = (
LEFTARG = integer ,
RIGHTARG = char,
PROCEDURE = equal_integer_vs_char
);
W przypadku potrzeby posiadania funkcji substr(INT, INT, INT) musimy zdefiniować funkcję przyjmującą odpowiednie parametry.
CREATE OR REPLACE FUNCTION substr(integer, integer, integer) RETURNS text AS $$
DECLARE
...
BEGIN
...
RETURN foo;
END;
$$ LANGUAGE plpgsql;