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(at)eisentraut(dot)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