How to return set of new rows in postgres function?-CodePudding

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 ?

CodePudding user response：

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();

CodePudding user response：

You cannot return the table via any variable. PostgreSQL has no table variables. When you create a table, PostgreSQL creates a composite type with the same name. Not table type - compound type - record.

  CREATE FUNCTION generate_report_trial()
RETURNS records AS $$
    SELECT form #>> '{sex}', '{city}', count(*) as count 
      FROM records GROUP BY form #>> '{sex}', form #>> '{city}';
$$ LANGUAGE plpgsql;