Thread: Trigger for modification timestamp column
Hello! I am trying to write a trigger for updating a modification column and am having some trouble getting it to behave as I want. The trigger should set the column to the supplied value if it is set in the UPDATE statement and to the current timestamp [NOW()] if it is not. The problem is that I don't know how to check if the column is set or not. I can check the column's value for NULL but that doesn't work if I want the column to accept NULL values (meaning "unmodified"). I would like something like: CREATE FUNCTION update_modified() RETURNS TRIGGER AS $$ BEGIN IF NOT isset(NEW.modified) THEN NEW.modified = NOW(); END IF; RETURN NEW; END; $$ LANGUAGE 'plpgsql'; Does anyone know how to do this? Thanks in advance! / Johan -- View this message in context: http://old.nabble.com/Trigger-for-modification-timestamp-column-tp29096359p29096359.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
2010/7/7 Johan Andersson <warb@mail.com>: > > Hello! > > I am trying to write a trigger for updating a modification column and am > having some trouble getting it to behave as I want. > > The trigger should set the column to the supplied value if it is set in the > UPDATE statement and to the current timestamp [NOW()] if it is not. The > problem is that I don't know how to check if the column is set or not. I can > check the column's value for NULL but that doesn't work if I want the column > to accept NULL values (meaning "unmodified"). > > I would like something like: > CREATE FUNCTION update_modified() > RETURNS TRIGGER AS $$ > BEGIN > IF NOT isset(NEW.modified) THEN > NEW.modified = NOW(); > END IF; > RETURN NEW; > END; > $$ LANGUAGE 'plpgsql'; > > Does anyone know how to do this? sorry - it isn't possible - you cannot do it. You need one column more for information if value is modified or not. Regards Pavel Stehule > > Thanks in advance! > > / Johan > -- > View this message in context: http://old.nabble.com/Trigger-for-modification-timestamp-column-tp29096359p29096359.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Can't you check it using something like this: IF OLD.modified = NEW.modified THEN NEW.modified = NOW(); END IF; RETURN NEW; Susan -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Johan Andersson Sent: Wednesday, July 07, 2010 6:49 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Trigger for modification timestamp column Hello! I am trying to write a trigger for updating a modification column and am having some trouble getting it to behave as I want. The trigger should set the column to the supplied value if it is set in the UPDATE statement and to the current timestamp [NOW()] if it is not. The problem is that I don't know how to check if the column is set or not. I can check the column's value for NULL but that doesn't work if I want the column to accept NULL values (meaning "unmodified"). I would like something like: CREATE FUNCTION update_modified() RETURNS TRIGGER AS $$ BEGIN IF NOT isset(NEW.modified) THEN NEW.modified = NOW(); END IF; RETURN NEW; END; $$ LANGUAGE 'plpgsql'; Does anyone know how to do this? Thanks in advance! / Johan -- View this message in context: http://old.nabble.com/Trigger-for-modification-timestamp-column-tp29096359p29096359.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
2010/7/7 Susan Cassidy <scassidy@stbernard.com>: > Can't you check it using something like this: > > IF OLD.modified = NEW.modified THEN > NEW.modified = NOW(); > END IF; > RETURN NEW; but this take only case where column modified was changed. There are not a functionality for detection if user explicitly updated column or not Pavel > > Susan > -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Johan Andersson > Sent: Wednesday, July 07, 2010 6:49 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Trigger for modification timestamp column > > > Hello! > > I am trying to write a trigger for updating a modification column and am > having some trouble getting it to behave as I want. > > The trigger should set the column to the supplied value if it is set in the > UPDATE statement and to the current timestamp [NOW()] if it is not. The > problem is that I don't know how to check if the column is set or not. I can > check the column's value for NULL but that doesn't work if I want the column > to accept NULL values (meaning "unmodified"). > > I would like something like: > CREATE FUNCTION update_modified() > RETURNS TRIGGER AS $$ > BEGIN > IF NOT isset(NEW.modified) THEN > NEW.modified = NOW(); > END IF; > RETURN NEW; > END; > $$ LANGUAGE 'plpgsql'; > > Does anyone know how to do this? > > Thanks in advance! > > / Johan > -- > View this message in context: http://old.nabble.com/Trigger-for-modification-timestamp-column-tp29096359p29096359.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
On Wed, Jul 7, 2010 at 8:48 AM, Johan Andersson <warb@mail.com> wrote:
It isn't very clear just what modification it is you're tracking, a specific column or the entire row?
Is this a column of type timestamp?
You can do a comparison between OLD.XXX and NEW.XXX in a before-update trigger but you will need to take into account null values. For example, is it possible for the modified flag go from non-null to null, and if so what does that mean?
Suppose the update statement explicitly sets XXX to the same value it already has, is that updating it or not?
Hello!
I am trying to write a trigger for updating a modification column and am
having some trouble getting it to behave as I want.
The trigger should set the column to the supplied value if it is set in the
UPDATE statement and to the current timestamp [NOW()] if it is not. The
problem is that I don't know how to check if the column is set or not. I can
check the column's value for NULL but that doesn't work if I want the column
to accept NULL values (meaning "unmodified").
It isn't very clear just what modification it is you're tracking, a specific column or the entire row?
Is this a column of type timestamp?
You can do a comparison between OLD.XXX and NEW.XXX in a before-update trigger but you will need to take into account null values. For example, is it possible for the modified flag go from non-null to null, and if so what does that mean?
Suppose the update statement explicitly sets XXX to the same value it already has, is that updating it or not?