Truncating all tables in a Postgres database

by

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');

More Related Contents:

  1. INSERT with dynamic table name in trigger function
  2. Execute a dynamic crosstab query
  3. Dynamic SQL (EXECUTE) as condition for IF statement
  4. Test for null in function with varying parameters
  5. Return SETOF rows from PostgreSQL function
  6. Error when setting n_distinct using a plpgsql variable
  7. Dynamically generate columns for crosstab in PostgreSQL
  8. Function to loop through and select data from multiple tables
  9. Format specifier for integer variables in format() for EXECUTE?
  10. How does the search_path influence identifier resolution and the “current schema”
  11. Refactor a PL/pgSQL function to return the output of various SELECT queries
  12. How do you find the row count for all your tables in Postgres
  13. What are ‘$$’ used for in PL/pgSQL
  14. The forgotten assignment operator “=” and the commonplace “:=”
  15. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  16. How to set value of composite variable field using dynamic SQL
  17. PostgreSQL parameterized Order By / Limit in table function
  18. How do I do large non-blocking updates in PostgreSQL?
  19. User defined variables in PostgreSQL
  20. Returning from a function with OUT parameter
  21. Postgres FOR LOOP
  22. Use variable set by psql meta-command inside of DO block
  23. COPY with dynamic file name
  24. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  25. SQL Sub queries in check constraint
  26. Cursor based records in PostgreSQL
  27. Select from a table variable
  28. Loop on tables with PL/pgSQL in Postgres 9.0+
  29. plpgsql error “RETURN NEXT cannot have a parameter in function with OUT parameters” in table-returning function
  30. Loop through columns of RECORD

Leave a Comment