pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddrop table where tableName like 'backup_2007%' ?
3+ messages / 3 participants
[nested] [flat]
* drop table where tableName like 'backup_2007%' ?
@ 2008-03-31 14:48 Emi Lu <emilu@encs.concordia.ca>
2008-03-31 15:55 ` Re: drop table where tableName like 'backup_2007%' ? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Emi Lu @ 2008-03-31 14:48 UTC (permalink / raw)
To: pgsql-sql
Good morning,
Is there a command to drop tables whose name begins a specific string?
For example, all backup tables begins with backup_2007:
Drop table where tableName like 'backup_2007%'?
Thanks a lot!
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: drop table where tableName like 'backup_2007%' ?
2008-03-31 14:48 drop table where tableName like 'backup_2007%' ? Emi Lu <emilu@encs.concordia.ca>
@ 2008-03-31 15:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-31 16:27 ` Re: drop table where tableName like 'backup_2007%' ? Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2008-03-31 15:55 UTC (permalink / raw)
To: emilu@encs.concordia.ca; +Cc: pgsql-sql
Emi Lu <emilu@encs.concordia.ca> writes:
> Is there a command to drop tables whose name begins a specific string?
No. The standard answer to this type of problem is to write a little
plpgsql function that scans the appropriate catalog and issues commands
constructed with EXECUTE.
for r in select relname from pg_class where relname like 'backup_2007%'
loop
execute 'DROP TABLE ' || quote_ident(r);
end loop;
Note that the above is overly simplistic --- it doesn't pay attention
to schemas, for example.
Some people prefer to just print out the constructed commands into a
file, so they can eyeball them before actually executing them.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: drop table where tableName like 'backup_2007%' ?
2008-03-31 14:48 drop table where tableName like 'backup_2007%' ? Emi Lu <emilu@encs.concordia.ca>
2008-03-31 15:55 ` Re: drop table where tableName like 'backup_2007%' ? Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-03-31 16:27 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2008-03-31 16:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: emilu@encs.concordia.ca; 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 $$
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-03-31 16:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-31 14:48 drop table where tableName like 'backup_2007%' ? Emi Lu <emilu@encs.concordia.ca>
2008-03-31 15:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-03-31 16:27 ` Richard Huxton <dev@archonet.com>
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