This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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.ter.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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; |
