BEFORE UPDATE trigger doesn't change column value

Clemens Eisserer <linuxhippy@gmail.com> wrote:

I am trying for quite some time now to get the following
trigger-function to work properly:

CREATE OR REPLACE FUNCTION update_synced_column()
   RETURNS trigger AS
$BODY$
BEGIN
    IF NEW.synced IS NULL THEN
     NEW.synced :=  false;
    END IF;
    RETURN NEW;
END;
$BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;

The idea is basically to always update the "synced" column to
false, unless a value has been provided manually in the
UPDATE-clause.
Synced is defined as BOOLEAN DEFAULT FALSE;
I execute this function in a BEFORE UPDATE trigger FOR EACH ROW,
however it doesn't seem to have any effect.

Any ideas what could be wrong here?

NEW reflects what the row will look like after the UPDATE.  There
is no way to tell which columns were specified in the SET clause of
the UPDATE; a column which is omitted from that clause will look
exactly the same as a column which is set to the value it already
had.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 5/4/13 at 6:59 AM, Clemens Eisserer wrote:

Sorry for this newbie-question, I am trying for quite some time now to get
the following trigger-function to work properly:

CREATE OR REPLACE FUNCTION update_synced_column()
RETURNS trigger AS
$BODY$
BEGIN
IF NEW.synced IS NULL THEN
NEW.synced := false;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

The idea is basically to always update the "synced" column to false, unless
a value has been provided manually in the UPDATE-clause.
Synced is defined as BOOLEAN DEFAULT FALSE;
I execute this function in a BEFORE UPDATE trigger FOR EACH ROW, however it
doesn't seem to have any effect.

You could also add 'NOT NULL' to the declaration of synced so
this column can never be set to NULL and this would further
ensure the trigger function has nothing to do.

By way of sanity testing, do you get any rows when doing
something like:
SELECT * FROM relevant_table WHERE synced IS NULL;

Any ideas what could be wrong here?

If the above does not apply and at the risk of being too obvious
(specifically not wishing to cause offence):

Has the trigger itself been declared?
refer: <http://www.postgresql.org/docs/9.2/static/sql-createtrigger.html&gt;

Is the function ever getting called?
refer: <http://www.postgresql.org/docs/9.2/static/plpgsql-errors-and-messages.html&gt;

Regards
Gavan Schneider

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,

NEW reflects what the row will look like after the UPDATE. There

is no way to tell which columns were specified in the SET clause of
the UPDATE; a column which is omitted from that clause will look
exactly the same as a column which is set to the value it already
had.

Thanks a lot for clarifying this ... my logic was flawed.
At stackoverflow.com I found an example which suggested testing against
NULL would allow this and I just relied on it.

Here is what I am trying to achieve: Set "synced" to false at any update,
except when it has been set explicitly to true.
This does not seem to be possible, without checking the value SET by UPDATE?

Regards, Clemens

Clemens Eisserer <linuxhippy@gmail.com> wrote:

Here is what I am trying to achieve: Set "synced" to false at any
update, except when it has been set explicitly to true.
This does not seem to be possible, without checking the value SET
by UPDATE?

Right; since there is no way to check whether a 'true' value there
was explicitly set or just carrying over from the old version of
the row without being set, you need some other mechanism for
handling this.  You could, for exampe, add a "force_sync" column
which could be tested in a trigger.  Your BEFORE UPDATE trigger
could leave the "synced" value in NEW alone if force_sync was
false, and set "synced" to false otherwise.  It could then set
NEW.force_sync to false, to leave you ready for the next update.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Kevin Grittner <kgrittn@ymail.com> wrote:

Your BEFORE UPDATE trigger could leave the "synced" value in NEW
alone if force_sync was false, and set "synced" to false
otherwise.  It could then set NEW.force_sync to false, to leave you
ready for the next update.

Sorry, that's the wrong way around.  I should have said:

Your BEFORE UPDATE trigger could leave the "synced" value in NEW
alone if force_sync was **true**, and set "synced" to false
otherwise.  It could then set NEW.force_sync to false, to leave you
ready for the next update.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi Kevin,

Sorry, that's the wrong way around. I should have said:

Your BEFORE UPDATE trigger could leave the "synced" value in NEW
alone if force_sync was **true**, and set "synced" to false
otherwise. It could then set NEW.force_sync to false, to leave you
ready for the next update.

Thanks for your advice (and the patience on this list in general).
Instead of using two columns, I now use an integer-column and set it to a
value taken from an incrementing sequence.

Thanks again, Clemens