Truncate all tables in postgres database

Question

In May 2010, Aaron and Henning both provided the code to register a function that when called later with a parameter for 'username' would truncate all the tables. It worked fine with postgres on Windows 7. Neither will work unfortunately for postgres 8.3 on Ubuntu.

An error has occurred:
ERROR:  syntax error at or near "$1"
LINE 1:   $1 
          ^
QUERY:    $1 
CONTEXT:  SQL statement in PL/PgSQL function "truncate_tables" near line 6

I have also tried simplifying the select statement to focus on the BEGIN For clause, by removing the complicated WHERE clause I used in Windows.
Can you see the problem here? Thanks. Is it unable to pass or read the tablenames after they are retrieved? Doesn't a problem with $1 mean it can't find its input?

DECLARE 
    stmt RECORD;  
    statements CURSOR FOR  
    SELECT tablename FROM pg_tables  
    WHERE  tablename !~* 'sql_*' and tablename !~* 'pg_*' and tablename !~* 'schema_*';  
BEGIN  
    FOR stmt IN statements LOOP  
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';  
    END LOOP;  
END;

$1 does not appear in the quoted partial function, so you have cut out critical information. Please provide the full function create; — Seth Robertson
– Seth Robertson, Commented May 19, 2011 at 5:44

Isaac van Bakel · Accepted Answer · 2021-08-25 12:06:50Z

3

It appears that some of the function syntax changed between version 8.3 and 8.4. Try this:

CREATE OR REPLACE FUNCTION public.truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
    stmt RECORD;
BEGIN
    FOR stmt IN SELECT tablename FROM pg_tables
        WHERE tableowner = username AND schemaname = 'public' LOOP
        execute 'TRUNCATE TABLE public.' || quote_ident(stmt.tablename) ||' CASCADE;';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

edited Aug 25, 2021 at 12:06

Isaac van Bakel

1,88212 silver badges23 bronze badges

answered Jun 21, 2011 at 21:42

gsiems

3,8201 gold badge25 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Truncate all tables in postgres database

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related