使用PostgreSQL函数上的变量删除模式

Question

我正试图在PostgreSQL上创建一个函数，但我在使用局部变量时遇到了一些问题。这是我的代码：

DECLARE query RECORD;
DECLARE schema_name TEXT;
BEGIN
FOR query IN SELECT * FROM context WHERE created_at + make_interval(days => duration) <= CURRENT_TIMESTAMP LOOP
SELECT lower(quote_ident(query.title)) INTO schema_name;
DROP SCHEMA schema_name CASCADE;
DELETE FROM context WHERE id = query.id;  
END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;

select和delete查询运行良好，我已经测试了返回schema_name变量的值，这没问题。

我的问题是这条线：

DROP SCHEMA schema_name CASCADE;

我得到一个错误，因为"架构'schema_name'不存在"。如果您有任何关于如何使用此变量执行drop查询的想法，我将不胜感激。

Answer 1

您需要动态SQL来实现这一点：

DECLARE 
query RECORD;
BEGIN
FOR query IN SELECT id, lower(title) as title 
FROM context 
WHERE created_at + make_interval(days => duration) <= CURRENT_TIMESTAMP 
LOOP
execute format('DROP SCHEMA %I CASCADE', query.title);
DELETE FROM context WHERE id = query.id;  
END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;

---

我还删除了不必要的SELECT语句，使标题小写，这最好直接在查询中完成。

另外：:=的变量分配比select更快，因此：

schema_name := lower(quote_ident(query.title));

如果需要变量的话会更好。