PostgreSQL 11 preview - 虚拟列(自动根据表达式产生值)

背景

通过增加虚拟字段，可以让数据库根据虚拟列的定义，自动填充值。

与自增，DEFAULT不同的是，虚拟列中可以从其他列的内容产生。

例如

  CREATE TABLE t1 (  
    ...,  
    height_cm numeric,  
    height_in numeric GENERATED ALWAYS AS (height_cm * 2.54)  
  );  

PostgreSQL 11中，有望提交这个PATCH，使用前请详细参考文档说明。

Here is another attempt to implement generated columns.  This is a  
well-known SQL-standard feature, also available for instance in DB2,  
MySQL, Oracle.  A quick example:  
  
  CREATE TABLE t1 (  
    ...,  
    height_cm numeric,  
    height_in numeric GENERATED ALWAYS AS (height_cm * 2.54)  
  );  
  
(This is not related to the recent identity columns feature, other than  
the similar syntax and some overlap internally.)  
  
In previous discussions, it has often been a source of confusion whether  
these generated columns are supposed to be computed on insert/update and  
stored, or computed when read.  The SQL standard is not explicit, but  
appears to lean toward stored.  DB2 stores.  Oracle computes on read.  
MySQL supports both.  So I target implementing both.  This makes sense:  
Both regular views and materialized views have their uses, too.  For the  
syntax, I use the MySQL/Oracle syntax of appending [VIRTUAL|STORED].  In  
this patch, only VIRTUAL is fully implemented.  I also have STORED kind  
of working, but it wasn't fully baked, so I haven't included it here.  
  
Known bugs:  
  
- pg_dump produces a warning about a dependency loop when dumping these.  
 Will need to be fixed at some point, but it doesn't prevent anything  
from working right now.  
  
Open design issues:  
  
- COPY behavior: Currently, generated columns are automatically omitted  
if there is no column list, and prohibited if specified explicitly.  
When stored generated columns are implemented, they could be copied out.  
 Some user options might be possible here.  
  
- Catalog storage: I store the generation expression in pg_attrdef, like  
a default.  For the most part, this works well.  It is not clear,  
however, what pg_attribute.atthasdef should say.  Half the code thinks  
that atthasdef means "there is something in pg_attrdef", the other half  
thinks "column has a DEFAULT expression".  Currently, I'm going with the  
former interpretation, because that is wired in quite deeply and things  
start to crash if you violate it, but then code that wants to know  
whether a column has a traditional DEFAULT expression needs to check  
atthasdef && !attgenerated or something like that.  
  
Missing/future functionality:  
  
- STORED variant  
  
- various ALTER TABLE variants  
  
- index support (and related constraint support)  
  
These can be added later once the basics are nailed down.  
  
--   
Peter Eisentraut              http://www.2ndQuadrant.com/  
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services  

参考

https://commitfest.postgresql.org/17/1443/

https://www.postgresql.org/message-id/flat/b151f851-4019-bdb1-699e-ebab07d2f40a@2ndquadrant.com#b151f851-4019-bdb1-699e-ebab07d2f40a@2ndquadrant.com

digoal’s 大量PostgreSQL文章入口