Hasura中PostgreSQL自定义函数无法追踪问题求助

问题场景 #

我在Hasura中编写了如下PostgreSQL函数，直接执行正常，但在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;

错误信息 #

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

背景 #

我基于基表创建了视图view_group_by_shipday_filter，该视图按carriercode、shipdate和shipday分组，原本期望视图中shipday唯一，但实际出现重复值。因此编写上述函数，希望按需过滤并仅按shipday分组以得到唯一的shipday。已更新查询移除循环，但仍报相同错误。

问题原因 #

Hasura对可追踪的SQL函数有严格的类型识别要求，虽然函数声明了RETURNS table(...)，但PostgreSQL的SQL语言函数在这种写法下，Hasura无法正确识别返回的复合表结构，导致判定函数不符合追踪条件。

解决方法 #

方法1：提前定义复合类型（最可靠） #

先创建一个与函数返回结构匹配的复合类型，再让函数返回该类型的表：

-- 1. 创建复合类型
CREATE TYPE shipday_stats AS (
    shipday text,
    delivered int,
    transit int,
    refused int,
    undeliverable int,
    total_exceptions int
);

-- 2. 修改函数返回类型为该复合类型的表
CREATE OR REPLACE FUNCTION get_shipday_filter(p_carriercode TEXT, p_shipdate DATE) 
RETURNS TABLE(shipday_stats)
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;

方法2：改用RETURNS SETOF写法 #

如果不想单独创建类型，也可以用RETURNS SETOF record并显式指定输出列，但推荐配合提前创建的复合类型使用：

CREATE OR REPLACE FUNCTION get_shipday_filter(p_carriercode TEXT, p_shipdate DATE) 
RETURNS SETOF shipday_stats
AS 
$$ 
  SELECT shipday, delivered, transit, refused, undeliverable, total_exceptions
  FROM (
    SELECT view_group_by_shipday_filter.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
  ) AS stats;
$$ 
LANGUAGE sql stable;

验证步骤 #

1. 执行上述SQL完成类型创建和函数修改
2. 回到Hasura控制台，重新尝试追踪该函数
3. 确保函数的STABLE属性保留（Hasura要求可追踪函数必须是STABLE或IMMUTABLE）

内容的提问来源于stack exchange，提问作者mused