红移存储过程无法按预期工作

Question

我在红移中创建并成功运行了存储过程，但没有按预期工作。例如，我想删除由参数设置的周期内的数据。

—存储过程

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;

——运行存储过程

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

,结果

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

脚本运行成功，但记录' 20121-01-16 '和' 20121-01-17 '仍然留在该表中

任何建议将不胜感激。提前谢谢。

Answer 1

多亏了@John Rotenstein，现在我可以按预期运行存储过程了。这只是一个有同样问题的人的简单例子。

—修订程序

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;

—已执行命令

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

——返回消息

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