to akurat poprawka malutka, ale jak dla mnie po prostu bogosławiona.

jak może wiecie polecenie update (a także delete) może aktualizować dane z pomocą innej tabeli. przykładowo:

# CREATE TABLE statusy (id serial PRIMARY KEY, kod TEXT);
NOTICE:  CREATE TABLE will CREATE implicit SEQUENCE "statusy_id_seq" FOR serial COLUMN "statusy.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX "statusy_pkey" FOR TABLE "statusy"
CREATE TABLE
# INSERT INTO statusy (kod) VALUES ('new'), ('open'), ('resolved'), ('rejected');
INSERT 0 4
# CREATE TABLE zgloszenia (id serial, temat TEXT, status_id INT NOT NULL REFERENCES statusy (id));
NOTICE:  CREATE TABLE will CREATE implicit SEQUENCE "zgloszenia_id_seq" FOR serial COLUMN "zgloszenia.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX "zgloszenia_pkey" FOR TABLE "zgloszenia"
CREATE TABLE
# INSERT INTO zgloszenia (temat, status_id) VALUES ('a', 1), ('b', 2), ('c', 3), ('d', 4);
INSERT 0 4
# ALTER TABLE zgloszenia ADD COLUMN STATUS TEXT;
ALTER TABLE
# UPDATE zgloszenia SET STATUS = s.kod FROM statusy s WHERE s.id = status_id;
UPDATE 4
# SELECT * FROM zgloszenia;

(4 rows)

fajne. ale – jeśli nazwy kolumn się powtarzają (np. ja mam w każdej tabeli pole “id" będące primary key'em) to trzeba postgresowi powiedzieć o które pole dokładnie mi chodzi. póki używam id z tabel dołączanych to nie problem – w “FROM" mogę podać alias na tabelę (w przykładzie powyżej: “s"). ale już tabeli którą aktualizuję – nie mogłem aliasować.

teraz już mogę. dzięki czemu nawet przy aktualizowaniu tabeli o długiej nazwie i używaniu w warunkach jej pól nie będę miał kosmicznie długiego sql'a – trudnego do czytania i poprawiania.

wygląda to na przykład tak:

UPDATE wynagrodzenia_pracownikow s SET pensje = SUM(p.wynagrodzenie) FROM wynagrodzenia_pracownikow p WHERE p.przelozony_id = s.id;

ten sam mechanizm działa w przypadku delete'ów join-ujących tabele w celu stworzenia odpowiednich warunków.

post zaległy od wczoraj 🙂

w czasie projektowania i proponowania nowości do postgresa 8.2 padła idea by zacząć obsługiwać inserty wstawiające wiele rekordów. tak jak ma np. mysql:

INSERT INTO `tabele` (`pole1`, `pole2`) VALUES (1,1),(2,4),(2,7);

jak zawsze – panowie z core-teamu stwierdzili, że skoro robią już coś takiego, to może to trochę podkręcić.

wyszło z tego zupełnie nowe polecenie sql'owe: VALUES.

dzięki temu mogli zachować kompatybilność z standardowymi sql'ami, ale także wykorzystać to dla innych zastosowań.

zacznijmy od podstaw:

# CREATE TABLE x (id serial PRIMARY KEY, p_a TEXT, p_b TEXT);
NOTICE:  CREATE TABLE will CREATE implicit SEQUENCE "x_id_seq" FOR serial COLUMN "x.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX "x_pkey" FOR TABLE "x"
CREATE TABLE
# INSERT INTO x (p_a, p_b) VALUES ('standardowa', 'metoda');
INSERT 0 1
# INSERT INTO x (p_a, p_b) VALUES ('nowsza', 'metoda'), ('drugi', 'rekord'), ('następne', 'rekordy');
INSERT 0 3
# SELECT * FROM x;

(4 rows)

fajne. tylko po co? no cóż. zrobiłem mały test. zrobiłem tabelkę:

# CREATE TABLE x (id serial PRIMARY KEY, liczba INT4);

i wstawiałem do niej dane (100000 kolejnych liczb) na kilka sposobów:

• copy x (liczba) from stdin;
• 100000 insertów – każdy po jednym rekordzie
• 100000 insertów – każdy po jednym rekordzie (ale z użyciem prepare i execute)
• 10000 insertów – każdy po dziesięć rekordów
• 10000 insertów – każdy po dziesięć rekordów (ale z użyciem prepare i execute)
• 5000 insertów – każdy po dwadzieścia rekordów
• 5000 insertów – każdy po dwadzieścia rekordów (ale z użyciem prepare i execute)

wyniki:

• copy: 0m1.045s
• insert (1): 1m5.473s
• insert + prepare (1): 1m3.483s
• insert (10): 0m8.500s
• insert + prepare (10): 0m7.552s
• insert (20): 0m4.065s
• insert + prepare (20): 0m3.656s

dla chętnych do powtórzenia testów/obejrzenia – skrypty oczywiście są dostępne.

graficznie wygląda to jeszcze bardziej interesująco:

jak widac przyspieszenie insertów jest znaczne. czy to wszystko? nie!

values można wykorzystać wszędzie tam gdzie potrzebujemy wielu rekordów. przykładowe zastosowanie – wyobraźmy sobie, że mamy pole typu int z zapisanymi statusami. statusów jest ledwie kilka. nie warto dla nich robić oddzielnej tabeli. ale jednak chcielibyśmy móc je w jakiś sposób odczytać z bazy … proszę:

# CREATE TABLE x (id serial PRIMARY KEY, liczba INT4);
NOTICE:  CREATE TABLE will CREATE implicit SEQUENCE "x_id_seq" FOR serial COLUMN "x.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX "x_pkey" FOR TABLE "x"
CREATE TABLE
# INSERT INTO x (liczba) VALUES (1), (2), (3), (2);
INSERT 0 4
# SELECT
x.id, x.liczba, k.kod
FROM x
JOIN (VALUES (1, 'closed'), (2, 'new'), (3, 'open')) AS k (id, kod) ON x.liczba = k.id
;

(4 rows)

oczywiście powyższy efekt można uzyskać używając (CASE WHEN … END), ale to rozwiązanie jest krótsze i mocno czytelniejsze.

zwracam też uwagę na to, że w oparciu o VALUES można zdefiniować widok:

# CREATE VIEW statusy AS SELECT * FROM (VALUES (1, 'closed'), (2, 'new'), (3, 'open')) AS k (id, kod);
CREATE VIEW
# SELECT * FROM statusy;

(3 rows)

co jeszcze można z tym zrobić? sporo. pobawcie się sami i zastanówcie do czego można tego użyć.

dziś pierwszy odcinek o zmianach w postgresie 8.2. tematem odcinka są zapytania “… returning".

do zapytań modyfikujących dane – INSERT INTO, UPDATE, DELETE FROM zostało dodane rozszerzenie “RETURNING".

składnia wygląda następująco:

• INSERT INTO tabelka (pola) VALUES (costam) RETURNING wyrażenie;
• UPDATE tabelka SET pole = X WHERE cośtam RETURNING wyrażenie;
• DELETE FROM tabelka WHERE cośtam RETURNING wyrażenie;

przykłady użycia dla INSERT:

# CREATE TABLE x (id serial PRIMARY KEY, ble INT4);
NOTICE:  CREATE TABLE will CREATE implicit SEQUENCE "x_id_seq" FOR serial COLUMN "x.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX "x_pkey" FOR TABLE "x"
CREATE TABLE
# INSERT INTO x (ble) VALUES ('123') returning id;
id
----
1
(1 ROW)
INSERT 0 1
# INSERT INTO x (ble) VALUES ('123') returning id, ble + 123;
id | ?COLUMN?
----+----------
2 |      246
(1 ROW)
INSERT 0 1
# INSERT INTO x (ble) VALUES ('123') returning *;
id | ble
----+-----
3 | 123
(1 ROW)
INSERT 0 1
# INSERT INTO x (ble) VALUES ('123') returning *, now(), (SELECT COUNT(*) FROM pg_class);
id | ble |              now              | ?COLUMN?
----+-----+-------------------------------+----------
4 | 123 | 2006-12-07 22:52:24.573777+01 |      209
(1 ROW)
INSERT 0 1
# SELECT * FROM x;
id | ble
----+-----
1 | 123
2 | 123
3 | 123
4 | 123
(4 ROWS)
# INSERT INTO x (ble) SELECT ble + 2 FROM x returning *;
id | ble
----+-----
5 | 125
6 | 125
7 | 125
8 | 125
(4 ROWS)
INSERT 0 4

miłe, czyż nie? głównym celem jest możliwość automatycznego pobierania wartości typu id – nadawanych z sekwencji. ale zadziała to także zwracając dane modyfikowane przez triggery.

to co jest po “RETURNING" musi być zgodne formatowo z tym co jest w standardowym select'cie między SELECT a FROM.

przykłady użycia dla UPDATE:

# UPDATE x SET ble = ble * 2 WHERE id % 2 = 0 returning *;
id | ble
----+-----
2 | 246
4 | 246
6 | 250
8 | 250
(4 ROWS)
UPDATE 4

zwracam uwagę na to, że returning zwraca nowe wartości! czyli tak po triggerowemu mówiąc: retyrning * oznacza return NEW; a nie return OLD;!

przykłady użycia dla DELETE:

# DELETE FROM x WHERE id % 2 = 1 returning *;
id | ble
----+-----
1 | 123
3 | 123
5 | 125
7 | 125
(4 ROWS)
DELETE 4

jak widać składnia wszędzie jest podobna, miła łatwa i prosta. a do tego niesie sporo ułatwień. mnie osobiście cieszy INSERT … returning id; bo załatwia mi w piękny sposób odpowiadanie ludziom pytającym o id, i reagującym nerwowo na sugestie: select currval() : “ale czy jak dwa połączenia wstawią rekordy …" 🙂