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

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