Kombinacje bez powtórzeń zbioru liczb – zapytanie SQL

Pytanie zgłoszone przez czytelnika. W jaki sposób z zestawu liczb będących w osobnych rekordach wyświetlić kombinacje bez powtórzeń. Dla przykładu wyświetlimy kombinacje bez powtórzeń zbioru 3 elementowego ze zbioru 6 elementowego. n!/k!(n-k)!

Tworzymy tabelę testową:

create table test(
id serial primary key,
  number float
);

insert into test(number) values(2.49);
insert into test(number) values(5.11);
insert into test(number) values(10);
insert into test(number) values(20);
insert into test(number) values(27.4);
insert into test(number) values(75);

W zapytaniu nasze dane złączamy 3 krotnie bo takiej wielkości zbiór unikalnych liczb chcemy uzyskać, złączamy z warunkiem na różne IDki aby uniknąć zduplikowanych liczb. Następnie w zapytaniu nadrzędnym odfiltrowujemy powtarzające się rekordy.

W przypadku gdybyśmy chcieli uzyskać zbiory 4 elementowe należy złączyć 4 krotnie, stworzyć kolumnę number4 i dodać na nią warunek w zapytaniu zewnętrznym.

SELECT 
*
FROM 
  (SELECT
    T1.number AS number1, 
    T2.number AS number2, 
    T3.number AS number3 
  FROM test AS T1 
  JOIN test AS T2 ON T1.id != T2.id
  JOIN test AS T3 ON T2.id != T3.id AND T1.id != T3.id
  ORDER BY number1, number2, number3
  ) AS sub
WHERE 
  sub.number1 <  sub.number2
AND
  sub.number2 <  sub.number3

Wynik zapytania:

 number1 | number2 | number3
---------+---------+---------
    2.49 |    5.11 |      10
    2.49 |    5.11 |      20
    2.49 |    5.11 |    27.4
    2.49 |    5.11 |      75
    2.49 |      10 |      20
    2.49 |      10 |    27.4
    2.49 |      10 |      75
    2.49 |      20 |    27.4
    2.49 |      20 |      75
    2.49 |    27.4 |      75
    5.11 |      10 |      20
    5.11 |      10 |    27.4
    5.11 |      10 |      75
    5.11 |      20 |    27.4
    5.11 |      20 |      75
    5.11 |    27.4 |      75
      10 |      20 |    27.4
      10 |      20 |      75
      10 |    27.4 |      75
      20 |    27.4 |      75
(20 rows)

Masz problem SQL do rozwiązania? Napisz.

Sprawdzanie permutacji ciągu znaków w PostgreSQL

Permutacja w języku matematycznym to „wzajemnie jednoznaczne przekształcenie pewnego zbioru na siebie”. Używając języka mniej technicznego permutacje, na przykładzie liter w słowie, to wszystkie możliwe ustawienia literek w słowie, zatem dla słowa „marcin” permutacjami będą: „amrcin”, „mracin”, „mrcain” etc… w słowie 6 znakowym będzie ich łącznie 6!.

Problem

W bazie danych mamy pole tekstowe z zapisanymi pewnymi ciągami znaków, chcemy sprawdzić te dane pod kątem występowania w nich permutacji pewnego stringu. Czyli mając w danych wejściowych np. „mrcain” chcemy sprawdzić czy w bazie nie ma „marcin” lub innej permutacji. Czytaj dalej Sprawdzanie permutacji ciągu znaków w PostgreSQL

Numerowanie listy zwracanych rekordów w MySQL

Przy różnorakich konkursach często zachodzi potrzeba wyciągnięcia pozycji konkretnego użytkownika (wpisu w bazie) na tle innych użytkowników (wpisów) w ograniczeniu czasowym.

Czyli np. mamy 10 tys wyników gry z danego dnia i chcemy poznać pozycje w rankingu, które zajmują użytkownicy o identyfikatorach 1, 2, 3.

Oczywistym rozwiązaniem jest zwykły selekt z ORDER BY po polu z punktami i następnie iterowanie w php, jednak gdy liczba danych będzie większa taki SELECT nie wykona się lub będzie bardzo niewydajny.

Rozwiązaniem dużo lepszym jest użycie podzapytania i wykonania obliczeń numerowania na samej bazie a następnie zwrócenie do php jedynie rekordów pasujących do naszych użytkowników. Czytaj dalej Numerowanie listy zwracanych rekordów w MySQL

Sphinx, czyli szybkie i skuteczne wyszukiwanie

Sphinx to open source’owy silnik wyszukiwarki SQL. Sphinx jest dostępny na licencji GPL 2 i jest całkowicie darmowy do zastosowań webowych.

Sphinx został stworzony przez rosyjskich programistów, aplikacja jest cały czas rozbudowywana i rozwijana, posiada płatny support.

Zasada działania Sphinxa

Sphinx jest aplikacją stand-alone, musi zostać skompilowany i zainstalowany na serwerze. Działa na zadadzie deamon-client.

Sphinx składa się z kilku narzędzi:

  • searchd – deamon wyszukiwarki. Musi być cały czas uruchomiony, na podstawie parametrów wywołania oraz konfiguracji przeszukuje indeksy i zwraca rezultaty.
  • indexer – generator indeksu. Służy do budowania indeksu, po którym następuje wyszukiwanie.
  • search – narzędzie pozwalają testować wyszukiwarkę z poziomu linii komend.
  • spelldump – narzędzie pomocne przy budowania słownika
  • indextool – narzędzie wprowadzone dopiero w wersji 0.9.9-rc2. Służy do debugowania i wyświetlania informacji o zbudowanym indeksie.

Cechy Sphinxa

  • Ekstremalnie szybkie indeksowanie (aż do 10MB/sec).
  • Ekstremalnie szybkie wyszukiwanie (około 0.1 sekundy dla 2-4 GB zbiorów danych).
  • Bardzo duża skalowalność (aż do 100 GB zbiorów danych na pojedyńczym CPU).
  • Współpracuje z wieloma typami baz danych: PostgreSQL, MySQL i MSSQL.
  • Posiada API dla języków takich jak PHP, Python, Java, Perl, Ruby i C/C++.
  • Obsługuję morfologię (przeszukiwanie pełnotekstowe (brak języka polskiego)).
  • Umożliwia sortowanie/filtrowanie wyników po zadanych atrybutach.
  • Umożliwia grupowanie rezultatów po zadanym atrybucie.
  • Wspiera stronnicowanie rezultatów.
  • Prosty, a zarazem dostarczający wiele możliwści plik konfiguracyjny.

Sphinx nie operuje bezpośrednio na bazie danych a jedynie na wcześniej przygotowanych indeksach. Dzięki temu zdejmujemy obciążenie z „wąskiego gardła”, którym w praktycznie każdej aplikacji jest baza danych i przeżucamy na serwer.

Wady Sphinxa

Jedyną znaną mi wadą Sphinxa jest to, że wcześniej stworzony indeks (po którym odbywa się wyszukiwanie) nie jest automatycznie aktualizowany o zmiany wykonane na bazie danych. Dotyczy to zarówno nowych wierszy w zaindeksowanej tabeli jak i modyfikacji zaindeksowanych już danych.

Nowe/zmienione dane pojawią się w indeksie dopiero po jego zaktualizowaniu poprzez polecenie:

indexer nazwa_indeksu --rotate

Pomimo dużej szybkości indexera, operację tą nie nie można wykonywać po każdej zmianie w bazie danych a jednie cyklicznie co kilka godzin/dni (w zależności od wielkości indeksu).

O ile brak aktualizacji indeksu o zmodyfikowane rekordy nie stanowi większego problemu, to brak nowych rekordów może stanowić dla wielu serwisów poważny problem. Często bywa tak, że użytkownicy wprowadzają jakieś dane do bazy a następnie chcą ich użyć wyszukując je. Rozwiązaniem jest mechanizm Live index updates.

W skrócie działa to następująco:

  • Na indeksowanej tabeli tworzymy 2 indexy: główny (main) i dodatkowy (delta).
  • Tworzymy tabelę sph_counter, w której będziemy zapisywać max(id) z indeksu głównego.
  • Indeks główny będzie wyszukiwał w 99,9% danych indeksowanej tabeli, indeks dodatkowy będzie wyszukiwał w 0.1% danych tabeli.
  • Dzięki rozbiciu indeksowania na 2 indeksy (duży główny, mały dodatkowy) możliwe jest dużo częstsze indeksowanie indeksu dodatkowego gdyż będzie zawierał on niewiele danych, zatem reindeksacja nie będzie obciążać serwera i bazy danych.
  • Modyfikujemy sphinx.conf wg. wytycznych z linka.
  • Ustawiamy w cronie reindeksowanie głównego indeksu „raz na dzień” oraz dodatkowego indeksu np. „co 3 minuty”.
  • Zatem, co 3 minuty będziemy uzupełniać indeks dodatkowy, natomiast raz dziennie będzie uzupełniany indeks główny.
  • Każdorazowo przy aktualizacji indeksu głównego do tabeli sph_counter zostanie zapisany wartość klucza głównego (sql_query_pre), przez co indeks pomocniczy będzie startował od zera.

Zasada działania aplikacji opartej na Sphinx’ie

  • Skonfigurowanie konfiga „sphinx.conf” i zbudowanie indeksów.
  • W aplikacji, poprzez api następuje wywołanie Sphinxa z parametrami (szukana fraza, limit, offset, order etc…).
  • Sphinx zwraca wyszukane rekordy w postaci tablicy kluczy głównych (+ zdefiniowane atrybuty).
  • Na podstawie ID’ków kluczy głównych wyciągamy dane z bazy (lub z cache’a) i prezentujemy w serwisie.

Szybki tutorial

  • Tworzymy i konfigurujemy plik konfiguracyjny sphinx.conf.
  • Bujemy indeks(y)
    indexer --all
    
  • Odpalamy demona:
    /usr/local/bin/searchd -c /usr/local/etc/sphinx.conf
    
  • Testujemy wyszukiwanie po indeksie:
    search [INDEX_NAME] SZUKANA_FRAZA
    
  • Podpinamy API
  • Testujemy wyszukiwanie poprzez API

Błąd „connection to 127.0.0.1:9312 failed (errno=111 ….”

W przypadku gdy najpierw został uruchomiony deamon Sphinxa a dopiero później stworzony indeks to aby API działało poprawnie należy zrestartować demona. To samo tyczy się przypadku gdy w indeksie sphinx.conf zostały zmienione zwracane atrybuty.

/usr/local/bin/searchd --stop
/usr/local/bin/searchd -c /usr/local/etc/sphinx.conf

Błąd „błąd „searchd error: offset out of bounds””

W przypadku gdy chcemy zwiększyć domyślną liczbę zwracanych (przetwarzanych) rekordów z 1000 na więcej należy:

  • zmienić max_matches w sekcji searchd w konfigu sphinx.conf,
  • zrestartować demona searchd,
  • ustawić 3 parametr w metodzie SetLimits().

Z Sphinx’a korzystają między innym: craigslist.org, filestube.com, netlog.com, thepiratebay.org, wikimapia.org i wiele więcej.

Niedługo, jak czas pozwoli, postaram się jeszcze napisać coś o samym pliku konfiguracyjnym Sphinxa „sphinx.conf”.

UPDATE z ORDER BY w jednym zapytaniu na przykładzie orderingu danych

Wstęp

Mamy przykładową tabelę z userami:

DROP TABLE users;
CREATE TABLE users(
	id SERIAL PRIMARY KEY,
	name VARCHAR(50),
	created TIMESTAMP DEFAULT NOW()
);

INSERT INTO users(name) VALUES('Marcin');
INSERT INTO users(name) VALUES('Bartek');
INSERT INTO users(name) VALUES('Agnieszka');
INSERT INTO users(name) VALUES('Dominika');

Na tabeli wykonywane były wielokrotnie operacje INSERT/UPDATE/DELETE przykładowo:

UPDATE users SET name = 'Ania' WHERE name = 'Bartek';
UPDATE users SET name = 'Dalia' WHERE name = 'Marcin';
DELETE FROM users WHERE name = 'Ania';

Zatem SELECT bez orderu wyświetli nam dane posortowane względem kolejności modyfikacji:

SELECT * FROM users;

psql=> select * from users;
 id |   name    |          created
----+-----------+----------------------------
  3 | Agnieszka | 2009-12-19 10:41:49.324851
  4 | Dominika  | 2009-12-19 10:41:49.958792
  1 | Dalia     | 2009-12-19 10:41:49.297223
(3 rows)

W interface aplikacji chcemy oprogramować własną obsługę kolejności rekordów. Dodajemy zatem kolumnę, która będzie obsługiwać tą funkcjonalność.

ALTER TABLE users ADD COLUMN order_id INTEGER;

Problem

Musimy teraz uzupełnić kolumne order_id o poprawne wartości. Kolejność orderu danych, które znajdują się w bazie powinna być kolejności wprowadzania tych danych do tabeli (czyli zgodna z kolejnością pola id).

Aby zrealizować poprawne uzupełnienie orderu tworzymy pomocniczą sekwencję, która wykorzystamy do wygenerowania kolejnych liczb do orderu:

DROP SEQUENCE order_id_seq;
CREATE SEQUENCE order_id_seq;

Wykonujemy oczywiste zapytanie przypisujące rekordom z tabeli users kolejne wartości sekwencji:

UPDATE users SET order_id = NEXTVAL('order_id_seq');  

Niestety przez to, że dane nie były ustawione w dobrej kolejności ORDER ustawiony jest niepoprawnie.

psql=> SELECT * FROM users;
 id |   name    |          created           | order_id
----+-----------+----------------------------+----------
  3 | Agnieszka | 2009-12-19 10:41:49.324851 |        1
  4 | Dominika  | 2009-12-19 10:41:49.958792 |        2
  1 | Dalia     | 2009-12-19 10:41:49.297223 |        4
(3 rows)

Rozwiązanie

Aby UPDATE poprawnie ustawił kolejność orderu należy nieco zmodyfikować zapytanie i nadać danym z tabeli users odpowiednią kolejność.

W tym celu tworzymy podzapytanie sorted_ids, w którym poprzez ORDER BY ustalamy kolejność rekordów, które następnie w zapytaniu nadrzędnym zostaną zmodyfikowane właśnie w tej kolejności.

UPDATE users
	SET order_id = correct_order_id
FROM
	(
	SELECT 
		id, nextval('order_id_seq') as correct_order_id
	FROM
		users
  	ORDER BY 
		id ASC
	) as sorted_ids
WHERE
	users.id = sorted_ids.id; 

select * from users order by order_id;

Jak widać dane zostały pięknie zorderowane:

psql=> select * from users order by order_id;
 id |   name    |          created           | order_id
----+-----------+----------------------------+----------
  1 | Dalia     | 2009-12-19 10:41:49.297223 |        1
  3 | Agnieszka | 2009-12-19 10:41:49.324851 |        2
  4 | Dominika  | 2009-12-19 10:41:49.958792 |        3
(3 rows)

Przy okazji wspomnę, że w aplikacji łatwiej obsłużyć ordering odwrotny, tzn. wiersze, które posiadają największą wartość order_id są na czele a nie na końcu listy. Przy takim podejściu przy wstawianiu nowego rekordu pole order_id powinno mieć wartość MAX(order_id) + 1. Dla orderingu odwrotnego należy zmienić w podzapytaniu kolejność sortowania z id ASC na id DESC.

Należy także dbać o to, aby przy usunięciu rekordu prenumerować order_id wszystkich rekordów, które znajdują się ‚nad nim’ o -1.

Relecyjna baza ‘ala MySQL’ w chmurze Amazon AWS

Amazon w ramach AWS (Amazon Web Services) uruchomił publiczną wersję beta usługi relacyjnaj bazy danych ‚ala MySQL’ (Amazon RDS) w swojej chmurze.

Amazon RDS ma cechować się:

  • prostotą konfiguracji i wdrożenia do aplikacji,
  • pełną kompatybilnością z bazą danych MySQL,
  • prostotą w zarządzaniu bazami, backupami i dostępnymi narzędziami,
  • pełną automatyką w backup’owaniu i czynnościach serwisowych,
  • skalowalnością w zależności od potrzeb uzytkownika
  • niezawodnością
  • bardzo dostępną ceną, tak jak w przypadku innych usług developerzy kasowani będą jedynie za zaosby, które rzeczywiście zużywają ich aplikacje.

Czy rzeczywiście tak będzie? Mam nadzieje. Przeczytaj więcej o Amazon RDS.

Usunięcie nieużywanych tagów z bazy WordPress’a

Czasami zachodzi potrzeba „ręcznego” kasowania postów z WordPress’a, jako że system ten działa na MySQL MyISAM to nie wspiera kaskadowego kasowania zależnych danych.

Skasowanie postów jest stosunkowo proste:

DELETE FROM wp_posts WHERE conditions;

Trudniejsza sprawa jest ze skasowaniem tagów. Na necie znalazłem poniższe zapytanie, działa wyśmielicie:

DELETE a,b,c
FROM
	wp_terms AS a
	LEFT JOIN wp_term_taxonomy AS c ON a.term_id = c.term_id
	LEFT JOIN wp_term_relationships AS b ON b.term_taxonomy_id = c.term_taxonomy_id
WHERE (
	c.taxonomy = 'post_tag' AND
	c.count = 0
	);

Czasami jednak pole „count” w relacji „wp_term_taxonomy” zawiera niepoprawne dane (liczbę większą od 0), wtedy nieużywane tagi musimy zidentyfikować poprzez LEFT JOINa z warunkiem ID IS NULL. Poniżej zapytanie wyświetlające nieużywane tagi.

SELECT DISTINCT wp_terms.slug FROM wp_terms wt
	INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id
	INNER JOIN wp_term_relationships wtr ON wtr.term_taxonomy_id=wtt.term_taxonomy_id
	LEFT JOIN wp_posts wp ON wp.ID=wtr.object_id
WHERE
	taxonomy='post_tag'
AND 
	ID IS NULL
AND 
	NOT EXISTS(SELECT * FROM wp_terms wt2
                INNER JOIN wp_term_taxonomy wtt2 ON wt2.term_id=wtt2.term_id WHERE wtt2.parent=wt.term_id)
ORDER BY name

Zrzut danych z bazy do pliku w MySQL

Metoda I

Zapytanie z poziomu bazy danych.

mysql> SELECT *
    -> INTO OUTFILE 'c:/data.csv'
    -> FIELDS TERMINATED BY ','
    -> ENCLOSED BY '"'
    -> ESCAPED BY '\\'
    -> LINES TERMINATED BY '\r\n'
    -> FROM table_to_export;
Query OK, 20 rows affected (0.02 sec)

Metoda II

Zapytanie z poziomu konsoli bez logowania się do bazy danych.

mysql -u user -h host -p --quick -e 'SELECT * FROM table_to_export' baza > data.txt

Warto pamiętać, że w metodzie I plik zapisywany jest po stronie serwera mySQL a w metodzie II po stronie klienta mySQL. Zatem gdy łączymy się z bazą danych z innego serwera niż wykonujemy zapytanie to należy użyć metody II.

Aktualny czas a transakcja w PostgreSQL

Ostatnio miałem ciekawy case’ik – musiałem w obrębie jednej transakcji zmienić dane rekordów. Wszystko odbywało się w kilku funkcjach plpgsql, funkcje mogły się wywoływać rekurencyjnie w triggerach a zmiany dotyczyły między innymi pól typu TIMESTAMP. Wartości pól TIMESTAMP rekordów były zmieniane i ich zmiany automatycznie wpływały na w działanie skryptu.

Jakież było moje zdziwienie gdy skrypt nie chciał działać, a wszystkie rekordy miały taką zamą wartość pola typu TIMESTAMP. Otóż okazało się, że po rozpoczęciu transakcji funkcje zwracające aktualny czas:

SELECT CURRENT_TIME;
SELECT CURRENT_TIMESTAMP;
SELECT NOW();

przestały zwracać aktualny czas a zamiast tego czas rozpoczęcia transakcji.

I o dziwo jest to rzeczywiście poprawne działanie. Na stronie http://www.postgresql.org/docs/8.3/static/functions-datetime.html można przeczytać:

Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the „current” time, so that multiple modifications within the same transaction bear the same time stamp.

Zatem wyjściem z tego problemu jest użycie postgres’owej funkcji timeofday() i zrzutowanie jej wyniku na TIMESTAMP, realizyje to zapytanie:

SELECT cast( timeofday() AS timestamp);

Edit: zamiast timeofday() można uzyć funkcji clock_timestamp(), która zwraca date w postaci TIMESTAMP’a

SELECT clock_timestamp();

Dla ciekawości sprawdziłem jak zachowują się inne dialekty SQL i okazało się, że zarówno MySQL jak i MSSQL realizują tą sytuację odmniennie niż PostgreSQL, zatem:

  • MySQL – w transakcji zwracany jest bieżący czas (nie czas rozpoczęcia transakcji)
  • MSSQL – w transakcji zwracany jest bieżący czas (nie czas rozpoczęcia transakcji)
  • PgSQL – w transakcji zwracany jest czas rozpoczęcia transakcji

Postgres 8.3 a zgodność typów danych

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:

  1. Poprawimy zły typ kolumn, które powodują błędy
  2. W każdym zapytaniu zrzutujemy zmienną na dobry typ
  3. 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;