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 1sCoLv-008IqG-6d for pgsql-general@arkaria.postgresql.org; Thu, 30 May 2024 22:34:33 +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 1sCoLs-006lau-Rk for pgsql-general@arkaria.postgresql.org; Thu, 30 May 2024 22:34:28 +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 1sCoLs-006lam-6y for pgsql-general@lists.postgresql.org; Thu, 30 May 2024 22:34:28 +0000 Received: from mail-bn7nam10olkn2023.outbound.protection.outlook.com ([40.92.40.23] helo=NAM10-BN7-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sCoLl-002jVn-0i for pgsql-general@lists.postgresql.org; Thu, 30 May 2024 22:34:26 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=IG76qs3XBPAgbH9GTHvQQ+LXOBNGheLt3X0K7htQtaqju8efjUPVr+iBwtKPfPD+n89v2qksrdVU8VSJd2p2H3PqnfS4xZofp1LNMgNlTFEftEJlSDok4W/M30APZwhNeFvpzWUGF130kD6z7Ewhzgh1Vqu8Z6Rw89s9Tc3W5nRYbSDtGNehXCQ+MttNwGPo9QWsBnkKKMCH2Ba7PHg3uGRFwPByGPS9ut1QR4MShPrIvLyu75A+6jOYqWMq+nUMr+aU2+Gy5JZZopSbSz0FWTFF77TDLdUcvYQCFVDTBluVnjJxxAZlF1IyktOGHAp3UA4Eawsga804T+8XZe5p/g== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=W+N9/GXIf1SjXQTCjRJucOhx/362AXAnZvRvjahCyfA=; b=JM54Ywh/tK+uIH696mJm9BHU1czWrUOkZ0L2sn4id6+nOMHhX215qSq9s5mAi5ydBIdk4m/L8FegRGU4/Mthlq5yNV6jEWHFxANGcx3VBHvMtDya7CIzWwc2HVXx4CkTBomD7YXlSKBBepV4DvNHEiynlrC5TkxmvoYxTGCMHvmTd9VFcUfQQk3VN8DcncBr/e428jq0crk3Iafhwc344rYJ/ukpV5SEzq4IXus6a8+Y4lgoOnoq7pnYa1mzjpAjF8ErIuYZCzpvoA7s3ZR8SzNnmbdPBjalOV/e4+elIsQG/lQ7Jt9BbceH60uIPNN3SueQrPXBIqkSB7a5btuEZw== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=hotmail.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=W+N9/GXIf1SjXQTCjRJucOhx/362AXAnZvRvjahCyfA=; b=AMZ/kZ+n8IjBB3GACGMfcx5q1kg0QJCRCGlwbr/77YT2MejlQf9/XnFWO6ALVL9i+naX+CK4BBTdvj9/ZmnTA7wKjwHzP676556oIhCfHEgTLYvxEnXTaj4Y8tSSyKozvbcgpO/N1Aos27pMjLUYZflqNg3SjFSXlqIRpvUh1Hij+ik/MJxPXK8V4i8CQN/qmfojk9LH547Bk5zQOJHvmUuGSCB3dSowXDhrk9sssWDd+cRuJFdSBKA9qreluvQpnpYZrTilD2BTyO7JfcIJc9fCg+mchbLm5BM6tUBvu5spYAW5sWhJJGA7hDxdcJn0rl4hxq4jOosBNPurlE9v7w== Received: from PH0PR10MB6959.namprd10.prod.outlook.com (2603:10b6:510:28f::15) by DS0PR10MB6870.namprd10.prod.outlook.com (2603:10b6:8:135::18) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7633.21; Thu, 30 May 2024 22:34:17 +0000 Received: from PH0PR10MB6959.namprd10.prod.outlook.com ([fe80::8a96:3eea:81c1:6ca9]) by PH0PR10MB6959.namprd10.prod.outlook.com ([fe80::8a96:3eea:81c1:6ca9%5]) with mapi id 15.20.7633.021; Thu, 30 May 2024 22:34:17 +0000 Content-Type: multipart/alternative; boundary="------------a6n8HikyVSd0m07bpWj7UMMa" Message-ID: Date: Thu, 30 May 2024 18:34:23 -0400 User-Agent: Mozilla Thunderbird Subject: Re: Rules and Command Status - update/insert/delete rule with series of commands in action To: pgsql-general@lists.postgresql.org References: Content-Language: en-US Cc: Adrian Klaver , "David G. Johnston" From: "johnlumby@hotmail.com" In-Reply-To: X-TMN: [uLbdNZy4t7c7omGNNr0+zzC/ajlvkUyR] X-ClientProxiedBy: YT4P288CA0002.CANP288.PROD.OUTLOOK.COM (2603:10b6:b01:d4::6) To PH0PR10MB6959.namprd10.prod.outlook.com (2603:10b6:510:28f::15) X-Microsoft-Original-Message-ID: MIME-Version: 1.0 X-MS-Exchange-MessageSentRepresentingType: 1 X-MS-PublicTrafficType: Email X-MS-TrafficTypeDiagnostic: PH0PR10MB6959:EE_|DS0PR10MB6870:EE_ X-MS-Office365-Filtering-Correlation-Id: 461c472f-0f6f-4c0c-043a-08dc80f8a392 X-Microsoft-Antispam: BCL:0;ARA:14566002|461199019|3412199016|440099019; X-Microsoft-Antispam-Message-Info: gKHsYG8E2Iuxfq1w2XeOO2cc2h7B/to2Ukwe/dHJ3fwyNFmutdWL6/foO34xnqUrmIok9U0If/R4nCEZlI8hI3X6ksO2x071ORAfFhSt4GFpF0Vct1zihPYC6Yxx+gakM6MSoerU8Nz1bynrgXX6pwgaZ+78ruloTWFB9JF7aD9mmpgkRP5bCncmOebE8gHM3SIo9iXYSA6+zgt9DICt4vx2sZfsR1lfFOMjm2RjmP5QJR27JCFeyK918KKP4qRpQ4ilEDCg/OYoX9yGggyfC2V6TGnFOVqcx6HXyePgTefvtdbhRDIVEhs1hy2K6yYeDcmjOo2pMGwHxSFA+P8E616aIxUAAAqGAQVUj/UAE0Na+WXqv+heF9xqzncYQ7bou5qYZgF2xJnXFFw5181E02LNP/K32ePuraWbwx1E0EF0IVS7lvHJ5AHqutTZt7u5DugIDVC4dKDofvTIKW3liT4UIH79ItH5aIwPsdNbWrGiY//Nb0ZhW0H+VhOXFO3P/Y5Gtw/RzfzalHx6rzUHVBdQzg9xYi2gz+OI6zs8nVnFHctL9v7LoFVm5jU3iQcW X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1 X-MS-Exchange-AntiSpam-MessageData-0: =?utf-8?B?RE4rQ1FCb3V6WGd4NCtlQlBUb0VzV0o3MjRUYXBlZUY0djJ6TXNUdjRvWS8r?= =?utf-8?B?dzNEd0F2cHFRUkxFcmE1eHVDMHhPTkZvdHZpQTl4bVA1VDdJUnFXa2RwbU5w?= =?utf-8?B?Z1FFNEpaQ0s4QzdLclRyQS9DNjB6QnFzQWpuT01MbEw0TmlKYlJuaFpEQk9x?= =?utf-8?B?cnR4Wko3Q1A4bllFSVRIU29ibytGREhNbXBLREJ0NVAvT3UvaUJ6bkZSamlk?= =?utf-8?B?bk0wOXBJWjQ4b0MvL2dIRnZyOVN0ZTJDT0NSNktldlZXakF2NFJyem5MSnRO?= =?utf-8?B?MzJBUmZuendNS2Q4b0NmazIvSGloNW5iZ0E4QUs3RmJkV0Jid1FOVFNXVnZw?= =?utf-8?B?UUt2SHc2MlNSWGZQWjZlb1d1Ti94NHNlcWNLL25TWFV1VHRNMTZ5ZDdWUnhD?= =?utf-8?B?djM0YlpiZWRvcXhab0ZLcjgzTE5IZVoxNFFzaGZtYWhJWDRaZ1FOZFhKRVpR?= =?utf-8?B?djU0U0hENVF3akZzTkVUbDNJQW5iTS8yS1VjU0dDUW0rQitoQ0J3VHdjNXFm?= =?utf-8?B?Z2tKWG0wbno1MEhqQ015K0JxZXlYRGJ6TTZwT1BMSVc0OXMraExDTWY2eFA1?= =?utf-8?B?Z0VYRTN6RUg2dmw5a21CRTIzWUMvSWVXVmtnS3NUb05MT29DaFJQa2U5dkJ4?= =?utf-8?B?Y1VpUXVDanNFNGZlVGc3bm1xRngvQUptZVJ4ZWFhTWNHeGs2bHZkVHR6UkZv?= =?utf-8?B?TEZkc2U0WFNCekVWQ0IyQkxIZHBLWG94aFhuTGlQQUg5dXA1SGYvaGpUVm9B?= =?utf-8?B?U1pHUWV2aTdqTGE3TWRFREcwQkVYS2dpeWxHYWhvZjRmb0NwTVRiOEVRSUFF?= =?utf-8?B?dXFlcjBQRHQzOTlUTjhRZC9hNWpvUndKYVFQUFJuK3FpQ2dMSUdldFpxNzNM?= =?utf-8?B?dzFlYWVqbWd4a0M0TnVsMHRMRlpwbzFJSmpwTzkzdWVocExNb0pNNThEcGwv?= =?utf-8?B?b0ZuazJGaUlUM0JKRFNvelZRS0ovNktPSkJxVVAvcnlhMHBYQW1DdFVkNjdk?= =?utf-8?B?K0RDa2F4Qm9EWGx1djRxQXFSTk4yejdqdUYxUjJRa29PeDlWMm9oeHZsTDE0?= =?utf-8?B?OE5oNDNSSmlkNzhVRFA5V016SlNEb0dmMzlDSlJlS0xHdDRvdm1wdmk2MmRo?= =?utf-8?B?WkJFMHh0UFFJQXAxeTFhWW9MajhydUd3WmRQUmhQWTUrdm52WVNRczJaTi9T?= =?utf-8?B?RnVZM1hpQ1N1Rkg1MmR0YzMzU3FFOUY0aHlKREpaNlR3cWZzTkR1NFhhNDFE?= =?utf-8?B?VkRFVEp0VmZZcXZDb3E3QjJKbSthQVl1aEMvYkNsbjBIWGJqbjA3S3VKUVJt?= =?utf-8?B?M25GS2NVSmVFQjZ6L016eDJ0cElUL0JsOGIxYzBWRy9WVDNFRW00VVF0TlFl?= =?utf-8?B?ZDkwbmlXQ2J2WCsvOFc1QmdMSEVQWm1CcVVGZ2JrVG85RzFuU0FhU3FzUmVO?= =?utf-8?B?VUlGWHlaaDdudWhXN0VobU0xYnRLUHByTXErSHowOWl4WmxmbElGVks5OXVm?= =?utf-8?B?b21YcEVSYzlIeFd4RVhlYi9Tb09PYWhpWFYyeVpnL2lHY1ExN2xGLzIzU2R3?= =?utf-8?B?cjA0VUY2eU1xWTNDWVpkekFvMCtnRTBJVGpnaE1UcUNFOEF4cSsxaDBZeVNp?= =?utf-8?Q?4OxdJpbH8KnOEdZGJxwGGFiegsUG9k/2CtQEO77ZqLY4=3D?= X-OriginatorOrg: sct-15-20-4755-11-msonline-outlook-926a2.templateTenant X-MS-Exchange-CrossTenant-Network-Message-Id: 461c472f-0f6f-4c0c-043a-08dc80f8a392 X-MS-Exchange-CrossTenant-AuthSource: PH0PR10MB6959.namprd10.prod.outlook.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-OriginalArrivalTime: 30 May 2024 22:34:17.0504 (UTC) X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted X-MS-Exchange-CrossTenant-Id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-Transport-CrossTenantHeadersStamped: DS0PR10MB6870 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --------------a6n8HikyVSd0m07bpWj7UMMa Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 5/30/24 4:56 PM, David G. Johnston wrote: > > Very little interest exists in working on user-specified rules.  They > are practically deprecated. Ah  -   pity  -   see my last comment to Adrian's > > Any interest in perhaps providing a choice via a configuration > parameter? > > >  If anything is done it would have to be new syntax. > > A much bigger task surely. On 5/30/24 5:19 PM, Adrian Klaver wrote: > > 2) Use INSTEAD OF triggers: > > Unfortunately the same functionality as in my example with the RULE is not supported for triggers on views :   from the manual |INSTEAD OF| triggers may only be defined on views, *and only at row level*; A RULE is essentially a statement-level operation which is what I need for this particular case.      A row-level trigger would not work because it cannot "see" the query causing it to be fired, and also ,  (most importantly) is not fired at all if no rows match the original query,     whereas a RULE is always in effect regardless of which rows are involved. before.      I should add that the RULE I showed in my example is not the only RULE being used on this view  -    there are other conditional RULEs,   and the combined effect is of being able to change the effect of the original statement into a set of new statements,   one of which does what is needed. And if you are now inclined to say "well,    maybe the application itself is poorly written and should be changed"  -   I would have to agree,    but that is not mine to change. But I suppose that my next question,   given what you both say about the RULE system being a dead-end,  is whether there is any likelihood of supporting an INSTEAD OF trigger on a view at statement level?   Maybe that stands more chance of going somewhere? Cheers,     John Lumby --------------a6n8HikyVSd0m07bpWj7UMMa Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 5/30/24 4:56 PM, David G. Johnston wrote:

Very little interest exists in working on user-specified rules.  They are practically deprecated.


Ah  -   pity  -   see my last comment to Adrian's



    Any interest in perhaps providing a choice via a configuration parameter?


 If anything is done it would have to be new syntax. 


A much bigger task surely.


On 5/30/24 5:19 PM, Adrian Klaver wrote:

2) Use INSTEAD OF triggers:



Unfortunately the same functionality as in my example with the RULE is not supported for triggers on views :   from the manual

INSTEAD OF triggers may only be defined on views, and only at row level;


A RULE is essentially a statement-level operation which is what I need for this particular case.      A row-level trigger would not work because it cannot "see" the query causing it to be fired,   and also ,  (most importantly) is not fired at all if no rows match the original query,     whereas a RULE is always in effect regardless of which rows are involved. before.      I should add that the RULE I showed in my example is not the only RULE being used on this view  -    there are other conditional RULEs,   and the combined effect is of being able to change the effect of the original statement into a set of new statements,   one of which does what is needed.


And if you are now inclined to say "well,    maybe the application itself is poorly written and should be changed"  -   I would have to agree,    but that is not mine to change.


But I suppose that my next question,   given what you both say about the RULE system being a dead-end,  is whether there is any likelihood of supporting an INSTEAD OF trigger on a view at statement level?   Maybe that stands more chance of going somewhere?



Cheers,     John Lumby


--------------a6n8HikyVSd0m07bpWj7UMMa--