Find and Drop XNAT views

drop_xnat_views.sql

-- Change 'xnat' to the database user that owns the XNAT tables.
-- You need to stop Tomcat before running this query.
SELECT drop_user_views('xnat');

find_and_drop_xnat_views.sql

CREATE OR REPLACE FUNCTION find_user_views(username TEXT)
  RETURNS TABLE(table_schema NAME, view_name NAME) AS $$
BEGIN
  RETURN QUERY
  SELECT
    n.nspname AS table_schema,
    c.relname AS view_name
  FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n
      ON (n.oid = c.relnamespace)
  WHERE c.relkind = 'v'
        AND c.relowner = (SELECT usesysid
                          FROM pg_catalog.pg_user
                          WHERE usename = $1);
END$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION drop_user_views(username TEXT)
  RETURNS INTEGER AS $$
DECLARE
  r RECORD;
  s TEXT;
  c INTEGER := 0;
BEGIN
  RAISE NOTICE 'Dropping views for user %', $1;
  FOR r IN
    SELECT * FROM find_user_views($1)
  LOOP
    S := 'DROP VIEW IF EXISTS ' || quote_ident(r.table_schema) || '.' || quote_ident(r.view_name) || ' CASCADE;';
    EXECUTE s;
    c := c + 1;
    RAISE NOTICE 's = % ', S;
  END LOOP;
  RETURN c;
END$$ LANGUAGE plpgsql;

find_xnat_views.sql

-- Change 'xnat' to the database user that owns the XNAT tables.
SELECT * FROM find_user_views('xnat');