Function to return SETOF Table in Postgres [duplicate]

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\"

Josha Inglis · Accepted Answer · 2019-07-03 02:08:50Z

2

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.

answered Jul 3, 2019 at 2:08

Josha Inglis

1,05811 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Function to return SETOF Table in Postgres [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related