SQL Triky

Z PostgreSQL
Přejít na: navigace, hledání

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

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 = ANY(ARRAY(SELECT ctid 
                  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;

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ů