create a procedure based on column data change in postgrersql

From: Jian He <hejian(dot)mark(at)gmail(dot)com>
To: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: create a procedure based on column data change in postgrersql
Date: 2021-10-19 13:45:50
Message-ID: CAMV54g3+kRnBca0WunJKEinTNgeq=y9-bXcGGNtd--DrCc3TbQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

https://stackoverflow.com/questions/25435669/fire-trigger-on-update-of-columna-or-columnb-or-columnc/25436314#25436314
This is very useful and interesting. I can see it's very useful. I am not
sure if it's costly or not?

begin;
create temp table account_details(email text primary key, username
text, password text);
insert into account_details(email, username, password)
values('a.com','b','c'),('b.com','d','e');
commit;

CREATE TRIGGER trigger_update_account_details
AFTER UPDATE ON account_details
FOR EACH ROW
WHEN (OLD.email IS DISTINCT FROM NEW.email
OR OLD.username IS DISTINCT FROM NEW.username
OR OLD.password IS DISTINCT FROM NEW.password)
EXECUTE PROCEDURE notify_insert_account_details();

________________________________________________________________________

What possible PROCEDURE notify_insert_account_details() can be.

The following is my immature test code.

CREATE OR REPLACE FUNCTION notify_insert_account_details()
RETURNS trigger
LANGUAGE plpgsql AS
$$
BEGIN
RAISE NOTICE 'hello world';
END
$$;

_______________________________________________________

when I update then error occurs. *the update clause didn't execute.
the function did fired.*

NOTICE: hello world
> ERROR: control reached end of trigger procedure without RETURN
> CONTEXT: PL/pgSQL function notify_insert_account_details()

______________________________________________
*I am wondering how sophisticated this procedure/function
notify_insert_account_details() can become, let's say linked within 3
table. *
*Can anyone showcase an example/demo? *

I also asked same question on stackoverflow. Since it's more easy to
search. (https://stackoverflow.com/questions/69631945/create-a-procedure-based-on-column-data-change-in-postgrersql)

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message aditya desai 2021-10-19 13:48:04 Re: Query out of memory
Previous Message Michael Lewis 2021-10-19 13:39:34 Re: Query out of memory