From: Erik Wienhold <ewie@ewie.name>
To: Russell Rose | Passfield Data Systems <russellrose@passfield.co.uk>
To: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Converting sql anywhere to postgres
Date: Tue, 15 Aug 2023 18:05:13 +0200 (CEST)
Message-ID: <1296438271.238073.1692115513737@office.mailbox.org> (raw)
In-Reply-To: <VI1P191MB0638057C9A6AD74BD09758AAF214A@VI1P191MB0638.EURP191.PROD.OUTLOOK.COM>
References: <VI1P191MB0638057C9A6AD74BD09758AAF214A@VI1P191MB0638.EURP191.PROD.OUTLOOK.COM>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ewie@ewie.name, russellrose@passfield.co.uk
Subject: Re: Converting sql anywhere to postgres
In-Reply-To: <1296438271.238073.1692115513737@office.mailbox.org>
References: <VI1P191MB0638057C9A6AD74BD09758AAF214A@VI1P191MB0638.EURP191.PROD.OUTLOOK.COM> <1296438271.238073.1692115513737@office.mailbox.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox