By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,835 Members | 2,333 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,835 IT Pros & Developers. It's quick & easy.

problem with function to report how many records were changed

P: n/a
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)

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
<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

Nov 12 '05 #2

P: n/a
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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.