CODEWITHSUNDEEP
sqlamazon-web-servicesamazon-redshift
Tpk43
Tpk43

Reputation: 331

Redshift UDF function with dynamic SQLstatement

I am very much new to Amazon redshift. I am trying to create the UDF function to create column aggregation by adding multiple columns, here is what I am tried

CREATE OR REPLACE FUNCTION pp_calc(identifier varchar(100),table_name varchar(100))
RETURNS float 
stable 
as $$

BEGIN

IF identifier ='OC' THEN
  EXECUTE 'SELECT identifier'||_1 + || 'identifier' ||_2 || 'FROM ' || table_name /* I want to return this addition result */
ELSE 
  'SELECT identifier'||_1 + || 'identifier' ||_2 || 'FROM ' || table_name
END IF;

END;

$$ LANGUAGE sql;

Errors I am getting near return statement. I am sure there is something wrong with syntax. I want to pass the indentifer as parameter based on parameter I want to add columns.

Upvotes: 0

Views: 1547

Answers (1)

Joe Harris
Joe Harris

Reputation: 14035

You need to use a stored procedure to perform dynamic SQL. See "Overview of Stored Procedures in Amazon Redshift"

CREATE PROCEDURE pp_calc(identifier varchar(100),table_name varchar(100))
RETURNS float  
AS $$
BEGIN
IF identifier ='OC' THEN
  EXECUTE 'SELECT '||identifier||'_1 + '||identifier||'_2 FROM '||table_name ;
/* I want to return this addition result */
ELSE
  EXECUTE 'SELECT '||identifier||'_1 + '||identifier||'_2 FROM '||table_name ;
END IF;
END;
$$ LANGUAGE plpgsql;

See also this previous answer: Redshift: Executing a dynamic query from a string

Upvotes: 1

Related Questions