Cross-tab Report

Another case with aggregate function. A sales table is defined and contains raw records of sales activity as follows:

CREATE TABLE sales
(
  id serial NOT NULL,
  item text NOT NULL,
  sales_unit text NOT NULL,
  amount numeric NOT NULL,
  CONSTRAINT sales_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

id item    sales_unit amount
1  ITEM_1  UNIT_1     1000.5
2  ITEM_1  UNIT_2     1100
3  ITEM_1  UNIT_3     1150.25
4  ITEM_1  UNIT_1     100.75
5  ITEM_2  UNIT_2     275
6  ITEM_2  UNIT_3     750.5
7  ITEM_2  UNIT_2     300.5

Total sales amount for each sales unit for each item will be presented in cross-tab format:

item    UNIT_1   UNIT_2   UNIT_3
ITEM_1  1101.25  1100     1150.25
ITEM_2  0        575.5    750.5

To create aggregate function, let us prepare the State Transition Function:

CREATE OR REPLACE FUNCTION sales_amount(
  amounts numeric[],
  unit text,
  amount numeric,
  units text,
  unitcount integer)
RETURNS numeric[] AS
$BODY$
DECLARE
  out_amounts numeric[];
  unit_pattern text;
  sub_unit text[];
  idx integer;
BEGIN
  out_amounts:=amounts;
  ——–units in format: [unit_name]:[array_index],[unit_name]:[array_index],…
  unit_pattern:= unit || ‘:[0-9]{1,}’;
  sub_unit:=regexp_matches(units,unit_pattern);
  IF sub_unit IS NULL THEN
    RETURN out_amounts;
  END IF;
  ——–get array_index for dept
  idx:=split_part(array_to_string(sub_unit,”),’:',2)::integer;
  IF array_upper(out_amounts,1) IS NULL THEN
  ——–initiate amount array with 0
    out_amounts:=string_to_array(
    ’0′ || repeat( ‘,0′, unitcount-1 ) ,
    ’,’ )::numeric[];
  END IF;
  out_amounts[idx]:=out_amounts[idx]+amount;
  RETURN out_amounts;
END;$BODY$
LANGUAGE ‘plpgsql’ IMMUTABLE STRICT;

Here is the aggregate function:

CREATE AGGREGATE sales_agg(text, numeric, text, integer) (
  SFUNC=sales_amount,
  STYPE=numeric[],
  INITCOND=’{}’
);

Now, let us create user defined function:

CREATE OR REPLACE FUNCTION create_sales_report(
  refcursor, –cursor name
  units text[] –array of your expected units to appear on corss-tab report
)
RETURNS void AS
$BODY$
DECLARE
  unit_count integer;
  units_series text;
  i integer;
  sql text;
BEGIN
  unit_count:=array_upper(units,1);
  ——–convert units into [dept_name]:[array_index],[dept_name]:[array_index],…
  ——–to avoid repetitive loops inside aggregate function
  units_series:=”;
  FOR i in 1..unit_count LOOP
    IF i>1 THEN
      units_series:= units_series || ‘,’;
    END IF;
  units_series:= units_series || units[i] || ‘:’ || i;
  END LOOP;
  ——–compose sql statement
  sql:=    ‘SELECT t.item’;
  i:=1;
  WHILE units[i] IS NOT NULL LOOP
    sql:= sql || ‘,t.amounts[' || i || '] AS ‘ || quote_ident(units[i]);
    i:=i+1;
  END LOOP;
  sql:=sql || ‘ FROM (SELECT item,’
    || ’sales_agg(sales_unit,amount,’
    || quote_literal(units_series) || ‘,’
    || unit_count || ‘) AS amounts ‘
  || ‘FROM sales GROUP BY item’
  || ‘) t ORDER BY t.item’;
  ——–open cursor
  OPEN $1 FOR EXECUTE sql;
RETURN;
END;$BODY$
LANGUAGE ‘plpgsql’ IMMUTABLE STRICT;

Following is how the function is called in psql console:

citra=# begin;
BEGIN
citra=# select create_sales_report(‘report’,'{UNIT_1,UNIT_2,UNIT_3}’);
create_sales_report
———————

(1 row)

citra=# FETCH ALL IN report;
item  | UNIT_1  | UNIT_2 | UNIT_3
——–+———+——–+———
ITEM_1 | 1101.25 |   1100 | 1150.25
ITEM_2 |       0 |  575.5 |   750.5
(2 rows)

citra=# COMMIT;

The query can be expanded to have more sales units:

#select create_sales_report(‘report’,'{UNIT_1,UNIT_2,UNIT_3,…,…}’);

Or, to display all available sales units in the sales table, just use array_agg (see my previous post):

#select create_sales_report(‘report’,
  (SELECT array_agg(t.sales_unit) FROM (SELECT DISTINCT ON (sales_unit) sales_unit FROM sales) t)
);