7

Consider a situation where one need to perform a bunch of essentially identical operations, with the only variable being the name of some object.

In my case, I need to drop some schemas, all of the form ceu_shard_test_merge_*, to use shell globbing terminology. So, conceptually, this can be written as

DROP SCHEMA ceu_shard_test_merge_* CASCADE;

by analogy with a Unix shell.

Of course this command doesn't work, so how can one do this with a single command? My understanding is that this cannot be done portably. I'm using PostgreSQL 8.4, but methods for more recent versions of PG are fine too.

It would be nice if the solution had a dry run or dummy option, so one could see what commands were going to be run before actually running them. Perhaps a way to just print the commands?

Also, an indication of how to deal with more general patterns than the example given would be nice.

2 Answers 2

10

This query on the system catalog generates the necessary DDL script:

SELECT string_agg(format('DROP SCHEMA %I CASCADE;', nspname), E'\n')
FROM   pg_namespace
WHERE  nspname LIKE 'ceu_shard_test_merge_%';

Note the use of format() to escape identifiers if necessary.

For Postgres 8.4, that would be:

SELECT array_to_string(
          array_agg('DROP SCHEMA ' || quote_ident(nspname) || ' CASCADE;')
        , E'\n')
FROM ...

Returns:

DROP SCHEMA ceu_shard_test_merge_1 CASCADE;
DROP SCHEMA ceu_shard_test_merge_2 CASCADE;
...

Inspect the result before executing in turn.

You can wrap all into a DO command for automatic execution or create a function for repeated use. Consider this closely related code example:

Or this related answer on SO.

Output in psql

To address @Stew's comment: display unadorned text as result in psql with the \pset meta-command:

\pset format unaligned
\pset tuples_only

Or use the short commands \x \a

5
  • Thanks very much for the detailed answer, Erwin. The alternatives when using more recent versions of PG are particularly interesting to me. Can you give an example when escaping identifiers would be necessary? Would special characters or spaces in the schema name qualify? Apr 28, 2013 at 15:52
  • @FaheemMitha: All of these qualify: upper case letters, white space, reserved words, special characters. Consider this worst case: CREATE SCHEMA "; DELETE FROM accounts; --". But careful with that! More on SQL injection here. Apr 28, 2013 at 17:05
  • 1
    A reminder that _, in a LIKE parameter (e.g. LIKE 'foo_%' is a single-character wildcard. To match the literal underscore, you must escape it: LIKE 'foo\_%'.
    – Stew
    Oct 27, 2015 at 18:43
  • @ErwinBrandstetter, when I execute your answer in psql, I get output like: pastebin.com/raw.php?i=vhNRmUVK Do you know how I can avoid the extraneous + that psql seems to want to append?
    – Stew
    Oct 27, 2015 at 18:48
  • 1
    @Stew: You could use a space (' ') instead of the newline (E'\n') character in the query to get a single line. Or modify the output options of psql. I added a bit to my answer. Oct 28, 2015 at 10:56
2

You could write a procedure with the EXECUTE and build the statement in the procedure by looping over the list of schema which match your query from the information_schema tables.

Then you can call your procedure from psql or other passing it your criteria such as 'ceu_shard_test_merge_%'.

You could have a parameter to do it or just dry run and instead of execute then it could output the statements or something along those lines.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.