SQL Triky: Porovnání verzí

Z PostgreSQL
Přejít na: navigace, hledání
 
Řádka 2: Řádka 2:
  
 
[[SQL Triky IV.|Starších dvacet triků]]
 
[[SQL Triky IV.|Starších dvacet triků]]
 +
==Přenesení hodnoty z klienta na server==
 +
Autor: Daniel Verite
 +
 +
Proměnnou PGOPTION lze pouužít i pro zákaznické proměnné:
 +
<pre>
 +
$ 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)
 +
</pre>
 +
případně s escapeovanými hodnotami:
 +
<pre>
 +
$ 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)
 +
</pre>
 +
 
==Logování všech příkazů z aplikace==
 
==Logování všech příkazů z aplikace==
 
Autor: Pavel Stěhule
 
Autor: Pavel Stěhule

Aktuální verze z 18. 8. 2020, 09:57

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.

Starších dvacet triků

Obsah

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)

Starších dvacet triků