title: Postgres: Drop all tables that belong to a specific role (or force dropping a role, ignoring dependent objects)
body: <p>In a Bash-script, i want to drop a Postgres user role. But Postgres does not let me to that, i'm getting <code>Cannot drop table X because other objects depend on it</code>.</p>
  
  <p>So i want to drop all tables that depend on that role to be able to remove the role. Therefore i wrote a Postgres function (my first one, inspired by some posts) which should remove all tables that belong to a specific role. </p>
  
  <p>This is the Bash script containing the function, and tries to apply that function to the role <code>bob</code>:</p>
  
  <pre><code>#!/bin/bash
  
  sudo su - postgres -c "psql -d postgres -U postgres" &lt;&lt; 'EOF'
  CREATE LANGUAGE plpgsql;
  CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
  DECLARE
      statements CURSOR FOR
          SELECT tablename FROM pg_tables
          WHERE tableowner = username;
  BEGIN
      FOR stmt IN statements LOOP
          EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ';';
      END LOOP;
  END;
  $$
  LANGUAGE plpgsql;
  EOF
  
  sudo su - postgres -c "psql -d postgres -U postgres -c \"SELECT truncate_tables('bob');\""
  </code></pre>
  
  <p>I don't get any errors, but the script has no effect - the tables that belong to the role are not dropped. This is the output:</p>
  
  <pre><code>CREATE FUNCTION
   truncate_tables 
  -----------------
  
  (1 row)
  </code></pre>
  
  <p>Where is the error in my function? Or are there other ways to force dropping a role, ignoring dependent objects?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I also tried to insert a <code>DROP OWNED BY bob;</code> before the removal, but there are still objects depending on that role which prevent the deletion of it.</p>