Redshift UDF aggregate or window error

I have written the following function

create function sevRun (text,text,int)
returns int stable as $$
SELECT count(*) 
  FROM (SELECT case_id
          FROM dbname.tablename
         WHERE create_date >= TO_DATE('2017-' || $1::text || '-01', 'YYYY-MM-DD') 
           AND create_date < TO_DATE('2017-' || $2::text || '-01', 'YYYY-MM-DD')
           AND min_impact <= $3
           AND assigned_to_group IN ('listitem','listitem','....')
       ) inline_view
$$language sql;

when I call the function select sevRun ('01','02',2); I get the following error

Invalid operation: The select expression can not have aggregate or window function.;

I’m trying to create a function that I can call multiple times and change the month to suit

Answer

You can’t select from the table inside the SQL UDF in Redshift. The only allowed functions are scalar functions that operate on the input parameters and return the output.

UPD: Redshift now support stored procedures in plpgsql. official docs

Advertisement

Answer