Thread: scripts in Postgres

scripts in Postgres

From
"Craig Bryden"
Date:
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


Re: scripts in Postgres

From
"Sean Davis"
Date:
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
>



Re: scripts in Postgres

From
"Craig Bryden"
Date:
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
> >
>
>
>
>


Re: scripts in Postgres

From
"Sean Davis"
Date:
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
>> >
>>
>>
>>
>>
>



Re: scripts in Postgres

From
"Craig Bryden"
Date:
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
> >> >
> >>
> >>
> >>
> >>
> >
>
>
>
>


Re: scripts in Postgres

From
Tino Wildenhain
Date:
Am Montag, den 02.05.2005, 14:05 +0200 schrieb Craig Bryden:
> 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.

How do you "run" it? if via psql, just drop the tables
unconditionally and ignore the errors.

(Or maybe you better want to drop/create the entire db)

There is no flow control in plain sql.

Regards
Tino


Re: scripts in Postgres

From
"Sean Davis"
Date:
Craig,

I think that pgAdmin III submits each block of SQL as a single block, so if
something has an error, it will rollback the entire query.  Someone might
correct me on this, but I think it is the case (I don't use pgAdmin III).  I
don't know what OS you are using, but you can use shell scripting with psql
to ENTIRELY automate the process--a pretty nice feature compared to pgAdmin.
Also, I don't know what your ultimate goal is, so you need to decide what
works for you.  If you really don't want the error, then you will have to
write a function to have the "drop if exists" functionality.  See here.

http://archives.postgresql.org/pgsql-novice/2004-10/msg00158.php

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:51 AM
Subject: Re: [GENERAL] scripts in Postgres


> 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
>> >> >
>> >>
>> >>
>> >>
>> >>
>> >
>>
>>
>>
>>
>



Re: scripts in Postgres

From
"Rolf Østvik"
Date:
postgresql@bryden.co.za ("Craig Bryden") wrote in
news:001601c54f15$b9d32fe0$0200a8c0@amd2800:

> 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.
>

Could it be that it pgAdmin creates a transaction.
What happens if you set in some begin and commit.
(Just a wild suggestion, not tested in any way).
----------------------------------------
begin;
DROP TABLE tb_messages;
commit;
begin;
CREATE TABLE tb_messages (
....
);
commit;

--
Rolf

Re: scripts in Postgres

From
Sean Davis
Date:
On May 3, 2005, at 2:50 AM, Rolf Østvik wrote:

> postgresql@bryden.co.za ("Craig Bryden") wrote in
> news:001601c54f15$b9d32fe0$0200a8c0@amd2800:
>
>> 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.
>>
>
> Could it be that it pgAdmin creates a transaction.
> What happens if you set in some begin and commit.
> (Just a wild suggestion, not tested in any way).
> ----------------------------------------
> begin;
> DROP TABLE tb_messages;
> commit;
> begin;
> CREATE TABLE tb_messages (
> ....
> );
> commit;

That was my suspicion, also.  I haven't tried to confirm it, though.
(I'm a MacOS user.)

Sean