Tuesday, November 20, 2007

Stacked recordset (multirecordset)

So I am little bit far now. Multirecorsets are available with current protocol, but some changes in libpq are necessary :(. Procedure's output can be much more dynamic than from SRF functions. There are not limits. I wrote small regress test. Its based on Roland Bauman's sample (that was be used in http://www.pgsql.cz/index.php/MySQL5:_Gentle_introduction_to_stored_procedures_of_MySQL5).
CREATE TYPE gender AS ENUM('m', 'f');

CREATE TABLE employees (
id serial PRIMARY KEY,
shop_id int,
gender gender,
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', 'Kirsten Ruegg', 5600),
(3, 'm', 'Ralph Teller', 5100),
(3, 'm', 'Peter Jonson', 5200);
Procedure crosstab generate and execute SQL like
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 e INNER JOIN shops s ON e.shop_id = s.id
GROUP BY shop
Source code:
CREATE OR REPLACE procedure crosstab(dimx_name varchar(32), dimx_source varchar(32),
dimy_name varchar(32), dimy_source varchar(32),
expr varchar(32)) AS
$$
DECLARE
cols_expr varchar;
xtab_expr varchar;
BEGIN
EXECUTE 'SELECT array_to_string(ARRAY(SELECT ''SUM(CASE ' || dimx_name || ' WHEN '''''' || x.'
|| dimx_name || ' || '''''' THEN ' || expr || ' ELSE 0 END) AS "'' || x.'|| dimx_name
|| ' || ''"'' FROM (SELECT DISTINCT '
|| dimx_name || ' ' || dimx_source || ') x),'', '')' INTO cols_expr;
xtab_expr := 'SELECT '|| dimy_name ||', ' || cols_expr || ', SUM(' || expr || ') AS Total '
|| dimy_source || ' GROUP BY '|| dimy_name;
EXECUTE xtab_expr;
END;
$$ LANGUAGE plpgsql;
Test output:
postgres=# CALL crosstab('gender', 'FROM employees','shop','FROM employees e INNER JOIN shops s ON e.shop_id = s.id','salary');
shop | m | f | total
---------+-------+------+-------
New York | 0 | 5600 | 5600
Zurich | 4500 | 4700 | 9200
London | 10300 | 0 | 10300
(3 rows)

CALL 0
postgres=# CALL crosstab('shop', 'FROM shops', 'gender','FROM employees e INNER JOIN shops s ON e.shop_id = s.id','salary');
gender | London | New York | Zurich | total
-------+--------+----------+--------+-------
m | 10300 | 0 | 4500 | 14800
f | 0 | 5600 | 4700 | 10300
(2 rows)

CALL 0
And one really multirecordset's sample:
create or replace procedure free(int) as $$
declare s varchar = 'SELECT 1';
begin
for i in 2..$1 loop
s := s || ',' || to_char(i,'999');
execute s;
end loop;
end$$ language plpgsql;
Output:
postgres=# call free(6);
?column? | ?column?
----------+----------
1 | 2
(1 row)

?column? | ?column? | ?column?
----------+----------+----------
1 | 2 | 3
(1 row)

?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------
1 | 2 | 3 | 4
(1 row)

?column? | ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------+----------
1 | 2 | 3 | 4 | 5
(1 row)

?column? | ?column? | ?column? | ?column? | ?column? | ?column?
----------+----------+----------+----------+----------+----------
1 | 2 | 3 | 4 | 5 | 6
(1 row)

CALL 0

Labels: ,

Wednesday, November 7, 2007

First real procedures on PostgreSQL

I am working on an enhancing of plpgsql. I would to add to plpgsql procedures and methods. Methods are flagged functions with different calling convention. Procedures are total different beast. Now I am able to run procedures without parameters. I would to support parameter passing by reference. It allows to take reference for any variable and potentially some interesting features (like string builder or array builder etc). What is best, I need not modify system tables. Procedure is function (from pg_proc perspective) that returns PROCEDURE type (it will be used for exit status).

postgres=#
create or replace procedure print()
as $$
begin
raise notice 'ahoj';
end;
$$ language plpgsql;
CREATE PROCEDURE

postgres=#
create or replace function test()
returns void as $$
begin
call print();
return;
end;
$$ language plpgsql;
CREATE FUNCTION

postgres=# select test();
NOTICE: Found ()
CONTEXT: PL/pgSQL function "test" line 4456553 at CALL
NOTICE: OID: 16821
CONTEXT: PL/pgSQL function "test" line 4456553 at CALL
NOTICE: ahoj
CONTEXT: PL/pgSQL function "test" line 4456553 at CALL
test
------

(1 row)

postgres=# \df print
List of functions
Schema | Name | Result data type | Argument data types
--------+-------+------------------+---------------------
public | print | procedure |
(1 row)

There is lot of work still, but first step is done.

Labels: ,