Thursday, March 7, 2013

Postgresql dynamic crosstab function

0 comments
Postgresql 9.0 has a contrib module named tablefunc which provide crosstab functionality.
http://www.postgresql.org/docs/9.1/static/tablefunc.html
It has two types of crosstab functions :-
  • crosstab(text sql)
  • crosstab(text source_sql, text category_sql)
We generally use the second one where we provide source and category sql.
source_sql is a SQL statement that produces the source set of data. This statement must return one row_name column, one category column, and one value column
category_sql is a SQL statement that produces the set of categories. This statement must return only one column. It must produce at least one row, or an error will be generated. Also, it must not produce duplicate values, or an error will be generated.
The biggest problem of this crosstab function is it needs to write all return column in sql queries to generate the report.
 
SELECT * FROM crosstab('...', '...')
    AS ct(row_name text, cat1 text, cat2 text, cat3 text, cat4 text);
One must know how many of categories are going to return as - cat1 text, cat2 text, cat3 text, cat4 text. So, it was not going with our idea to generate a report dynamically.
To overcome this, I create a function called crosstab_dynamic_sql_gen_fn. It takes following parameters as input :-
  • source_sql text - described above
  • category_sql text - described above
  • v_matrix_col_type varchar(100)  - data type for category values
  • v_matrix_rows_name_and_type varchar(100) - row name with data type like 'username text'
  • debug bool default false - to get debug output
And it returns the generated sql.
 
DROP FUNCTION crosstab_dynamic_sql_gen_fn (source_sql text, category_sql text, v_matrix_col_type varchar(100), v_matrix_rows_name_and_type varchar(100),debug bool);
CREATE OR REPLACE FUNCTION crosstab_dynamic_sql_gen_fn (source_sql text, category_sql text, v_matrix_col_type varchar(100), v_matrix_rows_name_and_type varchar(100),debug bool default false)
RETURNS text AS $$
DECLARE
v_sql text;
curs1 refcursor;
v_val text;
BEGIN
v_sql = v_matrix_rows_name_and_type;
OPEN curs1 FOR execute category_sql;
Loop
FETCH curs1 INTO v_val;
exit when v_val IS NULL;
v_sql = v_sql ||' , "'||v_val||'" '||v_matrix_col_type;
IF debug THEN
RAISE NOTICE 'v_val = %',v_val;
END IF;
END LOOP;
CLOSE curs1;
v_sql := 'SELECT * from crosstab(' || chr(10) || E' \''||source_sql || E'\','||chr(10) || E' \''||category_sql || E'\'' || chr(10)|| ' ) AS (' || v_sql ||')';
IF debug THEN
RAISE NOTICE 'v_sql = %',v_sql;
END IF;
RETURN v_sql;
END;
$$ language 'plpgsql';

Now, when I use the function, I get the sql to generate crosstab report.
select crosstab_dynamic_sql_gen_fn('select year, month, qty from sales order by 1','select distinct month from sales','int','year text');
crosstab_dynamic_sql_gen_fn
---------------------------------------------------------------------------------
SELECT * from crosstab( +
'select year, month, qty from sales order by 1', +
'select distinct month from sales' +
) AS (year text , "1" int , "5" int , "11" int , "12" int , "2" int , "7" int)
(1 row)
As long as you get the generated sql, you may do whatever you like with that. You may create function, view etc.
 
Up to this point, we can use this function for all our dynamic crosstab operation. Next turn is to use this for our specific purpose.
For this, I created another function populate_matrix_rep_fn which takes two parameters -
  • v_pkid int  - subsetid
  • v_outfile varchar(100) - output csv file path
It creates the csv file in the given path.
DROP FUNCTION populate_matrix_rep_fn(v_pkid int, v_outfile varchar(100));
CREATE OR REPLACE FUNCTION populate_matrix_rep_fn(v_pkid int, v_outfile varchar(100))
RETURNS void AS $$
DECLARE
v_source_sql text;
v_category_sql text;
v_sql text;
BEGIN
v_source_sql := 'SELECT rowname, ii.category, value '||
'FROM tab1 r, tab2 ii where ii.id=r.itemid '||
'and pkid=' || v_pkid ;

v_category_sql := 'SELECT distinct ii.category '||
'FROM tab1 r,tab2 ii where ii.id=r.itemid '||
'and pkid= '||v_pkid || ' order by 1 ';

v_sql := crosstab_dynamic_sql_gen_fn(v_source_sql,v_category_sql,'text','"user text"',false);
v_sql := E'COPY ( ' || v_sql || E' ) TO \'' || v_outfile || E'\' (FORMAT \'csv\', NULL \'0\' , HEADER)';
--RAISE NOTICE 'v_sql = %',v_sql;
EXECUTE v_sql;

END;
$$ Language 'plpgsql'
SET work_mem=1048576;

Now, simply, we can call the function to generate crosstab reports for different subsetid.
 
select populate_matrix_rep_fn(522219,'/tmp/rumman/out.csv');




Leave a Reply

Labels