Return a table from a user defined function in Redshift

Question

I have a complex query which gives multiple rows for some two dates- starting date and end date.

Now I want to create a function so that I can return multiple rows for a different combination of dates.

CREATE FUNCTION submit_cohort(DATE, DATE)
RETURNS TABLE(Month VARCHAR(10), Name1 VARCHAR(20), Name2 VARCHAR(20), x INTEGER)
STABLE
AS $$
  SELECT
    to_char((date + interval '330 minutes')::date, 'YYYY/MM') "Month",
    Name1,
    Name2,
    count(*) "x"
  FROM xyz
  WHERE date > $1
    AND date < $2
  GROUP BY 1,2,3
  ORDER BY 1,2,3
END 
$$ LANGUAGE sql;

I ran this query. It says:

Amazon Invalid operation: syntax error at or near "TABLE"

Answer 1

In Redshift, you can define only scalar functions, i.e. those which return a single value. Set based functions (those which return tables) are not supported in Redshift unfortunately.

Possible reason is that Redshift is a distributed database and functions are running on the compute nodes in parallel, independently of each other. Set based functions need to be able to read data from the database, but there is a chance that some data sits on the given node while another portion sits on another node. Such function can't run on a specific compute node independently. You would have to run such function on the master node only. Which you didn't want to do as it's against the whole concept of parallelism.

Try to express the same logic in a SQL query. From your code it seems like it can work as a regular query/subquery.