Home > Enterprise >  raise notice update query result in postgresql
raise notice update query result in postgresql

Time:09-24

How can I log if field is got updated or not using RAISE NOTICE or some other command if available.

do
$BODY$
DECLARE 
is_updated int;
BEGIN
is_updated := UPDATE my_schema.my_table  SET support_email = '[email protected]' WHERE id = '9e7080d9-97d9-4d8b-90c5-c02bf5f04ecd';
IF is_updated > 0 THEN
     RAISE NOTICE 'Row got updated';
ELSE
    RAISE NOTICE 'No Row is updated';
END IF;
END
$BODY$

Error:

SQL Error [42601]: ERROR: syntax error at or near "."
  Position: 75

CodePudding user response:

I could something like below, but I am not sure if there another UPDATE query followed by current one, ROW_COUNT will hold new result;

do
$BODY$
DECLARE 
is_updated int;
BEGIN
UPDATE my_schema.my_table  SET support_email = '[email protected]' WHERE id = 'bf2f9e57-2c98-447e-abe3-0edf2a61185e';
get diagnostics is_updated = ROW_COUNT;

if is_updated > 0 then
     raise notice 'Row got updated';
else
    raise notice 'No Row is updated';
end if;
END
$BODY$

CodePudding user response:

You can use the special variable FOUND to check if UPDATE command, update rows

do
$BODY$
BEGIN
UPDATE my_schema.my_table  SET support_email = "[email protected]" WHERE id = "9e7080d9-97d9-4d8b-90c5-c02bf5f04ecd";
IF FOUND THEN
     RAISE NOTICE 'Row got updated';
ELSE
    RAISE NOTICE 'No Row is updated';
END IF;
END
$BODY$
  • Related