Skip site navigation (1) Skip section navigation (2)

Re: Conditionally executing multiple statements in series as single SQL statement

From: Sean Davis <sdavis2(at)mail(dot)nih(dot)gov>
To: "Oliveiros C," <oliveiros(dot)cristina(at)marktest(dot)pt>
Cc: Nathaniel Trellice <naptrel(at)yahoo(dot)co(dot)uk>, "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Conditionally executing multiple statements in series as single SQL statement
Date: 2009-12-18 14:06:26
Message-ID: 264855a00912180606i41e57427mc5c5291bff07bd12@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
On Fri, Dec 18, 2009 at 8:45 AM, Oliveiros C,
<oliveiros(dot)cristina(at)marktest(dot)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(at)mail(dot)nih(dot)gov>
> To: "Nathaniel Trellice" <naptrel(at)yahoo(dot)co(dot)uk>
> Cc: <pgsql-novice(at)postgresql(dot)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(at)yahoo(dot)co(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>
>

In response to

pgsql-novice by date

Next:From: Marco CraveiroDate: 2009-12-18 19:22:28
Subject: Problems with stored procedure (function)
Previous:From: Oliveiros C,Date: 2009-12-18 13:45:24
Subject: Re: Conditionally executing multiple statements in series as single SQL statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group