everyday [un] happenings

while working on a script, i discovered how to use SAVEPOINTS inside a PostgreSQL Transaction.

typical PostgreSQL Transactions looks like this:

BEGIN;
INSERT/UPDATE/DELETE STATEMENTS;
IF ERROR THEN
ROLLBACK;
ELSE
COMMIT;
END IF;

sample postgreSQL Transaction with SAVEPOINT:

BEGIN;
SAVEPOINT my_svpnt;
INSERT STATEMENT 1;
IF ERROR THEN
ROLLBACK TO SAVEPOINT my_svpnt;
END IF;
INSERT STATEMENT 2;
RELEASE SAVEPOINT my_svpnt;
COMMIT;

what do you do if you want to commit some statements or just ignore some errors before committing everything? knowing that inside a transaction all succeeding statements after an error will be ignored.

sample scenario:

loop thru checkboxes from a HTML POST then INSERT or UPDATE rows.
problem then arises because i am too lazy to write long PHP codes -- i don't want to check each rows from a HTML POST to determine if what i need to do is INSERT or UPDATE. that is, to INSERT if the row doesn't exist or UPDATE if it exist.

there's an easy way, i can check the primary key -- i don't want to do that either.

i need to know if transactions can be done in such a way where i can decide which statements to commit.

test first if each rows exists in the table:

// begin postgreSQL transaction
$conn->execute('BEGIN');
// loop thru checkboxes, rows from HTML POST
FOR ($i = 0; $i <= $rownum; $i++) {  // check if checkbox is checked
IF ($checkbox[$i] == 1) {
// check if row exist or is not yet in the table
$checkRowSQL = "SELECT * FROM pay_schedule             
                WHERE
                pay_schedule_id = {$n_pay_schedule_id[$i]}";
// pg_query
$rsChk = $conn->execute($checkRowSQL);
// get result set from SELECT query
$result->set_resultset($rsChk);
// EOF() - SELECT returns 0 rows then INSERT new row
IF ($result->EOF()) {
$sql = "INSERT INTO pay_schedule
    (pay_schedule_code, pay_schedule_remarks, pay_schedule_schedule_id)
    VALUES
    ('$s_pay_schedule_code','$n_pay_schedule_remarks[$i]', $n_pay_schedule[$i])";
$rs = $conn->execute($sql);
// if INSERT returns an error, break from FOR loop
IF (!$rs) {
$header = 'Unable to Insert Pay Scheme';
$message = 'Check entries...';
break;
}
// not EOF() - SELECT returns a row then UPDATE row
} ELSE {
$sql = "UPDATE pay_schedule SET
          pay_schedule_code = '{$s_pay_schedule_code}',
          pay_schedule_remarks = '{$n_pay_schedule_remarks[$i]}',
          pay_schedule_schedule_id = {$n_pay_schedule[$i]}
          WHERE
          pay_schedule_id = {$n_pay_schedule_id[$i]}";
$rs = $conn->execute($sql);
IF (!$rs) {
// if UPDATE returns an error, break from FOR loop
$header = 'Unable to Update Pay Scheme';
$message = 'Check entries...';
break;
}
}
}
}
// no error then COMMIT everything
IF ($rs) {
$conn->execute('COMMIT');
$header = 'Update/Add Confirmation';
$message = 'Pay Scheme Updated/Added';
// if there's an error then ROLLBACK all changes
} ELSE {
$conn->execute('ROLLBACK');
}
$commandname = 'Ok';
$command = 'fnReturn()'; // return to original page

using SAVEPOINT my code looks like this:

// begin postgreSQL transaction
$conn->execute('BEGIN');
// loop thru checkboxes, rows from HTML POST
FOR ($i = 0; $i <= $rownum; $i++) { // check if checkbox is checked 
if ($checkbox[$i] == 1) {
// establish a SAVEPOINT
$conn->execute('SAVEPOINT bgn_svpnt');
// try to INSERT row
$sql = "INSERT INTO pay_schedule
   (pay_schedule_code, pay_schedule_remarks, pay_schedule_schedule_id)
   VALUES
   ('$s_pay_schedule_code','$n_pay_schedule_remarks[$i]', $n_pay_schedule[$i])";
// pg_query
$rs = $conn->execute($sql);
/* if INSERT error, i.e duplicate row or constraint violation
     do a ROLLBACK TO bgn_svpnt savepoint then UPDATE existing row
  */
IF (!$rs) {
$conn->execute('ROLLBACK TO SAVEPOINT bgn_svpnt');
$sql = "UPDATE pay_schedule SET
         pay_schedule_code = '{$s_pay_schedule_code}',
         pay_schedule_remarks = '{$n_pay_schedule_remarks[$i]}',
         pay_schedule_schedule_id = {$n_pay_schedule[$i]}
         WHERE
         pay_schedule_id = {$n_pay_schedule_id[$i]}";
$rs = $conn->execute($sql);
// if ERROR still then cancel FOR loop and ROLLBACK everything
IF (!$rs) {
$header = 'Unable to Update Pay Scheme';
$message = 'Check entries...';
break;
}
}
// good to do a RELEASE if savepoint is not needed anymore
$conn->execute('RELEASE SAVEPOINT bgn_svpnt');
}
}
IF ($rs) {
$conn->execute('COMMIT');
$header = 'Update/Add Confirmation';
$message = 'Pay Scheme Updated/Added';
} ELSE {
$conn->execute('ROLLBACK');
}
$commandname = 'Ok';
$command = 'fnReturn()';

i settled with the second code since it will eliminate unnecessary lame SELECTs, wherein i have to retrieve and check result sets.