How to create a PostgreSQL table function c with the column names as arguments?

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 :)
April 19th 20 at 12:39
1 answer
April 19th 20 at 12:41
As it should be.

If you want to build the query dynamically and then build it dynamically in a variable, then execute.
Thank you for your answer. Could you tell how to build dynamic queries? Perhaps an example of such a query? - Neoma22 commented on April 19th 20 at 12:44
A text variable with a text query and return query execute - jaylan.Von commented on April 19th 20 at 12:47

Find more questions by tags PostgreSQL