Thread: DROP TABLE wildcard

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?

Thanks.

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

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

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?