在PostgreSQL中截断模式下所有表格

Question

我正在尝试使用PostgreSQL截断架构中的所有表。它显示以下错误：

ERROR:  relation "Building" does not exist
CONTEXT:  SQL statement "TRUNCATE TABLE "Building" CASCADE"
PL/pgSQL function truncate_schema(character varying) line 15 at EXECUTE statement

这是我使用的函数：

CREATE OR REPLACE FUNCTION truncate_schema(schema IN VARCHAR) RETURNS void AS $$
DECLARE
    statements CURSOR FOR
        SELECT table_name FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND table_schema = schema;
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.table_name) || ' CASCADE';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

如何正确地做到这一点？

- User123

Answer 1

试试这样做

CREATE OR REPLACE FUNCTION truncate_schema(_schema character varying)
  RETURNS void AS
$BODY$
declare
    selectrow record;
begin
for selectrow in
select 'TRUNCATE TABLE ' || quote_ident(_schema) || '.' ||quote_ident(t.table_name) || ' CASCADE;' as qry 
from (
     SELECT table_name 
     FROM information_schema.tables
     WHERE table_type = 'BASE TABLE' AND table_schema = _schema
     )t
loop
execute selectrow.qry;
end loop;
end;
$BODY$
  LANGUAGE plpgsql

- Vivek S.

---

太好了。它运行正常。非常感谢。 - User123

你不觉得最后一行需要加上分号吗？ - itsji10dra

谢谢，非常有帮助！ - winnie damayo

Answer 2

这很可能是因为在你的TRUNCATE语句中没有包含schema的名称，所以它会在public schema中查找表。

试着将TRUNCATE语句改为以下内容：

EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.table_schema) || '.' ||
    quote_ident(stmt.table_name) || ' CASCADE';

此外，需要记住关于 CASCADE 的一件事是它将会 TRUNCATE 任何一个表，只要该表与该表存在 外键 关系，这可能包括模式之外的表。

根据OP的评论编辑：

您还需要在 statements 查询中添加 table_schema ，以便在 EXECUTE 语句中使用。

- khampson

---

感谢您的回复。它正在生成另一个错误记录，即“stmt”没有“table_schema”字段。 - User123