Re: Conditionally executing multiple statements in series as single SQL statement - Mailing list pgsql-novice
From | Sean Davis |
---|---|
Subject | Re: Conditionally executing multiple statements in series as single SQL statement |
Date | |
Msg-id | 264855a00912180606i41e57427mc5c5291bff07bd12@mail.gmail.com Whole thread Raw |
In response to | Re: Conditionally executing multiple statements in series as single SQL statement ("Oliveiros C," <oliveiros.cristina@marktest.pt>) |
List | pgsql-novice |
On Fri, Dec 18, 2009 at 8:45 AM, Oliveiros C, <oliveiros.cristina@marktest.pt> wrote: > Sean, I am not sure if > what Nathaniel needs is > really a transaction. > > The concept of transaction IIRC has just two possible outcomes, Either > everything is executed or nothing is executed. > > But it seems that he needs do_first_thing() and do_second_thing() to be > executed if, e.g. do_third_thing() fails. do_forth_thing() should not be > executed, in this scenario, but the first two actions do. > > If we bracket these actions in a transaction nothing would be executed if > any of the actions fail, but I guess Nataniel needs the previous actions to > be executed (and not the next). > > Nataniel, am I correctly undestanding the background of your question? > > I 'm not realizing if this can be done in pure SQL, but it should be easy to > be done in pqplsql or other procedural language Good point--my bad. SAVEPOINTS might still be helpful, though, if used in conjunction with the transaction. Sean > ----- Original Message ----- > From: "Sean Davis" <sdavis2@mail.nih.gov> > To: "Nathaniel Trellice" <naptrel@yahoo.co.uk> > Cc: <pgsql-novice@postgresql.org> > Sent: Friday, December 18, 2009 1:16 PM > Subject: Re: [NOVICE] Conditionally executing multiple statements in series > as single SQL statement > > > On Fri, Dec 18, 2009 at 8:01 AM, Nathaniel Trellice <naptrel@yahoo.co.uk> > wrote: >> Hi all, >> >> In C, and many other programming languages, statements like the following >> are popular: >> >> int status = (do_first_thing() && do_second_thing() && do_third_thing() && >> do_fourth_thing()); >> >> With this kind of expression, the program calls the function >> 'do_first_thing'. If, and only if, that returns non-zero, >> 'do_second_thing' will be executed. Again, if and only if that returns >> non-zero, 'do_third_thing' is executed. Etc. >> >> In other words, later statements will only be executed if all before them >> have 'gone well'. When a statement 'fails', no further expressions are >> executed.. The variable 'status' is non-zero if, and only if, all four >> things were successfully executed. >> >> For convenience, I'd really like to be able to achieve similar behaviour >> within an SQL statement, i.e. present multiple statements (such as INSERT >> statements) and only execute the later ones if the earlier ones have been >> executed without error. And I'd like to be able to present all the >> statements within a single, compound SQL statement to the database. >> >> Is such a thing possible, using any fancy SQL syntactic tricks? > > No tricks necessary. What you are describing is called a transaction. > > CREATE TABLE testing ( > id integer, > name text unique > ); > > BEGIN; > INSERT INTO testing(id,name) values (1,'Bob'); > INSERT INTO testing(id,name) values (2,'Joe'); > INSERT INTO testing(id,name) values (3,'Sally'); > COMMIT; > > BEGIN; > INSERT INTO testing(id,name) values (4,'Ann'); > -- the next statement will cause an error > -- due to violation of the unique constraint > INSERT INTO testing(id,name) values (5,'Bob'); > -- We do a rollback, which will put the database > -- back into the state it was in just before the > -- second BEGIN statement > ROLLBACK; > > SELECT * FROM TESTING; > > See the documentation and Google about transactions. > > Sean > > -- > Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-novice > >
pgsql-novice by date: