截断PostgreSQL中的模式中的所有表 [英] Truncating all the tables in a schema in PostgreSQL

问题描述

我试图使用PostgreSQL截断模式中的所有表。它显示这个错误：

 错误：关系Building不存在
 CONTEXT：SQL语句TRUNCATE TABLE 构建CASCADE
 PL / pgSQL函数truncate_schema（字符变化）第15行EXECUTE语句
  

这里是我使用的函数：

$ pre code> CREATE OR REPLACE FUNCTION truncate_schema（schema IN VARCHAR）RETURNS void AS $$
DECLARE
语句CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type ='BASE TABLE'AND table_schema = schema;
BEGIN
对于语句IN语句LOOP
EXECUTE'TRUNCATE TABLE'|| quote_ident（stmt.table_name）|| 'CASCADE';
END LOOP;
END;
$$语言plpgsql;

如何正确操作？

解决方案

试试这样

  CREATE OR REPLACE FUNCTION truncate_schema（_schema characters varying）
 RETURNS void AS 
 $ BODY $ 
申报
 selectrow记录; 
开始
在
中选择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 
循环
执行selectrow.qry; 
结束循环; 
 end; 
 $ BODY $ 
语言plpgsql 
  

I am trying to truncate all the tables in a schema using PostgreSQL. It is showing this error:

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?

解决方案

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

这篇关于截断PostgreSQL中的模式中的所有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！