problem with function to report how many records were changed

[enio]

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 ma*******@postgresql.org)

[Tom Lane]

<en**@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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[darren]

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 en**@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 ma*******@postgresql.org)

--
Darren Ferguson
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend