Trim all tables in a schema in PostgreSQL

Question

I am trying to trim all tables in a schema using PostgreSQL. It shows this error:

ERROR:  relation "Building" does not exist
CONTEXT:  SQL statement "TRUNCATE TABLE "Building" CASCADE"
PL/pgSQL function truncate_schema(character varying) line 15 at EXECUTE statement

Here is the function I used:

CREATE OR REPLACE FUNCTION truncate_schema(schema IN VARCHAR) RETURNS void AS $$
DECLARE
    statements CURSOR FOR
        SELECT table_name FROM information_schema.tables
WHERE table_type = 'BASE TABLE' AND table_schema = schema;
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.table_name) || ' CASCADE';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

How to do it right?

Answer 1

try it

CREATE OR REPLACE FUNCTION truncate_schema(_schema character varying)
  RETURNS void AS
$BODY$
declare
    selectrow record;
begin
for selectrow in
select 'TRUNCATE TABLE ' || quote_ident(_schema) || '.' ||quote_ident(t.table_name) || ' CASCADE;' as qry 
from (
     SELECT table_name 
     FROM information_schema.tables
     WHERE table_type = 'BASE TABLE' AND table_schema = _schema
     )t
loop
execute selectrow.qry;
end loop;
end;
$BODY$
  LANGUAGE plpgsql

Answer 2

This is most likely because you are not including the schema name in your statement TRUNCATE, so it is looking for a table in the schema public.

Try changing the statement TRUNCATEto the following:

EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.table_schema) || '.' ||
    quote_ident(stmt.table_name) || ' CASCADE';

, -, CASCADE, , TRUNCATE , , .

OP:

table_schema statements, EXECUTE.