Error executing postgres function: the function does not return a "COMPOSITE" type

I have written this postgres function in hasura

CREATE OR REPLACE FUNCTION get_shipday_filter(p_carriercode TEXT, p_shipdate DATE) 
RETURNS table(shipday text, delivered int, transit int, refused int, undeliverable int, total_exceptions int)
AS 
$$ 
  SELECT view_group_by_shipday_filter.shipday AS shipday, 
         sum(view_group_by_shipday_filter.delivered) AS delivered, 
         sum(view_group_by_shipday_filter.transit) AS transit, 
         sum(view_group_by_shipday_filter.refused) AS refused, 
         sum(view_group_by_shipday_filter.undeliverable) AS undeliverable, 
         sum((view_group_by_shipday_filter.undeliverable + view_group_by_shipday_filter.refused)) AS total_exceptions 
  FROM view_group_by_shipday_filter 
  WHERE (p_carriercode IS NULL OR view_group_by_shipday_filter.carriercode = p_carriercode) 
    AND (p_shipdate IS NULL OR DATE(view_group_by_shipday_filter.shipdate) = p_shipdate) 
  GROUP BY view_group_by_shipday_filter.shipday; 
$$ 
LANGUAGE sql stable;

When I run this in hasura without tracking it executes but when I track this it gives the error

Inconsistent object: in function "get_shipday_filter": 
the function "get_shipday_filter" cannot be tracked for the following reasons: 
• the function does not return a "COMPOSITE" type 
• the function does not return a table

Context: I've created this view view_group_by_shipday_filter from a base table which group by carriercode,shipdate and shipday.I had to add carriercode,shipdate in group by because I'll be doing filter based on them. I was expecting it will give me unique shipday in the view but it's creating duplicate shipday. That's why I'm trying to create a function on top of the view, to apply filter when necessary and group by only shipday so that I get unique shipday.

Incase it helps hasura has some constraints for creating postgres function: https://hasura.io/docs/latest/schema/postgres/custom-functions/#pg-supported-sql-functions

I'm still learning postgres function, tbf postgres in general. Stuck here for a while any help would be much appreciated.

Edit: I have updated the query without loop still getting the same error.

Solution

Solved this after going through hasura's documentation. It was more of a hasura related problem then postgres.

According to hasura If the SETOF table doesn't already exist or your function needs to return a custom type i.e. row set, create and track an empty table with the required schema to support the function before executing the above steps.

Since I didn't have a SETOF table, I created an empty table

CREATE TABLE shipday_filter (
  shipday text,
  delivered int,
  transit int,
  refused int,
  undeliverable int,
  total_exceptions int
);

then returned the empty table from the function. Btw I modified the initial function query to suit my need.

CREATE OR REPLACE FUNCTION get_shipday_filter(p_carriercode TEXT, p_shipdate_start DATE, p_shipdate_end DATE) 
RETURNS SETOF shipday_filter
AS 
$$ 
  SELECT shipday, 
         sum(delivered) AS delivered, 
         sum(transit) AS transit, 
         sum(refused) AS refused, 
         sum(undeliverable) AS undeliverable, 
         sum(undeliverable + refused) AS total_exceptions 
  FROM view_group_by_shipday_filter 
  WHERE (p_carriercode IS NULL OR carriercode = p_carriercode) 
    AND (p_shipdate_start IS NULL OR DATE(shipdate) >= p_shipdate_start) 
    AND (p_shipdate_end IS NULL OR DATE(shipdate) <= p_shipdate_end) 
  GROUP BY shipday; 
$$ 
LANGUAGE sql stable;