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" > 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 if the > table 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 4: Don't 'kill -9' the postmaster
