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 1qW1yo-001usH-Ai for pgsql-general@arkaria.postgresql.org; Tue, 15 Aug 2023 21:53:34 +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 1qW1ym-000bU1-Vt for pgsql-general@arkaria.postgresql.org; Tue, 15 Aug 2023 21:53:33 +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 1qW1ym-000bTs-Il for pgsql-general@lists.postgresql.org; Tue, 15 Aug 2023 21:53:32 +0000 Received: from mail-pl1-x62f.google.com ([2607:f8b0:4864:20::62f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qW1ye-000Lza-Nu for pgsql-general@lists.postgresql.org; Tue, 15 Aug 2023 21:53:32 +0000 Received: by mail-pl1-x62f.google.com with SMTP id d9443c01a7336-1bddac1b7bfso18177565ad.0 for ; Tue, 15 Aug 2023 14:53:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692136402; x=1692741202; h=mime-version:subject:references:in-reply-to:message-id:to:from:date :from:to:cc:subject:date:message-id:reply-to; bh=FXaq0+/58gWiF6XHggHwrerQGhLg4hMTX5Y5tlfFdVc=; b=c/m/Geu8V9qMQhoKHyQlOCYNjgefJLmpNUya11ZzWs3bVwVTH+ww8ldz3rKb5tzA3W S/bNZcxxYYfwn2Mpx9Gx5qfSfe8tXERmRw2J+eLpYlI6lLe+ZExJHG18fd0uJoQYWYFl YAsCX6bU9n0wro9QNR+O5phb/ebeCO/iZ9/9R8HjMI50Pj1xg9uhxIcPHJXsMgyjDbGy JPpfINEiNtmBSqHHHwfTYCvL5XkXgmYQz2AREDBkQDVT0KeXA9inBGPrmQmrlgUfZ9rK FB3Vx+gY/UBSLL3sRYGLmVSpggqftBABrpISJrAJOnEgJT1TsXLWYKCeQpalLvef+Hew Q3/g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692136402; x=1692741202; h=mime-version:subject:references:in-reply-to:message-id:to:from:date :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=FXaq0+/58gWiF6XHggHwrerQGhLg4hMTX5Y5tlfFdVc=; b=LjXVjbDM45/N1nolLDAzGtH66xH+HWVZRcbkpV7V6q4BcsKEKmEUaGlXSo5sO7VVEm LW7DS8W5qJ0rtyFNnFs0SrFaMQdQfB8r91AGIafgwxB7v+dbO6E1Hy2MxywHDWiOsTPD PCSInpPnMYakFIN6j6aHWdkVgW9YNk4ils62RF6cwwg/VK36bZHw3rlO1wGn+2M/gPsR /020GefCVY59mslNG2IQh+DKJu47zqYGq5UDSUXxGSwnA9s86ei4y/A7D2DTbLD8vFmf KOB+/BBHVLGJN4ZRxNxR2djUAcvBsfH5B6Bnhz2pn1RMKWX0dBQpDRCgQbkC3np/aGDp 8RnA== X-Gm-Message-State: AOJu0YzslJXxVdD6YTvvFr6+WSp/1ijO1ydHTK5UJnSPS69cBm5byg5B kFUXXcuxNIRCPfRhaN2dMV7N3Te5gZDj1A== X-Google-Smtp-Source: AGHT+IG3zp1MDd529FYTMJNhYj31FSTjf7FRxLJg46qo9jIhSbfCoh/JL5xH3Vr40qJFWgofiBrq3g== X-Received: by 2002:a17:902:e74c:b0:1bc:1d1b:2314 with SMTP id p12-20020a170902e74c00b001bc1d1b2314mr4338947plf.26.1692136402259; Tue, 15 Aug 2023 14:53:22 -0700 (PDT) Received: from [2600:8801:8600:631:42:7910:100:0] ([2600:8801:8600:631::e]) by smtp.gmail.com with ESMTPSA id 20-20020a170902e9d400b001b898595be7sm7511031plk.291.2023.08.15.14.53.21 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 15 Aug 2023 14:53:21 -0700 (PDT) Date: Tue, 15 Aug 2023 14:52:56 -0700 From: Guyren Howe To: "=?utf-8?Q?pgsql-general=40lists.postgresql.org?=" , =?utf-8?Q?Russell_Rose_=7C_Passfield_Data_Systems?= Message-ID: In-Reply-To: References: Subject: Re: Converting sql anywhere to postgres X-Readdle-Message-ID: c1a8ee64-711b-407a-b119-918a91d0bbfa@Spark MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="64dbf3cf_a42ab36_d703" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --64dbf3cf_a42ab36_d703 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I=E2=80=99m fairly confident you can do this using a Rule. It would even = be fairly simple. Be careful, though: Rules are Postgres=E2=80=99 biggest potential foot gu= n. Guyren G Howe On Aug 15, 2023 at 08:05 -0700, Russell Rose =7C Passfield Data Systems <= russellrose=40passfield.co.uk>, wrote: > Hi there > > 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 n= ot explicitly set then the current user is used. So for instance if I hav= e a field called mod=5Fuser in a table, but when I do an update on the ta= ble and do not set mod=5Fuser then SQL Anywhere sets the field to current= =5Fuer. I have tried to replicate this using a postgres trigger in the be= fore update. However, if I do not set the value then it automatically pic= ks 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 valu= e and postgres automatically picking it up. > > If the field myfield contains the word =E2=80=98me=E2=80=99. Can I tell= the 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 > --64dbf3cf_a42ab36_d703 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
I=E2=80=99m fairly confident you can do this using = a Rule. It would even be fairly simple.

Be careful, though: Rules are Postgres=E2=80=99 biggest potential foot gu= n.

Guyren G Howe
On Aug 15, 2023 at 08:05 -0700, Rus= sell Rose =7C Passfield Data Systems <russellrose=40passfield.co.uk>= ;, wrote:

Hi there

&=23160;

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=5Fuser in a table, but when= I do an update on the table and do not set mod=5Fuser then SQL Anywhere = sets the field to current=5Fuer. I have tried to replicate this using a p= ostgres 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. I= s 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.

&=23160;

If the field myfield contains the word =E2=80=98= me=E2=80=99. Can I tell the 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

&=23160;

--64dbf3cf_a42ab36_d703--