批量清空指定用户的表

    CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)
      RETURNS void
      LANGUAGE plpgsql AS
    $func$
    BEGIN
     EXECUTE  
      (SELECT 'TRUNCATE TABLE '
           || string_agg(format('%I.%I', schemaname, tablename), ', ')
           || ' CASCADE'
       FROM   pg_tables
       WHERE  tableowner = _username
       AND    schemaname = 'public'
       );
    END
    $func$;
复制

• 实操

[postgres@node01 ~]$ psql -d test
psql (14.5)
Type "help" for help.

test=# create table t1(id int);
CREATE TABLE
test=# create table t2(id int); 
CREATE TABLE
test=# insert into t1 values(1),(2);
INSERT 0 2
test=# insert into t2 values(1),(2),(3); 
INSERT 0 3
test=# select * from t1;
 id 
----
  1
  2
(2 rows)

test=# select * from t2;
 id 
----
  1
  2
  3
(3 rows)

test=# CREATE OR REPLACE FUNCTION f_truncate_tables(_username text)
test-#   RETURNS void
test-#   LANGUAGE plpgsql AS
test-# $func$
test$# BEGIN
test$#  EXECUTE  
test$#   (SELECT 'TRUNCATE TABLE '
test$#        || string_agg(format('%I.%I', schemaname, tablename), ', ')
test$#        || ' CASCADE'
test$#    FROM   pg_tables
test$#    WHERE  tableowner = _username
test$#    AND    schemaname = 'public'
test$#    );
test$# END
test$# $func$;
CREATE FUNCTION
test=# 
test=# select f_truncate_tables('postgres');
 f_truncate_tables 
-------------------
 
(1 row)

test=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
 public | t2   | table | postgres
(2 rows)

test=# select * from t1;
 id 
----
(0 rows)

test=# select * from t2;
 id 
----
(0 rows)

复制

参考:
https://stackoverflow.com/questions/2829158/truncating-all-tables-in-a-postgres-database/12082038#12082038