Good day to all.
There is a team
select supplier, car_category, count(car_type) from mytable group by 1,2 order by 1,2;
which returns me the desired table
supplier | car_category | count
----------+--------------+-------
Avis | Compact | 4
Avis | Fullsize | 2
Avis | Hatch | 6
Avis | Intermediate | 4
Avis | Sedan | 6
Avis | Standard | 2
Avis | SUV | 6
Budget | Compact | 8
Budget | Fullsize | 4
Budget | hatch | 2
Budget | Hatch | 2
Budget | Hatchback | 4
Budget | Intermediate | 8
Budget | Sedan | 4
Budget | Standard | 4
Europcar | Compact | 5
Europcar | Fullsize | 5
Europcar | Intermediate | 15
Europcar | Standard | 10
(19 rows)
The task is to write a function which will return the same. My attempt:
create or replace function pivot_car_type(a text, b text, c text)
returns table(supplier text, category text, car_type_qty bigint) as
$$
begin
return query
select a,b,count(c) from avis.rateshop group by 1,2 order by 1,2;
end;
$$
language 'plpgsql';
But when you call
select * from pivot_car_type(supplier, car_category, car_type);
get the answer form:
supplier | category | car_type_qty
----------+--------------+--------------
supplier | car_category | 101
(1 row)
where 101 is the number of records in the table.
Ie it turns out that the function uses the following command:
select 'supplier', 'car_category', count('car_type') from mytable group by 1,2 order by 1,2;
not
select supplier, car_category, count(car_type) from mytable group by 1,2 order by 1,2;
Tell me, what am I doing wrong, please :)