amazon web services - Redshift Stored Procedure Can't Work as Expected

Question

I created and ran successfully Stored Procedure in Redshift but not working as expected. For example, I'd like to delete data in the period set by the arguments.

-- Stored Procedure

CREATE OR REPLACE PROCEDURE sp_test(parm0 varchar(100), parm1 date, parm2 date)
AS '
BEGIN
  EXECUTE
    $_$ DELETE FROM test_table_b
    WHERE $_$|| parm0 ||$_$ 
    between $_$|| parm1 ||$_$ and $_$|| parm2 ||$_$ $_$;
end;
' language plpgsql;

-- Run Stored procedure

Begin;
Call sp_test('opsdt', '2021-01-16', '2021-01-17');
Commit;

-- Result

BEGIN executed successfully

Execution time: 0.07s
Statement 1 of 3 finished

0 rows affected
Call executed successfully

Execution time: 0.18s
Statement 2 of 3 finished

COMMIT executed successfully

Execution time: 0.13s
Statement 3 of 3 finished


Script execution finished
Total script execution time: 0.38s

Script ran successfully, but the record '2021-01-16' and '2021-01-17' is still remained in that table.

Any advice would be appreciated. Thanks in advance.

question from:https://stackoverflow.com/questions/65903872/redshift-stored-procedure-cant-work-as-expected

Answer 1

Thanks to @John Rotenstein, now I could run Stored Procedure as expected. Just simple example for someone who has the same issue.

-- Revised Procedure

CREATE OR REPLACE PROCEDURE sp_del_test(tbl_name varchar(50), col_name varchar(50), start_dt date, end_dt date)
AS $PROC$
DECLARE
    sql        VARCHAR(MAX) := '';
BEGIN

 sql := 'DELETE FROM ' || tbl_name || ' WHERE ' || col_name || ' BETWEEN ''' || start_dt || ''' AND ''' || end_dt || '''';
 
  RAISE INFO '%', sql;
  EXECUTE sql;
END;    
$PROC$ language plpgsql;

-- Executed Commands

Begin;
Call sp_del_test('test_table_b', 'opsdt', '2021-01-23', '2021-01-24');
Commit;

-- Return Message

BEGIN executed successfully

Execution time: 0.05s
Statement 1 of 3 finished

**Warnings:
DELETE FROM test_table_b WHERE opsdt BETWEEN '2021-01-23' AND '2021-01-24'**

0 rows affected
Call executed successfully

Execution time: 0.2s
Statement 2 of 3 finished

COMMIT executed successfully

Execution time: 0.12s
Statement 3 of 3 finished


Script execution finished
Total script execution time: 0.38s