使用存储过程删除架构中的旧行

Question

有没有办法在一个过程中选择架构中的所有表并删除不符合条件(早于某个日期(的行？我可以使用两个单独的查询来做同样的事情，它看起来像是：SELECT table_name FROM information_schema.tables WHERE table_schema = 'schemaName'，然后是DELETE FROM table_name WHERE time < now()-'12 months'::interval;"，但我无法思考如何使用一个存储过程来完成同样的操作，我认为我应该在某种类型的选择查询中使用for循环，但由于我从未真正使用过postgres中的循环，所以我在尝试执行此操作时总是会遇到某种类型的异常。任何帮助都非常感谢

Answer 1

通常可以使用CTE:组合SELECT和DELETE语句

WITH list AS
( SELECT ...
FROM ...
WHERE ...
RETURNING ...
)
DELETE FROM ...
USING list
WHERE ...

除了在您的情况下，SELECT语句的返回参数与必须删除行的表的名称相对应之外。这意味着表名在运行时之前是未知的，这意味着在plpgsqlFUNCTION:中使用动态sql命令

CREATE OR REPLACE FUNCTION delete_rows_from_table(table_name text)
RETURNS void LANGUAGE plpgsql AS
$$
BEGIN
EXECUTE E'
DELETE FROM ' || quote_ident(table_name) || E'
WHERE time < now()- '12 months':: interval' ;
END ;
$$ ;

然后，您可以在SELECT语句中使用FUNCTIONdelete_rows_from_table：

SELECT delete_rows_from_table(table_name)
FROM information_schema.tables
WHERE table_schema = 'schemaName'