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 1qVwXx-001eoJ-6i for pgsql-general@arkaria.postgresql.org; Tue, 15 Aug 2023 16:05:29 +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 1qVwXt-00FtJy-Kl for pgsql-general@arkaria.postgresql.org; Tue, 15 Aug 2023 16:05:25 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVwXt-00FtJq-6z for pgsql-general@lists.postgresql.org; Tue, 15 Aug 2023 16:05:25 +0000 Received: from mout-u-204.mailbox.org ([2001:67c:2050:101:465::204]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qVwXo-000JCw-Dy for pgsql-general@lists.postgresql.org; Tue, 15 Aug 2023 16:05:24 +0000 Received: from smtp1.mailbox.org (smtp1.mailbox.org [IPv6:2001:67c:2050:b231:465::1]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-u-204.mailbox.org (Postfix) with ESMTPS id 4RQGLy36s1z9sNv; Tue, 15 Aug 2023 18:05:14 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ewie.name; s=MBO0001; t=1692115514; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=SSms4FuQFITEx5Jl53dfmmmtNtVZI8AKG7JDr1vYWuo=; b=D2Jll77CXrJcfYZEeRmVTIBXlkhtO2Qhojd26fUDp5OHDgxnvnZFqKDCzAFaQdpd9LmGHl oxDcJBXzHA2nPbsjGZU1wJJvS/E43fDzBkRWfXJhZ20gEzcOgy1FITwExINXVYoyJxHOjv S2Jh5P0oFRkHYQQ5PzlO+Ay8amuexsGSrpNwVLxHl9SIewi3ZiVDRudLu6W1WO/Dfolv1S FD0eqDQB7UoOI+hB2vezKdvOaDQItvjix6tkvbmnFCJRtfw+PCIRFlYR7qBnU6v8U/sno2 HyZzgWYyMHydNpGvR2nLAtvN+Mo9Kn5Mx/oBZ6IboSG2sBKcb5jMfYDpvC8m/g== Date: Tue, 15 Aug 2023 18:05:13 +0200 (CEST) From: Erik Wienhold To: Russell Rose | Passfield Data Systems , "pgsql-general@lists.postgresql.org" Message-ID: <1296438271.238073.1692115513737@office.mailbox.org> In-Reply-To: References: Subject: Re: Converting sql anywhere to postgres MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 Importance: Normal X-Rspamd-Queue-Id: 4RQGLy36s1z9sNv List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 15/08/2023 17:04 CEST Russell Rose | Passfield Data Systems wrote: > > I am trying to convert a SQL Anywhere database to postgres. Within SQL > anywhere a field can have a default value of =E2=80=98last user=E2=80=99.= 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 automati= cally > picking it up. > > If the field myfield contains the word =E2=80=98me=E2=80=99. Can I tell t= he difference > between: > Update table1 set field1=3D=E2=80=99something=E2=80=99,myfield=3D=E2=80= =99me=E2=80=99 > And > Update table1 set field1=3D=E2=80=99something=E2=80=99 Do you also have a timestamp column (let's say mod_time) that goes along wi= th 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 ass= ign NEW.mod_user :=3D current_user and NEW.mod_time :=3D now(). Or use clock_timestamp() instead of now() if you need to handle multiple up= dates of the same row in one transaction. But this is only relevant when changin= g the current user with SET ROLE during the transaction. -- Erik