Re: scripts in Postgres - Mailing list pgsql-general

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