Получение матричного отчета

Вспомогательная функция для формирования текста динамического запроса: 

=> CREATE FUNCTION form_query() RETURNS text
AS $$
DECLARE
    query_text text;
    r record;
BEGIN
    -- Первые два столбца: имя схемы и общее количество функций в ней
    query_text := 'SELECT pronamespace::regnamespace::text AS schema,COUNT(*) AS total';

    -- Динамическая часть запроса. Получаем список владельцев функций.
    -- Для каждого владельца - отдельный столбец запроса
    FOR r IN (SELECT DISTINCT proowner AS owner FROM pg_proc ORDER BY 1) LOOP
        query_text := query_text || 
            format('
      ,SUM(CASE WHEN proowner = %s THEN 1 ELSE 0 END) %I',
                   r.owner, r.owner::regrole);
    END LOOP;

    -- Завершаем запрос
    query_text := query_text || 
        '
FROM pg_proc GROUP BY pronamespace::regnamespace ORDER BY schema';

    RETURN query_text;
END;
$$ STABLE LANGUAGE plpgsql;

CREATE FUNCTION

Итоговый текст запроса: 

=> select form_query();

                             form_query                              
---------------------------------------------------------------------
 SELECT pronamespace::regnamespace::text AS schema,COUNT(*) AS total+
       ,SUM(CASE WHEN proowner = 10 THEN 1 ELSE 0 END) postgres     +
       ,SUM(CASE WHEN proowner = 16384 THEN 1 ELSE 0 END) student   +
 FROM pg_proc GROUP BY pronamespace::regnamespace ORDER BY schema
(1 row)

Теперь создаем функцию для матричного отчета: 

=> CREATE FUNCTION matrix() RETURNS SETOF record
AS $$
BEGIN
    RETURN QUERY EXECUTE form_query();
END;
$$ STABLE LANGUAGE plpgsql;

CREATE FUNCTION

Простое выполнение запроса приведет к ошибке, т. к. не указана структура возвращаемых записей: 

=> SELECT * FROM matrix();

ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM matrix();
                      ^

В этом состоит важное ограничение на использование функций, возвращающих произвольную выборку. В момент вызова необходимо знать и указать структуру возвращаемой записи.

В общем случае структура возвращаемой записи может быть неизвестна, но, применительно к нашему матричному отчету, можно выполнить еще один запрос, который покажет, как правильно вызвать функцию matrix.

Подготовим текст запроса: 

=> CREATE FUNCTION form_query_call() RETURNS text
AS $$
DECLARE
    query_call text;
    r record;
BEGIN
    query_call := 'SELECT * FROM matrix() AS m(schema text, total bigint';

    FOR r IN (SELECT DISTINCT proowner AS owner FROM pg_proc ORDER BY 1) LOOP
        query_call := query_call || ', ' ||r.owner::regrole::text || ' bigint';
    END LOOP;
    query_call := query_call || ')';

    RETURN query_call;
END;
$$ STABLE LANGUAGE plpgsql;

CREATE FUNCTION

Теперь мы можем первым запросом получить структуру матричного отчета, а вторым запросом его сформировать: 

=> BEGIN ISOLATION LEVEL REPEATABLE READ;

BEGIN

=> SELECT form_query_call() AS query_call \gset





=> \echo :query_call





SELECT * FROM matrix() AS m(schema text, total bigint, postgres bigint, student bigint)





=> :query_call;





       schema       | total | postgres | student 
--------------------+-------+----------+---------
 information_schema |    12 |       12 |       0
 pg_catalog         |  2811 |     2811 |       0
 public             |     3 |        0 |       3
(3 rows)






=> COMMIT;





COMMIT




Матричный отчет корректно формируется.




Примечание. Уровень изоляции REPEATABLE READ гарантирует, что отчет
сформируется, даже если между двумя запросами появится функция у нового
владельца.




Примечание. Можно было бы и напрямую выполнить запрос, возвращаемый функцией
form_query. Но задача получить в клиентском приложении список возвращаемых
столбцов все равно останется. Функция form_query_call показывает, как ее
можно решить дополнительным запросом.




Еще один вариант решения заключается в том, чтобы вместо набора записей
произвольной структуры возвращать набор строк сложносоставного типа.  Например:
json, xml, hstore.




Пример функции, возвращающей строки с json:





=> CREATE FUNCTION matrix_to_json() RETURNS SETOF json
AS $$
DECLARE
    r record;
BEGIN
    FOR r IN EXECUTE form_query() LOOP
        RETURN NEXT row_to_json(r);
    END LOOP;
END;
$$ STABLE LANGUAGE plpgsql;





CREATE FUNCTION




При вызове структуру записи указывать не нужно:





=> SELECT * FROM matrix_to_json();





                            matrix_to_json                            
----------------------------------------------------------------------
 {"schema":"information_schema","total":12,"postgres":12,"student":0}
 {"schema":"pg_catalog","total":2811,"postgres":2811,"student":0}
 {"schema":"public","total":4,"postgres":0,"student":4}
(3 rows)





Но задача получить структуру json по-прежнему остается.