Zdarza się czasami, że musimy posortować dane inaczej niż zgodnie z kolejnością alfabetyczną. Załóżmy taką sytuację. Mamy tabelę zawierającą statusy produktów. Tak się zdarzyło, że zostały one zapisane w postaci tekstowej. Poniżej schemat tabeli

CREATE TABLE produkty
(
  id serial NOT NULL,
  nazwa character varying(500),
  status character varying(50)
)
WITH (
  OIDS=TRUE
);

oraz przykładowe dane

INSERT INTO produkty (id, nazwa, status) VALUES (1, 'produkt 1', 'magazyn 1');
INSERT INTO produkty (id, nazwa, status) VALUES (2, 'inny produkt', 'magazyn 2');
INSERT INTO produkty (id, nazwa, status) VALUES (3, 'super produkt', 'brak');
INSERT INTO produkty (id, nazwa, status) VALUES (4, 'produkt 2', 'magazyn 1');
INSERT INTO produkty (id, nazwa, status) VALUES (5, 'inny super produkt', 'dostawa');
INSERT INTO produkty (id, nazwa, status) VALUES (6, 'produkt 3', 'magazyn 2');

Jeśli chcielibyśmy posortować te dane w taki sposób, że najpierw wyświetlają się produkty znajdujące się w dowolnym magazynie, następnie produkty, których nie ma na składzie, a na końcu wszystkie, które są w dostawie, mielibyśmy spory problem. Na szczęście w postgresie jest takie coś jak wyrażenie warunkowe CASE, które idealnie nada się  do naszego problemu. Wprawdzie zapytanie się nieco rozrośnie, zwłaszcza jeśli mamy dużo warunków do sprawdzenia, ale pożądany efekt zostanie uzyskany bez konieczności zaprzęgania do pracy dodatkowego języka.

select 
	* 
from 
	produkty
order by 
	case
		when status = 'magazyn 1' then 1
		when status = 'magazyn 2' then 2
		when status = 'brak' then 3
		when status = 'dostawa' then 4
		else 5
	end

Powyższy sposób sprawdzi się również w przypadku sortowania danych numerycznych. Uzyskanie kolejności 6, 2, 3, pozostałe liczby malejąco,  jest równie prosty. Dla powyższych danych będzie to wyglądało następująco

select 
	* 
from 
	produkty
order by 
	case
		when id = 6 then 1
		when id = 2 then 2
		when id = 3 then 3
		else 4
	end
	, id desc

W ten oto prosty sposób, uzyskaliśmy niestandardowe sortowanie naszych danych.

Poniższy tutorial będzie wprowadzeniem do epizodu tutoriali o języku skryptowym (proceduralnym) PL/pgSQL. Język pl/sql został wymyślony przez Oracle, jego prostota i zarazem potężna siła daje nam wszystko czego potrzeba nam do pracy z postgresql. Język ten pozwala nam na wiele więcej działań niż sam SQL, począwszy od deklaracji zmiennych poprzez pętle skończywszy na triggerach (albo i nie).

Wprowadzenie do PL/pgSQL

Krótki i w miarę logicznie opisanym słowem wstępu jest artykuł na wiki o PL/pgSQL, link poniżej. Lecz ja chciałbym to omówić po swojemu, tak by każdy z Was to zrozumiał i przekonał się nad wielkością tego języka, zaczynajmy. Tak jak nam mówi manual składnia funkcji jest następująca:

CREATE FUNCTION "nazwaFunkcji"( argumenty [, ... ] ) RETURNS [typZwracanegoObiektu] AS $BODY$
  DECLARE -- blok nie obowiązkowy
    -- Możemy w tym bloku definiować zmienne, w "locie" zmiennych definiować nie możemy 
  BEGIN
    -- ciało funkcji
 
    RETURN [zwracanyObiekt] -- klauzula prawie nie obowiązkowa, o tym poniżej
  END;
$BODY$ LANGUAGE 'plpgsql';

Tak mniej więcej przedstawia się schemat konstrukcji funkcji w PL/pgSQL, Pominąłem kwestię bloków w bloku, w sensie że w bloku BEGIN … END możemy zdefiniować kolejny blok kodu. Nigdy mi to nie było jeszcze potrzebne, i wątpię by Wam było, to jest pewnego rodzaju przerost formy nad treścią. Jak widać stosuje tutaj podwójne dolary w sensie $BODY$, można się zapytać dlaczego? Otóż nie musicie stosować podwójnych dolarów zamiast nich możecie zrobić po prostu AS ‘ — i tutaj reszta kodu. Tylko nie wiem czy stać Was na nerwy stracone przy escepowaniu znaków. Otóż przy podwójnych dolarach możemy zachować taką konstrukcję:

SELECT * FROM users WHERE f_name='foobar';

Natomiast przy konstrucji z apostrofem:

SELECT * FROM users WHERE f_name=''foobar'';

Tam nie ma cudzysłowu przy foobar tylko podwójny apostrof. Także używajcie sobie czego chcecie, do woli. A i jeszcze jedno, to co sobie wstawicie pomiędzy podwójne dolarki nie ma znaczenia, ja wyniosłem z pracy nawyk wstawiania tam nazwy funkcji, co mi się osobiście trochę mało podoba, no ale nawyk pozostał. Przedstawiona przeze mnie powyżej konstrukcja nie jest oczywiście odzwierciedleniem tej z manuala, gdyż jest mało jasna, i rzadko się używa np CREATE OR REPLACE gdy co chwilę zmieniamy typy zmiennych przesyłanych w liście argumentów.

Argumenty funkcji PL/pgSQL

Lista argumentów może być podawana dwojako, a nawet trzyjako. Pierwszym z sposobów podawania argumentów do funkcji jest:

CREAT.... "nazwaFunkcji" ( INT, NUMERIC, TEXT, VARCHAR ) .... $BODY$
  DECLARE
    "_nazwaZmiennejInt" ALIAS FOR $1;
    "_nazwaZmiennejNumeric" ALIAS FOR $2;
    "_nazwaZmiennejText" ALIAS FOR $3;
    "_nazwaZmiennejVarchar" ALIAS FOR $4;
  BEGIN
   ....

drugi sposób:

CREAT.... "nazwaFunkcji" ( "_nazwaZmiennejInt" INT, "_nazwaZmiennejNumeric" NUMERIC,  "_nazwaZmiennejText" TEXT, "_nazwaZmiennejVarchar" VARCHAR ) .... $BODY$

trzeci sposób:

CREAT.... "nazwaFunkcji" ( "_nazwaZmiennejInt" INT, OUT "_nazwZwracanejZmiennej" INT, OUT "_nazwaZwracanejZmiennej2" INT ) .... $BODY$

Dzięki tej trzeciej metodzie możemy zwracać więcej niż jeden argument. Możemy też definiować typy zmiennych i też je zwracać o tym zraz.

Zwracane typy

Tak jak wyżej napisałem funkcje PostgreSQL mogą zwracać typy danych które też sami zdefiniujemy, metody mogą zwracać zarówno typ VOID, jeżeli nie potrzebujemy niczego zwrócić to po prostu ustawiamy RETURNS VOID i dzięki takiej konstrukcji klauzula RETURN w ciele kodu jest niewymagana, proste nie? PostgreSQL to nie PHP, źle mu prześlesz typ danych zdefiniowany jako INT a prześlesz jako ‘1′ i będzie to string, w PHP to przejdzie ale w postgre nie. Więc tutaj musimy się pilnować jeżeli chodzi o przesyłanie zmiennych. To samo tyczy się wszystkich typów oraz tych zwracanych, jeżeli w końcowej wersji uzyskamy inny typ niż ten który zdefiniowaliśmy to dostaniemy błędem po oczach próbując przypisać np NUMERIC do INT i w sumie funkcja się nie wykona, nic nie zwróci a weź to debuguj :>

Czytaj dalej tutaj (rozwija treść wpisu)
Czytaj dalej na blogu autora...

Zwiń
Czytaj na blogu autora...

W przypadku najprostszej implementacji struktury drzewiastej w bazie, jaką opisałam w pierwszym artykule tej serii, przeniesienie całej gałęzi do nowej lokalizacji jest zagadnieniem trywialnym. Wystarczy zmienić wartość parametru ‘parentID’ odpowiedniego rekordu i już. Jeśli chodzi o drzewo typu nested set, nie jest to już takie proste. Przeniesienie gałęzi wymaga przeliczenia parametrów ‘lft’ i ‘rgt’ wielu [...]

Skoro pokazałam już jak wygląda struktura drzewa nested set oraz jak wczytać całe drzewo a także jak pobrać wybraną gałąź, pora zająć się dodawaniem rekordów do omawianej struktury. W przypadku najprostszej implementacji struktury drzewiastej w bazie, jaką opisałam w pierwszym artykule tej serii, dodanie kolejnego rekordu jest proste i wymaga wykonania jednego zapytania. Wystarczy tylko aby [...]

W poprzednich artykułach tej serii pokazałam jak wygląda konstrukcja drzewa typu nested set a także jak wczytać zawartość drzewa tego typu. W tym odcinku pokażę, że wystarczą niewielkie modyfikacje omówionych wcześniej zapytań, by bez trudu odczytać dowolnie wybraną gałąź naszej struktury. Dla przypomnienia Na początek przypomnę tabelę zawierająca strukturę drzewa, którą wykorzystywałam w poprzednich artykułach oraz [...]

Ostatnimi czasy coś nie mam weny by napisać cokolwiek na blogu, także postanowiłem tym razem coś może o postgresql napisać i jego własnościach tudzież mowa o widokach w postgresql. Rzecz biorąc czym jest widok? Widok jest niczym innym jak zapytaniem kryjącym się pod krótką nazwą, jak pisałem w wcześniejszej notce o RBAC cz.3 z pewnego zapytania można było utworzyć sobie widok. Może więcej szczegółów technicznych, składnia widoku ma się tak:

CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] VIEW name [ ( column_name [, ...] ) ]
    AS query

Sama składnia może się wydawać prosta choć nie musi ;] składnia podana w nawiasach kwadratowych jest opcjonalna. Prze zemnie rzadko wykorzystywana, bo nie tworzę jakiś tymczasowych widoków, nie zapisuję widoku do innego schematu niż ten w którym się znajduję ani też nie używam OR REPLACE gdyż jak instaluje na nowo to drop db i amba.

Teraz może co do samej składni

OR REPLACE – co dziwnego o tym piszą w manualu, to się można dowiedzieć na stronie podanej poniżej. Ale przytoczę fragment.

CREATE OR REPLACE VIEW is similar, but if a view of the same name already exists, it is replaced. The new query must generate the same columns that were generated by the existing view query (that is, the same column names in the same order and with the same data types), but it may add additional columns to the end of the list. The calculations giving rise to the output columns may be completely different.

I teraz pytanie, po czorta w takim razie OR REPLACE jeżeli zmienimy sobie całkowicie widok a pozostawimy tą samą nazwę, trochę dziwna akcja, chociaż może nas to też z jednej strony zabezpieczać przed nałogowym wstawianiu OR REPLACE i nie daj Bóg strzelimy gdzieś dwa takie same nazwy widoków, hmmm w sumie może i dobrze.

Przykład jakiegoś widoku? Proszę bardzo, z naszego RBAC’a z zapytania sprawdzającego uprawnienia, zróbmy sobie teraz widok:

CREATE VIEW "checkRightView" AS
SELECT 
  "allow",
  "u2g"."idUser",
  "m"."moduleName",
  "a"."actionName"
FROM 
  "rbac_privilages" p 
LEFT JOIN 
  "rbac_module" m 
ON 
  ( p."idModule" = m."idModule" ) 
LEFT JOIN 
  "rbac_action" a 
ON 
  ( p."idAction" = a."idAction" ) 
LEFT JOIN 
  "rbac_user_to_group" u2g 
ON 
  ( p."idGroup" = u2g."idGroup" );

Dodaliśmy tylko nagłówek create view i ucięliśmy klauzule where, a teraz przykład jak takiego widoku użyć:

SELECT 
  "cRV"."allow"
FROM 
  "checkRightView" AS "cRV"  -- prawie jak honda ^^
WHERE 
  "cRV"."idUser" = 2 
AND 
  "cRV"."moduleName" = 'music' 
AND 
  "cRV"."actionName" = 'index';

Tadam, proste prawda? Tworzenie widoków można by porównać do polimorfizmu, lecz nim nie jest. Co nam to daje? Na pewno przejrzyste zapytania sql, co do debugowania to w sql zawsze był z tym problem, dlatego przed utworzeniem widoku pierw lepiej sprawdzić czy zapytanie śmiga.

Co do ograniczeń widoku, wiążą się z tym pewne niedogodności, gdyż jak widać by mieć dostęp do tabel łączonych w zapytaniu, trzeba pobrać ich elementy w samym zapytaniu (w widoku) by mieć do nich dostęp przy wywoływaniu widoku. Cóż jak to uważam za pewne ograniczenia, czy nim jest teoretycznie? Nie mam takiej wiedzy by to stwierdzić.

Czytaj dalej tutaj (rozwija treść wpisu)
Czytaj dalej na blogu autora...

Zwiń
Czytaj na blogu autora...

Mechanizm bardzo przydatny, umożliwiający przechodzenie do następnego lub poprzedniego rekordu z poziomu wybranego już rekordu. Dzięki temu użytkownik nie musi wracać do listy czy też z poziomu listy otwierać dziesiątek stron w osobnych zakładkach by je wszystkie...