function – 截断PostgreSQL中模式中的所有表

发布时间：2020-12-13 16:04:14 所属栏目：百科来源：网络整理

导读：参见英文答案 Truncating all tables in a Postgres database????????????????????????????????????9个我试图使用PostgreSQL截断模式中的所有表.它显示此错误： ERROR: relation "Building" does not existCONTEXT: SQL statement "TRUNCATE TABLE "Buildin

参见英文答案 > Truncating all tables in a Postgres database????????????????????????????????????9个
我试图使用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;

怎么做得好？

解决方法

试试这样

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!