Procedura pro generování kontingenční tabulky v PL/pgSQL

Donedávna jsem žil v omylu, že v PostgreSQL nelze napsat uloženou proceduru, která by vracela předem nedefinovanou tabulku. To proto, že v fakticky lze procedury v Pg implementovat pouze jako funkce - ty lze volat pouze z příkazu SELECT a u příkazu SELECT musí být již v době plánování jasné, jak bude vypadat výstupní tabulka (v podstatě jazyk SQL je v PostgreSQL přísně typový a statický). Mýlil jsem se - určitá možnost tu je, a tou je použití kurzoru. Příkaz FETCH slouží pouze k přesunu tabulky ze serveru na klienta, k ničemu jinému. A lze jej použít k zobrazení dynamicky vytvořeného obsahu. Jako příklad použiji generování kontingenční tabulky převzatý od Rolanda Baumana použitý v článku Jemný úvod do uložených procedur - generování křížové sestavy.

CREATE TABLE employees (
  id serial PRIMARY KEY,
  shop_id int,
  gender char(1),
  name varchar(32),
  salary int
);

CREATE TABLE shops (
  id serial PRIMARY KEY,
  shop varchar(32)
);

INSERT INTO shops(shop) VALUES('Zurich'),('New York'),('London');

INSERT INTO employees (shop_id, gender, name, salary)
VALUES 
(1, 'm', 'Jon Simpson', 4500),
(1, 'f', 'Barbara Breitenmoser',4700),
(2, 'f', 'KirstenRuegg',5600),
(3, 'm', 'Ralp Teller',5100),
(3, 'm', 'Peter Jonson',4700);

Jádro procedury je v generování SELECTu podle vzoru:

SELECT shop, 
		SUM(CASE gender WHEN 'f' THEN salary ELSE 0 END) AS f, 
		SUM(CASE gender WHEN 'm' THEN salary ELSE 0 END) AS m, 
		SUM(salary) AS total 
	FROM employees INNER JOIN shops USING (shop_id)
	GROUP BY shop

Vlastní funkce je zde - vrací pojmenovaný kurzor:

CREATE OR REPLACE FUNCTION do_cross_cursor(dimx_name varchar, dimx_source varchar,
       				dimy_name varchar, dimy_source varchar,
				expr varchar)
RETURNS refcursor AS $$
DECLARE 
  col_list text[] := '{}';
  query text;
  r RECORD;
  result refcursor := 'result';
BEGIN
  FOR r IN EXECUTE 'SELECT DISTINCT ' || dimx_name || '::text AS val ' || dimx_source 
  LOOP
    col_list := array_append(col_list, 'SUM(CASE ' || dimx_name || ' WHEN ' 
    	     			       || quote_literal(r.val) || ' THEN ' || expr || ' ELSE 0 END) AS ' 
				       || quote_ident(r.val) || '');
  END LOOP;
  query := 'SELECT ' || dimy_name || ', ' || array_to_string(col_list, ',') 
  	   	     || ', SUM(' || expr || ') AS Total ' || dimy_source || ' GROUP BY ' || dimy_name;
  OPEN result NO SCROLL FOR EXECUTE query;
  RETURN result;
END;
$$ LANGUAGE plpgsql STRICT;

Použití kurzorů je omezeno transakcí, musím tedy zapouzdřit funkci do explicitní transakce:

BEGIN;
  SELECT do_cross_cursor('gender', 'FROM employees','shop','FROM employees e JOIN shops s ON s.id = e.shop_id', 'salary'); 
  FETCH ALL FROM result;
END;

BEGIN;
  SELECT do_cross_cursor('shop', 'FROM shops','gender','FROM employees e JOIN shops s ON s.id = e.shop_id', 'salary'); 
  FETCH ALL FROM result;
END;

Výsledky:

postgres=# BEGIN;
BEGIN
postgres=#   SELECT do_cross_cursor('gender', 'FROM employees','shop','FROM employees e JOIN shops s ON s.id = e.shop_id', 'salary');
 do_cross_cursor 
-----------------
 result
(1 row)

postgres=#   FETCH ALL FROM result;
   shop   |  m   |  f   | total 
----------+------+------+-------
 New York |    0 | 5600 |  5600
 Zurich   | 4500 | 4700 |  9200
 London   | 9800 |    0 |  9800
(3 rows)

postgres=# END;BEGIN;
COMMIT
BEGIN
postgres=#   SELECT do_cross_cursor('shop', 'FROM shops','gender','FROM employees e JOIN shops s ON s.id = e.shop_id', 'salary');
 do_cross_cursor 
-----------------
 result
(1 row)

postgres=#   FETCH ALL FROM result;
 gender | New York | Zurich | London | total 
--------+----------+--------+--------+-------
 m      |        0 |   4500 |   9800 | 14300
 f      |     5600 |   4700 |      0 | 10300
(2 rows)

postgres=# END;
COMMIT