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@scw.org>
Director of Information Systems, Support Center of Washington

Thanks for the response Joel,

At 05:50 PM 5/2/2001 -0400, you wrote:

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.

Any postgresql hackers out there who'd like to arm me with this dangerous
knowledge - you won't be held accountable ;)

Andy.

PS - Thanks for making postgresql 7.1 - does it support pl/perl functions
being called by triggers?

Joel Burton <jburton@scw.org> writes:

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.

Quite ;-). The tables would still be there on your disk, but you
couldn't get to them anymore.

regards, tom lane