2

I have a database with many tables. Twelve of these tables start with the same prefix:

mystuff_table_1
mystuff_table_2
mystuff_table_3
mystuff_table_4
etc...

I don't want to type DROP TABLE mystuff_table_n CASCADE; over and over again, especially since I have to repeatedly drop the tables. How can I make my life easier?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
tadasajon
  • 14,276
  • 29
  • 92
  • 144
  • 2
    Your data model seems very strange. Any time you have "numbered" tables, it is a strong indication that your model isn't normalized –  Mar 18 '14 at 20:30
  • This is just an example to make my point - those aren't actually the names of my tables, my tables aren't numbered, and the question has nothing to do with normalization. Please stay focused on the topic at hand. – tadasajon Mar 19 '14 at 13:37
  • If the tables aren't numbered then why did you show us such an example? Numbered tables (or examples of those) always raise the suspicion that something about the model is wrong and maybe a single table would be totally enough - making the whole question void. When using "obfuscated" examples you shouldn't be surprised that people take your example for something else. If you had simply written "I have to drop a bunch of many, unrelated tables and I don't want to write all the names each time" - I wouldn't have questioned your model –  Mar 19 '14 at 13:51

2 Answers2

3

First of all, you can delete many tables in a single statement:

DROP TABLE mystuff_table_1, mystuff_table_2, mystuff_table_3 CASCADE;

Next, you could put all of those tables into a separate schema. Add that schema to the default search_path of your user(s), so it's all transparent.

Then all you need is:

DROP SCHEMA foo CASCADE;

If that's not short enough, create a function that executes the command.
A static SQL function:

CREATE OR REPLACE FUNCTION f_delete12()  RETURNS void AS
$func$
DROP TABLE mystuff_table_1, mystuff_table_2, mystuff_table_3, ... CASCADE;
$func$  LANGUAGE sql VOLATILE;

Or a dynamic PL/pgSQL function:

CREATE OR REPLACE FUNCTION f_delete12()
  RETURNS void AS
$func$
BEGIN
EXECUTE (
   SELECT 'DROP TABLE ' || string_agg('mystuff_table_' || g, ', ')
          || ' CASCADE'
   FROM generate_series(1,12) g  -- numbers 1 to 12
   );
END
$func$  LANGUAGE plpgsql VOLATILE;

Call:

SELECT f_delete12();

For an even more dynamic statement:
How do I drop all tables in psql (PostgreSQL interactive terminal) that starts with a common word?

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

you can create a procedure to do this automatically:

Try:

CREATE OR REPLACE FUNCTION public.p_drop_tables (
  p_start integer,
  p_end integer,
  p_table varchar
)
RETURNS void AS
$body$
DECLARE SQL VARCHAR := '';
BEGIN

FOR i IN p_start..p_end LOOP

SQL := 'DROP TABLE public.' || p_table || i || ';';

--RAISE NOTICE '%',SQL;

EXECUTE (SQL);

END LOOP; 

END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Att

abfurlan
  • 415
  • 5
  • 14