http://postgres.cz/index.php?title=Procedura_pro_generov%C3%A1n%C3%AD_kontingen%C4%8Dn%C3%AD_tabulky_v_PL/pgSQL&feed=atom&action=historyProcedura pro generování kontingenční tabulky v PL/pgSQL - Historie editací2024-03-29T10:10:47ZHistorie editací této stránkyMediaWiki 1.36.0http://postgres.cz/index.php?title=Procedura_pro_generov%C3%A1n%C3%AD_kontingen%C4%8Dn%C3%AD_tabulky_v_PL/pgSQL&diff=433&oldid=prev89.233.175.62 v 25. 8. 2008, 08:112008-08-25T08:11:48Z<p></p>
<p><b>Nová stránka</b></p><div>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]].<br />
<pre><br />
CREATE TABLE employees (<br />
id serial PRIMARY KEY,<br />
shop_id int,<br />
gender char(1),<br />
name varchar(32),<br />
salary int<br />
);<br />
<br />
CREATE TABLE shops (<br />
id serial PRIMARY KEY,<br />
shop varchar(32)<br />
);<br />
<br />
INSERT INTO shops(shop) VALUES('Zurich'),('New York'),('London');<br />
<br />
INSERT INTO employees (shop_id, gender, name, salary)<br />
VALUES <br />
(1, 'm', 'Jon Simpson', 4500),<br />
(1, 'f', 'Barbara Breitenmoser',4700),<br />
(2, 'f', 'KirstenRuegg',5600),<br />
(3, 'm', 'Ralp Teller',5100),<br />
(3, 'm', 'Peter Jonson',4700);<br />
</pre><br />
Jádro procedury je v generování SELECTu podle vzoru:<br />
<pre><br />
SELECT shop, <br />
SUM(CASE gender WHEN 'f' THEN salary ELSE 0 END) AS f, <br />
SUM(CASE gender WHEN 'm' THEN salary ELSE 0 END) AS m, <br />
SUM(salary) AS total <br />
FROM employees INNER JOIN shops USING (shop_id)<br />
GROUP BY shop<br />
</pre><br />
Vlastní funkce je zde - vrací pojmenovaný kurzor:<br />
<pre><br />
CREATE OR REPLACE FUNCTION do_cross_cursor(dimx_name varchar, dimx_source varchar,<br />
dimy_name varchar, dimy_source varchar,<br />
expr varchar)<br />
RETURNS refcursor AS $$<br />
DECLARE <br />
col_list text[] := '{}';<br />
query text;<br />
r RECORD;<br />
result refcursor := 'result';<br />
BEGIN<br />
FOR r IN EXECUTE 'SELECT DISTINCT ' || dimx_name || '::text AS val ' || dimx_source <br />
LOOP<br />
col_list := array_append(col_list, 'SUM(CASE ' || dimx_name || ' WHEN ' <br />
|| quote_literal(r.val) || ' THEN ' || expr || ' ELSE 0 END) AS ' <br />
|| quote_ident(r.val) || '');<br />
END LOOP;<br />
query := 'SELECT ' || dimy_name || ', ' || array_to_string(col_list, ',') <br />
|| ', SUM(' || expr || ') AS Total ' || dimy_source || ' GROUP BY ' || dimy_name;<br />
OPEN result NO SCROLL FOR EXECUTE query;<br />
RETURN result;<br />
END;<br />
$$ LANGUAGE plpgsql STRICT;<br />
</pre><br />
Použití kurzorů je omezeno transakcí, musím tedy zapouzdřit funkci do explicitní transakce:<br />
<pre><br />
BEGIN;<br />
SELECT do_cross_cursor('gender', 'FROM employees','shop','FROM employees e JOIN shops s ON s.id = e.shop_id', 'salary'); <br />
FETCH ALL FROM result;<br />
END;<br />
<br />
BEGIN;<br />
SELECT do_cross_cursor('shop', 'FROM shops','gender','FROM employees e JOIN shops s ON s.id = e.shop_id', 'salary'); <br />
FETCH ALL FROM result;<br />
END;<br />
</pre><br />
Výsledky:<br />
<pre><br />
postgres=# BEGIN;<br />
BEGIN<br />
postgres=# SELECT do_cross_cursor('gender', 'FROM employees','shop','FROM employees e JOIN shops s ON s.id = e.shop_id', 'salary');<br />
do_cross_cursor <br />
-----------------<br />
result<br />
(1 row)<br />
<br />
postgres=# FETCH ALL FROM result;<br />
shop | m | f | total <br />
----------+------+------+-------<br />
New York | 0 | 5600 | 5600<br />
Zurich | 4500 | 4700 | 9200<br />
London | 9800 | 0 | 9800<br />
(3 rows)<br />
<br />
postgres=# END;BEGIN;<br />
COMMIT<br />
BEGIN<br />
postgres=# SELECT do_cross_cursor('shop', 'FROM shops','gender','FROM employees e JOIN shops s ON s.id = e.shop_id', 'salary');<br />
do_cross_cursor <br />
-----------------<br />
result<br />
(1 row)<br />
<br />
postgres=# FETCH ALL FROM result;<br />
gender | New York | Zurich | London | total <br />
--------+----------+--------+--------+-------<br />
m | 0 | 4500 | 9800 | 14300<br />
f | 5600 | 4700 | 0 | 10300<br />
(2 rows)<br />
<br />
postgres=# END;<br />
COMMIT<br />
</pre></div>89.233.175.62