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

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

From: "Oliveiros C," <oliveiros(dot)cristina(at)marktest(dot)pt>
To: "Sean Davis" <sdavis2(at)mail(dot)nih(dot)gov>,"Nathaniel Trellice" <naptrel(at)yahoo(dot)co(dot)uk>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Conditionally executing multiple statements in series as single SQL statement
Date: 2009-12-18 13:45:24
Message-ID: 7B99C58FF3494551B6A0028F952E6685@marktestcr.marktest.pt (view raw or flat)
Thread:
Lists: pgsql-novice
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

Best,
Oliveiros


----- 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

In response to

Responses

pgsql-novice by date

Next:From: Sean DavisDate: 2009-12-18 14:06:26
Subject: Re: Conditionally executing multiple statements in series as single SQL statement
Previous:From: Rory Campbell-LangeDate: 2009-12-18 13:18:45
Subject: Re: Conditionally executing multiple statements in series as single SQL statement

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