Postgresql > Crosstab query > Need input

Question

Using Postgresql 9.4 version.

I have to prepare one report which compares table structure across the environment i.e. QA, UAT, PROD. I have loaded the table structure from all env in one table and wants to generate the report using SQL crosstab query.

I am able to compare the column name but not able to extend to the data type.

create table test_schema_rpt
(
    env_name    varchar2(100),
    tbl_name    varchar2(100),
    col_nm      varchar2(100),
    data_type   varchar2(100),
    col_len     integer

);

---

insert into test_schema_rpt values ('PROD','test_tbl','col1','character varying','100');
insert into test_schema_rpt values ('PROD','test_tbl','col2','character varying','200');
insert into test_schema_rpt values ('PROD','test_tbl','col3','character varying','300');
insert into test_schema_rpt values ('PROD','test_tbl','col4','integer',null);
insert into test_schema_rpt values ('UAT','test_tbl','col1','character varying','100');
insert into test_schema_rpt values ('UAT','test_tbl','col2','character varying','300');
insert into test_schema_rpt values ('UAT','test_tbl','col3','character','1');
insert into test_schema_rpt values ('UAT','test_tbl','col4','numeric',null);
insert into test_schema_rpt values ('UAT','test_tbl','col5','text',null);
insert into test_schema_rpt values ('QA','test_tbl','col1','character varying','100');
insert into test_schema_rpt values ('QA','test_tbl','col2','character varying','200');
insert into test_schema_rpt values ('QA','test_tbl','col3','character varying','300');
insert into test_schema_rpt values ('QA','test_tbl','col4','numeric',null);
insert into test_schema_rpt values ('QA','test_tbl','col5','text',null);
insert into test_schema_rpt values ('QA','test_tbl','col6','character varying','500');

---

Using this query

select * from crosstab 
(
    'select tbl_name||''.''||col_nm::text table_nm , env_name, col_nm::text       
            from test_schema_rpt order by 1,2'
    ,'select  distinct env_name from test_schema_rpt order by 1'
)
as tbl
( 
    table_nm  text
    ,"QA" text
    ,"UAT" text
    ,"PROD" text 
);

enter image description here

Please see expected result set image

Answer 1

Consider a multiple value crosstab as shown by Postgres guru, @ErwinBrandstetter using a composite type:

DROP TYPE IF EXISTS i2 CASCADE;
CREATE TYPE i2 AS (typ VARCHAR(100), col INTEGER);

SELECT tbl_name, 
       col_nm,
       (a1).typ AS "QA_Data_Type",  
       (a0).typ AS "PROD_Data_Type",  
       (a2).typ AS "UAT_Data_Type",       
       (a1).col AS "QA_col_len",
       (a0).col AS "PROD_col_len",
       (a2).col AS "UAT_col_len"

FROM   crosstab(
   'SELECT tbl_name || col_nm AS row_name, tbl_name, col_nm, env_name, 
           (data_type, col_len)::i2 AS vals
    FROM   test_schema_rpt
    ORDER  BY tbl_name, col_nm, env_name'
    ,
    'select distinct env_name from test_schema_rpt order by 1'
   ) 
AS tbl (   
    row_name VARCHAR(200),
    tbl_name VARCHAR(100),
    col_nm VARCHAR(100),
    a0 i2, 
    a1 i2, 
    a2 i2
);

DB Fiddle (press Run at top)