Обсуждение: A very simple question about rollback/commit
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]
It is that code for knowing that there was an error and doing the Rollback that I can’t remember how to do. I looked through the PostgreSQL Documentation for an example but I couldn’t find it. It has been a very long time since I wrote SQL code.
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?
Thanks to those that will help me with a simple solution.
Greg Della-Croce
Applications Administrator
Greg_Della-Croce@Wycliffe.org
SKYPE: GDellaCroce55
Cell: 407-408-2572
THINK ON THIS:
"All that is valuable in human society depends upon the opportunity for development accorded the individual" ~ Albert Einstein
“All this is valuable in Christ’s Kingdom depens upon the opportunity for development accorded the individual by the Holy Spirit to Glorify the Father” ~ Greg Della-Croce
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