sqlpostgresqlset-returning-functions

How to return set of new rows in postgres function?


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 ?


Solution

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