Thread: SQL Script Question

I'm trying to write SQL script to create tables as follows:

--------------------------------
drop table xxx;
create table xxx (
......
);
grant all on xxx to public;
--------------------------------

Before dropping the table, I'd like to check if the table exists as the
following psuedo code:

if <table exists>   drop table xxx;

How can i write this 'if' condition? Or are there any other ways to
check this??

Thanks in advance.

> if <table exists>
>     drop table xxx;
> 
> How can i write this 'if' condition? Or are there any other ways to
> check this??

This SELECT tells you whether a table exists or not:

SELECT tablename FROM pg_tables WHERE tablename='searched_table_name';

With this you can check the condition. Then you may write a PLPGSQL
function which can decide if there was any found table with
searched_table_name or not, see the PLPGSQL example in the src/plpgsql 
library.

Then you may call an SQL function (you should write it first) from the
PLPGSQL function which drops table. As I know, you cannot drop a table
inside a PLPGSQL function (only if you call an external SQL function).

I hope this helps. If not, ask for details.

Regards,
Zoltan