> On 15/08/2023 17:04 CEST Russell Rose | Passfield Data Systems <russellrose@passfield.co.uk> wrote:
>
> I am trying to convert a SQL Anywhere database to postgres. Within SQL
> anywhere a field can have a default value of ‘last user’. This means that
> when you perform an update on a table, if the field is not explicitly set
> then the current user is used. So for instance if I have a field called
> mod_user in a table, but when I do an update on the table and do not set
> mod_user then SQL Anywhere sets the field to current_uer. I have tried to
> replicate this using a postgres trigger in the before update. However, if
> I do not set the value then it automatically picks up the value that was
> already in the field. Is there a way to tell the difference between me
> setting the value to the same as the previous value and postgres automatically
> picking it up.
>
> If the field myfield contains the word ‘me’. Can I tell the difference
> between:
> Update table1 set field1=’something’,myfield=’me’
> And
> Update table1 set field1=’something’
Do you also have a timestamp column (let's say mod_time) that goes along with
mod_user (both updated together)?
In that case you can compare OLD.mod_time and NEW.mod_time in the BEFORE
trigger. Only if the timestamp does not change should the trigger then assign
NEW.mod_user := current_user and NEW.mod_time := now().
Or use clock_timestamp() instead of now() if you need to handle multiple updates
of the same row in one transaction. But this is only relevant when changing the
current user with SET ROLE during the transaction.
--
Erik