Thread: problem with function to report how many records were changed
Hi, I intend to get some answer from postgres concerning if an update command has altered some records in the database. To do this I coded this function: create or replace function upd_ok(text,text,text,text) returns integer as ' declare table alias for $1; field alias for $2; val1 alias for $3; val2 alias for $4; begin execute ''update '' || quote_ident(table) || '' set '' || quote_ident(field) '' = '' || quote_literal(val1) || '' where '' || quote_ident(field) || '' = '' || quote_literal(val2); if found then raise notice ''ok''; else raise exception ''not found''; end if; return 1; end; ' language 'plpgsql'; --- This function takes four parameters for the update command and then warn the user if the update command has performed any change. The problem is I tested this function with tables, fields and values correct, and the function still reports that no record was altered. Issuing the update manually the changes are made. I think this is kind of strange, so I'm sending this to the list. I am using postgresql version 7.2.3 If there are any unimplemented features, how else could I know if an update command altered records? Thanks in advance, Enio
<enio@pmpf.rs.gov.br> writes: > execute ''update '' || quote_ident(table) || '' set '' || > quote_ident(field) '' = '' || quote_literal(val1) || > '' where '' || quote_ident(field) || '' = '' || > quote_literal(val2); > if found then I don't believe EXECUTE sets FOUND. You'll need to do something else, perhaps use FOR IN EXECUTE. > I am using postgresql version 7.2.3 You'll probably also need to use a newer Postgres release; IIRC 7.2 didn't set FOUND properly in for-loops. regards, tom lane
You should look at the GET_DIAGNOSTICS functionality provided by Postgres There it supports ROWCOUNT and this gives you the number of rows affected by the UPDATE command HTH Darren On Wed, 15 Oct 2003 enio@pmpf.rs.gov.br wrote: > Hi, > I intend to get some answer from postgres concerning if an update command > has altered some records in the database. > To do this I coded this function: > > create or replace function upd_ok(text,text,text,text) returns integer > as ' > declare > table alias for $1; > field alias for $2; > val1 alias for $3; > val2 alias for $4; > begin > execute ''update '' || quote_ident(table) || '' set '' || > quote_ident(field) '' = '' || quote_literal(val1) || > '' where '' || quote_ident(field) || '' = '' || > quote_literal(val2); > if found then > raise notice ''ok''; > else > raise exception ''not found''; > end if; > return 1; > end; > ' > language 'plpgsql'; > > --- > > This function takes four parameters for the update command and then warn the > user if the update command has performed any change. The problem is I tested > this function with tables, fields and values correct, and the function still > reports that no record was altered. Issuing the update manually the changes > are made. I think this is kind of strange, so I'm sending this to the list. > I am using postgresql version 7.2.3 > If there are any unimplemented features, how else could I know if an update > command altered records? > > Thanks in advance, > Enio > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Darren Ferguson