SQL Triky
SQL je ukázkou jazyka, kdy jeden problém můžeme vyřešit deseti různými způsoby. Různě efektivně. Zde uvedené příklady jsou ukázkou, jak efektivně využívat možností PostgreSQL.
Použití clipboardu v psql
Autor: Pavel Stěhule
psql jako terminálová aplikace clipboard nativně nepodporuje, nicméně díky možnosti přesměrování výstupu a speciálním terminálovým aplikacím pro práci se schránkou (wlclipboard, xclip, pbcopy), můžeme přesměrovat výsledek dotazu do clipboardu, a ten pak vložit například do LibreOffice:
select * from obce \g (format=csv) | cat > tmpfile.csv; libreoffice --view --calc tmpfile.csv "--infilter='Text - txt - csv (StarCalc)':44,34,0,1"; rm tmpfile.csv select * from pg_class limit 10 \g (format=csv tuples_only=off csv_fieldsep='\t') | wl-copy -t application/x-libreoffice-tsvc \set gclip '\\g (format=csv tuples_only=off csv_fieldsep=\'\\t\') | wl-copy -t application/x-libreoffice-tsvc' select * from pg_class limit 10 :gclip
Pole dokumentů typu jsonb
Autor: Pavel Stěhule
V Postgresu můžeme pracovat s poli libovolných datových typů, které nejsou samy poli. Pro přístup se používá zápis indexu v hranatých závorkách. Počínaje PostgreSQL 14 můžeme tento zápis použít i pro adresování hodnoty v jsonb dokumentu. Tady ovšem začíná schizofrénní situace - potřebujeme zápisem oddělit indexy pole, a indexy (pozicování) v dokumentu. To bohužel Postgres neumí (ani na úrovni parseru, ani interně - pole polí nejsou podporována). Můžeme si ale pomoci vložením kompozitní hodnoty. Tento trik se používá i v případě, že chceme pracovat s polem polí. Přímo to možné není, ale je možné pracovat s polem kompozitních hodnot, které obsahují pole:
CREATE TYPE jsonb_singleval AS (v jsonb);
DO $$
DECLARE a jsonb_singleval[];
BEGIN
a[1].v['x1'] = jsonb '100';
RAISE NOTICE 'first item %', a[1].v;
END;
$$;
NOTICE: first item {"x1": 100}
DO
Přenesení hodnoty z klienta na server
Autor: Daniel Verite
Proměnnou PGOPTION lze pouužít i pro zákaznické proměnné:
$ env PGOPTIONS="-c os.user=$USER" psql -d postgres psql (12.4 (Debian 12.4-1.pgdg90+1)) Type "help" for help. postgres=> show "os.user"; os.user --------- daniel (1 row)
případně s escapeovanými hodnotami:
$ env PGOPTIONS="-c os.user=user\\ is\\ $USER" psql -d postgres
psql (12.4 (Debian 12.4-1.pgdg90+1))
Type "help" for help.
postgres=> show "os.user";
os.user
----------------
user is daniel
(1 row)
Logování všech příkazů z aplikace
Autor: Pavel Stěhule
Nastavte systémovou proměnnou PGOPTIONS - vynucený login všech příkazů pg_dump(u):
PGOPTIONS="-c log_min_duration_statement=0" pg_dump -s postgres > /dev/null
Vacuuování pouze systémových tabulek
Autor: Pavel Stěhule
psql -At -c "select 'VACUUM ANALYZE pg_catalog.'||table_name from information_schema.tables where table_schema = 'pg_catalog' and table_type <> 'VIEW'" db_test \ | psql -S db_test
Nastavení položek DETAIL a HINT u vyjímky z PLPythonu
Autor: Pavel Stěhule PLPythonu neumožňuje nastavit všechny položky, které lze nastavit u vyjímek PostgreSQL. Nicméně položky DETAIL, HINT nastavit lze:
postgres=# do $$
x = plpy.SPIError('Nazdarek');
x.spidata = (100, "Some detail", "some hint", None, None);
raise x;
$$ language plpythonu;
ERROR: plpy.SPIError: Nazdarek
DETAIL: Some detail
HINT: some hint
CONTEXT: Traceback (most recent call last):
PL/Python anonymous code block, line 4, in <module>
raise x;
PL/Python anonymous code block
Aliasování psql
Autor: Pavel Stěhule
Na psql nemám rád jeho defaultní přihlašování k databázi stejného jména jako je účet uživatele. Pomocí funkce v bashi lze toto chování změnit:
function psql {
if [[ $# -eq 0 ]]; then
env psql postgres
else
env psql "$@"
fi
}
Poznámka - téhož lze dosáhnout nastavením systémové proměnné PGDATABASE.
Nastavení kódování výstupu z příkazové řádky
[pavel@localhost regress]$ PGCLIENTENCODING='latin2' psql -c "SHOW client_encoding" postgres client_encoding ----------------- LATIN2 (1 row)
Generování pěkně (pretty) formátovaného xml dokumentu
Autor: Pavel Stěhule (s využitím zdrojů z internetu - xslt šablona)
Extenze xml2 obsahuje podporu xslt transformací - funkci xslt_process. S touto funkcí lze jednoduše přeformátovat generovaný xml dokument:
create or replace function xml_pretty(xml) returns xml as $$ select xslt_process($1, '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> <xsl:strip-space elements="*" /> <xsl:output method="xml" indent="yes" /> <xsl:template match="node() | @*"> <xsl:copy> <xsl:apply-templates select="node() | @*" /> </xsl:copy> </xsl:template> </xsl:stylesheet>')::xml $$ language sql immutable strict;
Nezbytná ukázka:
postgres=# select xml_pretty('<root><a><b>some text</b></a></root>');
xml_pretty
----------------------
<root> +
<a> +
<b>some text</b>+
</a> +
</root> +
(1 row)
Logování komunikace mezi klientem a Postgresem
Autor: Pavel Stěhule
Expertem na monitoring protokolu PostgreSQL je pgShark.
unbuffer ./pgs-debug --host 172.30.34.72 -i lo --port 6432 | while read line; do echo `date +"%T.%3N"` $line; done | gzip > /mnt/ebs/pgsharklog.gz
Rychlé nahrazení indexu v omezení primárního klíče
Autor: Petr Novák
Pokud potřebujete na produkčním serveru rychle reindexovat indexy primárních klíčů větších tabulek, může Vám pomoci následující postup:
BEGIN; CREATE UNIQUE INDEX CONCURRENTLY tab_pkey_idx2 ON tab(id); ALTER TABLE tab DROP CONSTRAINT tab_pkey CASCADE, ADD CONSTRAINT tab_pkey PRIMARY KEY USING INDEX tab_pkey_idx2; ALTER TABLE second_tab ADD CONSTRAINT second_tab_fkey FOREIGN KEY (tab_id) REFERENCES tab(id) NOT VALID; COMMIT;
V dalším kroku lze zvalidovat omezení (constraint) s cizím klíčem.
Parametrizace shellového skriptu obsahující příkaz DO
Autor: Pavel Stěhule
Příkaz DO nepodporuje parametry - prostřednictvím psql proměnných je ale určitou parametrizaci provést:
bash-4.1$ cat test.sh
echo "
set myvars.msgcount TO :'msgcount';
DO \$\$
BEGIN
FOR i IN 1..current_setting('myvars.msgcount')::int LOOP
RAISE NOTICE 'Hello';
END LOOP;
END \$\$" | psql postgres -v msgcount=$1
bash-4.1$ sh test.sh 3
SET
Time: 0.341 ms
NOTICE: Hello
NOTICE: Hello
NOTICE: Hello
DO
Time: 2.415 ms
Test zda připojený server běží jako slave
[postgres@localhost:/opt/PostgreSQL/9.3~]$ psql -p 5433 -c "select pg_is_in_recovery();" pg_is_in_recovery ------------------- f (1 row)
Redukce časové řady s použitím analytických funkcí
Autor: Pavel Stěhule
Představme si, že máme následující knihu jízd, a chceme ji zredukovat tak, že pokud následují jízdy za stejným účelem, jak je nahradíme jednou jízdou odpovídající součtu nahrazených jízd. K tomu můžeme s výhodou použít analytické funkce:
postgres=# select * from jizdy order by kdy;
kdy │ proc │ kolik
═════════════════════╪══════════╪═══════
2014-01-01 08:00:00 │ soukrome │ 10
2014-01-01 09:00:00 │ firemne │ 1
2014-01-01 10:00:00 │ firemne │ 1
2014-01-01 10:10:00 │ firemne │ 15
2014-01-01 11:00:00 │ soukrome │ 10
2014-01-01 12:10:00 │ firemne │ 20
(6 rows)
postgres=# SELECT min(kdy) AS kdy, min(proc) AS proc, sum(kolik) AS kolik
FROM (SELECT kdy, proc, kolik, sum(start) OVER (ORDER BY kdy) AS grnum
FROM (SELECT *, CASE WHEN proc IS DISTINCT FROM lag(proc, 1) OVER (ORDER BY kdy) THEN 1 ELSE 0 END AS start
FROM jizdy) s) s2
GROUP BY grnum
ORDER BY kdy;
kdy │ proc │ kolik
═════════════════════╪══════════╪═══════
2014-01-01 08:00:00 │ soukrome │ 10
2014-01-01 09:00:00 │ firemne │ 17
2014-01-01 11:00:00 │ soukrome │ 10
2014-01-01 12:10:00 │ firemne │ 20
(4 rows)
Součet vektorů - (jednodimenzionálních polí)
Autor: Erwin Brandstetter
PostgreSQL nemá speciální funkce pro operace s poly jako vektory. Nicméně díky lateral joinu (PostgreSQL 9.3) není problém základní operace realizovat. Součet vektorů by mohl být implementován následujícím dotazem:
postgres=# SELECT * FROM xx;
a
---------
{1,2,3}
{3,4,5}
(2 rows)
postgres=# SELECT array_agg(sum)
FROM (SELECT sum(a[i])
FROM xx,
generate_series(1,3) g(i)
GROUP BY i
ORDER BY i) x;
array_agg
-----------
{4,6,8}
(1 row)
Případne s použitím klauzule WITH ORDINALITY (PostgreSQL 9.4):
postgres=# SELECT array(SELECT sum(el)
FROM xx,
unnest(a) WITH ORDINALITY x(el, r)
GROUP BY r
ORDER BY r);
array
---------
{4,6,8}
(1 row)
Určení velikosti zpoždění (posunu) mezi replikou a replikovaným serverem (slaveem a masterem)
Autor: Josh Berkus http://www.databasesoup.com/2014/04/simplifying-replication-position.html#comment-form
Základem je použití funkce pg_xlog_location_diff(loc1, loc2). loc obsahuje descriptor (stav) transakčního logu: '0/0000000' - absolutní začátek, pg_current_xlog_location(), pg_last_xlog_receive_location() nebo pg_last_xlog_replay_location():
-- stáří transakčního logu SELECT pg_xlog_location_diff ( pg_current_xlog_location(), '0/0000000' ); -- zpoždění v MB v aplikaci transakčního logu - kvuli snížení rizika replikačních kolizí může být replika oddálit aplikaci transakčního logu SELECT pg_xlog_location_diff ( pg_last_xlog_receive_location(), pg_last_xlog_replay_location() ) / 1000000; -- vrátí true, pokud všechny přijaté segmenty transakčního logu jsou aplikovány SELECT pg_xlog_location_diff ( pg_last_xlog_receive_location(), pg_last_xlog_replay_location() ) = 0;
Rozbalení pole s přidáním indexu
V některých případech by se hodilo mít funkci, která rozbalí pole, ale k vlastní hodnotě přidá ještě pořadové číslo (index), který je možný použít pro řazení nebo spojování tabulek. Možností jak to udělat je několik:
Autor: Pavel Stěhule (PostgreSQL 9.1)
CREATE OR REPLACE FUNCTION unnest_rownum(anyarray)
RETURNS TABLE (id int, element anyelement) AS $$
BEGIN
id := 1;
FOREACH element IN array $1
LOOP
RETURN NEXT;
id := id + 1;
END LOOP;
RETURN;
END
$$ LANGUAGE plpgsql;
postgres=# select * from unnest_rownum(ARRAY['A','B','C']);
id | element
----+---------
1 | A
2 | B
3 | C
(3 rows)
Autor: Tom Lane (PostgreSQL 9.3)
SELECT i, arraycol[i]
FROM tab,
LATERAL generate_subscripts(arraycol, 1) as i;
V 9.4 je možné použít klauzuli WITH ORDINALITY:
postgres=# SELECT * FROM unnest(ARRAY['A','D','C']) WITH ORDINALITY; unnest | ordinality --------+------------ A | 1 D | 2 C | 3 (3 rows)
Omezení na pouze jednu hodnotu NULL ve sloupci
Omezení UNIQUE zajišťuje unikátní hodnoty v daném sloupci (sloupcích), nad kterým je deklarováno. NULL se ovšem nebere jako hodnota, která by byla porovnatelná s jakoukoliv jinou hodnotou a proto omezení UNIQUE vůbec neřeší hodnoty NULL. Což se nám někdy nehodí. Jedinečnost hodnoty NULL lze jednoduše zajistit podmíněným funkcionálním indexem (podmíněným - aby byl index malý, funkcionální - NULL by bylo ignorováno:
postgres=# CREATE TABLE omega(a int UNIQUE); CREATE TABLE postgres=# INSERT INTO omega VALUES(NULL); INSERT 0 1 postgres=# INSERT INTO omega VALUES(NULL); INSERT 0 1 postgres=# TRUNCATE omega; TRUNCATE TABLE postgres=# CREATE UNIQUE INDEX ON omega ((1)) WHERE a IS NULL; CREATE INDEX postgres=# INSERT INTO omega VALUES(NULL); INSERT 0 1 postgres=# INSERT INTO omega VALUES(NULL); ERROR: duplicate key value violates unique constraint "omega_expr_idx" DETAIL: Key ((1))=(1) already exists. postgres=# UPDATE omega SET a = 10; UPDATE 1 postgres=# INSERT INTO omega VALUES(NULL); INSERT 0 1 Time: 7.601 ms
Konverze bytea do blobu
Autor: unknown
Uloží bytea řetězec do lo objektu.
CREATE OR REPLACE FUNCTION make_lo(bytea)
RETURNS oid AS $$
DECLARE
loid oid;
fd integer;
bytes integer;
BEGIN
loid := lo_creat(-1);
fd := lo_open(loid, 131072);
bytes := lowrite(fd, $1);
IF (bytes != LENGTH($1)) THEN
RAISE EXCEPTION 'Not all data copied to blob';
END IF;
PERFORM lo_close(fd);
RETURN loid;
END;
$$ LANGUAGE plpgsql STRICT;
Pouze jeden sloupec z vybrané skupiny sloupců může být NULL
Autor: Daniel Vérité
Jak jednoduše zjistit kolik hodnot je NULL?
CREATE OR REPLACE FUNCTION public.null_count(VARIADIC anyarray) RETURNS integer LANGUAGE sql AS $function$ SELECT sum(CASE WHEN v IS NULL THEN 1 ELSE 0 END)::int FROM unnest($1) g(v) $function$
pak použití:
CREATE TABLE xxx( a int, b int, c int, CHECK (null_count(a,b,c) <= 1))
Nastavení application_name pro jednoúčelové skripty využívající psql
Autor: Erik Rijkers
Application_name je atribut připojení, který můžeme připojit k záznamům v logu a podle něj identifikovat zdroj SQL příkazu. Možností je více
[pavel@localhost ~]$ PGAPPNAME=splunk psql -c "select application_name from pg_stat_activity where pid = pg_backend_pid();" postgres application_name ------------------ splunk (1 row) [pavel@localhost ~]$ psql -c "select application_name from pg_stat_activity where pid = pg_backend_pid();" "dbname=postgres application_name=splunk" application_name ------------------ splunk (1 row) [pavel@localhost ~]$ psql -c "select application_name from pg_stat_activity where pid = pg_backend_pid();" postgresql://localhost/postgres?application_name=splunk application_name ------------------ splunk (1 row)
Získání N unikátních nejpodobnějších hodnot s využitím KNNsearch a modulu pg_tgrm
Autor: Tom Lane
SELECT DISTINCT ON (nazobce <-> 'Benešov') nazobce, nazobce<->'Benešov'
FROM obce
ORDER BY nazobce <-> 'Benešov'
LIMIT 10;
nazobce | ?column?
-----------------------+----------
Benešov | 0
Benešovice | 0.416667
Dolní Benešov | 0.428571
Benešov u Semil | 0.5
Benešov nad Černou | 0.578947
Benešov nad Ploučnicí | 0.636364
Benecko | 0.666667
Benetice | 0.692308
Bečov | 0.727273
Bezkov | 0.75
(10 rows)
postgres=# EXPLAIN SELECT DISTINCT ON (nazobce <-> 'Benešov') nazobce, nazobce<->'Benešov'
FROM obce
ORDER BY nazobce <-> 'Benešov'
LIMIT 10;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Limit (cost=0.00..2.76 rows=10 width=10)
-> Unique (cost=0.00..1474.75 rows=5341 width=10)
-> Index Scan using obce_nazobce_idx on obce (cost=0.00..1433.14 rows=16644 width=10)
Order By: (nazobce <-> 'Benešov'::text)
(4 rows)
Využití vlastního přetypování při migraci Oracle do PostgreSQL
Autor: Pavel Stěhule Oracle nepodporuje datový typ boolean, který se obvykle nahrazuje typem varchar(1) nebo number(1). Při změně JDBC driveru dochází k problémům, neboť do varcharu o šířce jednoho znaku není možné uložit hodnoty true nebo false. V rámci dané databáze lze nahradit výchozí konverzi boolean->varchar vlastní konverzní funkcí (UPDATE musí být proveden pod uživatelem s právy superusera) a tím problém vyřešit bez nutnosti změny tabulek - jakkoliv změna typu v tabulce je čistší řešení:
CREATE OR REPLACE FUNCTION public.boolcast_varchar(boolean)
RETURNS character varying
LANGUAGE sql
AS $$
SELECT CASE WHEN $1 THEN 't' ELSE 'f' END
$$
UPDATE pg_cast SET castfunc='boolcast_varchar'::regproc,
castcontext='i'
WHERE castsource=16 and casttarget=1043;
CREATE TABLE test(a varchar(1));
INSERT INTO test VALUES(true);
Generování JSONu
Autor: Merlin
select
row_to_json(t1)
from (
select
'joe' as username,
(select project from (values(1, 'prj1')) as project(project_id,
project_name)) as project
) t1;
Trimování hodnot v sloupci s typem pole
Autor: a_horse_with_no_name
postgres=# select * from g;
a
--------------------------------
{" ssss ssss","sss ssss "}
{" ssss ssssaaaa "}
(2 rows)
postgres=# select row_number() over(), unnest(a) e
from g;
row_number | e
------------+-----------------
1 | ssss ssss
1 | sss ssss
2 | ssss ssssaaaa
(3 rows)
postgres=# select array_agg(trim(x.e))
from (select row_number() over() rn, unnest(a) e
from g) x
group by rn;
array_agg
---------------------------
{"ssss ssss","sss ssss"}
{"ssss ssssaaaa"}
(2 rows)
Tento trik by bylo možné použít i jinde - místo chybějící klauzule LATERAL - jinak tenhle trik je implementačně závislý - záleží na pořádí vyhodnocování SRF a analytických funkcí. Počínaje 9.3 používejte raději klauzuli LATERAL.
Jednuduché zpracování výsledku dotazu v Bashi
Autor: Pavel Stěhule
I v Bashi lze relativně snadno zpracovat výsledek SQL dotazu:
pavel ~ $ psql postgres -A -t --field-separator=" " \ > -c "copy (select 1,'Ahoj Svete', i > from generate_series(1,3) g(i)) > to stdout delimiter ' '" | \ > while read var1 var2 var3; > do > echo "a=$var1,b=$var2,c=$var3 "; > done a=1,b=Ahoj Svete,c=1 a=1,b=Ahoj Svete,c=2 a=1,b=Ahoj Svete,c=3
Podobné řešení navrh Hubert Depesz Lubaczewski:
psql -qAtX -c "copy (select * from t) to stdout" | while IFS=$'\t' read -r a b c; do echo -e "a=[$a] b=[$b] c=[$c]"; done
Přilepení komentáře k SQL příkazu
Autor: Thomas Kellerer
V některých případech bychom potřebovali okomentovat SQL příkaz takovým způsobem, abychom v logu PostgreSQL uviděli SQL příkaz včetně komentáře. Řešením je vložený komentář.
SELECT /* my comments, that I would to see in PostgreSQL log */
a, b, c
FROM mytab;
Odhlášení všech přihlášených uživatelů
Autor: Pavel Stěhule
Jako uživatel postgres nebo jiný superuser proveďte dotaz:
postgres=# select * from pg_stat_activity ;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start |
-------+----------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+
12894 | postgres | 21091 | 10 | postgres | psql | | | -1 | 2012-03-06 09:16:02.466983+01 |
12894 | postgres | 21103 | 16384 | pavel | psql | | | -1 | 2012-03-06 09:17:02.827352+01 |
12894 | postgres | 21104 | 16384 | pavel | psql | | | -1 | 2012-03-06 09:17:12.176979+01 |
(3 rows)
postgres=# select pg_terminate_backend(pid)
from pg_stat_activity
where pid <> pg_backend_pid() ;
pg_terminate_backend
----------------------
t
t
(2 rows)
postgres=# select * from pg_stat_activity ;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start |
-------+----------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+
12894 | postgres | 21091 | 10 | postgres | psql | | | -1 | 2012-03-06 09:16:02.466983+01 |
(1 row)
Dohledání děr v časové posloupnosti s využitím window funkcí
Zdroj: Magnus Hagander http://blog.hagander.net/archives/203-Finding-gaps-in-partitioned-sequences.html
Funkci lag je možné použít na libovolnou seřazenou posloupnost:
SELECT * FROM (
SELECT
gropid,
year,
month,
seq,
seq-lag(seq,1) OVER (PARTITION BY groupid, year, month ORDER BY seq) AS gap FROM mytable
) AS t
WHERE NOT (t.gap=1)
ORDER BY groupid, year, month, seq
Konverze BLOBu do Bytea
Zdroj: http://snipplr.com/view/62576/convert-oid-to-bytea/
CREATE OR REPLACE FUNCTION merge_oid(val oid)
RETURNS bytea AS $$
DECLARE
merged bytea;
arr bytea;
BEGIN
FOR arr IN SELECT DATA
FROM pg_largeobject
WHERE loid = val
ORDER BY pageno
LOOP
IF merged IS NULL THEN
merged := arr;
ELSE
merged := merged || arr;
END IF;
END LOOP;
RETURN merged;
END
$$ LANGUAGE plpgsql;
Získání prvního nezamčeného záznamu z tabulky
Při implementaci fronty, která má více konzumentů se muže hodit následující trik:
postgres=# select * from queue; id | processed | cmd ----+-----------+------- 1 | f | task1 2 | f | task2 3 | f | task2 4 | f | task3 (4 rows)
Použití:
-- konzument1 postgres=# begin; BEGIN postgres=# select * from queue where not processed and pg_try_advisory_xact_lock(id) for update limit 1; id | processed | cmd ----+-----------+------- 1 | f | task1 (1 row) postgres=# update queue set processed = true where id = 1; UPDATE 1 postgres=# commit; COMMIT -- konzument2 postgres=# begin; BEGIN postgres=# select * from queue where not processed and pg_try_advisory_xact_lock(id) for update limit 1; id | processed | cmd ----+-----------+------- 2 | f | task2 (1 row) postgres=# update queue set processed = true where id = 2; UPDATE 1 postgres=# commit; COMMIT
Pomocí zámků lze realizovat neblokující se paralelní zpracování fronty.
Funkce pro dokódování url kódování
Autor: Marti Raudsepp
CREATE OR REPLACE FUNCTION url_decode(input text) RETURNS text
LANGUAGE plpgsql IMMUTABLE STRICT AS $$
DECLARE
bin bytea = '';
byte text;
BEGIN
FOR byte IN (select (regexp_matches(input, '(%..|.)', 'g'))[1]) LOOP
IF length(byte) = 3 THEN
bin = bin || decode(substring(byte, 2, 2), 'hex');
ELSE
bin = bin || byte::bytea;
END IF;
END LOOP;
RETURN convert_from(bin, 'utf8');
END
$$;
Použití:
ohs=# select url_decode('Hell%C3%B6%20World%21');
url_decode
──────────────
Hellö World!
(1 row)
Konfigurace emacsu pro zvýraznění syntaxe při editaci PL/pgSQL funkce aktivovanou z konzole
Autor: Peter Eisentraut
do souboru .profile
PSQL_EDITOR=emacs; export PSQL_EDITOR.
do souboru .emacs
(add-to-list 'auto-mode-alist
'("/psql.edit.[0-9]+\\'" . sql-mode))
Vyhledávání prefixu pomocí fulltextu s českou konfigurací
Tento blok vlastně není trik, ale varování - při vyhledávání prefixu jsem byl překvapen chováním:
postgres=# select to_tsvector('cs', 'Stěhule') @@ to_tsquery('cs','Stě:*') ;
?column?
──────────
f
(1 row)
Očekával jsem true - tj. "Stě" je prefixem "Stěhule". Po chvíli zkoumání a zjišťování, jestli se nejedná o chybu PostgreSQL, jsem zjistil, že se o chybu PostgreSQL nejedná - je to chování TSearch, které se snaží použít stejnou transformaci i na prefixy. V tomto případě se "Stě" transformuje na "sto", a zákonitě se nic nenajde. Pro vyhledávání prefixu proto doporučuji nepoužívat konfiguraci cs, ale konfiguraci simple:
postgres=# select to_tsvector('simple', 'Stěhule') @@ to_tsquery('simple','Stě:*') ;
?column?
──────────
t
(1 row)
Odstranění duplicit s použitím window funkcí
Window funkce nám umožňují velice efektivně odstranit duplicitní funkce:
DELETE FROM tab
WHERE id IN (SELECT id
FROM (SELECT row_number() OVER (PARTITION BY duplicitni_sloupec), id
FROM tab) x
WHERE x.row_number > 1);
Optimalizovaná verze pro PostgreSQL s použitím ctid:
DELETE FROM tab
WHERE (ctid, tableoid) = ANY(ARRAY(SELECT (ctid, tableoid)
FROM (SELECT row_number() OVER (PARTITION BY duplicitni_sloupec), ctid
FROM tab) x
WHERE x.row_number > 1));
Převod bytea do typu text
Pokud potřebujeme obsah typu bytea převést na odpovídající textovou hodnotu (za předpokladu, že obsah bytea odpovídá přímo kódům znaků), lze použít funkci:
CREATE OR REPLACE FUNCTION bytea_to_text(bytea)
RETURNS text AS $$
SELECT convert_from($1, current_setting('server_encoding'))
$$ LANGUAGE sql;
xxx=# SELECT bytea_to_text('žluťoučký kůň se napil žluté vody'::bytea);
bytea_to_text
───────────────────────────────────
žluťoučký kůň se napil žluté vody
(1 row)
Dynamické modifikování položky záznamu
V PL/pgSQL není možné (jednoduše) modifikovat položku kompozitního typu, v případě, že položka je určená až v době běhu funkce. K dispozici je několik řešení v C, které jsou velice rychlé. Ty ovšem v některých případech nemusí být k dispozici, případně je nelze použít. V takovém případě lze použít následující funkci:
CREATE OR REPLACE FUNCTION x.setfield2(anyelement, text, text)
RETURNS anyelement
LANGUAGE plpgsql
AS $function$
DECLARE
_name text;
_values text[];
_value text;
_attnum int;
BEGIN
FOR _name, _attnum
IN SELECT a.attname, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (SELECT typrelid
FROM pg_type
WHERE oid = pg_typeof($1)::oid)
LOOP
IF _name = $2 THEN
_value := $3;
ELSE
EXECUTE 'SELECT (($1).' || quote_ident(_name) || ')::text' INTO _value USING $1;
END IF;
_values[_attnum] := COALESCE('"' || replace(replace(_value, '"', '""'), '''', '''''') || '"', '');
END LOOP;
EXECUTE 'SELECT (' || pg_typeof($1)::text || '''(' || array_to_string(_values,',') || ')'').*' INTO $1;
RETURN $1;
END;
$function$;
postgres=# select setfield2(mypoint '(10,)', 'b', '33');
setfield2
───────────
(10,33)
(1 row)
Time: 9,480 ms
Autor: Pavel Stěhule
Erwin Brandstetter prišel ještě s lepším řešením, které je jednodušší a rychlejší:
-- Erwin 1
CREATE OR REPLACE FUNCTION x.setfield3(anyelement, text, text)
RETURNS anyelement
AS $body$
DECLARE
_list text;
BEGIN
_list := (
SELECT string_agg(x.fld, ',')
FROM (
SELECT CASE WHEN a.attname = $2
THEN quote_literal($3)
ELSE quote_ident(a.attname)
END AS fld
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (SELECT typrelid
FROM pg_type
WHERE oid = pg_typeof($1)::oid)
ORDER BY a.attnum
) x
);
EXECUTE '
SELECT ' || _list || '
FROM (SELECT $1.*) x'
USING $1
INTO $1;
RETURN $1;
END;
$body$ LANGUAGE plpgsql;
a tuto funkci ještě dokázal zrychlit:
CREATE FUNCTION f_setfield(INOUT _comp_val anyelement, _field text, _val text)
RETURNS anyelement AS
$func$
BEGIN
EXECUTE 'SELECT ' || array_to_string(ARRAY(
SELECT CASE WHEN attname = _field
THEN '$2'
ELSE '($1).' || quote_ident(attname)
END AS fld
FROM pg_catalog.pg_attribute
WHERE attrelid = pg_typeof(_comp_val)::text::regclass
AND attnum > 0
AND attisdropped = FALSE
ORDER BY attnum
), ',')
USING _comp_val, _val
INTO _comp_val;
END
$func$ LANGUAGE plpgsql;
Snadné určení věku z datumu narození
Pomocí funkce age lze snadno určit věk z dne narození:
postgres=# SELECT CURRENT_DATE;
date
------------
2011-09-20
(1 row)
postgres=# SELECT EXTRACT(YEAR FROM age('1972-08-20'::date));
date_part
-----------
39
(1 row)
Identifikace master/slave serveru
K získání stavu serveru lze použít následující funkci:
SELECT pg_is_in_recovery();
Autor: Simon Riggs
Určení md5 obsahu tabulky
Pro porovnání dvou hodnot lze použít funkci md5. V psql lze pro tento případ použít systémovou proměnnou PAGER:
[robert@client-168] export PAGER=md5 -=[11:40:25 Thu Sep 01]=---=[ pagila-0.10.1 ]=- [robert@client-168] psql -hlocalhost -dpagila psql (9.0.4, server 9.1beta3) pagila=# select * from actor order by actor_id; f381ebdefe0aada9c0bc14e657962c1f
Autor: Robert Treat
LIKE vůči seznamu hodnot
PostgreSQL (stejně tak SQL) neumožňuje zadat operátoru (I)LIKE více než jeden vzorový řetězec. Takže
-- this is not valid
WHERE sloupec LIKE ('%neco%','%necojineho%')
není syntakticky správný. Požadovaný test lze zapsat pomocí polí (a nebo vyřešit regulárním výrazem):
-- this is valid WHERE slopec ~~* any(array['%neco%', '%necojineho']));
Autor: Tom Lane
Vynucení použití unikátního názvu kurzoru v PL/pgSQL
Za normálních okolností vede použití PL/pgSQL kurzoru k vytvoření SQL kurzoru se stejným názvem, jako má PL/pgSQL kurzor. Toto chování v určitých případech nemusí být žádoucí. Řešením je přiřazení hodnoty NULL PL/pgSQL kurzoru. V takovém případě je otevřen SQL kurzor s unikátním názvem:
DECLARE mycursor CURSOR FOR SELECT * FROM int4_tbl WHERE f1 > id; newid INTEGER; out TEXT; BEGIN out := id::text || ' '; mycursor := null; OPEN mycursor; ..
Iterace napříč proměnnou typu RECORD v triggeru
Pro tuto funkci je vyžadována min. verze 8.4 - autor Tom Lane
CREATE OR REPLACE FUNCTION dynamic_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
ri RECORD;
t TEXT;
BEGIN
RAISE NOTICE E'\n Operation: %\n Schema: %\n Table: %',
TG_OP,
TG_TABLE_SCHEMA,
TG_TABLE_NAME;
FOR ri IN
SELECT ordinal_position, column_name, data_type
FROM information_schema.columns
WHERE
table_schema = quote_ident(TG_TABLE_SCHEMA)
AND table_name = quote_ident(TG_TABLE_NAME)
ORDER BY ordinal_position
LOOP
EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO t USING NEW;
RAISE NOTICE E'Column\n number: %\n name: %\n type: %\n value: %.',
ri.ordinal_position,
ri.column_name,
ri.data_type,
t;
END LOOP;
RETURN NEW;
END; $$;
pozn. Tato technika vyžaduje 1 dynamický SQL dotaz na každou položku záznamu - mnohem efektivnější je použití funkcí z knihovny PL Toolbox nebo contrib modulu Hstore
Zobrazení zdrojového kódu funkce
Pokud znáte oid funkce, je velice jednoduché získat její generující DDL příkaz:
postgres=# select oid from pg_proc where proname = 'fu';
oid
-------
16389
(1 row)
postgres=# select pg_get_functiondef(16389);
pg_get_functiondef
---------------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.fu(anyelement)
RETURNS anyelement
LANGUAGE plpgsql
AS $function$
begin
raise notice '%', $1;
return $1;
end; $function$
(1 row)
Seznam souborů v rpm balíčku
Sice tento bod nesouvisí s PostgreSQL, nicméně věřím, že je přínosem pro každého hackera:
[pavel@pavel-stehule ~]$ rpm --query --filesbypkg perl-Net-SCP perl-Net-SCP /usr/share/doc/perl-Net-SCP-0.08 perl-Net-SCP /usr/share/doc/perl-Net-SCP-0.08/Changes perl-Net-SCP /usr/share/doc/perl-Net-SCP-0.08/README perl-Net-SCP /usr/share/man/man3/Net::SCP.3pm.gz perl-Net-SCP /usr/share/perl5/Net perl-Net-SCP /usr/share/perl5/Net/SCP.pm rpm -qa zobrazi vsechny balicky rpm -qf soubor dohleda balicek obsahujici soubor
Operace UPSERT pomocí triggeru
Zdroj: http://database-programmer.blogspot.com/2009/06/approaches-to-upsert.html PostgreSQL nepodporuje příkaz MERGE, který spojuje funkce příkazů INSERT a UPDATE. Existuje několik řešení - představované řešení je založené na použití BEFORE TRIGGERU. Uvnitř triggeru si zjistíme, zda-li se jedná o existující řádek, a pokud ano, tak provedeme UPDATE a vrátíme NULL, čímž se zabrání zápisu, jinak vrátíme původní hodnotu, která se zapíše:
CREATE OR REPLACE FUNCTION upsert_body()
RETURNS TRIGGER AS $$
BEGIN
UPDATE tab SET val = new.val WHERE key = new.key;
IF NOT FOUND THEN
RETURN new;
ELSE
RETURN NULL;
END IF;
END;
$$ LANGUAGE plpgsql;
Zaokrouhlení času na nejblížší pětiminutovku
Autor: Pavel Stěhule
CREATE OR REPLACE FUNCTION round_time(TIMESTAMP WITH TIME ZONE)
RETURNS TIMESTAMP WITH TIME ZONE AS $$
SELECT date_trunc('hour', $1) + INTERVAL '5 min' * round(date_part('minute', $1) / 5.0)
$$ LANGUAGE SQL;
postgres=# select now(), round_time(now());
now | round_time
-------------------------------+------------------------
2010-09-19 09:59:17.053782+02 | 2010-09-19 10:00:00+02
(1 row)
Funkce to_string a to_array
Oblíbené funkce array_to_string a string_to_array nepodporují pole obsahující NULL, resp. hodnotu NULL tiše ignorují. Nové funkce to_string a to_array jsou obdobou výše uvedených funkcí, přičemž umožňují nahradit NULL definovaným řetězcem / definovaný řetězec hodnotou NULL:
CREATE OR REPLACE FUNCTION to_string(anyarray, sep text, nullstr text DEFAULT '')
RETURNS text AS $$
SELECT array_to_string(ARRAY(SELECT coalesce(v::text, $3)
FROM unnest($1) g(v)),
$2)
$$ LANGUAGE sql;
postgres=# select to_string(array[1,2,3,4,null,5],',');
to_string
------------
1,2,3,4,,5
(1 row)
postgres=# select to_string(array[1,2,3,4,null,5],',','<NULL>');
to_string
------------------
1,2,3,4,<NULL>,5
(1 row)
CREATE OR REPLACE FUNCTION to_array(text, sep text, nullstr text DEFAULT '')
RETURNS text[] AS $$
SELECT ARRAY(SELECT CASE
WHEN v = $3 THEN NULL::text
ELSE v END
FROM unnest(string_to_array($1,$2)) g(v))
$$ LANGUAGE sql;
postgres=# select to_array('1,2,3,4,,5',',');
to_array
------------------
{1,2,3,4,NULL,5}
(1 row)
postgres=# select to_array('1,2,3,4,<NULL>,5',',','<NULL>');
to_array
------------------
{1,2,3,4,NULL,5}
(1 row)
Tichá instalace na MS Windows (neověřeno)
zdroj: http://www.dbforums.com/postgresql/1651789-postgres-silent-installation.html
REM create Windows user net user postgres windows_password /ADD REM create a PW file for the Postgres super user echo my_secret_postgres_password > password.txt unzip postgresql-8.4.2-1-windows-binaries.zip -d mypostgresinstalldir REM run initdb mypostgresinstalldir\pgsql\bin\initdb -D mydatadir -U postgres --pwfile=password.txt -E UTF8 -A md5 REM register the Postgres windows service pg_ctl register -N PostgreSQL -U postgres -P windows_password -D mydatadir
Případně další varianta (zdroj:http://code.google.com/p/mysde/source/browse/trunk/mysde/pgsql/install.bat?r=7):
@echo off rem net user postgres pgsql8@tt /ADD /EXPIRES:NEVER /PASSWORDCHG:NO rem net localgroup users postgres /delete rem runas /noprofile /env /user:postgres "initdb -D data -E UTF8 --no-locale -A md5 -U root --pwfile=rootpass.txt" call ../setEnv.bat initdb -D data -E UTF8 --no-locale -A md5 -U root --pwfile=rootpass.txt echo on
startup script:
@echo off rem runas /noprofile /env /user:postgres "pg_ctl start -w -D data -l pgsql.log" call ../setEnv.bat pg_ctl start -w -D %WORK_HOME%\pgsql\data -l pgsql.log echo on
stop script
@echo off call ../setEnv.bat pg_ctl -D data -l pgsql.log stop -m smart echo on
Vynucení vytištění znaků v konverzní funkci to_char
V určitých případech můžeme požadovat zobrazení znaků, které se shodují s maskami specifikující formát. Text v uvozovkách se neinterpretuje, a tudíž se zobrazí, tak jak je:
postgres=# select to_char(current_timestamp,'YYYY-MM-DDTHH24:MI:SS');
to_char
-----------------------
2010-02-07THH24:38:10
(1 row)
postgres=# select to_char(current_timestamp,'YYYY-MM-DD"T"HH24:MI:SS');
to_char
---------------------
2010-02-07T07:38:22
(1 row)
Autor: Milen A. Radev
Ochrana před nechtěnou konverzí char(n) na text
Prakticky všechny funkce pro operace s řetězci mají parametry typu text. PostgreSQL zajišťuje automatickou konverzi z char(n) na varchar. V některých případech toto chování přináší problémy - zejména ve starších aplikacích, které datový typ char(n) používali (jasné doporučení - typu char(n) se vyhnout obloukem). Jedním z příkladů takového problému je portace starších aplikací a porovnávání char(n) hodnot n znaků:
SELECT ... WHERE substr(sloupec,1,4) = 'ab ';
Tato konstrukce v PostgreSQL není funkční - substr má jako parametr text a vrací text, čímž pádem neplatí 'ab' = 'ab ' (což by platilo u typu char(n)). Naštěstí PostgreSQL dovoluje přetížení funkcí, takže si můžeme zaregistrovat vlastní funkci substr, kde nedojde k přímé konverzi z char(n) do textu:
create or replace function substr(character, int, int) returns character as $$ select substr($1::cstring::text,$2,$3) $$ language sql;
O něco efektivnější je návrh Toma Lane:
create function substr(char,int,int) returns char strict immutable language internal as 'text_substr' ;
Což je o něco špinavější nicméně 100% funkční trik.
postgres=# create table f(a character(5));
CREATE TABLE
postgres=# insert into f values('a'),('ab'),('abc');
INSERT 0 3
postgres=# select * from f;
a
-------
a
ab
abc
(3 rows)
postgres=# select * from f where substr(a,1,3) = 'a ';
a
-------
a
(1 row)
postgres=# select * from f where substr(a,1,3) = 'ab ';
a
-------
ab
(1 row)
Vytvoření domény časová zóna
Další hezký trik zaslal David E. Wheleer. PostgreSQL nepodporuje žádný datový typ pro uložení časové zóny a neobsahuje ani žádnou funkci, kterou bychom mohli použít pro kontrolu, zda-li zadaný text je časovou zónou či není. Nicméně lze použít operátor AT:
CREATE OR REPLACE FUNCTION is_timezone( tz TEXT ) RETURNS BOOLEAN as $$ BEGIN PERFORM now() AT TIME ZONE tz; RETURN TRUE; EXCEPTION WHEN invalid_parameter_value THEN RETURN FALSE; END; $$ language plpgsql STABLE; CREATE DOMAIN timezone AS CITEXT CHECK ( is_timezone( value ) );
Zadání hesla z příkazové řádky pro psql
psql nemá žádný přepínač pro zadání hesla. Proto se uživatelé relativně často ptají, jak neinteraktivně zadat heslo. Jednou z možností je nastavit systémovou proměnnou PGPASSWORD:
postgres=# CREATE USER tom;
CREATE ROLE
postgres=# ALTER USER tom PASSWORD 'tiger';
ALTER ROLE
...
postgres=> \q
[pavel@nemesis ~]$ PGPASSWORD=tiger psql postgres -U tom
Welcome to psql 8.3.8, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
postgres=> ...
Výhodou tohoto přístupu, a také smyslem, je skrytí, resp. neuvedení hesla ve výpisu procesů.
Urychlení výpisu seznamu tabulek
Pokud Vaše db obsahuje velký počet tabulek - tisíce až desetitisíce, oceníte následující trik pro verze 8.3 a 8.4:
alter function pg_table_is_visible(oid) cost 10;
Autorem je Tom Lane a v 8.5 bude vyšší cena u funkce pg_table_is_visible nastavena jako výchozí.
Pole polí
Přestože PostgreSQL podporuje n-rozměrné pole, nepodporuje pole polí, a tudíž nepodporuje kumulační agregační funkce nad poli libovolných typů - výsledkem by bylo pole polí, a tato struktura není podporována. Tuto chybějící funkci lze obejít zapouzdřením vstupních polí do kompozitního typu (pole kompozitních hodnot jsou podporovány):
postgres=# create type intarr as (f int[]);
CREATE TYPE
postgres=# select * from t;
a
─────────────
{10,20,30}
{1,2,3,4,5}
(2 rows)
postgres=# select array_agg(a) from t;
ERROR: could not find array type for data type integer[]
postgres=# select array_agg(distinct row(a)::intarr) from t;
array_agg
──────────────────────────────────────────
{"(\"{1,2,3,4,5}\")","(\"{10,20,30}\")"}
(1 row)
postgres=# select (unnest(array_agg(distinct row(a)::intarr))::intarr).f from t;
f
─────────────
{1,2,3,4,5}
{10,20,30}
(2 rows)
Do konference pgsql-general zaslal Sam Mason.
Pozor na opakované volání funkce použité v rozvoji záznamu
PostgreSQL umožňuje rozvinutí typu record konstrukcí (..).*. Pozor! Pokud zdrojem rozvíjené hodnoty je funkce, pak se funkce volá tolikrát, kolik má rozvoj prvků. Je to z toho důvodu, že rozvoj znamená implicitní náhradu divokého znaku za jednotlivé prvky funkce. Tj. pro funkci foo:
create or replace function foo(out a int, out b int) returns record as $$ begin raise notice 'start foo'; a := 10; b := 20; return; end; $$ language plpgsql;
pak SELECT (foo()).* je automaticky nahrazeno SELECT (foo()).a, (foo()).b, přičemž dochází k zbytečnému volání funkce foo. V jazyce SQL to nemusí být problém - dojde k vícenásobné substituci. Ta v plpgsql není možná, a tak může dojít k zbytečnému zpomalení.
postgres=# SELECT (foo()).*; NOTICE: start foo NOTICE: start foo a │ b ────┼──── 10 │ 20 (1 row) --řešení postgres=# SELECT * FROM foo(); NOTICE: start foo a │ b ────┼──── 10 │ 20 (1 row) postgres=# SELECT (f.foo).* FROM (SELECT foo()) f; NOTICE: start foo a │ b ────┼──── 10 │ 20 (1 row)