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 1tVbeY-009wW7-IV for pgsql-hackers@arkaria.postgresql.org; Wed, 08 Jan 2025 19:23:43 +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 1tVbeY-0053ab-12 for pgsql-hackers@arkaria.postgresql.org; Wed, 08 Jan 2025 19:23:41 +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 1tVbeX-0053aT-KS for pgsql-hackers@lists.postgresql.org; Wed, 08 Jan 2025 19:23:41 +0000 Received: from relay6-d.mail.gandi.net ([217.70.183.198]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1tVbeU-000aQM-13 for pgsql-hackers@postgresql.org; Wed, 08 Jan 2025 19:23:40 +0000 Received: by mail.gandi.net (Postfix) with ESMTPSA id A2728C0003; Wed, 8 Jan 2025 19:23:36 +0000 (UTC) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=postgresfriends.org; s=gm1; t=1736364217; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=RbUylFnFalrSrRfhANOSjVt4muxw8KdjBFOajIstYFw=; b=bJT586pAoIPF5ppD5L3S8gZ4XPPfklnQFL2tL+Y8QP/rFXJwtCi7/bQycVs6yrjYaJrqZG 7NgHvgimsV0WdvTyPxlTPzB6j6S62Bf5Enk7a+X3ReokTtq8mJtVtNHllLzXAAXbCl6GkQ krDsUIYZWiybArGhF/o/Kl2nZ+k1x7L1jjaDMLKo/MQX/WGePfLbQjeHSBGu8gu+ifFVXl 6mTGdfVP2Z1mFbQcVm0eco6POZJyuUYvnxtJ31tsIkmyCLOmrbHEhNQmkE+lu+SrxhHFzs bIbQD6slhCC0/sYLl5SG1Qd9GIN/70bAsBfKGsxQG8joxFwiHGp738zBrAuh2w== Message-ID: <3c6b81c5-35b9-45b2-a74f-6c79be205d1d@postgresfriends.org> Date: Wed, 8 Jan 2025 20:23:35 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Virtual generated columns To: Marcos Pegoraro , Peter Eisentraut Cc: pgsql-hackers , jian he , Dean Rasheed References: <732a9543-0312-4f0c-a471-857b507a3546@eisentraut.org> <0f811f71-8c86-4e1b-b3bb-6a535674a440@eisentraut.org> <09800cb1-43aa-4239-8918-46b71ede4900@eisentraut.org> <536b281b-2cb3-4477-971f-c64838bc6263@eisentraut.org> <51875199-d5f5-41ed-92dc-3e7b1b996991@eisentraut.org> <84adfb1b-826c-42b2-a907-b327d3760c7e@eisentraut.org> Content-Language: en-US From: Vik Fearing In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-GND-Sasl: vik@postgresfriends.org List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 08/01/2025 20:19, Marcos Pegoraro wrote: > Em qua., 8 de jan. de 2025 às 13:14, Peter Eisentraut > escreveu: > > Here is a new patch version where I have gathered various pieces of > feedback and improvement suggestions that are scattered over this > thread.  I hope I got them all.  I will respond to the respective > messages directly to give my response to each item. > > > This new version you are not accepting subqueries, like previous ones. > But we can create an immutable SQL function which will do the same. > Wouldn't it be better to explain that on DOCs ? > > create table Orders(Order_ID integer not null primary key, Customer_ID > integer references Customer); > create function lkCustomer(integer) returns text language sql > immutable as $function$select Name from Customer where Customer_ID = > $1;$function$; > alter table Orders add lkCustomer text generated always as > (lkCustomer(Customer_ID)) stored; This is lying to the planner, and you get to enjoy whatever breaks because of it.  A function that accesses external data is not immutable; it is stable at best. -- Vik Fearing