pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Exceptions when 0 rows affected.
3+ messages / 2 participants
[nested] [flat]

* Exceptions when 0 rows affected.
@ 2004-05-10 13:08 Stef <svb@ucs.co.za>
  2004-05-11 06:02 ` Re: Exceptions when 0 rows affected. Andrei Bintintan <klodoma@ar-sd.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Stef @ 2004-05-10 13:08 UTC (permalink / raw)
  To: pgsql-sql

Hi all,

I've been looking on Google,the archives and documentation,
but cannot find what I'm looking for. Maybe I've read the 
answer, but it's still evading me. 

I'm working with postgres 7.3.4 using the psql client.

I want to know if it's possible to raise an error in a
transactional BEGIN->END block when e.g. an update
or delete statement affects zero rows.

I want the entire transaction block to rollback when 
this happens. I can do it in perl and java , but would 
in this instance like to load a file with SQL statements
using psql -f . Is it possible?

Kind Regards
Stefan

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Exceptions when 0 rows affected.
  2004-05-10 13:08 Exceptions when 0 rows affected. Stef <svb@ucs.co.za>
@ 2004-05-11 06:02 ` Andrei Bintintan <klodoma@ar-sd.net>
  0 siblings, 0 replies; 3+ messages in thread

From: Andrei Bintintan @ 2004-05-11 06:02 UTC (permalink / raw)
  To: Stef <svb@ucs.co.za>; pgsql-sql

Hi Stef,

Your problem depends on what interface/programming language you're using.
In a Begin End transaction you will have to verify if the returned data is
good for you so then you decide if you run at the end "COMMIT" or
"ROLLBACK".

In php (and where is compatible) I suggest this:

//this is php code:

$error = false;
dbExec("BEGIN");

//I use a switch statement so I can jump out of it with a break at any time
switch (1){
case 1:
    ...
    $sql = "Insert....";
    if (!dbExec($sql)) {$error = true; break;}//was an error?? we jump out
from switch
    ...
    $sql = "Select ....";
    if (!dbExec($sql)) {$error = true; break;}
    ...
    $sql = "Update....";
    if (!dbExec($sql)) {$error = true; break;}
    ...
}//switch

if ($error) dbExec("ROLBACK"); //an error ocured
else dbExec("COMMIT");//there was no error

Hope this helps.

Best regards,
Andy.

----- Original Message -----
From: "Stef" <svb@ucs.co.za>
To: <pgsql-sql@postgresql.org>
Sent: Monday, May 10, 2004 4:08 PM
Subject: [SQL] Exceptions when 0 rows affected.

Hi all,

I've been looking on Google,the archives and documentation,
but cannot find what I'm looking for. Maybe I've read the
answer, but it's still evading me.

I'm working with postgres 7.3.4 using the psql client.

I want to know if it's possible to raise an error in a
transactional BEGIN->END block when e.g. an update
or delete statement affects zero rows.

I want the entire transaction block to rollback when
this happens. I can do it in perl and java , but would
in this instance like to load a file with SQL statements
using psql -f . Is it possible?

Kind Regards
Stefan






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Exceptions when 0 rows affected.
@ 2004-05-13 14:49 Stef <svb@ucs.co.za>
  0 siblings, 0 replies; 3+ messages in thread

From: Stef @ 2004-05-13 14:49 UTC (permalink / raw)
  To: pgsql-sql


Andrei Bintintan mentioned :
=> Your problem depends on what interface/programming language you're using.

Yep, I tried to do it using rules or triggers, but I can't get it to do what I want 
exactly, and it's not a good idea to put any rules or triggers on my database.

I'm sticking to psql though, and managed to create the query with a nested
nullif and coalesce to make my query fail if there are 0 rows matching for
an update or delete.

Cheers
Stef



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2004-05-13 14:49 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-10 13:08 Exceptions when 0 rows affected. Stef <svb@ucs.co.za>
2004-05-11 06:02 ` Andrei Bintintan <klodoma@ar-sd.net>
2004-05-13 14:49 Re: Exceptions when 0 rows affected. Stef <svb@ucs.co.za>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox