Re: scripts in Postgres

["Sean Davis" <sdavis2@xxxxxxxxxxxx>]

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@xxxxxxxxxxxx>
To: "Sean Davis" <sdavis2@xxxxxxxxxxxx>; "pgsql" 
<pgsql-general@xxxxxxxxxxxxxx>
Sent: Monday, May 02, 2005 8:51 AM
Subject: Re:  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@xxxxxxxxxxxx>
> To: "Craig Bryden" <postgresql@xxxxxxxxxxxx>; "pgsql"
> <pgsql-general@xxxxxxxxxxxxxx>
> Sent: Monday, May 02, 2005 2:11 PM
> Subject: 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@xxxxxxxxxxxx>
> > To: "Sean Davis" <sdavis2@xxxxxxxxxxxx>; "pgsql"
> > <pgsql-general@xxxxxxxxxxxxxx>
> > Sent: Monday, May 02, 2005 8:05 AM
> > Subject: Re:  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@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