Re: DROP TABLE wildcard

On Wed, 2 May 2001, Andy Koch wrote:

> Is it possible to drop multiple tables with SQL on the system tables:
>
> I tried this:
>
> DROP TABLE from pg_tables where tablename LIKE 'table_num_%';
>
> Which for whatever reason would delete 0 items despite it should have
> matched on several.
>
> Of course I'm not even sure pg_tables would be the smart place to make the
> delete from?

First of all, DROP TABLE doesn't use SELECT SQL syntax. There is no
DROP TABLE ... FROM ...

Second, pg_tables is a *view* of pg_class. If you want to make
changes, make them to pg_class.

That said, though, I don't think you want to drop tables by just deleting
the rows from pg_class. You might want to hear what the hackers have to
say about the subject, but I'm assuming its a Bad Idea.

No, there is no wildcharacter in the DROP TABLE syntax.

You could, though, make a plpgsql function that dropped tables,
and took a text parameter that it used as a regex. Then you could
SELECT dev_drop_table('tblfoo.*').

(or, instead of regex-able param, do a like-able param)

--
Joel Burton <jburton(at)scw(dot)org>
Director of Information Systems, Support Center of Washington