Common table expression (CTE) - Postgres

Common table expression (CTE) patrí medzi rozšírené prvky každého databázového systému vrátane postgres. Predstavuje dočasný SQL dopyt, ktorý môže byť vrámci dlhšieho a zložitého selektu použitý viackrát, sql je čitateľnejšie a script je rýchlejší a optimalizovanejší.V postgres sa dá na základe výsledku CTE robiť aj delete a update. CTE štruktúra vyzerá nasledovne:

WITH RECURSIVE cte_name (column1, column2, ...)
AS(
    -- anchor member
    SELECT select_list FROM table1 WHERE condition

    UNION [ALL]

    -- recursive term
    SELECT select_list FROM cte_name WHERE recursive_condition
) 
SELECT * FROM cte_name;
                        

Z uvedeného vyplýva, že každé CTE musí začínať s kľúčovým slovom WITH za ktorým nasleduje názov CTE (ak chceme opakujúce sa CTE, musíme dať slovo "RECURSIVE"), nasleduje AS a v zátvorke je použitý selekt, následne sa uzatvorí zátvorka, ak chceme pridať ďalšie CTE, za uzavretou zátvorkou sa pridá čiarka a názov CTE a opäť skript v zátvorke:

    
WITH RECURSIVE cte_name (column1, column2, ...)
AS(
    -- anchor member
    SELECT select_list FROM table1 WHERE condition
),
cte_name2 (column1, column2, ...)
AS(
    -- anchor member
    SELECT select_list FROM table1 WHERE condition
)  
SELECT * FROM cte_name
union all
SELECT * FROM cte_name2
;
  

Ak chceme zaznamenávať počty vymazaných riadkov alebo zmenených napr. do nejakej logovacej tabulky nazvanej log_table, môžeme využiť CTE:

--delete
WITH MOVED_ROWS AS 

(DELETE 

FROM TABLE_NAME RETURNING *) 

INSERT INTO LOG_TABLE

SELECT MR.*, 

CURRENT_TIMESTAMP 

FROM MOVED_ROWS MR; 

-update
WITH CTE AS 

(UPDATE TABLE_NAME 

SET COLUMN_NAME_NUMERIC = 100

RETURNING *) 
INSERT INTO LOG_TABLE

SELECT * 

FROM CTE ; 
                    

Ďalšou možnosťou je využitie CTE v Recursive query, kedy opakujúci sa skript v CTE môže ísť cez viac levelov a tak zaznamenať všetky záznamy vo výslednom dopyte.Ak máme napríklad tabuľku zamestnancov a v rámci tej tabulky zamestnancov sú zobrazený manažéri zamestnancov tzn aj manažéri aj zamestnanci a potrebujeme nájsť kto je koho manažér a aká je postupnosť.

create table employees
(
    employee_id    numeric(6)  not null
        constraint emp_emp_id_pk
            primary key,
    first_name     varchar(20),
    last_name      varchar(25) not null,
    email          varchar(25) not null,
    phone_numeric  varchar(20),
    hire_date      date        not null,
    job_id         varchar(10) not null,
    salary         numeric(8, 2),
    commission_pct numeric(2, 2),
    manager_id     numeric(6)
        constraint emp_manager_fk
            references employees,
    department_id  numeric(4)
);

insert into public.employees (employee_id, first_name, last_name, email, phone_numeric, hire_date, job_id, salary, commission_pct, manager_id, department_id)
values  (100, 'Steven', 'King', 'SKING', '515.123.4567', '2003-06-17', 'AD_PRES', 24000.00, null, null, 90),
        (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '2005-09-21', 'AD_VP', 17000.00, null, 100, 90),
        (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', '2001-01-13', 'AD_VP', 15000.00, null, 100, 90),
        (114, 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', '2002-12-07', 'PU_MAN', 11000.00, null, 100, 30),
        (120, 'Matthew', 'Weiss', 'MWEISS', '650.123.1234', '2004-07-18', 'ST_MAN', 8000.00, null, 100, 50),
        (121, 'Adam', 'Fripp', 'AFRIPP', '650.123.2234', '2005-04-10', 'ST_MAN', 8200.00, null, 100, 50),
        (122, 'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', '2003-05-01', 'ST_MAN', 7900.00, null, 100, 50),
        (123, 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', '2005-10-10', 'ST_MAN', 6500.00, null, 100, 50),
        (124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', '2007-11-16', 'ST_MAN', 5800.00, null, 100, 50),
        (145, 'John', 'Russell', 'JRUSSEL', '011.44.1344.429268', '2004-10-01', 'SA_MAN', 14000.00, 0.40, 100, 80),
        (146, 'Karen', 'Partners', 'KPARTNER', '011.44.1344.467268', '2005-01-05', 'SA_MAN', 13500.00, 0.30, 100, 80),
        (147, 'Alberto', 'Errazuriz', 'AERRAZUR', '011.44.1344.429278', '2005-03-10', 'SA_MAN', 12000.00, 0.30, 100, 80),
        (148, 'Gerald', 'Cambrault', 'GCAMBRAU', '011.44.1344.619268', '2007-10-15', 'SA_MAN', 11000.00, 0.30, 100, 80),
        (149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', '2008-01-29', 'SA_MAN', 10500.00, 0.20, 100, 80),
        (201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', '2004-02-17', 'MK_MAN', 13000.00, null, 100, 20);

WITH RECURSIVE ZAMESTNANCI_REC AS

(SELECT first_name,last_name,employee_id,'manažér' as naz

FROM employees s where employee_id=100

UNION SELECT e.first_name,e.last_name,e.manager_id,'podriadeny' as naz

FROM employees e

INNER JOIN ZAMESTNANCI_REC S ON S.employee_id = E.manager_id)

SELECT *

FROM ZAMESTNANCI_REC;
                    

Na príkladoch sme si ukázali využitie CTE v databázovom nástroji postgres. Základaným myšlienkou je neopakovať zbytočne query viackrát v skripte a redukovať tak I/O operácie a mať dočasný skript , na ktorý nasledujúce skripty nadväzujú. Zároveň je to riešenie, ktoré nealokuje žiadnu pamäť v porovnaní s temp tabuľkami a má viacej možností využitia. Je možné vytvoriť logovaciu tabuľku alebo urobiť recursivné query čo je výhodou CTE v postgres oproti iným databázovým nástrojom (Oracle, MS SERVER) ktoré dané operácie robia cez zložité funkcie a procedúry. Odporúčam CTE využívať denno denne pre všetkých a tak písať optimalizovanejšie skripty.