Hi Tom, I have a very basic question about the use of SELECT FOR UPDATE cursors especially when used with DELETE statements.Could you please explain to me why someone would want to get an an exclusive lock on the rows that are to be deleted ?I'll re-run the pf31 block again as an AUTONOMOUS (sub or nested transaction -- runs AS IF it were in another session) transaction to show that it would be blocked. WORLD declare 2 PRAGMA AUTONOMOUS_TRANSACTION; 3 cursor c1 is 4 select PRS_WOO_PRCS_ID,prs_sts 5 from prcs_sts 6 where PRS_WOO_PRCS_ID = 'PF32' 7 for update of prs_sts; 8 begin 9 for c1_rec in c1 loop 10 update prcs_sts 11 set prs_sts = 'Y' 12 where current of c1; 13 end loop; 14 COMMIT; 15 end; 16 / PL/SQL procedure successfully completed. The only difference I can see in your plsql and mine is the kind of data that was selected for update.I modified my WHERE clause to include all the columns of my primary key in the table (PRS_BSNS_SGMNT, PRS_BSNS_SUB_SGMNT, PRS_DATE, PRS_WOO_PRCS_ID). declare cursor c1 is select PRS_BSNS_SGMNT, PRS_BSNS_SUB_SGMNT, PRS_DATE, PRS_WOO_PRCS_ID,prs_sts from prcs_sts where PRS_BSNS_SGMNT = 'FRX' AND PRS_BSNS_SUB_SGMNT = 'USDINR' AND PRS_DATE = '28-MAR-02' AND PRS_WOO_PRCS_ID = 'PF31' for update of prs_sts; begin for c1_rec in c1 loop update prcs_sts set prs_sts = 'Y' where current of c1; end loop; end; / PL/SQL procedure successfully completed.
Now I am simply returning the whole updated table as an answer of the function (which replaced the original table with the updated one).How should I properly adjust the following line of code? Would postgres create the fully-fledged SQL query (with all the syntactic and semantic checking, plan computation and so on) to identify and update the record, or it tells postgres, that it could use the pointer to the data pointed out by the cursor to update one field in it? It's dynamic SQL, which has to be planned and executed.If it wasn't dynamic, the query plan would saved and reused.I then run this PL/SQL script: declare cursor cur is select t.id, from my_table t order by 1; begin for cur_row in cur loop if (cur_= 'test') then dbms_output.put_line('everything is fine!'); else dbms_output.put_line('error error error!!!!! '); exit; end if; end loop; end; / A cursor effectively runs a SELECT and then lets you iterate over the result set, which is kept in a snapshot of the DB state.