时间戳

首页 > 解决方案 > 在 PostgreSQL 函数上使用变量来删除模式

postgresql - 在 PostgreSQL 函数上使用变量来删除模式

问题描述

我正在尝试在 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;

选择和删除查询工作正常，我做了一个测试，返回 schema_name 变量的值，没关系。

我的问题是这一行：

DROP SCHEMA schema_name CASCADE;

我收到一个错误，因为“架构‘schema_name’不存在”。我真的很感激任何关于如何使用这个变量来做 drop 查询的想法。

标签： postgresqlfunctionschemaplpgsqldynamic-sql

解决方案

为此，您需要动态 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 语句以使标题小写，最好直接在查询中完成。

:=另外：使用then 进行变量赋值更快select，所以：

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

如果需要变量会更好。

推荐阅读