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

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@bryden.co.za>
To: "pgsql" <pgsql-general@postgresql.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

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@mail.nih.gov>
To: "Craig Bryden" <postgresql@bryden.co.za>; "pgsql"
<pgsql-general@postgresql.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@bryden.co.za>
To: "pgsql" <pgsql-general@postgresql.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

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

Hi Sean

Thanks for that. Does psql work differently to pgAmin III's Query program?
I
have tried exactly what you showed me below, and it did not work. It seems
like the script stops on first error, and the first error is that the
table
does not exist.

Thanks
Craig

----- Original Message -----
From: "Sean Davis" <sdavis2@mail.nih.gov>
To: "Craig Bryden" <postgresql@bryden.co.za>; "pgsql"
<pgsql-general@postgresql.org>
Sent: Monday, May 02, 2005 2:11 PM
Subject: Re: [GENERAL] 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@bryden.co.za>
To: "Sean Davis" <sdavis2@mail.nih.gov>; "pgsql"
<pgsql-general@postgresql.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@mail.nih.gov>
To: "Craig Bryden" <postgresql@bryden.co.za>; "pgsql"
<pgsql-general@postgresql.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@bryden.co.za>
To: "pgsql" <pgsql-general@postgresql.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