Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVx8a-001gkF-M1 for pgsql-general@arkaria.postgresql.org; Tue, 15 Aug 2023 16:43:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qVx8Z-00G8yW-CX for pgsql-general@arkaria.postgresql.org; Tue, 15 Aug 2023 16:43:19 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVx8Y-00G8yM-Tj for pgsql-general@lists.postgresql.org; Tue, 15 Aug 2023 16:43:19 +0000 Received: from mout.kundenserver.de ([212.227.126.131]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVx8V-000ICm-TQ for pgsql-general@lists.postgresql.org; Tue, 15 Aug 2023 16:43:17 +0000 Received: from [192.168.0.150] ([93.204.37.190]) by mrelayeu.kundenserver.de (mreue009 [212.227.15.167]) with ESMTPSA (Nemesis) id 1MY60L-1qHQQH3TwL-00YUOr; Tue, 15 Aug 2023 18:43:12 +0200 Content-Type: multipart/alternative; boundary="------------5Dl5FwGrIDBPqEhoWahrJO7D" Message-ID: Date: Tue, 15 Aug 2023 18:43:11 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:102.0) Gecko/20100101 Thunderbird/102.14.0 Subject: Re: Converting sql anywhere to postgres Content-Language: de-DE To: russellrose@passfield.co.uk, pgsql-general@lists.postgresql.org References: From: "Georg H." In-Reply-To: X-Provags-ID: V03:K1:FCN3NcC0uNShmLx+gONNdXzAq7f1sghjqOz/7FLJ7/m1RnAlmFG OiEADIRar31QQ3/6OB9g+ERf6WVSa2rz3UevP/ZbaqOkrPQLLFWWa/A2H8+lQsX8/Wy7cmq jMz9aovYJXm2EYUuVqdImHIQzbW98fyJ00+VHNQ7CWJgqjmE9RoWHCWMys2B2wYeU9uEa7T FPMxsZcEnPEn4khMB5H0w== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:AIgeCWBe+s4=;xLVO7abZQ1S7Ft7yQVQSWBvkzVS rRNqaA7CpX8ZKHm/w6HE3K2bkMKHSnGpMeqj71edB0rtWhADnqPmBG/Joqzn+9nOok0xUr62E lN5JlE09V0mZmFFwInYMysLtP0vFYz+RFGSEHZSA2aOfQdACFxS4Emk54WTAtkEMR/0KTh8fL 437cEGw/8Rs4CDDWHwOs0FCdoI2hLIlJOMlJ9d7Lx1g0ODnZ11IQ1gaUw+O/Ka01NRUWR8mf7 MEvOIo4JemuzBAIR5lCHYu3py2GyTwVxrVlFovl5XT8wkrVVpU7zHbk9uBqiOggUsJcDokm1R dxouM9fc46RD+jp7COCwJxfmtKxqUFIQYHFfSrZ6kDT7zk8n1718pAMvoqFxNA2DYrsziLnyj X9wHdHy4NKdnGAbtHMZGrmBDxu3drWCnEgXBegNO7OPAmYszg13nOcRKvGXtegknaUGRc1sIO JUib8cX+ckZ8ldWkGmyPXFHXGzuXNLsk6fnNJQoAu8/u2KSILrAi3eaoKLfZJgbqsr+SV+ebA XuU2tJ9fFohfxwRWUKmbK4NIzSANY/PQquuiHMPWz2ME+iiK5/5TbKjry284dk0SS62ZwkhCq wGkARQJM3rQoaDZwQreL9oVt5JfI5tex6pufkEkrNbzg91CEpzonM2YrIKNAOMtmJPkpaQKAd JzkAgIhRnJS85cnCuiBbh1C44ctRBwLXS6rspkvhM8n9xLyBodUmUSRsENYhKq6n1uVao6Uml PW6ilflmuropdYYPPurVZmecFSs6vDBuCiQPpxoxOjCQ1P9vC4//OT7r1ZDJbj28Dvhr8khoW iWdXtre5TDKCWYp931VfxEc+Hrezi9uK7/JcElOYb3kXCWrc9fqSanxQj/pWeUmRJRhGdTEFM PaH3txrs72h7Hqw== List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------5Dl5FwGrIDBPqEhoWahrJO7D Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit Hi, Am 15.08.2023 um 17:04 schrieb Russell Rose | Passfield Data Systems: > > Hi there > > 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’ > maybe this is what you're looking for (without a trigger) CREATE TABLE mytest.autovalues (     key serial NOT NULL,     product text,     updated_by text DEFAULT current_user,     updated_at timestamp without time zone DEFAULT current_timestamp,     PRIMARY KEY (key) ) TABLESPACE pg_default; ALTER TABLE IF EXISTS mytest.autovalues     OWNER to postgres; -- instead of current_user you may also use |session_user see https://www.postgresql.org/docs/current/functions-info.html| | | |then try: | |insert into mytest.autovalues (product) values ('apple') ; insert into mytest.autovalues (product,updated_by) values ('apple','justanotheruser') ; insert into mytest.autovalues (product,updated_by) values ('peach','justanotheruser') ; select * from mytest.autovalues; update mytest.autovalues set product='pear', updated_by=DEFAULT where key=2; select * from mytest.autovalues;| | | |In case you want to "automate" the update command (not setting |||updated_by to DEFAULT manually/programmatically)| you may use an on update trigger that compares current_user/session_user with old.|updated_by and if they are different you could set new.updated_by to DEFAULT (or whatever logic fits your needs)|| kind regards Georg --------------5Dl5FwGrIDBPqEhoWahrJO7D Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

Hi,

Am 15.08.2023 um 17:04 schrieb Russell Rose | Passfield Data Systems:

Hi there

 

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’

 


maybe this is what you're looking for (without a trigger)

CREATE TABLE mytest.autovalues
(
    key serial NOT NULL,
    product text,
    updated_by text DEFAULT current_user,
    updated_at timestamp without time zone DEFAULT current_timestamp,
    PRIMARY KEY (key)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS mytest.autovalues
    OWNER to postgres;
   

-- instead of current_user you may also use session_user see https://www.postgresql.org/docs/current/functions-info.html


then try:

insert into mytest.autovalues (product) values ('apple') ;
insert into mytest.autovalues (product,updated_by) values ('apple','justanotheruser') ;
insert into mytest.autovalues (product,updated_by) values ('peach','justanotheruser') ;
select * from mytest.autovalues;
update mytest.autovalues set product='pear', updated_by=DEFAULT where key=2;
select * from mytest.autovalues;


In case you want to "automate" the update command (not setting updated_by to DEFAULT manually/programmatically) you may use an on update trigger that compares current_user/session_user with old.updated_by and if they are different you could set new.updated_by to DEFAULT (or whatever logic fits your needs)


kind regards

Georg

--------------5Dl5FwGrIDBPqEhoWahrJO7D--