http://postgres.cz/index.php?title=SQL_Triky_IV.&feed=atom&action=historySQL Triky IV. - Historie editací2024-03-28T14:27:58ZHistorie editací této stránkyMediaWiki 1.36.0http://postgres.cz/index.php?title=SQL_Triky_IV.&diff=507&oldid=previmported>Pavel: /* Konfigurace terminálů */2011-09-29T21:45:28Z<p><span dir="auto"><span class="autocomment">Konfigurace terminálů</span></span></p>
<p><b>Nová stránka</b></p><div>[[SQL Triky III.|Starších dvacet triků]] [[SQL Triky|Nejnovější triky]]<br />
==Dotaz pomocí kurzoru je pomalý==<br />
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.<br />
<br />
==Vícenásobné přiřazení do pole v rámci jednoho příkazu INSERT==<br />
Opakovaný přístup ke sloupci má smysl pouze v případě polí (z vestavěných typů). Do konference zaslal Tom Lane:<br />
<pre><br />
postgres=# create table bar(a varchar[]);<br />
CREATE TABLE<br />
postgres=# insert into bar(a[2],a[7]) values('Pavel','Petr');<br />
INSERT 0 1<br />
postgres=# select * from bar;<br />
a <br />
────────────────────────────────────────<br />
[2:7]={Pavel,NULL,NULL,NULL,NULL,Petr}<br />
(1 row)<br />
</pre><br />
<br />
==Rozdělení lidí do skupin s omezením==<br />
Hezký trik založený na window funkcích se objevil na stránkách [http://www.postgresonline.com/journal/index.php?/archives/137-Allocating-People-into-Groups-with-Window-functions.html 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:<br />
<pre><br />
CREATE TABLE passengers(passenger_name varchar(20) PRIMARY KEY, weight_kg integer);<br />
INSERT INTO passengers(passenger_name, weight_kg)<br />
VALUES ('Johnny', 60),<br />
('Jimmy', 120),<br />
('Jenny', 50),<br />
('Namy', 20),<br />
('Grendel', 500),<br />
('Charlie', 200),<br />
('Gongadin', 400),<br />
('Tin Tin', 10),<br />
('Thumb Twins', 10),<br />
('Titan', 600),<br />
('Titania', 550),<br />
('Titan''s Groupie', 55);<br />
<br />
SELECT carriage,COUNT(passenger_name) As cnt_pass, <br />
array_agg(passenger_name) As passengers, SUM(weight_kg) As car_kg<br />
FROM (SELECT passenger_name, weight_kg,<br />
ceiling(SUM(weight_kg) <br />
OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)/750.00) As carriage<br />
FROM passengers) As congregation<br />
GROUP BY carriage<br />
ORDER BY carriage;<br />
</pre><br />
Výsledek:<br />
<pre><br />
carriage | cnt_pass | passengers | car_kg<br />
----------+----------+--------------------------------------------+--------<br />
1 | 5 | {Johnny,Jimmy,Jenny,Namy,Grendel} | 750<br />
2 | 4 | {Charlie,Gongadin,"Tin Tin","Thumb Twins"} | 620<br />
3 | 1 | {Titan} | 600<br />
4 | 2 | {Titania,"Titan's Groupie"} | 605<br />
</pre><br />
<br />
==Analogie funkce GROUP_CONCAT v PostgreSQL==<br />
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:<br />
<pre><br />
postgres=# SELECT * FROM x;<br />
kat | mesto <br />
-----+---------<br />
1 | Benešov<br />
1 | Tábor<br />
1 | Písek<br />
2 | Praha<br />
2 | Hradec<br />
3 | Cheb<br />
3 | Aš<br />
(7 rows)<br />
<br />
postgres=# SELECT kat, array_to_string(ARRAY(SELECT unnest(array_agg(mesto)) <br />
ORDER BY 1),',') <br />
FROM x <br />
GROUP BY kat;<br />
kat | array_to_string <br />
-----+---------------------<br />
1 | Benešov,Písek,Tábor<br />
3 | Aš,Cheb<br />
2 | Hradec,Praha<br />
(3 rows)<br />
</pre><br />
<br />
==Ekvivalent funkce ''field()'' z MySQL v PostgreSQL==<br />
Zdroj: http://stackoverflow.com/questions/1309624/simulating-mysqls-order-by-field-in-postgresql<br />
<br />
V případě, že chcete určit pořadí výpisu seznamem hodnot, tak v MySQL máte možnost použít funkci field:<br />
<pre><br />
select * from pet order by field(species, 'cat', 'dog', 'bird') desc;<br />
<br />
+----------+--------+---------+------+------------+------------+<br />
| name | owner | species | sex | birthday | death |<br />
+----------+--------+---------+------+------------+------------+<br />
| Fluffy | Harold | cat | f | 1993-02-04 | NULL | <br />
| Claws | Gwen | cat | m | 1994-03-17 | NULL | <br />
| Buffy | Harold | dog | f | 1989-05-13 | NULL | <br />
| Fang | Benny | dog | m | 1990-08-27 | NULL | <br />
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 | <br />
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL | <br />
| Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | <br />
+----------+--------+---------+------+------------+------------+<br />
</pre><br />
<br />
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ů:<br />
<pre><br />
select * <br />
from pet <br />
order by species, case species when 'cat' then 1 <br />
when 'dog' then 2 <br />
when 'bird' then 3 <br />
else 0 end desc;<br />
</pre><br />
Zdrojový kód funkce field:<br />
<pre><br />
CREATE OR REPLACE FUNCTION field(varchar, VARIADIC text[]) <br />
RETURNS int AS $$<br />
SELECT i <br />
FROM generate_subscripts($2,1) g(i) <br />
WHERE $1 = $2[i] <br />
UNION ALL<br />
SELECT 0 <br />
LIMIT 1 1<br />
$$ LANGUAGE sql;<br />
</pre><br />
Pro úplnost přidám zdrojový kód komplementární funkce elt:<br />
<pre><br />
CREATE OR REPLACE FUNCTION elt(int, VARIADIC text[])<br />
RETURNS text AS $$<br />
SELECT $2[$1];<br />
$$ LANGUAGE sql;<br />
</pre><br />
<br />
==Nejlépe placený zaměstnanec po n-té==<br />
Tentokrát s využitím window funkcí:<br />
<pre><br />
postgres=# SELECT * FROM zamestnanci;<br />
┌───────────┬───────────┬──────────┬──────────┐<br />
│ jmeno │ prijmeni │ oddeleni │ mzda │<br />
├───────────┴───────────┴──────────┴──────────┤<br />
│ Pavel │ Stehule │ 1 │ 10000.00 │<br />
│ Zdenek │ Stehule │ 1 │ 9000.00 │<br />
│ Vladimira │ Stehulova │ 2 │ 9000.00 │<br />
└─────────────────────────────────────────────┘<br />
(3 rows)<br />
<br />
postgres=# SELECT * <br />
FROM (SELECT *, max(mzda) OVER (PARTITION BY oddeleni) <br />
FROM zamestnanci) x <br />
WHERE max = mzda;<br />
┌───────────┬───────────┬──────────┬──────────┬──────────┐<br />
│ jmeno │ prijmeni │ oddeleni │ mzda │ max │<br />
├───────────┴───────────┴──────────┴──────────┴──────────┤<br />
│ Pavel │ Stehule │ 1 │ 10000.00 │ 10000.00 │<br />
│ Vladimira │ Stehulova │ 2 │ 9000.00 │ 9000.00 │<br />
└────────────────────────────────────────────────────────┘<br />
(2 rows)<br />
</pre><br />
<br />
Další variace:<br />
*[[SQL_Triky_II.#Nejl.C3.A9pe_placen.C3.BD_zam.C4.9Bstnanec_po_n-t.C3.A9|Nejlépe placený zaměstnanec po n-té]]<br />
*[[SQL_Triky_I.#V.C3.BDb.C4.9Br_prvn.C3.ADch_n_.C5.99.C3.A1dk.C5.AF_ze_skupiny|Výběr prvních n řádků ze skupiny]]<br />
<br />
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.<br />
<br />
==Převod bigintu na ip==<br />
Do konference zaslal Jasen Betts<br />
<pre><br />
create function bigint_to_inet(bigint) returns inet as $$<br />
select (($1>>24&255)||'.'||($1>>16&255)||'.'||($1>>8&255)||'.'||($1>>0&255))::inet<br />
$$ language sql;<br />
</pre><br />
<br />
==Označení originálních souborů na základě patche==<br />
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:<br />
<pre><br />
cat named.diff | egrep -e "^\*\*\*.*(sql|out|h|c|sgml|y|lex|Makefile)$" | replace "*** a/" ""| xargs cporig<br />
<br />
cat mnnotation.diff | egrep -o -e "^\*\*\*.*(sql|out|h|c|sgml|y|lex|Makefile)" | replace "*** " "" | xargs cporig<br />
</pre><br />
replace lze nahradit:<br />
<pre><br />
sed 's/reg//'<br />
</pre><br />
<br />
==Doplnění základních konstant do systému==<br />
Zdroj: http://www.simple-talk.com/sql/t-sql-programming/avoiding-the-eav-of-destruction/<br />
<pre><br />
CREATE VIEW Constants (pi, e, phi)<br />
AS <br />
SELECT X.*<br />
FROM (VALUES (CAST (3.141592654 AS FLOAT), <br />
CAST (2.718281828 AS FLOAT), <br />
CAST (1.6180339887 AS FLOAT))<br />
) AS X (pi, e, phi); <br />
</pre><br />
<br />
==Výpočet mediánu s využitím Window funkcí==<br />
O možnostech určení mediánu se [http://www.depesz.com/index.php/2009/07/13/calculating-median/ se rozepsal] Depesz. Použil řešení, které navrhl Andrew Gierth (RhodiumToad):<br />
<pre><br />
select avg(x)<br />
from ( select x, row_number() over (order by x),count(*) over () from zdrojdat ) s<br />
where row_number between floor((count::float8-1)/2+1) and ceil((count::float8-1)/2+1)<br />
</pre><br />
<br />
pro zajímavost přidávám řešení Joe Celka (SQL2005):<br />
<pre><br />
WITH SortedData (x, hi, lo)<br />
AS<br />
(SELECT x,<br />
ROW_NUMBER() OVER (ORDER BY x ASC),<br />
ROW_NUMBER() OVER (ORDER BY x DESC)<br />
FROM RawData)<br />
SELECT AVG(x * 1.0) AS median <br />
FROM SortedData <br />
WHERE hi IN (lo, lo+1, lo-1);<br />
</pre><br />
v úpravě pro PostgreSQL:<br />
<pre><br />
select avg(x) <br />
from (select x, <br />
row_number() over (order by x asc) as hi, <br />
row_number() over (order by x desc) as lo from foobar) s <br />
where hi in (lo-1,lo, lo+1);<br />
</pre><br />
Na webu jsem [http://sqlblog.com/blogs/adam_machanic/archive/2006/12/18/medians-row-numbers-and-performance.aspx narazil] ještě na Itzik Ben-Ganovu metodu,:<br />
<pre><br />
SELECT CustomerId,AVG(TotalDue)<br />
FROM (SELECT CustomerId, TotalDue,<br />
ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY TotalDue) AS RowNum,<br />
COUNT(*) OVER (PARTITION BY CustomerId) AS RowCnt<br />
FROM Sales.SalesOrderHeader<br />
) x<br />
WHERE RowNum IN ((RowCnt + 1) / 2, (RowCnt + 2) / 2)<br />
GROUP BY CustomerId<br />
ORDER BY CustomerId;<br />
</pre><br />
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).<br />
<br />
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:<br />
<pre><br />
SELECT avg(x)::float<br />
FROM (SELECT x, row_number() OVER (ORDER BY x asc) AS hi,<br />
count(*) OVER () + 1 - row_number() OVER (ORDER BY x) AS lo<br />
FROM foobar) qs<br />
WHERE hi IN (lo-1,lo,lo+1);<br />
<br />
</pre><br />
<br />
==Bezpečná konverze řetězce na číslo==<br />
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:<br />
<pre><br />
CREATE OR REPLACE FUNCTION read_int(varchar) <br />
RETURNS int AS $$ <br />
SELECT CASE WHEN $1 ~ e'^\\d+$' THEN $1::int END; <br />
$$ LANGUAGE SQL IMMUTABLE STRICT;<br />
</pre><br />
<br />
==Odstranění milisekund z timestampu==<br />
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:<br />
<pre><br />
postgres=# select current_timestamp;<br />
now <br />
------------------------------<br />
2009-05-23 20:42:21.57899+02<br />
(1 row)<br />
<br />
Time: 196,784 ms<br />
postgres=# select current_timestamp::timestamp(2);<br />
now <br />
------------------------<br />
2009-05-23 20:42:27.74<br />
(1 row)<br />
<br />
Time: 51,861 ms<br />
postgres=# select current_timestamp::timestamp(0);<br />
now <br />
---------------------<br />
2009-05-23 20:42:31<br />
(1 row)<br />
<br />
Time: 0,729 ms<br />
</pre><br />
<br />
==Zobrazení čísla ve vědeckém formátu==<br />
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):<br />
<pre><br />
create or replace function floating_format(double precision, int) <br />
returns varchar as $$<br />
sprintf("%.$_[1]e", $_[0])<br />
$$ language plperl;<br />
<br />
create or replace function floating_format(double precision) <br />
returns varchar as $$<br />
sprintf("%e", $_[0])<br />
$$ language plperl;<br />
<br />
postgres=# select floating_format(10.223);<br />
floating_format <br />
-----------------<br />
1.022300e+01<br />
(1 row)<br />
<br />
postgres=# select floating_format(10.223,2);<br />
floating_format <br />
-----------------<br />
1.02e+01<br />
(1 row)<br />
<br />
postgres=# select floating_format(10.223,4);<br />
floating_format <br />
-----------------<br />
1.0223e+01<br />
(1 row)<br />
</pre><br />
<br />
==Konfigurace terminálů==<br />
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:<br />
*emacs (.emacs)<br />
<pre><br />
(setq-default truncate-lines t)<br />
(ansi-color-for-comint-mode-on)<br />
(setq lazy-lock-defer-on-scrolling t)<br />
(setq inhibit-startup-echo-area-message t)<br />
(setq inhibit-startup-message t)<br />
(show-paren-mode t)<br />
(setq show-paren-style 'mixed)<br />
(fset 'yes-or-no-p 'y-or-n-p)<br />
(global-set-key "\M-g" 'goto-line)<br />
(setq sql-database "postgres") ;; nejčastěji používaná databáze<br />
(setq sql-postgres-options '("-P" "pager=off" "-P" "title= ")) ;; nepoužívat pager, nastavit falešný title<br />
(defun psql-init ()<br />
(sql-highlight-postgres-keywords))<br />
(add-hook 'sql-mode-hook 'psql-init)<br />
</pre><br />
*psql<br />
<pre><br />
export PAGER="less -RSX"<br />
psql ... -P pager=always<br />
</pre><br />
Vyplatí se nastavit i automatický rollback chybových SQL příkazů:<br />
<pre><br />
\set ON_ERROR_ROLLBACK interactive<br />
</pre><br />
<br />
==Pozor na chování operátorů IS NULL a IS NOT NULL pro složené typy==<br />
Č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.<br />
<pre><br />
CREATE OR REPLACE FUNCTION test_isnull()<br />
RETURNS TABLE (a int, b int, isnull bool, isnotnull bool) AS $$<br />
DECLARE r foo;<br />
BEGIN<br />
isnull := r IS NULL; isnotnull := r IS NOT NULL; RETURN NEXT;<br />
a := NULL; b := 10; r := ROW(a, b);<br />
isnull := r IS NULL; isnotnull := r IS NOT NULL; RETURN NEXT;<br />
a := 10; b := 10; r := ROW(a, b);<br />
isnull := r IS NULL; isnotnull := r IS NOT NULL; RETURN NEXT;<br />
RETURN;<br />
END;<br />
$$ LANGUAGE plpgsql;<br />
SELECT * FROM test_isnull();<br />
<br />
a | b | isnull | isnotnull<br />
----+----+--------+-----------<br />
| | t | f<br />
| 10 | f | f<br />
10 | 10 | f | t<br />
(3 rows)<br />
</pre><br />
<br />
==Pozor na implicitní přetypování v PL/pgSQL==<br />
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:<br />
<pre><br />
create or replace function test1() <br />
returns int as $$<br />
declare s int := 0; <br />
begin <br />
for i in 1..100000 loop s := 4e3; end loop; -- numeric <br />
return s; <br />
end; <br />
$$ language plpgsql immutable;<br />
<br />
create or replace function test2() <br />
returns int as $$<br />
declare s int := 0; <br />
begin <br />
for i in 1..100000 loop s := 4e3::int; end loop; -- int <br />
return s; <br />
end; <br />
$$ language plpgsql immutable;<br />
<br />
postgres=# select test1();<br />
test1 <br />
-------<br />
4000<br />
(1 row)<br />
<br />
Time: 176,623 ms<br />
<br />
postgres=# select test2();<br />
test2 <br />
-------<br />
4000<br />
(1 row)<br />
<br />
Time: 47,673 ms<br />
</pre><br />
nebo v konverzi návratové hodnoty:<br />
<pre><br />
create or replace function test1() returns int as $$begin return 4e1; end; $$ language plpgsql; <br />
create or replace function test2() returns int as $$begin return 4e1::int; end; $$ language plpgsql;<br />
<br />
postgres=# select count(test1()) from generate_series(1,100000);<br />
count <br />
--------<br />
100000<br />
(1 row)<br />
<br />
Time: 682,005 ms<br />
postgres=# select count(test2()) from generate_series(1,100000);<br />
count <br />
--------<br />
100000<br />
(1 row)<br />
<br />
Time: 528,099 ms<br />
</pre><br />
<br />
==Zabránění paralelnímu spuštění funkce==<br />
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.<br />
<pre><br />
create or replace function long_run() <br />
returns void as $$<br />
begin <br />
if exists(select procpid <br />
from pg_stat_activity <br />
where procpid <> pg_backend_pid() <br />
and current_query like '%long_run(%') <br />
then <br />
raise notice 'procedura jiz bezi'; <br />
return; <br />
end if; <br />
perform pg_sleep(10); <br />
end$$ <br />
language plpgsql volatile;<br />
CREATE FUNCTION<br />
</pre><br />
Další možností je použití tzv. advisory locks http://www.postgresql.org/docs/8.3/interactive/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS. <br />
<pre><br />
create or replace function long_run()<br />
returns void as $$<br />
declare foid oid:= 'long_run'::regproc::oid;<br />
begin<br />
if pg_try_advisory_lock(foid::bigint) then<br />
perform pg_sleep(10); <br />
pg_advisory_unlock(foid::bigint); <br />
else<br />
raise notice 'procedura jiz bezi';<br />
end if;<br />
return;<br />
end;<br />
$$ language plpgsql;</pre><br />
<br />
==Přesun tabulek z jednoho schématu do druhého==<br />
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:<br />
<pre><br />
create or replace function mvtable(src varchar, dest varchar, mask varchar)<br />
returns void as $$<br />
declare r record;<br />
begin<br />
for r in<br />
select *<br />
from information_schema.tables<br />
where table_schema = src and table_name like mask<br />
loop<br />
execute 'alter table ' || quote_ident(r.table_schema)<br />
|| '.' || quote_ident(r.table_name)<br />
|| ' set schema ' || quote_ident(dest);<br />
end loop;<br />
return;<br />
end;<br />
$$ language plpgsql;<br />
</pre><br />
<br />
==Převod čísla do libovolné číselné soustavy==<br />
CTE můžeme využít pro převod čísla do libovolné číselné soustavy:<br />
<pre><br />
postgres=# create function to_base(num int, base int = 10) <br />
returns varchar as $$<br />
with recursive z as (<br />
select $1 as a, null::int as b, 0 as i <br />
union all <br />
select a/$2, a % $2, i+1 <br />
from z <br />
where a > 0<br />
) <br />
select array_to_string(array(select substring('0123456789abcdef' from b + 1 for 1) <br />
from z <br />
where i > 0 <br />
order by i desc),'');<br />
$$ language sql immutable strict;<br />
<br />
postgres=# select to_base(255);<br />
to_base <br />
---------<br />
255<br />
(1 row)<br />
<br />
postgres=# select to_base(255,16);<br />
to_base <br />
---------<br />
ff<br />
(1 row)<br />
<br />
postgres=# select to_base(255,8);<br />
to_base <br />
---------<br />
377<br />
(1 row)<br />
<br />
postgres=# select to_base(255,2);<br />
to_base <br />
----------<br />
11111111<br />
(1 row)<br />
</pre><br />
Inverzní funkce:<br />
<pre><br />
create or replace function base(str varchar, base int = 10) <br />
returns int as $$<br />
select sum((position(substring($1 from i for 1) in '0123456789abcdef')- 1) * $2 ^ (char_length($1) - i))::int <br />
from generate_series(1,char_length($1)) g(i);<br />
$$ language sql immutable strict<br />
</pre><br />
<br />
==Funkce to_bin==<br />
V PostgreSQL chybí funkce, která by provedla konverzi čísla do binárního tvaru. Zde je jedna varianta:<br />
<pre><br />
create or replace function to_bin(int) <br />
returns varchar as $$<br />
declare r varchar; <br />
begin <br />
execute 'select ' || $1 || '::bit(' || case when $1 = 0 then 1 else trunc(log(2, $1)) + 1 end || ')' into r; <br />
return r; <br />
end;<br />
$$ language plpgsql immutable strict;<br />
<br />
create or replace function bin(varchar) <br />
returns varchar as $$<br />
declare r varchar; <br />
begin <br />
execute 'select b' || quote_literal($1) || '::int' into r; <br />
return r; <br />
end;<br />
$$ language plpgsql immutable strict;<br />
<br />
postgres=# select to_bin(5), bin('101');<br />
to_bin | bin <br />
--------+-----<br />
101 | 5<br />
(1 row)<br />
</pre><br />
<br />
------<br />
[[SQL Triky III.|Starších dvacet triků]] [[SQL Triky|Nejnovější triky]]</div>imported>Pavel