Re: Re: Partitioning such that key field of inherited tables no longer retains any selectivity

[Tim Kane <tim.kane@xxxxxxxxx>]

Title: Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation)

From: Tom Lane <tgl@xxxxxxxxxxxxx>

David G Johnston <david.g.johnston@xxxxxxxxx> writes:

Two approaches:

1. Standard virtual column name that, when used, gets rewritten into a

constant that is stored at the table level.

2. A way for a column's value to be defined as a function call.

Recent versions of the SQL spec have a notion of "generated columns"

that I think subsumes both of these concepts.  We had a draft patch

awhile back that attempted to implement that feature.  It crashed

and burned for reasons I don't recall ... but certainly implementing

an already-standardized feature is more attractive than just inventing

behavior on our own.

That sounds interesting.

Is this what you are referring to?  Actually, it looks like it would fit the bill and then some.

—————————————————

4.14.8 Base columns and generated columns

A column of a base table is either a base column or a generated column. A base column is one that is not a generated column. A generated column is one whose values are determined by evaluation of a generation _expression_, a <value _expression_> whose declared type is by implication that of the column. A generation _expression_ can reference base columns of the base table to which it belongs but cannot otherwise access SQL- data. Thus, the value of the field corresponding to a generated column in row R is determined by the values of zero or more other fields of R.

A generated column GC depends on each column that is referenced by a <column reference> in its generation _expression_, and each such referenced column is a parametric column of GC.

 —————————————————