SQL Triky IV.

Z PostgreSQL
Skočit na navigaci Skočit na vyhledávání

Starších dvacet triků Nejnovější triky

Dotaz pomocí kurzoru je pomalý

PostgreSQL podporuje dva přístupy k zpracování dotazů. Klasický EXECUTE a prostřednictvím kurzoru. Pokud potřebujeme rychle prvních N řádků, pak je vhodnější použít kurzor. Prováděcí plán je vybrán tak, aby generoval data co nejdříve. Nehledí se na celkový čas provádění dotazu - jednak se předpokládá, že se nezpracuje kompletní dotaz, druhak se počítá s prodlevami (pro uživatele) během čtení kurzoru. Pokud potřebujeme kompletní výsledek, tak naopak použijeme EXECUTE. Chování optimalizátoru lze ovlivnit - pomocí proměnné cursor_tuple_fraction, která určuje odhad poměru přečtených řádků ku celkovému počtu řádků. Nastavením proměnné na hodnotu 1.0 určujeme, že předpokládáme čtení kompletní výsledku dotazu - a planner tomu přizpůsobí prováděcí plán.

Vícenásobné přiřazení do pole v rámci jednoho příkazu INSERT

Opakovaný přístup ke sloupci má smysl pouze v případě polí (z vestavěných typů). Do konference zaslal Tom Lane: 

postgres=# create table bar(a varchar[]);
CREATE TABLE
postgres=# insert into bar(a[2],a[7]) values('Pavel','Petr');
INSERT 0 1
postgres=# select * from bar;
                   a                    
────────────────────────────────────────
 [2:7]={Pavel,NULL,NULL,NULL,NULL,Petr}
(1 row)

Rozdělení lidí do skupin s omezením

Hezký trik založený na window funkcích se objevil na stránkách PostgreSQL Online Journalu. Představte si, že máte seznam lidí, které musíte rozdělit do skupin, přičem pro hmotnost skupiny platí, že nesmí přesáhnout 750Kg. Naznačené řešení nepochybně nebude optimální, také se nejedná o optimalizaci: 

CREATE TABLE passengers(passenger_name varchar(20) PRIMARY KEY, weight_kg integer);
INSERT INTO passengers(passenger_name, weight_kg)
VALUES ('Johnny', 60),
        ('Jimmy', 120),
        ('Jenny', 50),
        ('Namy', 20),
        ('Grendel', 500),
        ('Charlie', 200),
        ('Gongadin', 400),
        ('Tin Tin', 10),
        ('Thumb Twins', 10),
        ('Titan', 600),
        ('Titania', 550),
        ('Titan''s Groupie', 55);

SELECT carriage,COUNT(passenger_name) As cnt_pass, 
    array_agg(passenger_name) As passengers, SUM(weight_kg) As car_kg
FROM (SELECT passenger_name, weight_kg,
  ceiling(SUM(weight_kg) 
    OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)/750.00) As carriage
FROM passengers) As congregation
GROUP BY carriage
ORDER BY carriage;

Výsledek: 

 carriage | cnt_pass |                 passengers                 | car_kg
----------+----------+--------------------------------------------+--------
        1 |        5 | {Johnny,Jimmy,Jenny,Namy,Grendel}          |    750
        2 |        4 | {Charlie,Gongadin,"Tin Tin","Thumb Twins"} |    620
        3 |        1 | {Titan}                                    |    600
        4 |        2 | {Titania,"Titan's Groupie"}                |    605

Analogie funkce GROUP_CONCAT v PostgreSQL

MySQL má poměrně silnou agregační funkci group_concat. Přestože v pg lze definovat vlastní agregační funkce, syntakticky kompatibilní agregační funkci, která by byla obdobou group_concat v postresql vytvořit nelze. Nicméně díky vestavěné podpoře polí je možné suplovat funkcionalitu: 

postgres=# SELECT * FROM x;
 kat |  mesto  
-----+---------
   1 | Benešov
   1 | Tábor
   1 | Písek
   2 | Praha
   2 | Hradec
   3 | Cheb
   3 | Aš
(7 rows)

postgres=# SELECT kat, array_to_string(ARRAY(SELECT unnest(array_agg(mesto)) 
                                               ORDER BY 1),',') 
              FROM x 
             GROUP BY kat;
 kat |   array_to_string   
-----+---------------------
   1 | Benešov,Písek,Tábor
   3 | Aš,Cheb
   2 | Hradec,Praha
(3 rows)

Ekvivalent funkce field() z MySQL v PostgreSQL

Zdroj: http://stackoverflow.com/questions/1309624/simulating-mysqls-order-by-field-in-postgresql

V případě, že chcete určit pořadí výpisu seznamem hodnot, tak v MySQL máte možnost použít funkci field: 

select * from pet order by field(species, 'cat', 'dog', 'bird') desc;

+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birthday   | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       | 
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       | 
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       | 
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       | 
| Bowser   | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 | 
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       | 
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       | 
+----------+--------+---------+------+------------+------------+

V PostgreSQL taková funkce není - buďto ji můžeme nahradit konstrukcí CASE nebo si ji implementovat - lze díky podpoře variadických parametrů: 

select * 
   from pet 
  order by species, case species when 'cat' then 1 
                                 when 'dog' then 2 
                                 when 'bird' then 3 
                                 else 0 end desc;

Zdrojový kód funkce field: 

CREATE OR REPLACE FUNCTION field(varchar, VARIADIC text[]) 
RETURNS int AS $$
  SELECT i 
     FROM generate_subscripts($2,1) g(i) 
    WHERE $1 = $2[i] 
  UNION ALL
  SELECT 0 
  LIMIT 1 1
$$ LANGUAGE sql;

Pro úplnost přidám zdrojový kód komplementární funkce elt: 

CREATE OR REPLACE FUNCTION elt(int, VARIADIC text[])
RETURNS text AS $$
  SELECT $2[$1];
$$ LANGUAGE sql;

Nejlépe placený zaměstnanec po n-té

Tentokrát s využitím window funkcí: 

postgres=# SELECT * FROM zamestnanci;
┌───────────┬───────────┬──────────┬──────────┐
│   jmeno   │ prijmeni  │ oddeleni │   mzda   │
├───────────┴───────────┴──────────┴──────────┤
│ Pavel     │ Stehule   │        1 │ 10000.00 │
│ Zdenek    │ Stehule   │        1 │  9000.00 │
│ Vladimira │ Stehulova │        2 │  9000.00 │
└─────────────────────────────────────────────┘
(3 rows)

postgres=# SELECT * 
              FROM (SELECT *, max(mzda) OVER (PARTITION BY oddeleni) 
                       FROM zamestnanci) x 
             WHERE max = mzda;
┌───────────┬───────────┬──────────┬──────────┬──────────┐
│   jmeno   │ prijmeni  │ oddeleni │   mzda   │   max    │
├───────────┴───────────┴──────────┴──────────┴──────────┤
│ Pavel     │ Stehule   │        1 │ 10000.00 │ 10000.00 │
│ Vladimira │ Stehulova │        2 │  9000.00 │  9000.00 │
└────────────────────────────────────────────────────────┘
(2 rows)

Další variace:

Pozor, zde je drobná zrada - intuitivně by člověk použil HAVING. Podle standardu ale nejsou výsledky window funkcí v dotazu vůbec dostupné, takže je nutné použít derivovanou tabulku.

Převod bigintu na ip

Do konference zaslal Jasen Betts 

create function bigint_to_inet(bigint) returns inet as $$
select (($1>>24&255)||'.'||($1>>16&255)||'.'||($1>>8&255)||'.'||($1>>0&255))::inet
$$ language sql;

Označení originálních souborů na základě patche

Při vývoji PostgreSQL se často používají příkazy difforig a cporig. Pokud se chci podílet na vývoji patche, pak je nutné, ještě před samotnou aplikací patche, označit modifikované soubory pomocí příkazu cporig. Potom jsme schopni vytvořit patch příkazem difforig. Ruční značkování se lze vyhnout následujícím skriptem: 

cat named.diff | egrep -e "^\*\*\*.*(sql|out|h|c|sgml|y|lex|Makefile)$" | replace "*** a/" ""| xargs cporig

cat mnnotation.diff | egrep -o -e "^\*\*\*.*(sql|out|h|c|sgml|y|lex|Makefile)" | replace "*** " "" | xargs cporig

replace lze nahradit: 

sed 's/reg//'

Doplnění základních konstant do systému

Zdroj: http://www.simple-talk.com/sql/t-sql-programming/avoiding-the-eav-of-destruction/ 

CREATE VIEW Constants (pi, e, phi)
AS 
SELECT X.*
   FROM (VALUES (CAST (3.141592654 AS FLOAT), 
                 CAST (2.718281828 AS FLOAT), 
                 CAST (1.6180339887 AS FLOAT))
        ) AS X (pi, e, phi);

Výpočet mediánu s využitím Window funkcí

O možnostech určení mediánu se se rozepsal Depesz. Použil řešení, které navrhl Andrew Gierth (RhodiumToad): 

select avg(x)
   from ( select x, row_number() over (order by x),count(*) over () from zdrojdat ) s
  where row_number between floor((count::float8-1)/2+1) and ceil((count::float8-1)/2+1)

pro zajímavost přidávám řešení Joe Celka (SQL2005): 

WITH SortedData (x, hi, lo)
AS
(SELECT x,
        ROW_NUMBER() OVER (ORDER BY x ASC),
        ROW_NUMBER() OVER (ORDER BY x DESC)
   FROM RawData)
SELECT AVG(x * 1.0) AS median 
  FROM SortedData 
 WHERE hi IN (lo, lo+1, lo-1);

v úpravě pro PostgreSQL: 

select avg(x) 
   from (select x, 
                row_number() over (order by x asc) as hi, 
                row_number() over (order by x desc) as lo from foobar) s 
  where hi in (lo-1,lo, lo+1);

Na webu jsem narazil ještě na Itzik Ben-Ganovu metodu,: 

SELECT CustomerId,AVG(TotalDue)
   FROM (SELECT CustomerId, TotalDue,
                ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY TotalDue) AS RowNum,
                COUNT(*) OVER (PARTITION BY CustomerId) AS RowCnt
            FROM Sales.SalesOrderHeader
         ) x
  WHERE RowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2)
  GROUP BY CustomerId
  ORDER BY CustomerId;

která by měla být efektivnější než Celkovo řešení (reálně ovšem není) a pravděpodobně je vzorem pro Gierthův způsob. Pozn: fráze COUNT(*) OVER () je z nějakého důvodu pomalá - mnohem rychlejší je subselect (SELECT COUNT(*) FROM tab).

Pozor! Originální Celkova metoda vrací správný výsledek pouze pokud jsou vstupní hodnoty unikátní - což většinou není pravda - a je nutné použít modifikovanou metodu Joe Celka: 

SELECT avg(x)::float
              FROM (SELECT x, row_number() OVER (ORDER BY x asc) AS hi,
                              count(*) OVER () + 1 - row_number() OVER (ORDER BY x) AS lo
                       FROM foobar) qs
             WHERE hi IN (lo-1,lo,lo+1);

Bezpečná konverze řetězce na číslo

Vestavěné konverzní rutiny vyhodí výjimku, pokud detekují, že řetězec není číslo. To v některých případech může být příliš agresivní chování. Nicméně velice jednoduše lze detekovat, zda řetězec je číslo a jinak vrátit NULL: 

CREATE OR REPLACE FUNCTION read_int(varchar) 
RETURNS int AS $$ 
  SELECT CASE WHEN $1 ~ e'^\\d+$' THEN $1::int END; 
$$ LANGUAGE SQL IMMUTABLE STRICT;

Odstranění milisekund z timestampu

Obyčejně nepotřebujeme timestamp v maximální přesnosti - někdy dokonce může být maximální dostupná přesnost na úkor přehlednosti - proto je možné určit přesnost pro typ timestamp: 

postgres=# select current_timestamp;
             now              
------------------------------
 2009-05-23 20:42:21.57899+02
(1 row)

Time: 196,784 ms
postgres=# select current_timestamp::timestamp(2);
          now           
------------------------
 2009-05-23 20:42:27.74
(1 row)

Time: 51,861 ms
postgres=# select current_timestamp::timestamp(0);
         now         
---------------------
 2009-05-23 20:42:31
(1 row)

Time: 0,729 ms

Zobrazení čísla ve vědeckém formátu

PostgreSQL nepodporuje formát EEEE ve funkci to_char. Tento nedostatek můžeme překonat voláním externí funkce. Jednou z možností je použít implementaci v perlu (funkce sprintf): 

create or replace function floating_format(double precision, int) 
  returns varchar as $$
   sprintf("%.$_[1]e", $_[0])
$$ language plperl;

create or replace function floating_format(double precision) 
  returns varchar as $$
   sprintf("%e", $_[0])
$$ language plperl;

postgres=# select floating_format(10.223);
 floating_format 
-----------------
 1.022300e+01
(1 row)

postgres=# select floating_format(10.223,2);
 floating_format 
-----------------
 1.02e+01
(1 row)

postgres=# select floating_format(10.223,4);
 floating_format 
-----------------
 1.0223e+01
(1 row)

Konfigurace terminálů

Pro přístup k PostgreSQL používám dvě rozhraní - emacs a psql. Pro obé si lze práci zpříjemnit následujícími nastaveními:

  • emacs (.emacs)
(setq-default truncate-lines t)
(ansi-color-for-comint-mode-on)
(setq lazy-lock-defer-on-scrolling t)
(setq inhibit-startup-echo-area-message t)
(setq inhibit-startup-message t)
(show-paren-mode t)
(setq show-paren-style 'mixed)
(fset 'yes-or-no-p 'y-or-n-p)
(global-set-key "\M-g" 'goto-line)
(setq sql-database "postgres")  ;; nejčastěji používaná databáze
(setq sql-postgres-options '("-P" "pager=off" "-P" "title= ")) ;; nepoužívat pager, nastavit falešný title
(defun psql-init ()
   (sql-highlight-postgres-keywords))
(add-hook 'sql-mode-hook 'psql-init)
  • psql
export PAGER="less -RSX"
psql ... -P pager=always

Vyplatí se nastavit i automatický rollback chybových SQL příkazů: 

\set ON_ERROR_ROLLBACK interactive

Pozor na chování operátorů IS NULL a IS NOT NULL pro složené typy

Člověk tak nějak počítá s tím, že platí !(x IS NULL) = x IS NOT NULL. To ovšem v případě složených typů neplatí. Pokud je alespoň jedna položka NULL a alespoň jedna položka NOT NULL, pak výsledkem obou operátorů je nepravda. IS NULL je pravdivý, pokud jsou všechny položky NULL. IS NOT NULL pokud jsou všechny položky NOT NULL. Pokud je něco mezi, tak oba oparátory vrací false. 

CREATE OR REPLACE FUNCTION test_isnull()
RETURNS TABLE (a int, b int, isnull bool, isnotnull bool) AS $$
DECLARE r foo;
BEGIN
  isnull := r IS NULL; isnotnull := r IS NOT NULL; RETURN NEXT;
  a := NULL; b := 10; r := ROW(a, b);
  isnull := r IS NULL; isnotnull := r IS NOT NULL; RETURN NEXT;
  a := 10; b := 10; r := ROW(a, b);
  isnull := r IS NULL; isnotnull := r IS NOT NULL; RETURN NEXT;
  RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM test_isnull();

 a  | b  | isnull | isnotnull
----+----+--------+-----------
    |    | t      | f
    | 10 | f      | f
 10 | 10 | f      | t
(3 rows)

Pozor na implicitní přetypování v PL/pgSQL

Typ výsledeku každého vyhodnoceného výrazu v PL/pgSQL se porovnává s cílovým typem a v případě, že se neshoduje, dochází k IO přetypování (konverze na string/string na hodnotu). Tato konverze je relativně náročná a navíc je nutné dohledat odpovídající konverzní rutiny. Pokud se typy shodují, tak je výsledek bez jakýchkoliv změn přiřazen cílové proměnné. Ke konverzím dochází nejčastěji v přiřazovacím příkazu: 

create or replace function test1() 
returns int as $$
declare s int := 0; 
begin 
  for i in 1..100000 loop s := 4e3; end loop; -- numeric 
  return s; 
end; 
$$ language plpgsql immutable;

create or replace function test2() 
returns int as $$
declare s int := 0; 
begin 
  for i in 1..100000 loop s := 4e3::int; end loop; -- int 
  return s; 
end; 
$$ language plpgsql immutable;

postgres=# select test1();
 test1 
-------
  4000
(1 row)

Time: 176,623 ms

postgres=# select test2();
 test2 
-------
  4000
(1 row)

Time: 47,673 ms

nebo v konverzi návratové hodnoty: 

create or replace function test1() returns int as $$begin return 4e1; end; $$ language plpgsql; 
create or replace function test2() returns int as $$begin return 4e1::int; end; $$ language plpgsql;

postgres=# select count(test1()) from generate_series(1,100000);
 count  
--------
 100000
(1 row)

Time: 682,005 ms
postgres=# select count(test2()) from generate_series(1,100000);
 count  
--------
 100000
(1 row)

Time: 528,099 ms

Zabránění paralelnímu spuštění funkce

Teoreticky lze z interních údajů vyčíst, zda ta či ona funkce běží - nicméně tyto údaje nejsou běžně k dispozici (bez vývoje v C). Jedinou dostupnou informací ohledně ostatních procesů je pohled pg_stat_activity. Pokud název funkce nenajdeme v prováděných dotazech, tak víme, že funkce nebyla spuštěna přímo z SQL - je ale možné, že funkce byla spuštěna z uložené procedury a to tímto způsobem detekovat nelze. 

create or replace function long_run() 
returns void as $$
begin 
  if exists(select procpid 
               from pg_stat_activity 
              where procpid <> pg_backend_pid() 
                    and current_query like '%long_run(%') 
  then 
    raise notice 'procedura jiz bezi'; 
    return; 
  end if; 
  perform pg_sleep(10); 
end$$ 
language plpgsql volatile;
CREATE FUNCTION

Další možností je použití tzv. advisory locks http://www.postgresql.org/docs/8.3/interactive/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS. 

create or replace function long_run()
returns void as $$
declare foid oid:= 'long_run'::regproc::oid;
begin
  if pg_try_advisory_lock(foid::bigint) then
    perform pg_sleep(10); 
    pg_advisory_unlock(foid::bigint);   
  else
    raise notice 'procedura jiz bezi';
  end if;
  return;
end;
$$ language plpgsql;

Přesun tabulek z jednoho schématu do druhého

V PostgreSQL není příkaz, kterým bychom přesunuli tabulky z jednoho schématu do druhého. Díky uloženým procedurám to ovšem není problém: 

create or replace function mvtable(src varchar, dest varchar, mask varchar)
returns void as $$
declare r record;
begin
  for r in
          select *
             from information_schema.tables
            where table_schema = src and table_name like mask
  loop
    execute 'alter table ' || quote_ident(r.table_schema)
                   || '.' || quote_ident(r.table_name)
                   || ' set schema ' || quote_ident(dest);
  end loop;
  return;
end;
$$ language plpgsql;

Převod čísla do libovolné číselné soustavy

CTE můžeme využít pro převod čísla do libovolné číselné soustavy: 

postgres=# create function to_base(num int, base int = 10) 
           returns varchar as $$
             with recursive z as (
                                  select $1 as a, null::int as b, 0 as i 
                                  union all 
                                  select a/$2, a % $2, i+1 
                                     from z 
                                  where a > 0
                                 ) 
                select array_to_string(array(select substring('0123456789abcdef' from b + 1 for 1) 
                   from z 
                  where i > 0  
                 order by i desc),'');
           $$ language sql immutable strict;

postgres=# select to_base(255);
 to_base 
---------
 255
(1 row)

postgres=# select to_base(255,16);
 to_base 
---------
 ff
(1 row)

postgres=# select to_base(255,8);
 to_base 
---------
 377
(1 row)

postgres=# select to_base(255,2);
 to_base  
----------
 11111111
(1 row)

Inverzní funkce: 

create or replace function base(str varchar, base int = 10) 
returns int as $$
  select sum((position(substring($1 from i for 1) in '0123456789abcdef')- 1) * $2 ^ (char_length($1) - i))::int 
     from generate_series(1,char_length($1)) g(i);
$$ language sql immutable strict

Funkce to_bin

V PostgreSQL chybí funkce, která by provedla konverzi čísla do binárního tvaru. Zde je jedna varianta: 

create or replace function to_bin(int) 
returns varchar as $$
declare r varchar; 
begin 
  execute 'select ' || $1 || '::bit(' || case when $1 = 0 then 1 else trunc(log(2, $1)) + 1 end || ')' into r; 
  return r; 
end;
$$ language plpgsql immutable strict;

create or replace function bin(varchar) 
returns varchar as $$
declare r varchar; 
begin 
  execute 'select b' || quote_literal($1) || '::int' into r; 
  return r; 
end;
$$ language plpgsql immutable strict;

postgres=# select to_bin(5), bin('101');
 to_bin | bin 
--------+-----
 101    | 5
(1 row)

Starších dvacet triků Nejnovější triky

Citováno z „http://postgres.cz/index.php?title=SQL_Triky_IV.&oldid=507“