Message-ID: <47F110D4.40904@archonet.com>
Date: Mon, 31 Mar 2008 17:27:00 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.12 (X11/20080213)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: emilu@encs.concordia.ca, pgsql-sql@postgresql.org
Subject: Re: drop table where tableName like 'backup_2007%' ?
References: <47F0F9BB.3060706@encs.concordia.ca>
	<28893.1206978914@sss.pgh.pa.us>
In-Reply-To: <28893.1206978914@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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