Re: How can I do conditional 'drop table' in Postgres

Dragan Matic wrote:

> if exists (select * from sysobjects where id =
> object_id(N'[dbo].[pp_fisk]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[pp_fisk]
> GO
>
> For instance, this is a valid script in Ms SQL, it will drop table
> pp_fisk only if it exists, is there a way to do something similar in
> Postgres? Tnx in advance.
>
> Dragan

I'm working from memory here, so exact syntax might be incorrect.

perform select * from pg_table where ....;
if found then
drop table ...
end if

This applies to plpgsql only. Please pay attention, however, that if you
are going to be dropping and recreating tables from this function, you
must have all queries relating this table use "execute". Otherwise, the
table's OID is going to be cached the first time code referencing this
table (in "from") is run, and subsequent runs  in the same session will
not find the table (even if you create a new table with the same name).
If that is a problem for you, consider replacing "drop table" with
"delete from table", which will delete all elements form the table, but
leave the table itself.

             Shachar

--
Shachar Shemesh
Lingnu Open Source Consulting
http://www.lingnu.com/