By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,203 Members | 1,560 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,203 IT Pros & Developers. It's quick & easy.

How can I do conditional 'drop table' in Postgres

P: n/a
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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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/
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.