Re: drop table where tableName like 'backup_2007%' ? - Mailing list pgsql-sql

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 $$
DECLAREr    RECORD;sql TEXT;out TEXT;
BEGINout := cmd || ': ';
-- Tables includes viewsIF 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_nameLIKE 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 $$
DECLAREr    RECORD;sql TEXT;g   TEXT;
BEGINg := perms || ' ON ' || schname || '( ';
-- Tables includes viewsIF 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 viewsschname  - target
schema(NOT wildcarded)objname  - wildcard (_%) name to matchperms    - permissions to grantroles    - comma-separated
listof 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 $$
DECLAREr    RECORD;sql TEXT;g   TEXT;
BEGINg := perms || ' ON ' || schname || '( ';
-- Tables includes viewsIF 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 $$
DECLAREr    RECORD;sql  TEXT;res  TEXT;
BEGINres := 'Dropped: ';FOR r IN SELECT rolname FROM pg_roles WHERE rolname LIKE patternLOOP    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