Truncating all tables in a Postgres database
database-table, dynamic-sql, plpgsql, postgresql, truncate
Solution
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');
Problem
I regularly need to delete all the data from my PostgreSQL database before a rebuild. How would I do this directly in 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.