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 1qWP3A-0038r4-G1 for pgsql-general@arkaria.postgresql.org; Wed, 16 Aug 2023 22:31: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 1qWP38-00AKCn-Vq for pgsql-general@arkaria.postgresql.org; Wed, 16 Aug 2023 22:31: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 1qWP37-00AKCd-Ex for pgsql-general@lists.postgresql.org; Wed, 16 Aug 2023 22:31:34 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qWP2y-000Y3U-Qw for pgsql-general@lists.postgresql.org; Wed, 16 Aug 2023 22:31:32 +0000 Received: from compute3.internal (compute3.nyi.internal [10.202.2.43]) by mailout.nyi.internal (Postfix) with ESMTP id 8FA025C0072; Wed, 16 Aug 2023 18:31:22 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute3.internal (MEProxy); Wed, 16 Aug 2023 18:31:22 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm3; t= 1692225082; x=1692311482; bh=ZW6Q6hzsFFwJWrTQwOkmaYmDMrDvV65fPVi O+BpEzO4=; b=IxQlqFocJl0vKbjt9ZXI9YrgajabT8xRuPG5k0dIlEmBoVjzUaj sHz1KvBaJtazegkfYz2+Ap7zZBM1njdti2moOBdG8ViDHGgk6vS8Eb6ip0WWdu48 7i0jGYOPfMYVSFhAbCBniD0TAmrvE4wDDy9ejGcg0a5KrKYvlJdpaJX/0iOKy6os bz+GZAbOWrWOA1KErc7t64UVMahCnHDBJ7wRunERppXjXkAuD0aBiqq3no/gMSST jcOMT9AKOiNTH3mz8fgQSRAbkJnI9m13UJEnCL8qiuo2V9bphZaaXpQC+5H5oQ5e s6YTIWEjDJmZ7T1mhGsdrSla/TUxAlbcqMQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t= 1692225082; x=1692311482; bh=ZW6Q6hzsFFwJWrTQwOkmaYmDMrDvV65fPVi O+BpEzO4=; b=wxAk9AO1ibTZlxl5sSHQL6H0uJ5uHS8dwYhtHjgN16+BLF4CKkf MAp628aTIKJZVsopt7wX7a/BhOAFUAukXRP7pkbJZ7OuexjLX97C8FXa++fVIWg3 S/ir67D74PmL4N+FKMm8CJxsteyBoya547Rleh+pRcOlg/cDGzE6Fu1ps5k7W1tH vzNiC5b/9tu0eh+96wpvZuSlJZpJKNuMIkAnDm3vWXwoMT4ZiADohUOF82SNBcFO gfuU8nJNjsMLj0HmYtog1ZTK1lWkc77sGeQjdKZMWmUAEqskQWP/44tGUFs/doKR EQI5d6AzX8XzJin4W021EEXKJh9FbK0szIg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedviedruddutddguddtucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvvehfhfgjtgfgsehtkeertddtfeejnecuhfhrohhmpeetughr ihgrnhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtg homheqnecuggftrfgrthhtvghrnhephedugfethfettdehieevteeukedtjedvhfevkefg udefgeevteduvdeuvdeggeegnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpe hmrghilhhfrhhomheprggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 16 Aug 2023 18:31:21 -0400 (EDT) Message-ID: <41ced76e-3096-d0f4-549d-e15d7db6964c@aklaver.com> Date: Wed, 16 Aug 2023 15:31:20 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.14.0 Subject: Re: Converting sql anywhere to postgres To: Rob Sargent Cc: pgsql-general@lists.postgresql.org References: <3fa3d5c7-67b5-444e-9bbd-4e1a8d7f549a@Spark> <94459638-f09a-64c5-49c5-9b8462b391ab@gmail.com> <7de51aeb-600d-3375-ad65-6249f8e8f8a9@aklaver.com> <04070FB3-728A-450D-8B54-FA8DFDA4500E@gmail.com> Content-Language: en-US From: Adrian Klaver In-Reply-To: <04070FB3-728A-450D-8B54-FA8DFDA4500E@gmail.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 8/16/23 14:16, Rob Sargent 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: >>>>> >>>>> Update mytable set afield='something' >>>>> and >>>>> Update mytable set afield='something',myfield='me' >>>>> >>>>> 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 >>>>> >>> If the original value in the user column is "me", what is the difference between "set other_column = some_value, user = 'me'" and "set other_column = some_value" at the business level? >> >> Affirmation that the user updating the record explicitly set the user value. >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com >> > Agreed. But at the end of the day, the difference is what exactly? Wouldn't auditing (short of sql logging) say “no change” with respect to “me” column? That is above my pay grade, that is for the OP to elaborate on. I was just saying that the Postgres server could not make that distinction with the information provided. -- Adrian Klaver adrian.klaver@aklaver.com