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
Advertisement
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