Reputation: 1
I have a fact table like
CREATE TABLE fact_table (
customer VARCHAR(50),
product VARCHAR(50),
city VARCHAR(50),
measure INTEGER
);
INSERT INTO fact_table (customer, product, city, measure)
VALUES
('Oleg', 'Shampoo', 'Zurich', 5),
('Oleg', 'Bread', 'Bern', 1),
('Murad', 'Shampoo', 'Bern', 3),
('Murad', 'Beer', 'Lausanne', 4),
('Olva', 'Beer', 'Lausanne', 2),
('Olva', 'Bread', 'Zurich', 2),
('Olva', 'Shampoo', 'Bern', 1);
CREATE EXTENSION IF NOT EXISTS tablefunc;
and I want to dynamically (as I don't have full knowledge of the city names a priori) obtain and output a table like
customer | bern | lausanne | zurich
----------+------+----------+--------
Murad | 3 | 4 |
Oleg | 1 | | 5
Olva | 3 | 2 | 2
without using \crosstabview
.
Since creating a temporary table and querying it gave the desired output, i.e., using
CREATE TABLE temp_t AS
SELECT *
FROM
crosstab(
' SELECT customer, city, sum(measure) FROM fact_table
GROUP BY customer, city ORDER BY 1,2'
,
$values$ VALUES ('Bern'), ('Lausanne'), ('Zurich')$values$
)
AS ct(customer text, bern integer, lausanne integer, zurich integer)
;
I thought about doing that dynamically
DO $$
DECLARE
city_list text;
city_values text;
dynamic_query text;
BEGIN
-- Retrieve the distinct city names from the fact_table and map them into column definitions
SELECT string_agg(DISTINCT lower(city), ' integer, ')
INTO city_list
FROM fact_table;
-- Retrieve the distinct city names from the fact_table
SELECT string_agg(DISTINCT city, ' ''), ('' ')
INTO city_values
FROM fact_table;
-- drop table if already existing
DROP TABLE IF EXISTS temp_t CASCADE;
-- Generate the dynamic SQL query with the pivoted columns
dynamic_query := 'CREATE TABLE temp_t AS
SELECT *
FROM
crosstab(
$inner$
SELECT customer, city, sum(measure) FROM fact_table
GROUP BY customer, city ORDER BY 1,2
$inner$,
$values$ VALUES ('' ' || city_values || ' '') $values$
) AS ct(customer text, ' || city_list || ' integer)';
-- Execute the dynamic SQL query
EXECUTE dynamic_query;
END $$;
However, querying that temporary table yields an empty table
customer | bern | lausanne | zurich
----------+------+----------+--------
Murad | | |
Oleg | | |
Olva | | |
What's wrong here?
Upvotes: 0
Views: 52