截断Postgres数据库中的所有表 [英] Truncating all tables in a Postgres database
本文介绍了截断Postgres数据库中的所有表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在重建之前,我通常需要从PostgreSQL数据库中删除所有数据.我将如何直接在SQL中执行此操作?
I regularly need to delete all the data from my PostgreSQL database before a rebuild. How would I do this directly in SQL?
目前,我设法提出了一条SQL语句,该语句返回我需要执行的所有命令:
At the moment I've managed to come up with a SQL statement that returns all the commands I need to execute:
SELECT 'TRUNCATE TABLE ' || tablename || ';' FROM pg_tables WHERE tableowner='MYUSER';
但是一旦有了它们,我就看不到以编程方式执行它们的方法.
But I can't see a way to execute them programmatically once I have them.
推荐答案
FrustratedWithFormsDesigner是正确的,PL/pgSQL可以做到这一点.这是脚本:
FrustratedWithFormsDesigner is correct, PL/pgSQL can do this. Here's the script:
CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
statements CURSOR FOR
SELECT tablename FROM pg_tables
WHERE tableowner = username AND schemaname = 'public';
BEGIN
FOR stmt IN statements LOOP
EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';
END LOOP;
END;
$$ LANGUAGE plpgsql;
这将创建一个存储的函数(您只需执行一次),之后就可以像下面这样使用:
This creates a stored function (you need to do this just once) which you can afterwards use like this:
SELECT truncate_tables('MYUSER');
这篇关于截断Postgres数据库中的所有表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文