Re: How to specify that a trigger should fire when column is NOT in SET-clause?

2020-12-25 Thread Adrian Klaver 

On 12/25/20 10:19 AM, Andreas Joseph Krogh wrote:
På fredag 25. desember 2020 kl. 17:48:39, skrev Adrian Klaver 
mailto:adrian.kla...@aklaver.com>>:


On 12/25/20 5:52 AM, Andreas Joseph Krogh wrote:
 > Hi.
 > I need to set a value in a trigger if a column is explicitly NOT
 > specified in UPDATE's SET-clause.
 > Like for example having a "BEFORE UPDATE OF NOT"
 >
 > create TRIGGER my_trigger
 >      BEFORE UPDATE OF NOT modified ON my_table FOR EACH ROW WHEN
(OLD.val <>NEW.val)
 > EXECUTE PROCEDURE do_stuff();
 >
 > I want the trigger to be fired when the column "modified" is NOT
 > specified, is it possible?

It will always be specified, it may or may not be changed. As example:

True, but what I'm after is using the value from the "modified" column, 
if specified, else use CURRENT_TIMESTAMP

My use-case is this;
I have this table:

create table person (
 id serial primary key,
 username varchar not null unique,
 password varchar not null,
 credentials_last_updated timestamp NOT NULL default CURRENT_TIMESTAMP,
 created timestamp NOT NULL default CURRENT_TIMESTAMP,
 modified timestamp );

Then this trigger to update "credentials_last_updated" whenever "password" is 
modified.

create or replace FUNCTION person_password_updated_tf()returns TRIGGER AS $$ BEGIN 
NEW.credentials_last_updated =NEW.modified;-- OR CURRENT_TIMESTAMP if 
"modified" isn't specified RETURN NEW;
END;
$$ LANGUAGE plpgsql;

create TRIGGER person_password_updated_tBEFORE UPDATE OF password ON onp_user FOR 
EACH ROW WHEN (OLD.password <>NEW.password )
EXECUTE PROCEDURE person_password_updated_tf();

So, I want to set "credentials_last_updated to NEW.modified if 
"modified" is specified, else to CURRENT_TIMESTAMP


Because of this:

Is UPDATE the same as DELETE + INSERT in PostgreSQL?

https://www.cybertec-postgresql.com/en/is-update-the-same-as-delete-insert-in-postgresql/

you will always get a NEW.modified. There is no way(AFAIK) to determine 
whether the value came from a SET or just came in as part of the NEW tuple.


All you can do is compare the OLD and NEW values of modified to see if 
it changed, using the IS DISTINCT FROM from Tom's post to deal with NULL 
values.


Not sure why modified is there anyway. Why not just use 
credentials_last_updated and set it to CURRENT_TIMESTAMP whenever the 
password is changed?



--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How to specify that a trigger should fire when column is NOT in SET-clause?

2020-12-25 Thread Andreas Joseph Krogh 

På fredag 25. desember 2020 kl. 17:48:39, skrev Adrian Klaver <
adrian.kla...@aklaver.com >: 
On 12/25/20 5:52 AM, Andreas Joseph Krogh wrote:
 > Hi.
 > I need to set a value in a trigger if a column is explicitly NOT
 > specified in UPDATE's SET-clause.
 > Like for example having a "BEFORE UPDATE OF NOT"
 >
 > create TRIGGER my_trigger
 > BEFORE UPDATE OF NOT modified ON my_table FOR EACH ROW WHEN (OLD.val 
<>NEW.val)
 > EXECUTE PROCEDURE do_stuff();
 >
 > I want the trigger to be fired when the column "modified" is NOT
 > specified, is it possible?

 It will always be specified, it may or may not be changed. As example: 

True, but what I'm after is using the value from the "modified" column, if 
specified, else use CURRENT_TIMESTAMP 

My use-case is this; 

I have this table: 
create table person ( id serial primary key, username varchar not null unique, 
passwordvarchar not null, credentials_last_updated timestamp NOT NULL default 
CURRENT_TIMESTAMP, created timestamp NOT NULL default CURRENT_TIMESTAMP, 
modifiedtimestamp ); Then this trigger to update "credentials_last_updated" 
whenever "password" is modified.create or replace FUNCTION 
person_password_updated_tf() returns TRIGGER AS $$ BEGIN  NEW.
credentials_last_updated= NEW.modified; -- OR CURRENT_TIMESTAMP if "modified" 
isn't specified RETURN NEW; END; $$ LANGUAGE plpgsql; create TRIGGER 
person_password_updated_tBEFORE UPDATE OF password ON onp_user FOR EACH ROW WHEN
(OLD.password <> NEW.password ) EXECUTE PROCEDURE person_password_updated_tf(); 

So, I want to set "credentials_last_updated to NEW.modified if "modified" is 
specified, else toCURRENT_TIMESTAMP 



-- 
Andreas Joseph Krogh 
CTO / Partner - Visena AS 
Mobile: +47 909 56 963 
andr...@visena.com  
www.visena.com

Re: How to specify that a trigger should fire when column is NOT in SET-clause?

2020-12-25 Thread Tom Lane 
Adrian Klaver  writes:
> On 12/25/20 5:52 AM, Andreas Joseph Krogh wrote:
>> Or - is it possible to check for this in the trigger-function?

> As David Johnson mentioned you can check whether the value for the 
> column is changed:

> NEW.animal <> OLD.animal

Better to use IS DISTINCT FROM, to get sane behavior when one or
the other value is NULL.

regards, tom lane

Re: How to specify that a trigger should fire when column is NOT in SET-clause?

2020-12-25 Thread Adrian Klaver 

On 12/25/20 5:52 AM, Andreas Joseph Krogh wrote:

Hi.
I need to set a value in a trigger if a column is explicitly NOT 
specified in UPDATE's SET-clause.

Like for example having a "BEFORE UPDATE OF NOT"

create TRIGGER my_trigger
 BEFORE UPDATE OF NOT modified ON my_table FOR EACH ROW WHEN (OLD.val 
<>NEW.val)
EXECUTE PROCEDURE do_stuff();

I want the trigger to be fired when the column "modified" is NOT 
specified, is it possible?


It will always be specified, it may or may not be changed. As example:

  Table "public.animals"
 Column |  Type  | Collation | Nullable | Default
++---+--+-
 id | integer|   | not null |
 cond   | character varying(200) |   | not null |
 animal | character varying(200) |   | not null |

CREATE OR REPLACE FUNCTION public.trg_test()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
RAISE NOTICE 'New id is: %, Old id is: %', NEW.id, OLD.id;
RAISE NOTICE 'New cond is: %, Old cond is: %', NEW.cond, OLD.cond;
RAISE NOTICE 'New animal is: %,  Old animal is: %', NEW.animal, 
OLD.animal;

RETURN NEW;
END;
$function$
;

update animals set cond = 'skinny' where id = 4;
NOTICE:  New id is: 4, Old id is: 4
NOTICE:  New cond is: skinny, Old cond is: slim
NOTICE:  New animal is: dog,  Old animal is: dog
UPDATE 1




Or - is it possible to check for this in the trigger-function?


As David Johnson mentioned you can check whether the value for the 
column is changed:


NEW.animal <> OLD.animal




--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How to specify that a trigger should fire when column is NOT in SET-clause?

2020-12-25 Thread David G. Johnston 
On Friday, December 25, 2020, Andreas Joseph Krogh 
wrote:

> Hi.
>
> I need to set a value in a trigger if a column is explicitly NOT specified
> in UPDATE's SET-clause.
>
>

Beat you can probably do is rely on the fact that if its not specified it
doesn’t change and do nothing within the trigger in that case.

David J.

How to specify that a trigger should fire when column is NOT in SET-clause?

2020-12-25 Thread Andreas Joseph Krogh 

Hi. 

I need to set a value in a trigger if a column is explicitly NOT specified in 
UPDATE's SET-clause. 


Like for example having a "BEFORE UPDATE OF NOT" 
create TRIGGER my_trigger BEFORE UPDATE OF NOT modified ON my_table FOR EACH 
ROW WHEN(OLD.val <> NEW.val) EXECUTE PROCEDURE do_stuff(); 


I want the trigger to be fired when the column "modified" is NOT specified, is 
it possible? 
Or - is it possible to check for this in the trigger-function? 

-- 
Andreas Joseph Krogh 
CTO / Partner - Visena AS 
Mobile: +47 909 56 963 
andr...@visena.com  
www.visena.com