Zobrazení zdroje stránky Časté chyby v PL/pgSQL

Při auditu zdrojových kódů v PL/pgSQL se relativně často setkávám s následujícími programátorskými prohřešky:

*nepoužívání vestavěných funkcí, C-čkový styl programování - v případě jakéhokoliv interpretovaného jazyku je zásadní používat vestavěné funkce. To platí pro PL/pgSQL dvojnásob. Snažte se zmenšit počet výrazů na rozumné minimum (snaha o absolutní minimum může být na škodu - runtime PL/pgSQL rozlišuje mezi jednoduchým výrazem a obecným výrazem. Jednoduché jsou vyhodnocovány v speciálním řádově rychlejším režimu).
<pre>
-- špatně
IF x1 IS NOT NULL THEN  
  s := s || x1 || ','; 
ELSE
  s := s || 'NULL,';
END IF;
IF x2 IS NOT NULL THEN
  s := s || x2 || ','
ELSE 
  s := s || 'NULL,';
END IF;
...

-- správně
s := coalesce(x1 || ',', 'NULL,') || coalesce(x2 || ',','NULL,') || ...
</pre>

*Získávání měsíce, dnu, roku z datumové proměnné pomocí přetypování na text a výběrem podřetězce
<pre>
--špatně
m = substring(current_date::text FROM 6 FOR 2)::int;

-- lépe
m = substring(to_char(current_date, 'YYYY-MM-DD') FROM 6 FOR 2):: int;

--správně
m = EXTRACT(month FROM current_date);
</pre>
*''Naprosto zásadní bezpečnostní chybou je nezabezpečené dynamické SQL'':
<pre>
-- špatně
EXECUTE 'SELECT ' || column_name || ' FROM ' || table_name
           || ' WHERE ' || column_name || e'=\'' || some_variable || e'\''
  INTO var;

-- správně
EXECUTE 'SELECT ' || quote_ident(column_name) || ' FROM ' || quote_ident(table_name)
           || ' WHERE ' || quote_ident(column_name) || '=' || quote_literal(some_variable)
  INTO var;

-- správně pro 8.4 a novější
EXECUTE 'SELECT ' || quote_ident(column_name) || ' FROM ' || table_name::regclass
           || ' WHERE ' || quote_ident(column_name) || '= $1'
  INTO var
  USING some_variable

-- špatně
CREATE OR REPLACE FUNCTION create_table(schemaname varchar, tablename varchar)
RETURNS void AS $$
BEGIN
  EXECUTE 'CREATE TABLE ' || coalesce(schemaname || '.','') || tablename;
  RETRUN;
END;
$$ LANGUAGE plpgsql;

-- správně
CREATE OR REPLACE FUNCTION create_table(schemaname varchar, tablename varchar)
RETURNS void AS $$
BEGIN
  EXECUTE 'CREATE TABLE ' || coalesce(quote_ident(schemaname) || '.','') || quote_ident(tablename);
  RETURN;
END;
$$ LANGUAGE plpgsql;
</pre>
*Drobnější, nicméně častou chybou je generování intervalu z čísla pomocí přetypování:
<pre>
-- neefektivně
x := (d || ' days')::interval;

-- efektivně
x := d * interval '1 day';
</pre>
* Konečně - zbytečné použití explicitních kurzorů 
<pre>
-- zbytečně složité
DECLARE 
  curs CURSOR FOR SELECT * FROM tenk1;
  r record;
BEGIN
  open curs;
  FETCH curs INTO r;
  LOOP
    EXIT WHEN NOT found;
    ...
    FETCH curs INTO r;
  END LOOP;
  close curs;
END;

-- jednodušeji
DECLARE r record;
BEGIN
  FOR r IN 
          SELECT * FROM tenk1
  LOOP
    ...
  END LOOP;
END;
</pre>
*Nevhodné použití PL/pgSQL - PL/pgSQL je nevhodný jazyk pro iterace s velkým počtem cyklů obsahující modifikaci polí nebo řetězců. Např. funkce pro seřazení pole v PL/pgSQL je šikovná pro výuku PL/pgSQL ale naprosto nevhodná pro produkční prostředí. Tam bude řádově rychlejší funkce v SQL právě proto, že používá vestavěnou funkcionalitu PostgreSQL.
<pre>
CREATE OR REPLACE FUNCTION array_sort(anyarray) 
RETURNS anyarray AS $$ 
  SELECT ARRAY(SELECT unnest($1) ORDER BY 1);
$$ LANGUAGE sql;
</pre>
* Zbytečné použití SELECT INTO, zbytečné použití PL/pgSQL
<pre>
-- špatně
CREATE OR REPLACE FUNCTION foo(a float)
RETURNS float AS $$
DECLARE result float;
BEGIN
  SELECT 0.23 * a INTO result;
  return result;
END;
$$ LANGUAGE plpgsql;

-- lépe
CREATE OR REPLACE FUNCTION foo(a float)
RETURNS float AS $$
BEGIN
  RETURN 0.23 * a;
END;
$$ LANGUAGE plpgsql;

-- správně -- použít SQL proceduru
CREATE OR REPLACE FUNCTION foo(a float)
RETURNS float AS $$
  SELECT 0.23 * $1;
$$ LANGUAGE sql;
</pre>
*Jiný příklad na stejné téma - hledání minim a rychlost pro 100 000 volání:
<pre>
-- špatně (210 ms)
CREATE OR REPLACE FUNCTION min1(a int, b int)
RETURNS int AS $$
BEGIN
  IF a < b THEN
    RETURN a;
  ELSE
    RETURN b;
  END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

-- lépe, ale stále špatně (190 ms)
CREATE OR REPLACE FUNCTION min1(a int, b int)
RETURNS int AS $$
BEGIN
  RETURN least(a,b);
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

-- lépe (30 ms)
CREATE OR REPLACE FUNCTION min2(a int, b int)
RETURNS int AS $$
SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END;
$$ LANGUAGE sql;

-- nejrychlejší varianta (20 ms)
CREATE OR REPLACE FUNCTION min3(a int, b int)
RETURNS int AS $$
SELECT least($1,$2);
$$ LANGUAGE sql;
</pre>
PL/pgSQL není optimalizovaný na rekurzivní volání - nepoužívejte PL/pgSQL na hluboká rekurzivní volání, používejte nerekurzivní algoritmy
<pre>
CREATE OR REPLACE FUNCTION public.psqlfibnr(n integer)
 RETURNS integer
 LANGUAGE plpgsql
 IMMUTABLE STRICT
AS $function$
DECLARE 
  prev1 int = 0;
  prev2 int = 1;
  result int = 0;
BEGIN
  FOR i IN 1..n
  LOOP
    result := prev1 + prev2;
    prev2 := prev1;
    prev1 := result;
  END LOOP;
  RETURN result;
END;
$function$

CREATE OR REPLACE FUNCTION public.psqlfibr(n integer)
 RETURNS integer
 LANGUAGE plpgsql
 IMMUTABLE STRICT
AS $function$
BEGIN
  IF n < 2 THEN
    RETURN n;
  END IF;
  RETURN psqlfib(n-1) + psqlfib(n-2);
END;
$function$

postgres=# select n, psqlfibnr(n)
              from generate_series(0,35,5) as n;
 n  | psqlfibnr 
----+-----------
  0 |         0
  5 |         5
 10 |        55
 15 |       610
 20 |      6765
 25 |     75025
 30 |    832040
 35 |   9227465
(8 rows)

Time: 1.178 ms

postgres=# select n, psqlfibr(n)                                                                                                                                                                                                               
              from generate_series(0,35,5) as n;                                                                                                                                                                                                 
 n  | psqlfib                                                                                                                                                                                                                                 
----+---------                                                                                                                                                                                                                                
  0 |       0                                                                                                                                                                                                                                 
  5 |       5                                                                                                                                                                                                                                 
 10 |      55                                                                                                                                                                                                                                 
 15 |     610                                                                                                                                                                                                                                 
 20 |    6765                                                                                                                                                                                                                                 
 25 |   75025                                                                                                                                                                                                                                 
 30 |  832040                                                                                                                                                                                                                                 
 35 | 9227465                                                                                                                                                                                                                                 
(8 rows)                                                                                                                                                                                                                                      
                                                                                                                                                                                                                                              
Time: 282992.820 ms              
</pre>