In this post, I am sharing one PostgreSQL user define function to truncate all Tables which are created by particular X User.
Sometimes, Database administrator or Database Developer creates one user only for test purpose and this TEST user created an N number of test rows or tables which is require drop or truncate.
Using this function you can truncate all tables of a database with the help of Owner and Schema input parameter.
CREATE OR REPLACE FUNCTION fn_TruncateAllTable
InputUserName CHARACTER VARYING
,InputSchemaName CHARACTER VARYING
) RETURNS void AS $$
statements CURSOR FOR
SELECT tablename FROM pg_tables
WHERE tableowner = InputUserName AND schemaname = InputSchemaName;
FOR stmt IN statements LOOP
EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';
$$ LANGUAGE plpgsql;
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.