CODEWITHSUNDEEP
amazon-web-servicesamazon-redshiftplpgsql
Connor Meek
Connor Meek

Reputation: 93

Redshift function syntax

I have study this over and over, without figuring out what is wrong I feel like the syntax for this function is right

I have tried to take single quotes out of plpgsql, triple checked for commas, added body to the dollar signs with no luck, I can't figure out why this i erroring. returns table should be the correct syntax right?

CREATE OR REPLACE FUNCTION fn_ytd_costs_of_business (param1 date)
RETURNS TABLE (year INTEGER, month TEXT , revenue FLOAT8 ,transactiondate TIMESTAMP , Flag varchar(28),)
AS 
$$
BEGIN
select
  extract(year from transactiondate) as year
  , to_char(transactiondate, 'Mon') as month
  --, extract(month from transactiondate) as month_number
  , sum(netamount) as revenue
  ,transactiondate
  ,Flag
from
  vw_costs_of_businesss_copy a

--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(transactiondate, 'YYYY-MM-DD') ->=  concat(to_char(extract(year from '2019-01-01'), 'YYYY'),'01-01') --Convert the date param to year and concatenate with '01/01'
--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(concat(to_char(extract(year from to_date('2019-01-01', 'YYYY-MM-DD'))),'01-01') , 'YYYY-MM-DD') >= '2019-01-01' --Convert the date param to year and concatenate with '01/01'
--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(concat(to_char(Cas(extract(year from to_date('2019-01-01', 'YYYY-MM-DD')) as text ),'-01-01') , 'YYYY-MM-DD') >= '2019-01-01' --Convert the date param to year and concatenate with '01/01'
--where to_date(transactiondate, 'YYYY-MM-DD') <= '2019-06-07' and to_date(concat(to_char(Cast(extract(year from to_date('2019-01-01', 'YYYY-MM-DD')) as Text),'0000'),'-01') , 'YYYY-MM-DD') >= '2019-01-01' --Convert the date param to year and concatenate with '01/01'
where to_date(transactiondate, 'YYYY-MM-DD') <= $1 and to_date(transactiondate, 'YYYY-MM-DD')>= to_date(concat(to_char(Cast(extract(year from to_date($1, 'YYYY-MM-DD')-1) as Text),'0000'),'-01') , 'YYYY-MM-DD')

group by
  year
  , month
  ,transactiondate
 , Flag;

on a.
--order by  year;
END;
$$ 
LANGUAGE plpgsql;

this is the error I am receiving:

RETURNS TABLE (year INTEGER, month TEXT , revenue FLOAT8 ,transactiondate TIMESTAMP , Flag varchar(28),)

Upvotes: 0

Views: 309

Answers (1)

John Rotenstein
John Rotenstein

Reputation: 270069

The error is probably because TABLE is not a valid RETURN type.

However, this is unimportant because an Amazon Redshift User-Defined Function is not allowed to SELECT data from tables. It can only operate on the data that is passed into the function.

See: UDF Constraints - Amazon Redshift

Depending upon your use-case, a Stored Procedure might be suitable. It would not "return" a table, but it could CREATE INTO a table.

Upvotes: 0

Related Questions