PostgreSQL ma rozbudowany system typów, wśród których całkiem przydatną strukturą jest typ tablicowy (array). Niestety, funkcjonalność związana z tym typem jest dość niesymetryczna: jest możliwość tworzenia nowych obiektów tego typu, wyszukiwania, dodawania elementów do istniejących tablic, ale nie ma nigdzie żadnej funkcji ani operatora do usuwania elementów z arraya. Nie ma jednak co rozpaczać, gdyż PostgreSQL pozwala na definiowanie własnych funkcji i operatorów (yaay! thank you, Captain Obvious!), co niniejszym opisuję.
Brakującą funkcjonalność usuwania z array'a nadrobimy funkcjami array_remove(array, element), array_remove(array, otherarray) oraz operatorami array - array i array - element. Ważne! Ponieważ typ array może odnosić się do różnych typów bazowych i nie można mieszać różnych typów w jednej tablicy, zrobimy wersje dla typu int8[].
Jeśli używacie PostgreSQL'a w wersji >=8.3, to w contribie jest pakiet intarray, który dostarcza podobną funkcjonalność (+ kilka innych ciekawych rozszerzeń) dla tablic typu int[]. Implementacja intarray jest w C, więc jest szybsza, ale ograniczona koniecznością posiadania uprawnień do instalacji języka C, co nie zawsze jest możliwe. Implementacja w pl/PgSQL jest więc, kosztem wydajności, możliwa do użycia z poziomu zwykłego usera. Dodatkowo, zmieniając typy parametrów z int[] na inny, np. text[], rozszerzamy funkcjonalność na następny typ.
Najperw funkcje:
create or replace function array_remove(arr int8[], el int8) returns int8[] as arr_out int8[]; begin if arr is null then if not el = any(arr) then for arr_idx in array_lower(arr, 1)..array_upper(arr, 1) loop return arr_out; end;
$$
declare
arr_idx int;
return arr;
end if;
return arr;
end if;
if el != arr[arr_idx] then
arr_out = array_append(arr_out, arr[arr_idx]);
end if;
end loop;
$$ language plpgsql immutable;
create or replace function array_remove(arr int8[], other_arr int8[]) returns int8[] as
$$
declare
out_arr int8[];
el_idx int;
begin
if arr is null or other_arr is null then
return arr;
end if;
for el_idx in array_lower(arr, 1)..array_upper(arr, 1) loop
if not arr[el_idx] =any(other_arr) then
out_arr = array_append(out_arr, arr[el_idx]);
end if;
end loop;
return out_arr;
end;
$$ language plpgsql immutable;
i na koniec operatory:
CREATE OPERATOR - (
leftarg = int8[],
rightarg = int8[],
procedure = array_remove
);
CREATE OPERATOR - (
leftarg = int8[],
rightarg = int8,
procedure = array_remove
);
To co? mały test? Porównajmy sutuację przed utworzeniem operatora..
test=> select array[1,2,3]::int8[] - array[3,1]::int8[];
ERROR: operator does not exist: bigint[] - bigint[]
LINE 1: select array[1,2,3]::int8[] - array[3,1]::int8[];
^
HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.
test=>
z sytuacją po:
test=> select array[1,2,3]::int8[] - array[3,1]::int8[];
?column?
----------
{2}
(1 row)
test=>
UPDATE
Już po napisaniu posta znalazłem w sieci tego maila. Rozwiązanie zaproponowane przez Michaela Fuhra jest ciut zgrabniejsze - nie wymaga oddzielnych funkcji dla różnych typów arraya, bo typ arraya pobiera z podanych parametrów.
