This question already has answers here:
PostgreSQL “Column does not exist” but it actually does
(3个答案)
sql statement error: “column .. does not exist”
(1个答案)
I keep getting the error “relation [TABLE] does not exist”
(1个答案)
5个月前关闭。
我试图构建一个满足以下条件的简单函数(需要使用Hasura API fwiwlink
Function behaviour: ONLY STABLE or IMMUTABLE
Return type: MUST be SETOF <table-name>
Argument modes: ONLY IN

这是功能
CREATE FUNCTION fn_sum_views(text) RETURNS SETOF page_analytics AS $$
  SELECT prodID, sum(views) FROM page_analytics WHERE prodID = $1 group by prodID
$$ LANGUAGE SQL STABLE;

我得到错误:"column \"prodid\" does not exist"。当我使用page_analytics.prodID"page_analytics.prodID"\"page_analytics.prodID\"时,会得到相同的错误

最佳答案

对于任何包含大写字母的表名或列名,请始终确保使用双引号。否则,它们是converted to lowercase

CREATE FUNCTION fn_sum_views(text) RETURNS SETOF page_analytics AS $$
  SELECT "prodID", sum(views) FROM page_analytics WHERE "prodID" = $1 group by "prodID"
$$ LANGUAGE SQL STABLE;

另外请注意,引用“页面分析”时,“prodID”也会起作用。

关于sql - 在Postgres中返回SETOF表的函数,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/56861888/

10-10 12:47