From: Richard Huxton <dev@archonet.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: emilu@encs.concordia.ca
Cc: pgsql-sql@postgresql.org
Subject: Re: drop table where tableName like 'backup_2007%' ?
Date: Mon, 31 Mar 2008 17:27:00 +0100
Message-ID: <47F110D4.40904@archonet.com> (raw)
In-Reply-To: <28893.1206978914@sss.pgh.pa.us>
References: <47F0F9BB.3060706@encs.concordia.ca>
<28893.1206978914@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, tgl@sss.pgh.pa.us, emilu@encs.concordia.ca
Subject: Re: drop table where tableName like 'backup_2007%' ?
In-Reply-To: <47F110D4.40904@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox