Postgres crosstab query on multiple columns

Question

I am trying to build a crosstab query on multiple columns.

To accomplish that I first created a type:

create type product_status as (product varchar(255), status varchar(255))

Then I created my query:

select pivot.*
from crosstab(
 $$
 select
 uniqueid,
 (product, status)::product_status,
 count(*)
 from policycase
 inner join policy
 on policy.id = policycase.selected_id
 group by 1,2
 order by 1,2
 $,ドル
 $$
 values(
-- ('(HOUSEFIRE,HISTORICAL)'::product_status),
-- ('(HOUSECONTENT,HISTORICAL)'::product_status),
 ('(LIABILITY,ARCHIVED)'::product_status)
 )
 $$
) as pivot (
 uniqueid bigint,
-- housefire_hist smallint,
-- housecontent_hist smallint,
 liability_arch smallint
)

Which works and gives me correct results back. However when I uncomment the commented lines in the example above, I get the following error message:

[Exception, Error code 0, SQLState 42601] ERROR: provided "categories" SQL must return 1 column of at least one row Line 1, column 1

Two tables are used:

The policycase has a id, selected_id and a status

The policy has a id, uniqueid and product

Answer 1

The values clause here:

values(
 ('(HOUSEFIRE,HISTORICAL)'::product_status),
 ('(HOUSECONTENT,HISTORICAL)'::product_status),
 ('(LIABILITY,ARCHIVED)'::product_status)
 )

produces a single line with 3 columns, whereas you want 3 lines with a single column, as in:

values ('(HOUSEFIRE,HISTORICAL)'::product_status),
 ('(HOUSECONTENT,HISTORICAL)'::product_status),
 ('(LIABILITY,ARCHIVED)'::product_status)