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.
Zpracování logů ve formátu JSON s nástrojem jq
Autor: Pavel Stěhule
Bez dalšího zpracování nejsou logy ve formátu JSON extra čitelné. Díky nástroji jq filtrování, transformace do formátu tsv nebo csv je jednoduché.
cat postgresql-Sun.json | jq 'select(.error_severity=="ERROR").message'|sort -n | uniq -c 1 "canceling statement due to user request" 1 "column \"de.id\" must appear in the GROUP BY clause or be used in an aggregate function" 1 "column reference \"modify_time\" is ambiguous" 3 "column \"us.show_name\" must appear in the GROUP BY clause or be used in an aggregate function" 24 "current transaction is aborted, commands ignored until end of transaction block" 3 "deadlock detected" cat postgresql-Sun.json | \ jq -r 'select(.error_severity=="ERROR") | [.timestamp, .user, .ps, .error_severity, .message ] | @csv' \ | pspg --csv
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)