Shall we use udf in stored procedure in Redshift

Question

I need to use a udf in stored procedure. Since Redshift needs either a temp table or a cursor to use the select statement inside the stored procedure, I chose to use temp table. Inside the stored procedure the code to call the udf looks like this:

EXECUTE 'drop table if exists ' || tmp_name; EXECUTE 'create temp table ' || tmp_name || ' as select f_MSG_log(' || i_trn_id,i_trm_msg_type_cd,i_trm_key_cd,i_trm_obj_cd,i_trm_msg_txt,i_log_level_opt,i_trm_file_nm_opt || ');';

When I call the stored procedure, it is throwing an error saying: Amazon Invalid operation: query "SELECT 'create temp table ' || $1 || ' as select f_MSG_log(' || $2 , $3 , $4 , $5 , $6 , $7 , $8 || ');'" returned 7 columns; 1 statement failed.

Answer 1

I found an answer:

EXECUTE 'drop table if exists ' || tmp_name; EXECUTE 'create temp table ' || tmp_name || ' as select toolbox.f_MSG_log(' || i_trn_id || ',' || quote_literal(i_trm_msg_type_cd) || ',' || quote_literal(var_trm_key_cd) || ',' || quote_literal(var_trm_obj_cd) || ',' || quote_literal(var_trm_msg_txt) || ',' || i_log_level_opt || ',' || quote_literal(var_trm_file_nm_opt) || ');';