crosstab dynamic

crosstab_dynamic.sql

drop table tmp_sales;

CREATE TABLE if not exists tmp_sales (
    VendorID INT,
    Employee VARCHAR,
    Orders   INT
);
insert
  into tmp_sales
values (1, 'employee_a', 10)
     , (2, 'employee_b', 43)
     , (3, 'employee_c', 22)
;

create or replace function f() returns setof record as $$
declare
   list_columns TEXT;
begin
   SELECT string_agg(DISTINCT employee,' ' ORDER BY employee)
     into list_columns
     FROM tmp_sales
   ;
   return query
   execute format($stmt$
      select *
      from tablefunc.crosstab
         (
            'select vendorid, employee, orders from tmp_sales order by vendorId, employee'::text,
            'select distinct employee from tmp_sales order by employee'::text
         )
      as (vendorId int, %1$s)
      $stmt$,
      replace(list_columns, ' ', ' INT,') || ' INT'
   );
end;
$$ language plpgsql;

-- works
select * from f() rec(vendorid int, employee_a int, employee_b int, employee_c int);

-- DROP TABLE
-- CREATE TABLE
-- INSERT 0 3
-- CREATE FUNCTION
--  vendorid | employee_a | employee_b | employee_c
-- ----------+------------+------------+------------
--         1 |         10 |            |
--         2 |            |         43 |
--         3 |            |            |         22
-- (3 рядки)

-- doesn't work
select * from f() rec(vendorid int, employee_a int, employee_b int);

-- psql:tmp/temp.sql:53: ERROR:  structure of query does not match function result type
-- ДЕТАЛІ:   Number of returned columns (4) does not match expected column count (3).
-- КОНТЕКСТ:  PL/pgSQL function f() line 9 at RETURN QUERY