Re: A very simple question about rollback/commit

On Mon, 2012-07-09 at 11:09 -0400, Della-Croce, Gregory wrote:
> I am new to working with PostgreSQL and need to create a pgSQL process
> that I can run against a 8.4 PostgreSQL database. The code will need
> to look something like:
>
> BEGIN;
>
> SAVEPOINT sp1;
>
> UPDATE location SET delete = 1 WHERE delete=0 RETRUNING count;
>
> [code to rollback if there is an error or commit if everything was
> good]

...

> Oh, another small question, since this is not being embedded in an
> program like C#, VB, etc, the two tool I have are pgADMIN III and SSH.
> Can this be done in pgADMIN III?

Because you are using 8.4, you will need to define a function, and I
recommend PL/pgSQL:

http://www.postgresql.org/docs/8.4/static/sql-createfunction.html
http://www.postgresql.org/docs/8.4/static/plpgsql.html

In particular, look at:

http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

That last one will tell you about the BEGIN/EXCEPTION block, which is I
think what you want. Internally, that uses savepoints so you don't have
to. (In fact, it might be impossible to use savepoints in PL/pgSQL, and
you might have to use the BEGIN/EXCEPTION block).

If PL/pgSQL is not already installed, you may need to create it in your
database first:

http://www.postgresql.org/docs/8.4/static/app-createlang.html
or
http://www.postgresql.org/docs/8.4/static/sql-createlanguage.html

If you use later versions of postgresql, then PL/pgSQL is installed by
default, and you can use the a "DO" block rather than creating a
function:

http://www.postgresql.org/docs/9.1/static/sql-do.html

I know that's a lot of information all at once, but I hope it's helpful.

Regards,
Jeff Davis