Re: drop table where tableName like 'backup_2007%' ?

[Richard Huxton <dev(at)archonet(dot)com>]

Tom Lane wrote:
> Note that the above is overly simplistic --- it doesn't pay attention
> to schemas, for example.

These are what I use.


BEGIN;

CREATE SCHEMA util;

CREATE OR REPLACE FUNCTION util.exec_all(objtype name, schname name, 
objname name, cmd TEXT) RETURNS text AS $$
DECLARE
	r	RECORD;
	sql TEXT;
	out TEXT;
BEGIN
	out := cmd || ': ';

	-- Tables includes views
	IF objtype = 'tables' THEN
		FOR r IN SELECT table_name AS nm FROM information_schema.tables
		WHERE table_schema=schname AND table_name LIKE objname
		LOOP
			sql := regexp_replace(cmd, E'\\?', quote_ident(schname) || '.' || 
quote_ident(r.nm));
			EXECUTE sql;
			out := out || r.nm || ' ';
		END LOOP;
	ELSIF objtype = 'sequences' THEN
		FOR r IN SELECT sequence_name AS nm FROM information_schema.sequences
		WHERE sequence_schema=schname AND sequence_name LIKE objname
		LOOP
			sql := regexp_replace(cmd, E'\\?', quote_ident(schname) || '.' || 
quote_ident(r.nm));
			EXECUTE sql;
			out := out || r.nm || ' ';
		END LOOP;
	END IF;

	RETURN out;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION util.grant_all(objtype name, schname name, 
objname name, perms TEXT, roles TEXT) RETURNS text AS $$
DECLARE
	r	RECORD;
	sql TEXT;
	g   TEXT;
BEGIN
	g := perms || ' ON ' || schname || '( ';

	-- Tables includes views
	IF objtype = 'tables' THEN
		FOR r IN SELECT table_name AS nm FROM information_schema.tables
		WHERE table_schema=schname AND table_name LIKE objname
		LOOP
			sql := 'GRANT ' || perms || ' ON TABLE ' || quote_ident(schname) || 
'.' || quote_ident(r.nm) || ' TO ' || roles;
			-- RAISE NOTICE 'granting: %', sql;
			EXECUTE sql;
			g := g || r.nm || ' ';
		END LOOP;
	ELSIF objtype = 'sequences' THEN
		FOR r IN SELECT sequence_name AS nm FROM information_schema.sequences
		WHERE sequence_schema=schname AND sequence_name LIKE objname
		LOOP
			sql := 'GRANT ' || perms || ' ON SEQUENCE ' || quote_ident(schname) 
|| '.' || quote_ident(r.nm) || ' TO ' || roles;
			-- RAISE NOTICE 'granting: %', sql;
			EXECUTE sql;
			g := g || r.nm || ' ';
		END LOOP;
	END IF;
	g := g || ') TO ' || roles;

	RETURN g;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION util.grant_all(objtype name, schname name, objname 
name, perms TEXT, roles TEXT) IS
$$Pattern-matching permission granter.
	objtype  - (tables|sequences) where "tables" includes views
	schname  - target schema (NOT wildcarded)
	objname  - wildcard (_%) name to match
	perms    - permissions to grant
	roles    - comma-separated list of roles to grant perms to.
$$;


CREATE OR REPLACE FUNCTION util.revoke_all(objtype name, schname name, 
objname name, perms TEXT, roles TEXT) RETURNS text AS $$
DECLARE
	r	RECORD;
	sql TEXT;
	g   TEXT;
BEGIN
	g := perms || ' ON ' || schname || '( ';

	-- Tables includes views
	IF objtype = 'tables' THEN
		FOR r IN SELECT table_name AS nm FROM information_schema.tables
		WHERE table_schema=schname AND table_name LIKE objname
		LOOP
			sql := 'REVOKE ' || perms || ' ON TABLE ' || quote_ident(schname) || 
'.' || quote_ident(r.nm) || ' FROM ' || roles;
			-- RAISE NOTICE 'granting: %', sql;
			EXECUTE sql;
			g := g || r.nm || ' ';
		END LOOP;
	ELSIF objtype = 'sequences' THEN
		FOR r IN SELECT sequence_name AS nm FROM information_schema.sequences
		WHERE sequence_schema=schname AND sequence_name LIKE objname
		LOOP
			sql := 'REVOKE ' || perms || ' ON SEQUENCE ' || quote_ident(schname) 
|| '.' || quote_ident(r.nm) || ' FROM ' || roles;
			-- RAISE NOTICE 'granting: %', sql;
			EXECUTE sql;
			g := g || r.nm || ' ';
		END LOOP;
	END IF;
	g := g || ') TO ' || roles;

	RETURN g;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION util.revoke_all(objtype name, schname name, objname 
name, perms TEXT, roles TEXT) IS
$$Pattern-matching permission revoker. See grant_all(...) for details.
$$;


CREATE OR REPLACE FUNCTION util.drop_all_roles(pattern name) RETURNS 
TEXT AS $$
DECLARE
	r    RECORD;
	sql  TEXT;
	res  TEXT;
BEGIN
	res := 'Dropped: ';
	FOR r IN SELECT rolname FROM pg_roles WHERE rolname LIKE pattern
	LOOP
		sql := 'DROP ROLE ' || quote_ident(r.rolname);
		res := res || r.rolname || ' ';
		EXECUTE sql;
	END LOOP;
	res := substr(res, 1, length(res)-1);

	RETURN res;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION util.drop_all_roles(name) IS
$$Drop all roles matching the supplied pattern.
$$;

COMMIT;

--
  Richard Huxton
  Archonet Ltd