Function to return SETOF Table in Postgres

Question

I'm trying to build a simple function that meets the following criteria (needed to work with Hasura API fwiw link)

Function behaviour: ONLY STABLE or IMMUTABLE
Return type: MUST be SETOF <table-name>
Argument modes: ONLY IN

This is the function

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;

And I get the error: "column \"prodid\" does not exist". I get the same error when I use page_analytics.prodID, "page_analytics.prodID", or \"page_analytics.prodID\"

score 2 · Accepted Answer · answered Jul 03 '19 at 02:08

For any table names or column names that include capital letters, always ensure you use doublequotes. Otherwise, they are 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;

Also note, when quoting "page_analytics"."prodID" would also work.

Function to return SETOF Table in Postgres

1 Answers1