title: Drop all tables in a Redshift schema - without dropping permissions
body: <p>I would be interested to drop all tables in a Redshift schema. Even though this solution works</p>
  
  <pre><code>DROP SCHEMA public CASCADE;
  CREATE SCHEMA public;
  </code></pre>
  
  <p>is <strong>NOT</strong> good for me since that it drops SCHEMA permissions as well. </p>
  
  <p>A solution like </p>
  
  <pre><code>DO $$ DECLARE
  r RECORD;
  BEGIN
      -- if the schema you operate on is not "current", you will want to
      -- replace current_schema() in query with 'schematodeletetablesfrom'
      -- *and* update the generate 'DROP...' accordingly.
      FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
          EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
      END LOOP;
  END $$;
  </code></pre>
  
  <p>as reported in this thread <a href="https://stackoverflow.com/questions/3327312/how-can-i-drop-all-the-tables-in-a-postgresql-database">How can I drop all the tables in a PostgreSQL database?</a>
  would be ideal. Unfortunately it doesn't work on Redshift (apparently there is no support for <code>for loops</code>).</p>
  
  <p>Is there any other solution to achieve it?</p>