Redshift UDF function with dynamic SQLstatement

Question

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.

I don't know about Redshift, but in Postgres you can only use dynamic sql in PL/pgSQL, not in language sql functions — user330315
– user330315, Commented Jan 13, 2021 at 14:14
Yes I tried that as well, I am getting error [Amazon](500310) Invalid operation: language plpgsql not supported for CREATE FUNCTION; — Tpk43
– Tpk43, Commented Jan 13, 2021 at 14:20

Joe Harris · Accepted Answer · 2021-01-14 13:54:38Z

1

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

answered Jan 14, 2021 at 13:54

Joe Harris

14.1k4 gold badges49 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Redshift UDF function with dynamic SQLstatement

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related