Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

How to create a Function to truncate all Tables created by Particular User

In this post, I am sharing a PostgreSQL user define a function to truncate all Tables which are created by particular X User.
Sometimes, Database administrator or Database Developer creates a user only for test purpose, and this TEST user created an N number of test rows or tables which are required drop or truncate.
Using this function, you can truncate all tables of a database with the help of Owner and Schema input parameters.
CREATE OR REPLACE FUNCTION fn_TruncateAllTable
(
 InputUserName CHARACTER VARYING 
 ,InputSchemaName CHARACTER VARYING 
) RETURNS void AS $$
DECLARE
 statements CURSOR FOR
 SELECT tablename FROM pg_tables
 WHERE tableowner = InputUserName AND schemaname = InputSchemaName;
BEGIN
    FOR stmt IN statements LOOP
 EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;'; 
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL Sequence

Postgresql maximum size

How to configure Replication Manager (repmgr) ?

PostgreSQL pgBadger