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 1qWNsa-0034uK-U3 for pgsql-general@arkaria.postgresql.org; Wed, 16 Aug 2023 21:16:37 +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 1qWNsZ-009sEL-K8 for pgsql-general@arkaria.postgresql.org; Wed, 16 Aug 2023 21:16:35 +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 1qWNsZ-009sEC-7b for pgsql-general@lists.postgresql.org; Wed, 16 Aug 2023 21:16:35 +0000 Received: from mail-pl1-x62c.google.com ([2607:f8b0:4864:20::62c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qWNsV-000XXx-3s for pgsql-general@lists.postgresql.org; Wed, 16 Aug 2023 21:16:34 +0000 Received: by mail-pl1-x62c.google.com with SMTP id d9443c01a7336-1bf078d5f33so4813305ad.3 for ; Wed, 16 Aug 2023 14:16:30 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692220589; x=1692825389; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=s7rUsD3We3vKN4QdaxvwwaHQgxDaDk+13+HLSGiCjBk=; b=p5p0iEAZI23LpaZq9luKHmEuJ+XPpAQsfqcVkJbJsHCpjgFGur5CLu8l8LHgT1srdY DsLzZfE8TbEs53FrNiYAd0XZHWfx9Usn62Ea4i4qBB+9i254Rb8LCMK6QA+bjPzbGyOF tKp0cP3wkdaT1JpWugZmuMpSxOqdn2G3yqOD/UUKuVAh+U6so8GpHMLPMWFGO3dSmLff 9jWBtqy/teYVF6TtF/UK9ehqW4qXqxzhVlLKtGV8TzVXzCNaqP5JpPnX08bz7DJVrIQl vVVwM9WIPNIWAaqyaEU0kqtXHIoJDo/SkDKN1v+cnp4zCBEpEnV6RoJ77hBU/UJdgMx2 +iAA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692220589; x=1692825389; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=s7rUsD3We3vKN4QdaxvwwaHQgxDaDk+13+HLSGiCjBk=; b=AmaFoCLpL19C1YVs4VWeVyuc6QCFN/KXrr2IWCFIv/xDpKF1hb/DjpQgjF9hgQdhSy DLDBubwjqLM2KopPkS/nnM57Iaww3jvz0Ssak1fNfMf8Zw9tyeEZTS143sT4B7dWYst+ C/NDsIAzA3nNBjlIGLy2jsjqH6kAYB0Z2zfzfceGyJdC1reWNnMuY30mlFPN1qthYwcZ z/2PwLyxemnbD6Au+XfbqyjmE/67W1x9z4Zp/E3O98idvYm0+y1PSGFPJ3fq1sK2BN1K vWrgYSARm8Q4FM4ftu4kEq0RizvZSfnAxvsax1AjcfJpCmrh3/hUgqNTvKCj5Hs73q7o ulqA== X-Gm-Message-State: AOJu0YzBhqT9WzLVDdu86PdX/uOeqTJCnTWkipt38GbXEbi3HEUIhaEG 2cXXkel28XVYg8nW5q1vQH4FQtzN1fE= X-Google-Smtp-Source: AGHT+IHgQwHoH7jmCz36DQ3ywMS4kgU7fjzLRW5sDiawmctSGU17YtQIrATqdAxrG5wiWYLnnrPblg== X-Received: by 2002:a17:903:11d1:b0:1b9:cb8b:3bd3 with SMTP id q17-20020a17090311d100b001b9cb8b3bd3mr3959050plh.31.1692220588651; Wed, 16 Aug 2023 14:16:28 -0700 (PDT) Received: from smtpclient.apple ([76.140.27.218]) by smtp.gmail.com with ESMTPSA id d4-20020a170903230400b001bb9aadfb04sm252791plh.220.2023.08.16.14.16.27 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 16 Aug 2023 14:16:27 -0700 (PDT) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.15\)) Subject: Re: Converting sql anywhere to postgres From: Rob Sargent In-Reply-To: <7de51aeb-600d-3375-ad65-6249f8e8f8a9@aklaver.com> Date: Wed, 16 Aug 2023 15:16:26 -0600 Cc: pgsql-general@lists.postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: <04070FB3-728A-450D-8B54-FA8DFDA4500E@gmail.com> References: <3fa3d5c7-67b5-444e-9bbd-4e1a8d7f549a@Spark> <94459638-f09a-64c5-49c5-9b8462b391ab@gmail.com> <7de51aeb-600d-3375-ad65-6249f8e8f8a9@aklaver.com> To: Adrian Klaver X-Mailer: Apple Mail (2.3654.120.0.1.15) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On Aug 16, 2023, at 1:35 PM, Adrian Klaver = wrote: >=20 > On 8/16/23 12:01, Rob Sargent wrote: >> On 8/16/23 12:30, Guyren Howe wrote: >>> For some reason, I was thinking the rule could see just the fields = from the command, but you=E2=80=99re right; a rule won=E2=80=99t work. = Sorry. >>>=20 >>> Guyren G Howe >>> On Aug 15, 2023 at 23:22 -0700, Russell Rose | Passfield Data = Systems , wrote: >>>> I have just had a quick look at rules and I am not sure how it can = be done. Rules still use the concept of NEW and OLD. If my original row = has 'myfield' set to 'me' then I don't think I can tell the difference = between: >>>>=20 >>>> Update mytable set afield=3D'something' >>>> and >>>> Update mytable set afield=3D'something',myfield=3D'me' >>>>=20 >>>> Within the rule I think NEW.myfield will be set to 'me' in both = cases. Please can you explain how I can tell the difference between the = two update statements >>>>=20 >> If the original value in the user column is "me", what is the = difference between "set other_column =3D some_value, user =3D 'me'" and = "set other_column =3D some_value" at the business level? >=20 > Affirmation that the user updating the record explicitly set the user = value. >=20 > --=20 > Adrian Klaver > adrian.klaver@aklaver.com >=20 Agreed. But at the end of the day, the difference is what exactly? = Wouldn't auditing (short of sql logging) say =E2=80=9Cno change=E2=80=9D = with respect to =E2=80=9Cme=E2=80=9D column? This then is a client issue, no? There has to be two paths in the = client code, one which generates an update without =E2=80=9Cme=E2=80=9D = and one which includes =E2=80=9Cme" and the second path does not take in = to account current value. If it=E2=80=99s worth the effort the latter = code path needs to be cognizant of the current state of the record = (=E2=80=9Cme=E2=80=9D column). =20