89.233.175.62 v 25. 8. 2008, 08:11

2008-08-25T08:11:48Z

Nová stránka

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 [[MySQL5:Jemný_úvod_do_uložených_procedur_MySQL5#Generování křížové sestavy|Jemný úvod do uložených procedur - generování křížové sestavy]].
<pre>
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);
</pre>
Jádro procedury je v generování SELECTu podle vzoru:
<pre>
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
</pre>
Vlastní funkce je zde - vrací pojmenovaný kurzor:
<pre>
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;
</pre>
Použití kurzorů je omezeno transakcí, musím tedy zapouzdřit funkci do explicitní transakce:
<pre>
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;
</pre>
Výsledky:
<pre>
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
</pre>

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

89.233.175.62 v 25. 8. 2008, 08:11