How do I remove all tables and not the schema in Postgresql?

Question

I have a schema assigned to my user (jason). I can't remove the schema as I don't have permissions to do so. Is there a nice way to remove each tables, data, everything in a schema and make it as if I had a freshly created schema. Basically the same effect as:

drop schema jason cascade;
create schema jason;

But without the actually dropping the schema.

Answer 1

The accepted answer is great, but you can do this in just one step taking advantage from anonymous blocks (PosgreSQL 9.0+):

DO $$
DECLARE 
    r record;
BEGIN
    FOR r IN SELECT quote_ident(tablename) AS tablename, quote_ident(schemaname) AS schemaname FROM pg_tables WHERE schemaname = 'public'
    LOOP
        RAISE INFO 'Dropping table %.%', r.schemaname, r.tablename;
        EXECUTE format('DROP TABLE IF EXISTS %I.%I CASCADE', r.schemaname, r.tablename);
    END LOOP;
END$$;