Re: Virtual generated columns

On 08/01/2025 20:19, Marcos Pegoraro wrote:
> Em qua., 8 de jan. de 2025 às 13:14, Peter Eisentraut 
> <peter@eisentraut.org> 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