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

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.