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, May 19 '11 at 05:44

score 2 · Answer 1 · edited Aug 25 '21 at 12:06

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;

Truncate all tables in postgres database

1 Answers1