Postgresql – Perform update and raise notice in Postgres Function

functionspostgresqlpostgresql-9.6trigger

I'm trying to add a notice (or something similar) to a function that is executed on update by a trigger in PostgreSQL 9.6 db. I want to let the user who edited the table that something happened.
I don't know is "NOTICE" is the proper RAISE (Why not "INFO"?).

The central question is: how I can add the raise notice argument after the update?

Best Answer

The problem can be, that level for messages is too high

so try this appproach

CREATE TABLE id ( "id1" INT)

INSERT INTO id VALUES (1)

1 rows affected

do
$$
declare
  newnumer int = 90;
begin
   
  UPDATE id SET id1 =  newnumer WHERE id1 = 1;
SET client_min_messages TO INFO;
  raise info   '"NOTICE" is the proper RAISE (Why not "INFO"?)' ;
        -- catch notification
      
end;
$$
language plpgsql;

✓

SELECT * FROM id;

| id1 |
| --: |
|  90 |

db<>fiddle here

Related Solutions

Postgresql – Automate update columns in a view with trigger function in plpgsql

I was able to solve my problem in a more manual way. It took some time to complete for all columns but at least it is done. Heres an example, of what I did.

-- update col_d
        IF (NEW.col_d != OLD.col_d) THEN
          UPDATE poi
          SET col_d = NEW.col_d
          WHERE poi_id = NEW.poi_id;
          RAISE NOTICE 'col_d was % , changed to %',OLD.col_d,NEW.col_d;
        END IF;
-- in case col_d is NULL
        IF OLD.col_d IS NULL AND NEW.col_d IS NOT NULL THEN
          UPDATE poi
          SET col_d = NEW.col_d
          WHERE poi_id = NEW.poi_id;
          RAISE NOTICE 'col_d updated NULL to %', NEW.col_d;
        END IF;

Postgresql – How to access to other INSERTed/UPDATEd rows from trigger in PostgreSQL

See the docs, you should be able to access the old and new records from a statement trigger:

CREATE TRIGGER some_table_update_trigger
  AFTER UPDATE ON some_table
  REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
  FOR EACH STATEMENT
  EXECUTE PROCEDURE do_something_with_newtab_and_oldtab();

Best Answer

Related Solutions

Postgresql – Automate update columns in a view with trigger function in plpgsql

Postgresql – How to access to other INSERTed/UPDATEd rows from trigger in PostgreSQL

Related Question