postgresql - 从查询中获取列定义列表

Question

我有什么:

两张 postgres 表，一张有数据，一张有要聚合的组。两个表都可以更改，例如它计划稍后添加位置并定义新类别。

locations:
| id | zipcode | name    | type   |
|----+---------+---------+--------|
|  1 |    1234 | Burger1 | burger | 
|  2 |    1234 | Burger2 | burger |
|  3 |    1234 | Gas1    | gas    |
|  4 |    5678 | FriesA  | fries  |
|  5 |    9876 | FriesB  | fries  |
|  6 |    9876 | GarageA | garage |

categories:
| category | item   |
|----------+--------|
| food     | burger | 
| food     | fries  |
| car      | gas    | 
| car      | garage | 

我期望得到的:每个邮政编码的设施数量，按给定类别汇总:

result:
| zipcode | cnt(food) | cnt(car) |
|---------+-----------+----------|
|    1234 |         2 |        1 | 
|    5678 |         1 |          |
|    9876 |         1 |        1 |

我尝试了什么:使用 postgres 的 crosstab() 函数旋转表:(参见 https://www.postgresql.org/docs/current/static/tablefunc.html#AEN186219)。

不幸的是 crosstab() 返回类型是记录，所以你需要定义明确的列定义。为了允许稍后添加类别，我尝试从查询中获取列定义列表:

SELECT * FROM crosstab(
    'SELECT location.zipcode, categories.category, count(location.id)
    FROM locations
    JOIN categories
    ON categories.item = location.type
    GROUP BY zipcode, categories.category'
    ,
    'SELECT DISTINCT category FROM categories ORDER BY category ASC;')
AS
    ct(
        SELECT array_to_string(
            array_cat(
                array(SELECT 'zipcode varchar'::varchar),
                array(SELECT DISTINCT (category || ' int')::varchar AS category FROM categories ORDER BY category ASC)
            ),
        ', '
        )
    );

有什么问题

Postgres 不会接受查询作为列定义列表。如果可能，我想避免使用 PL\pgSQL 函数，而只使用“常规”查询:

ERROR:  syntax error at or near »select«
LINE 16:   select array_to_string(
           ^

Answer 1

select zipcode, jsonb_object_agg(category, total)
from (
        select zipcode, category, count(*) as total
        from
            locations l
            inner join
            categories c on l.type = c.item
        group by zipcode, category
    ) a
    right join (
        (select distinct category from categories) c
        cross join
        (select distinct zipcode from locations) l
    ) dc using (zipcode, category)
group by zipcode
;
 zipcode |     jsonb_object_agg     
---------+--------------------------
    9876 | {"car": 1, "food": 1}
    5678 | {"car": null, "food": 1}
    1234 | {"car": 1, "food": 2}