I am using Postgres 14.4 and have table records with jsonb form
column name
, sex
, and city
.
id | form
a | { "name": "John", "sex": "Male", "city": "Amsterdam" }
b | { "name": "Eva", "sex": "Female", "city": "London" }
c | { "name": "Doe", "sex": "Male", "city": "Amsterdam" }
I wanted to return the data embedded by function.
Here is what I tried
CREATE FUNCTION generate_report_trial()
RETURNS records AS $$
SELECT form #>> '{sex}', '{city}', count(*) as count
FROM records GROUP BY form #>> '{sex}', form #>> '{city}';
$$ LANGUAGE SQL STABLE;
Returning void
doesn't work since I am returning new aggregate data as specified
here https://www.postgresql.org/docs/current/sql-createfunction.html
The error says return type mismatch in function declared to return records
.
How can I achieve it ?
You need to return a table:
CREATE FUNCTION generate_report_trial()
RETURNS table (sex text, city text, num_rows bigint)
AS
$$
SELECT form #>> '{sex}',
form #>> '{city}',
count(*) as count
FROM records
GROUP BY form #>> '{sex}', form #>> '{city}';
$$
LANGUAGE SQL
STABLE;
You need to use it the from clause then:
select *
from generate_report_trial();