kto z was wie o istnieniu funkcji generate_series() w postgresie? a kto z niej kiedykolwiek (nie testowo!) skorzystał?
wydaje mi się, że jest to najbardziej niedoceniania funkcja istniejąca w tym systemie bazodanowym.
co daje? zobacz:

select * from generate_series(0, 100);

na pierwszy rzut oka nic rewolucyjnego? taki tam sobie liczniczek?
zobaczmy więc kilka przykładowych zastosowań.
pierwsze:
mamy tabelkę, z polem id (z seriala), i chcemy sprawdzić czy są dziury w numeracji. tradycyjna metoda to jakieś joiny czy skanowanie sekwencyjne ze zliczaniem dziur w aplikacji klienckiej.
a zobaczmy coś takiego:

create table x as select * from generate_series(1, 10000) as x (i);

delete from x where random() < 0.0010;

w ten sposób uzyskaliśmy tabelę x, z numerycznym polem i. a w numeracji tego pola są dziury.
jak je wylistować? tak:

select * from generate_series(1, 10000) except select i from x;

voila. działa. ślicznie. i szybko 🙂

to było proste i oczywiste.
teraz przykład trochę mniej oczywisty – przykład jest z mojej pracy, ale użyję trochę uproszczonej wersji struktur danych 🙂
stwórzmy sobie tabelkę z użytkownikami:

CREATE TABLE users (id serial PRIMARY KEY, username TEXT, registered date);

ok. teraz – wstawmy do niej 10 użytkowników, z losowymi unikalnymi username'ami, i różnymi, losowymi datami rejestracji:

insert into users (username, registered) select 'user: ' || x.i, '2000-01-01'::date + cast(random()*1000 as int4) from generate_series(1, 10) as x(i);

mamy teraz w users 10 użytkowników. daty rejestracji są losowane z zakresu ‘2000-01-01' do ‘2002-09-27';
teraz. to co potrzebowałem to musiałem wygenerować "faktury" dla użytkowników. w tym zaległe.
idea była taka, że w tabelce z fakturami:

CREATE TABLE invoices (id serial PRIMARY KEY, user_id INT4, paid_from date, paid_to date, status INT4);

są trzymane faktury użytkowników. każda faktura jest na 30 dni. musimy wstawić każdemu użytkownikowi tyle faktur by obejmowały cały okres od daty rejestracji użytkownika do chwili obecnej.
przykładowo:
jeśli mielibyśmy użytkownika który zarejestrował się 2006-12-01 (a dziś jest 2007-01-11) to musielibyśmy wygenerować dla niego 2 faktury:

1. od 2006-12-01 do 2006-12-30
2. od 2007-12-31 do 2007-01-29

dalszych nie, bo musimy zakończyć faktury na fakturze "aktualnej".
pierwsze co potrzebujemy to wiedzieć ile maksymalnie nam się może faktur wygenerować dla pojedynczego użytkownika. w naszym przypadku sprawdzamy to poprzez:

select ceil((now()::date - min(registered))/30.0) from users;

u mnie pokazało się 83.
ponieważ lubię proste liczby, zaokrąglę sobie to do 100. nie ma żadnego matematycznego uzasadnienia – po prostu tak wolę.
tak więc. to co nam się przyda to generate_series(1,100), a dokładniej generate_series(0,100).
pierwsze zapytanie które napiszemy zwróci nam dla danego użytkownika (na razie tylko jednego) listę 101 faktur (od 0 do 100):

SELECT u.registered + (x.i * 30), u.registered + (x.i*30) + 29 FROM users u, generate_series(0, 100) as x(i) WHERE u.id = 1;

zwracam uwagę na to, że nie mamy żadnego warunku na połączenie users i generate_series – nie jest to join, ale tzw. kartezjan.
jak widać – zapytanie zadziałało.
teraz musimy je ograniczyć, by zwróciło dane tylko do aktualnych:

SELECT

    u.registered + (x.i * 30), u.registered + (x.i*30) + 29

FROM

    users u, generate_series(0, 100) as x(i)

WHERE

    u.id = 1

    AND now()::date >= u.registered + (x.i * 30)

;

wow. działa.
to teraz. ponieważ mamy do czynienia z kartezjanem a nie joinem, to usunięcie "where u.id = 1" nie spowoduje uszkodzenia danych, tylko wypełnienie ich dla wszystkich userów.
dzięki czemu możemy zapisać finalne zapytanie w postaci:

INSERT INTO invoices (user_id, paid_from, paid_to, status)

SELECT

    u.id,

    u.registered + (x.i * 30),

    u.registered + (x.i*30) + 29,

    0

FROM

    users u, generate_series(0, 100) as x(i)

WHERE

    now()::date >= u.registered + (x.i * 30)

;

czy na pewno zadziałało dobrze?
zobaczmy:

SELECT

    u.id,

    u.registered,

    ( SELECT count(*) FROM invoices i WHERE i.user_id = u.id ) as invoice_count,

    i1.paid_from as invoice1_from,

    i1.paid_to as invoice1_to,

    i2.paid_from as invoice2_from,

    i2.paid_to as invoice2_to,

    now()::date as "now"

FROM

    users u,

    invoices i1,

    invoices i2

WHERE

    i1.id = (SELECT min(t.id) FROM invoices t WHERE t.user_id = u.id)

    AND

    i2.id = (SELECT max(t.id) FROM invoices t WHERE t.user_id = u.id)

;

u mnie wszystko zadziałało ok.
jak widać (mam nadzieję) – generate_series potrafi sporo. generowanie list liczb pozwala na robienie np. robienie zestawień, włącznie z wyświetlaniem sum danych nieistniejących, sprawdzanie braków, "zgadywanie" pewnych wartości.
pozostaje zachęcić was do testowania i bawienia się – to czym i jak się bawicie jest niejednokrotnie ważniejsze od finalnego efektu – efekt jest jednorazowy. zabawa owocuje wiedzą która zostaje i kiedyś sie przyda.
aha. ważna notatka – generate_series zostało dodane w 8.0. no ale przecież chyba nikt już nie używa baz starszych 🙂 (żartuję oczywiście)