CODEWITHSUNDEEP
sqlamazon-redshift
pyth0nBen
pyth0nBen

Reputation: 101

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

Upvotes: 1

Views: 1510

Answers (2)

AlexYes
AlexYes

Reputation: 4208

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

Upvotes: 2

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Why not this query - 

SELECT COUNT(*)
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','....')

Upvotes: 0

Related Questions