DROP TABLE tb_messages; CREATE TABLE tb_messages ( .... );
That should do it. Save that as a text file, for example 'test.sql', from an editor. Then, start up psql:
psql databasename
and type at the psql prompt (where test.sql is in the current directory):
\i test.sql
Sean
----- Original Message ----- From: "Craig Bryden" <postgresql@xxxxxxxxxxxx>
To: "Sean Davis" <sdavis2@xxxxxxxxxxxx>; "pgsql" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Monday, May 02, 2005 8:05 AM
Subject: Re: scripts in Postgres
Hi
Thanks for the reply. Since I don't intend using any of the interfaces at the moment, How would I write the script below in SQL then. Please keep in mind that I will be sending this script to other people to run and that it needs to be totally automated.
Thanks Craig
----- Original Message ----- From: "Sean Davis" <sdavis2@xxxxxxxxxxxx>
To: "Craig Bryden" <postgresql@xxxxxxxxxxxx>; "pgsql"
<pgsql-general@xxxxxxxxxxxxxx>
Sent: Monday, May 02, 2005 1:57 PM
Subject: Re: scripts in Postgres
Yes, pl/pgsql needs to be written as a function. If you want to "script"the
things, that is done in SQL or via one of the interfaces for perl, python,
java, etc. You can just do the DROP TABLE, ignore the error message iftable doesn't exist, and then create the table. The documenation is quite
good, so check out:
http://www.postgresql.org/docs/8.0/interactive/server-programming.html
for many examples.
Sean
----- Original Message ----- From: "Craig Bryden" <postgresql@xxxxxxxxxxxx>
To: "pgsql" <pgsql-general@xxxxxxxxxxxxxx>
Sent: Monday, May 02, 2005 7:21 AM
Subject: scripts in Postgres
> Hi
>
> I am fairly new to Postgres and am struggling to understand one > concept.
> If
> I wish to use pl/pgsql, must it be in a function?
> An example of where I would not want it to be in a function is:
>
> I have a CREATE TABLE statement that I want to execute. But the script
> must
> first check for the existence of the table. I wrote the following code,
> but
> it errors on the first word (IF). Please tell me how to do this:
>
>
> IF EXISTS (SELECT * FROM information_schema.tables WHERE table_Name =
> 'tb_Messages')
> DROP TABLE tb_Messages;
>
> CREATE TABLE tb_Messages (
> MessageID bigserial PRIMARY KEY,
> From varchar(255),
> To varchar(255),
> DateSent timestamp not null DEFAULT current_timestamp,
> Subject varchar(255) NULL,
> MessageBody Text null,
> IsRead smallint default 0,
> DeleteFlag smallint default 0,
> AdditionalInfo int NULL,
> ReplyToMessage bigint NULL
>
> ) WITHOUT OIDS;
>
> Thanks
> Craig
>
>
> ---------------------------(end of > broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
