Обсуждение: 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