Skip to content
Advertisement

raise notice update query result in postgresql

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 = 'support@gmail.com' 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

Advertisement

Answer

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 = 'hell@gmail.com' 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$
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement