Обсуждение: 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
>