Re: scripts in Postgres

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(at)bryden(dot)co(dot)za>
To: "Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov>; "pgsql"
<pgsql-general(at)postgresql(dot)org>
Sent: Monday, May 02, 2005 8:05 AM
Subject: Re: [GENERAL] 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(at)mail(dot)nih(dot)gov>
> To: "Craig Bryden" <postgresql(at)bryden(dot)co(dot)za>; "pgsql"
> <pgsql-general(at)postgresql(dot)org>
> Sent: Monday, May 02, 2005 1:57 PM
> Subject: Re: [GENERAL] 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(at)bryden(dot)co(dot)za>
>> To: "pgsql" <pgsql-general(at)postgresql(dot)org>
>> Sent: Monday, May 02, 2005 7:21 AM
>> Subject: [GENERAL] 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
>> >
>>
>>
>>
>>
>