How to create a function to delete all database records with Postgresql?

Question

I need to create a script to delete all records from my database in all tables, without deleting the tables and restarting the sequence. I know I should use the TRUNCATE or the DELETE. My bank has several schemes and each scheme has its tables. I started doing the function as follows:

CREATE OR REPLACE FUNCTION public.funcao_zerar_banco_dados()
RETURNS VOID AS 
$$
DECLARE
tabelas RECORD;
BEGIN
    FOR tabelas IN
    SELECT table_name FROM information_schema.tables
    LOOP
        EXECUTE 'TRUNCATE ' || tabelas.table_name || ' RESTART IDENTITY';
    END LOOP;
END
$$ LANGUAGE plpgsql

But when executing the function presents the following error:

SQL Error [42501]: ERROR: permission denied: "pg_statistic" is a system Catalog Where: SQL statement "TRUNCATE pg_statistic RESTART IDENTITY" function PL/pgsql function_zerar_banco_data() line 8 in EXECUTE

I tried that other way too:

CREATE OR REPLACE FUNCTION public.funcao_zerar_banco_dados()
RETURNS VOID AS 
$$
DECLARE
tabelas RECORD;
BEGIN
    FOR tabelas IN
        SELECT schema_name FROM information_schema.schemata
    LOOP
        EXECUTE 'TRUNCATE TABLE ' || tabelas.schema_name || ' RESTART IDENTITY';
    END LOOP;
END
$$ LANGUAGE plpgsql

But another error appears:

SQL Error [42P01]: ERROR: relation "pg_toast" does not exist Where: SQL statement "TRUNCATE TABLE pg_toast RESTART IDENTITY" function PL/pgsql funcao_zerar_banco_data() line 9 in EXECUTE

If anyone knows how to help me, I’d be grateful.

Answer 1

With the help of the comments I was able to finish my job. Follow:

CREATE OR REPLACE FUNCTION public.funcao_zerar_banco_dados()
RETURNS VOID AS 
$$
DECLARE
tabelas RECORD;
sequencia RECORD;
BEGIN
    -- SELECIONA TODAS OS ESQUEMAS QUE NÃO SÃO 'NATIVOS' POSTGRESQL
    FOR tabelas IN
        SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema <> 'information_schema' 
        AND table_schema <> 'pg_catalog' AND table_type = 'BASE TABLE'
    LOOP
        RAISE NOTICE 'Tabelas: %', tabelas.table_schema|| '.' || tabelas.table_name;
        -- EXCLUI OS DADOS EM CASCATA, CONCATENA O NOME DO ESQUEMA E DA TABELA
        EXECUTE 'TRUNCATE ' || tabelas.table_schema || '.' || tabelas.table_name || ' CASCADE';
    END LOOP;
        -- REINICIAR SEQUENCIAS
        FOR sequencia IN
            SELECT sequence_schema, sequence_name FROM information_schema.sequences
        LOOP
            PERFORM SETVAL(sequencia.sequence_schema || '.' || sequencia.sequence_name, 1, false);
        END LOOP;
    RETURN;
RETURN;
END
$$ LANGUAGE plpgsql

I restarted the sequences using SETVAL because the database contains sequences that are not table fields, but there is also a way to do it using RESTART IDENTITY as long as the sequences are a table field, as follows:

CREATE OR REPLACE FUNCTION public.funcao_zerar_banco_dados()
RETURNS VOID AS 
$$
DECLARE
tabelas RECORD;
sequencia RECORD;
BEGIN
    -- SELECIONA TODAS OS ESQUEMAS QUE NÃO SÃO 'NATIVOS' POSTGRESQL
    FOR tabelas IN
        SELECT table_schema, table_name FROM information_schema.tables WHERE table_schema <> 'information_schema' 
        AND table_schema <> 'pg_catalog' AND table_type = 'BASE TABLE'
    LOOP
        RAISE NOTICE 'Tabelas: %', tabelas.table_schema|| '.' || tabelas.table_name;
        -- EXCLUI OS DADOS EM CASCATA, CONCATENA O NOME DO ESQUEMA E DA TABELA E REINICIA AS SEQUENCIAS
        EXECUTE 'TRUNCATE ' || tabelas.table_schema || '.' || tabelas.table_name || ' 
 RESTART IDENTITY CASCADE';
        END LOOP;
    RETURN;
    END
    $$ LANGUAGE plpgsql