在 PostgreSQL 中截断模式中的所有表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27793726/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Truncating all the tables in a schema in PostgreSQL
提问by User123
I am trying to truncate all the tables in a schema using PostgreSQL. It is showing this error:
我正在尝试使用 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
Here is the function I used:
这是我使用的函数：
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;
How to do this properly?
如何正确地做到这一点？
回答by Vivek S.
try like this
像这样尝试
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
回答by khampson
This is likely because you aren't including the name of the schema in your TRUNCATEstatement, so it's looking for the table in the publicschema.
这可能是因为您没有在TRUNCATE语句中包含架构的名称，因此它正在架构中查找表public。
Try changing the TRUNCATEstatement to something like this:
尝试将TRUNCATE语句更改为如下所示：
EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.table_schema) || '.' ||
quote_ident(stmt.table_name) || ' CASCADE';
Also, something to keep in mind about CASCADEis that it will TRUNCATEany tablethat has a foreign-keyrelationship to that table, which can include tables outside of that schema.
此外，要记住的一点CASCADE是，它会包含与该表具有外键关系的任何表，其中可以包括该模式之外的表。TRUNCATE
Edit in response to comment from OP:
编辑回应来自 OP 的评论：
You would also need to add table_schemato the query behind statementsso that it is available within the EXECUTEstatement.
您还需要添加table_schema到后面的查询中，statements以便它在EXECUTE语句中可用。
