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
From f0938109f995adf7b4b0b4adbe652d9881549cee Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Wed, 30 Aug 2017 23:38:08 -0400
Subject: [PATCH] Generated columns
This is an SQL-standard feature that allows creating columns that are
computed from expressions rather than assigned, similar to a view but on
a column basis.
---
doc/src/sgml/catalogs.sgml | 11 +
doc/src/sgml/information_schema.sgml | 10 +-
doc/src/sgml/ref/copy.sgml | 3 +-
doc/src/sgml/ref/create_table.sgml | 46 +++-
src/backend/access/common/tupdesc.c | 5 +
src/backend/catalog/genbki.pl | 3 +
src/backend/catalog/heap.c | 93 +++++--
src/backend/catalog/index.c | 1 +
src/backend/catalog/information_schema.sql | 8 +-
src/backend/commands/copy.c | 12 +-
src/backend/commands/indexcmds.c | 24 +-
src/backend/commands/tablecmds.c | 45 +++-
src/backend/commands/trigger.c | 36 +++
src/backend/commands/typecmds.c | 6 +-
src/backend/executor/execMain.c | 7 +-
src/backend/nodes/copyfuncs.c | 2 +
src/backend/nodes/equalfuncs.c | 2 +
src/backend/nodes/outfuncs.c | 9 +
src/backend/parser/gram.y | 26 +-
src/backend/parser/parse_agg.c | 11 +
src/backend/parser/parse_expr.c | 5 +
src/backend/parser/parse_func.c | 12 +
src/backend/parser/parse_utilcmd.c | 87 ++++++-
src/backend/replication/logical/worker.c | 2 +-
src/backend/rewrite/rewriteHandler.c | 122 +++++++++-
src/backend/utils/cache/lsyscache.c | 32 +++
src/backend/utils/cache/relcache.c | 1 +
src/bin/pg_dump/pg_dump.c | 39 ++-
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/t/002_pg_dump.pl | 39 +++
src/bin/psql/describe.c | 28 ++-
src/include/catalog/catversion.h | 2 +-
src/include/catalog/heap.h | 5 +-
src/include/catalog/pg_attribute.h | 23 +-
src/include/catalog/pg_class.h | 2 +-
src/include/nodes/parsenodes.h | 12 +-
src/include/parser/kwlist.h | 2 +
src/include/parser/parse_node.h | 3 +-
src/include/rewrite/rewriteHandler.h | 3 +-
src/include/utils/lsyscache.h | 1 +
src/test/regress/expected/create_table_like.out | 46 ++++
src/test/regress/expected/generated.out | 309 ++++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/serial_schedule | 1 +
src/test/regress/sql/create_table_like.sql | 14 ++
src/test/regress/sql/generated.sql | 192 +++++++++++++++
46 files changed, 1262 insertions(+), 83 deletions(-)
create mode 100644 src/test/regress/expected/generated.out
create mode 100644 src/test/regress/sql/generated.sql
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index ef7054cf26..f5fb5e9291 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1140,6 +1140,17 @@ <title><structname>pg_attribute</> Columns</title>
</entry>
</row>
+ <row>
+ <entry><structfield>attgenerated</structfield></entry>
+ <entry><type>char</type></entry>
+ <entry></entry>
+ <entry>
+ If a zero byte (<literal>''</literal>), then not a generated column.
+ Otherwise, <literal>s</literal> = stored, <literal>v</literal> =
+ virtual.
+ </entry>
+ </row>
+
<row>
<entry><structfield>attisdropped</structfield></entry>
<entry><type>bool</type></entry>
diff --git a/doc/src/sgml/information_schema.sgml
b/doc/src/sgml/information_schema.sgml
index e07ff35bca..3b7dd3ac17 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -1648,13 +1648,19 @@ <title><literal>columns</literal> Columns</title>
<row>
<entry><literal>is_generated</literal></entry>
<entry><type>character_data</type></entry>
- <entry>Applies to a feature not available in
<productname>PostgreSQL</></entry>
+ <entry>
+ If the column is a generated column, then <literal>ALWAYS</literal>,
+ else <literal>NEVER</literal>.
+ </entry>
</row>
<row>
<entry><literal>generation_expression</literal></entry>
<entry><type>character_data</type></entry>
- <entry>Applies to a feature not available in
<productname>PostgreSQL</></entry>
+ <entry>
+ If the column is a generated column, then the generation expression,
+ else null.
+ </entry>
</row>
<row>
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index 8de1150dfb..f08fc7d518 100644
--- a/doc/src/sgml/ref/copy.sgml
+++ b/doc/src/sgml/ref/copy.sgml
@@ -103,7 +103,8 @@ <title>Parameters</title>
<listitem>
<para>
An optional list of columns to be copied. If no column list is
- specified, all columns of the table will be copied.
+ specified, all columns of the table excepted generated columns will be
+ copied.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_table.sgml
b/doc/src/sgml/ref/create_table.sgml
index e9c2c49533..a0e9751a93 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -62,6 +62,7 @@
NULL |
CHECK ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO
INHERIT ] |
DEFAULT <replaceable>default_expr</replaceable> |
+ GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ VIRTUAL
| STORED ] |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ (
<replaceable>sequence_options</replaceable> ) ] |
UNIQUE <replaceable class="PARAMETER">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="PARAMETER">index_parameters</replaceable> |
@@ -82,7 +83,7 @@
<phrase>and <replaceable class="PARAMETER">like_option</replaceable>
is:</phrase>
-{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | IDENTITY | INDEXES |
STORAGE | COMMENTS | ALL }
+{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | IDENTITY | GENERATED |
INDEXES | STORAGE | COMMENTS | ALL }
<phrase>and <replaceable class="PARAMETER">partition_bound_spec</replaceable>
is:</phrase>
@@ -530,6 +531,12 @@ <title>Parameters</title>
sequence is created for each identity column of the new table, separate
from the sequences associated with the old table.
</para>
+ <para>
+ Generated columns will only become generated columns in the new table
+ if <literal>INCLUDING GENERATED</literal> is specified, which will copy
+ the generation expression and the virtual/stored choice. Otherwise, the
+ new column will be a regular base column.
+ </para>
<para>
Not-null constraints are always copied to the new table.
<literal>CHECK</literal> constraints will be copied only if
@@ -562,7 +569,7 @@ <title>Parameters</title>
</para>
<para>
<literal>INCLUDING ALL</literal> is an abbreviated form of
- <literal>INCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING CONSTRAINTS
INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS</literal>.
+ <literal>INCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING GENERATED
INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING
COMMENTS</literal>.
</para>
<para>
Note that unlike <literal>INHERITS</literal>, columns and
@@ -676,6 +683,31 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>GENERATED ALWAYS AS (
<replaceable>generation_expr</replaceable> ) [ VIRTUAL | STORED
]</literal><indexterm><primary>generated column</primary></indexterm></term>
+ <listitem>
+ <para>
+ This clause creates the column as a <firstterm>generated
+ column</firstterm>. The column cannot be written to, and when read it
+ will be computed from the specified expression.
+ </para>
+
+ <para>
+ When <literal>VIRTUAL</literal> is specified, the column will be
+ computed when it is read, and it will not occupy any storage.
+ When <literal>STORED</literal> is specified, the column will be computed
+ on write and will be stored on disk. <literal>VIRTUAL</literal> is the
+ default.
+ </para>
+
+ <para>
+ The generation expression can refer to other columns in the table, but
+ not other generated columns. Any functions and operators used must be
+ immutable. References to other tables are not allowed.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ (
<replaceable>sequence_options</replaceable> ) ]</literal></term>
<listitem>
@@ -1832,6 +1864,16 @@ <title>Multiple Identity Columns</title>
</para>
</refsect2>
+ <refsect2>
+ <title>Generated Columns</title>
+
+ <para>
+ The options <literal>VIRTUAL</literal> and <literal>STORED</literal> are
+ not standard but are also used by other SQL implementations. The SQL
+ standard does not specify the storage of generated columns.
+ </para>
+ </refsect2>
+
<refsect2>
<title><literal>LIKE</> Clause</title>
diff --git a/src/backend/access/common/tupdesc.c
b/src/backend/access/common/tupdesc.c
index 4436c86361..aae3735697 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -114,6 +114,7 @@ CreateTupleDescCopy(TupleDesc tupdesc)
att->attnotnull = false;
att->atthasdef = false;
att->attidentity = '\0';
+ att->attgenerated = '\0';
}
desc->tdtypeid = tupdesc->tdtypeid;
@@ -226,6 +227,7 @@ TupleDescCopyEntry(TupleDesc dst, AttrNumber dstAttno,
dstAtt->attnotnull = false;
dstAtt->atthasdef = false;
dstAtt->attidentity = '\0';
+ dstAtt->attgenerated = '\0';
}
/*
@@ -372,6 +374,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
return false;
if (attr1->attidentity != attr2->attidentity)
return false;
+ if (attr1->attgenerated != attr2->attgenerated)
+ return false;
if (attr1->attisdropped != attr2->attisdropped)
return false;
if (attr1->attislocal != attr2->attislocal)
@@ -531,6 +535,7 @@ TupleDescInitEntry(TupleDesc desc,
att->attnotnull = false;
att->atthasdef = false;
att->attidentity = '\0';
+ att->attgenerated = '\0';
att->attisdropped = false;
att->attislocal = true;
att->attinhcount = 0;
diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl
index 2eebb061b7..8a2b79e972 100644
--- a/src/backend/catalog/genbki.pl
+++ b/src/backend/catalog/genbki.pl
@@ -446,6 +446,7 @@ sub emit_pgattr_row
atttypmod => '-1',
atthasdef => 'f',
attidentity => '',
+ attgenerated => '',
attisdropped => 'f',
attislocal => 't',
attinhcount => '0',
@@ -475,12 +476,14 @@ sub emit_schemapg_row
# Replace empty string by zero char constant
$row->{attidentity} ||= '\0';
+ $row->{attgenerated} ||= '\0';
# Supply appropriate quoting for these fields.
$row->{attname} = q|{"| . $row->{attname} . q|"}|;
$row->{attstorage} = q|'| . $row->{attstorage} . q|'|;
$row->{attalign} = q|'| . $row->{attalign} . q|'|;
$row->{attidentity} = q|'| . $row->{attidentity} . q|'|;
+ $row->{attgenerated} = q|'| . $row->{attgenerated} . q|'|;
# We don't emit initializers for the variable length fields at all.
# Only the fixed-size portions of the descriptors are ever used.
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 45ee9ac8b9..441ef7d637 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -67,6 +67,7 @@
#include "parser/parse_collate.h"
#include "parser/parse_expr.h"
#include "parser/parse_relation.h"
+#include "parser/parsetree.h"
#include "storage/lmgr.h"
#include "storage/predicate.h"
#include "storage/smgr.h"
@@ -144,37 +145,37 @@ static List *insert_ordered_unique_oid(List *list, Oid
datum);
static FormData_pg_attribute a1 = {
0, {"ctid"}, TIDOID, 0, sizeof(ItemPointerData),
SelfItemPointerAttributeNumber, 0, -1, -1,
- false, 'p', 's', true, false, '\0', false, true, 0
+ false, 'p', 's', true, false, '\0', '\0', false, true, 0
};
static FormData_pg_attribute a2 = {
0, {"oid"}, OIDOID, 0, sizeof(Oid),
ObjectIdAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, '\0', false, true, 0
+ true, 'p', 'i', true, false, '\0', '\0', false, true, 0
};
static FormData_pg_attribute a3 = {
0, {"xmin"}, XIDOID, 0, sizeof(TransactionId),
MinTransactionIdAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, '\0', false, true, 0
+ true, 'p', 'i', true, false, '\0', '\0', false, true, 0
};
static FormData_pg_attribute a4 = {
0, {"cmin"}, CIDOID, 0, sizeof(CommandId),
MinCommandIdAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, '\0', false, true, 0
+ true, 'p', 'i', true, false, '\0', '\0', false, true, 0
};
static FormData_pg_attribute a5 = {
0, {"xmax"}, XIDOID, 0, sizeof(TransactionId),
MaxTransactionIdAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, '\0', false, true, 0
+ true, 'p', 'i', true, false, '\0', '\0', false, true, 0
};
static FormData_pg_attribute a6 = {
0, {"cmax"}, CIDOID, 0, sizeof(CommandId),
MaxCommandIdAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, '\0', false, true, 0
+ true, 'p', 'i', true, false, '\0', '\0', false, true, 0
};
/*
@@ -186,7 +187,7 @@ static FormData_pg_attribute a6 = {
static FormData_pg_attribute a7 = {
0, {"tableoid"}, OIDOID, 0, sizeof(Oid),
TableOidAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, '\0', false, true, 0
+ true, 'p', 'i', true, false, '\0', '\0', false, true, 0
};
static const Form_pg_attribute SysAtt[] = {&a1, &a2, &a3, &a4, &a5, &a6, &a7};
@@ -624,6 +625,7 @@ InsertPgAttributeTuple(Relation pg_attribute_rel,
values[Anum_pg_attribute_attnotnull - 1] =
BoolGetDatum(new_attribute->attnotnull);
values[Anum_pg_attribute_atthasdef - 1] =
BoolGetDatum(new_attribute->atthasdef);
values[Anum_pg_attribute_attidentity - 1] =
CharGetDatum(new_attribute->attidentity);
+ values[Anum_pg_attribute_attgenerated - 1] =
CharGetDatum(new_attribute->attgenerated);
values[Anum_pg_attribute_attisdropped - 1] =
BoolGetDatum(new_attribute->attisdropped);
values[Anum_pg_attribute_attislocal - 1] =
BoolGetDatum(new_attribute->attislocal);
values[Anum_pg_attribute_attinhcount - 1] =
Int32GetDatum(new_attribute->attinhcount);
@@ -1901,7 +1903,7 @@ heap_drop_with_catalog(Oid relid)
*/
Oid
StoreAttrDefault(Relation rel, AttrNumber attnum,
- Node *expr, bool is_internal)
+ Node *expr, bool is_internal, bool
generated_col)
{
char *adbin;
char *adsrc;
@@ -1988,7 +1990,22 @@ StoreAttrDefault(Relation rel, AttrNumber attnum,
/*
* Record dependencies on objects used in the expression, too.
*/
- recordDependencyOnExpr(&defobject, expr, NIL, DEPENDENCY_NORMAL);
+ if (generated_col)
+ /*
+ * Generated column: Dropping anything that the generation
expression
+ * refers automatically drops the generated column.
+ */
+ recordDependencyOnSingleRelExpr(&colobject, expr,
RelationGetRelid(rel),
+
DEPENDENCY_AUTO,
+
DEPENDENCY_AUTO, false);
+ else
+ /*
+ * Normal default: Dropping anything that the default refers to
+ * requires CASCADE and drops the default only.
+ */
+ recordDependencyOnSingleRelExpr(&defobject, expr,
RelationGetRelid(rel),
+
DEPENDENCY_NORMAL,
+
DEPENDENCY_NORMAL, false);
/*
* Post creation hook for attribute defaults.
@@ -2153,7 +2170,7 @@ StoreConstraints(Relation rel, List *cooked_constraints,
bool is_internal)
{
case CONSTR_DEFAULT:
con->conoid = StoreAttrDefault(rel,
con->attnum, con->expr,
-
is_internal);
+
is_internal, false);
break;
case CONSTR_CHECK:
con->conoid =
@@ -2252,7 +2269,8 @@ AddRelationNewConstraints(Relation rel,
expr = cookDefault(pstate, colDef->raw_default,
atp->atttypid,
atp->atttypmod,
- NameStr(atp->attname));
+ NameStr(atp->attname),
+ atp->attgenerated);
/*
* If the expression is just a NULL constant, we do not bother
to make
@@ -2269,7 +2287,8 @@ AddRelationNewConstraints(Relation rel,
(IsA(expr, Const) &&((Const *) expr)->constisnull))
continue;
- defOid = StoreAttrDefault(rel, colDef->attnum, expr,
is_internal);
+ defOid = StoreAttrDefault(rel, colDef->attnum, expr,
is_internal,
+
(atp->attgenerated != '\0'));
cooked = (CookedConstraint *) palloc(sizeof(CookedConstraint));
cooked->contype = CONSTR_DEFAULT;
@@ -2617,6 +2636,46 @@ SetRelationNumChecks(Relation rel, int numchecks)
heap_close(relrel, RowExclusiveLock);
}
+/*
+ * Check for references to generated columns
+ */
+static bool
+check_nested_generated_walker(Node *node, void *context)
+{
+ ParseState *pstate = context;
+
+ if (node == NULL)
+ return false;
+ else if (IsA(node, Var))
+ {
+ Var *var = (Var *) node;
+ Oid relid;
+ AttrNumber attnum;
+
+ relid = getrelid(var->varno, pstate->p_rtable);
+ attnum = var->varattno;
+
+ if (relid && attnum && get_attgenerated(relid, attnum))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot use generated column
\"%s\" in column generation expression",
+ get_attname(relid,
attnum)),
+ errdetail("A generated column cannot
reference another generated column."),
+ parser_errposition(pstate,
var->location)));
+
+ return false;
+ }
+ else
+ return expression_tree_walker(node,
check_nested_generated_walker,
+ (void
*) context);
+}
+
+static void
+check_nested_generated(ParseState *pstate, Node *node)
+{
+ check_nested_generated_walker(node, pstate);
+}
+
/*
* Take a raw default and convert it to a cooked format ready for
* storage.
@@ -2634,7 +2693,8 @@ cookDefault(ParseState *pstate,
Node *raw_default,
Oid atttypid,
int32 atttypmod,
- char *attname)
+ char *attname,
+ char attgenerated)
{
Node *expr;
@@ -2643,17 +2703,20 @@ cookDefault(ParseState *pstate,
/*
* Transform raw parsetree to executable expression.
*/
- expr = transformExpr(pstate, raw_default, EXPR_KIND_COLUMN_DEFAULT);
+ expr = transformExpr(pstate, raw_default, attgenerated ?
EXPR_KIND_GENERATED_COLUMN : EXPR_KIND_COLUMN_DEFAULT);
/*
* Make sure default expr does not refer to any vars (we need this check
* since the pstate includes the target table).
*/
- if (contain_var_clause(expr))
+ if (!attgenerated && contain_var_clause(expr))
ereport(ERROR,
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
errmsg("cannot use column references in
default expression")));
+ if (attgenerated)
+ check_nested_generated(pstate, expr);
+
/*
* transformExpr() should have already rejected subqueries, aggregates,
* window functions, and SRFs, based on the EXPR_KIND_ for a default
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index c7b2f031f0..7b8ca2ce6f 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -354,6 +354,7 @@ ConstructTupleDescriptor(Relation heapRelation,
to->attnotnull = false;
to->atthasdef = false;
to->attidentity = '\0';
+ to->attgenerated = '\0';
to->attislocal = true;
to->attinhcount = 0;
to->attcollation = collationObjectId[i];
diff --git a/src/backend/catalog/information_schema.sql
b/src/backend/catalog/information_schema.sql
index 236f6be37e..f0c8b2dec8 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -299,7 +299,7 @@ CREATE VIEW attributes AS
CAST(c.relname AS sql_identifier) AS udt_name,
CAST(a.attname AS sql_identifier) AS attribute_name,
CAST(a.attnum AS cardinal_number) AS ordinal_position,
- CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS
attribute_default,
+ CAST(pg_get_expr(ad.adbin, ad.adrelid, true) AS character_data) AS
attribute_default,
CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull)
THEN 'NO' ELSE 'YES' END
AS yes_or_no)
AS is_nullable, -- This column was apparently removed between
SQL:2003 and SQL:2008.
@@ -656,7 +656,7 @@ CREATE VIEW columns AS
CAST(c.relname AS sql_identifier) AS table_name,
CAST(a.attname AS sql_identifier) AS column_name,
CAST(a.attnum AS cardinal_number) AS ordinal_position,
- CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS
column_default,
+ CAST(CASE WHEN a.attgenerated = '' THEN pg_get_expr(ad.adbin,
ad.adrelid, true) END AS character_data) AS column_default,
CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull)
THEN 'NO' ELSE 'YES' END
AS yes_or_no)
AS is_nullable,
@@ -745,8 +745,8 @@ CREATE VIEW columns AS
CAST(seq.seqmin AS character_data) AS identity_minimum,
CAST(CASE WHEN seq.seqcycle THEN 'YES' ELSE 'NO' END AS yes_or_no)
AS identity_cycle,
- CAST('NEVER' AS character_data) AS is_generated,
- CAST(null AS character_data) AS generation_expression,
+ CAST(CASE WHEN a.attgenerated <> '' THEN 'ALWAYS' ELSE 'NEVER' END
AS character_data) AS is_generated,
+ CAST(CASE WHEN a.attgenerated <> '' THEN pg_get_expr(ad.adbin,
ad.adrelid, true) END AS character_data) AS generation_expression,
CAST(CASE WHEN c.relkind IN ('r', 'p') OR
(c.relkind IN ('v', 'f') AND
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index cfa3f059c2..13e2a15d86 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -3039,12 +3039,12 @@ BeginCopyFrom(ParseState *pstate,
fmgr_info(in_func_oid, &in_functions[attnum - 1]);
/* Get default info if needed */
- if (!list_member_int(cstate->attnumlist, attnum))
+ if (!list_member_int(cstate->attnumlist, attnum) &&
!att->attgenerated)
{
/* attribute is NOT to be copied from input */
/* use default value if one exists */
Expr *defexpr = (Expr *)
build_column_default(cstate->rel,
-
attnum);
+
attnum, true);
if (defexpr != NULL)
{
@@ -4719,6 +4719,8 @@ CopyGetAttnums(TupleDesc tupDesc, Relation rel, List
*attnamelist)
{
if (TupleDescAttr(tupDesc, i)->attisdropped)
continue;
+ if (TupleDescAttr(tupDesc, i)->attgenerated)
+ continue; /* TODO: could be a
COPY option */
attnums = lappend_int(attnums, i + 1);
}
}
@@ -4743,6 +4745,12 @@ CopyGetAttnums(TupleDesc tupDesc, Relation rel, List
*attnamelist)
continue;
if (namestrcmp(&(att->attname), name) == 0)
{
+ if (att->attgenerated)
+ ereport(ERROR,
+
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+ errmsg("column
\"%s\" is a generated column",
+
name),
+
errdetail("Generated columns cannot be used in COPY.")));
attnum = att->attnum;
break;
}
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index b61aaac284..655a58e4d8 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -596,6 +596,8 @@ DefineIndex(Oid relationId,
/*
* We disallow indexes on system columns other than OID. They would not
* necessarily get updated correctly, and they don't seem useful anyway.
+ *
+ * Also disallow generated columns in indexes. (could be implemented)
*/
for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
{
@@ -605,10 +607,16 @@ DefineIndex(Oid relationId,
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("index creation on system
columns is not supported")));
+
+ if (get_attgenerated(relationId, attno))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("index creation on generated
columns is not supported")));
}
/*
- * Also check for system columns used in expressions or predicates.
+ * Also check for system and generated columns used in expressions or
+ * predicates.
*/
if (indexInfo->ii_Expressions || indexInfo->ii_Predicate)
{
@@ -617,14 +625,20 @@ DefineIndex(Oid relationId,
pull_varattnos((Node *) indexInfo->ii_Expressions, 1,
&indexattrs);
pull_varattnos((Node *) indexInfo->ii_Predicate, 1,
&indexattrs);
- for (i = FirstLowInvalidHeapAttributeNumber + 1; i < 0; i++)
+ i = -1;
+ while ((i = bms_next_member(indexattrs, i)) >= 0)
{
- if (i != ObjectIdAttributeNumber &&
- bms_is_member(i -
FirstLowInvalidHeapAttributeNumber,
- indexattrs))
+ AttrNumber attno = i +
FirstLowInvalidHeapAttributeNumber;
+
+ if (attno < 0 && attno != ObjectIdAttributeNumber)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("index creation on
system columns is not supported")));
+
+ if (get_attgenerated(relationId, attno))
+ ereport(ERROR,
+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("index creation on
generated columns is not supported")));
}
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 0f08245a67..60cdd936b3 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -722,6 +722,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
if (colDef->identity)
attr->attidentity = colDef->identity;
+
+ if (colDef->generated)
+ attr->attgenerated = colDef->generated;
}
/*
@@ -5270,6 +5273,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab,
Relation rel,
attribute.attnotnull = colDef->is_not_null;
attribute.atthasdef = false;
attribute.attidentity = colDef->identity;
+ attribute.attgenerated = colDef->generated;
attribute.attisdropped = false;
attribute.attislocal = colDef->is_local;
attribute.attinhcount = colDef->inhcount;
@@ -5355,7 +5359,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab,
Relation rel,
if (relkind != RELKIND_VIEW && relkind != RELKIND_COMPOSITE_TYPE
&& relkind != RELKIND_FOREIGN_TABLE && attribute.attnum > 0)
{
- defval = (Expr *) build_column_default(rel, attribute.attnum);
+ defval = (Expr *) build_column_default(rel, attribute.attnum,
true);
if (!defval && DomainHasConstraints(typeOid))
{
@@ -7147,6 +7151,41 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab,
Relation rel,
*/
checkFkeyPermissions(pkrel, pkattnum, numpks);
+ /*
+ * Foreign keys on generated columns are not yet implemented.
+ */
+ for (i = 0; i < numpks; i++)
+ {
+ if (get_attgenerated(RelationGetRelid(pkrel), pkattnum[i]))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("foreign key constraints
referencing generated columns are not supported")));
+ }
+ for (i = 0; i < numfks; i++)
+ {
+ if (get_attgenerated(RelationGetRelid(rel), fkattnum[i]))
+ {
+ /*
+ * Check restrictions on UPDATE/DELETE actions, per SQL
standard
+ */
+ if (fkconstraint->fk_upd_action ==
FKCONSTR_ACTION_SETNULL ||
+ fkconstraint->fk_upd_action ==
FKCONSTR_ACTION_SETDEFAULT ||
+ fkconstraint->fk_upd_action ==
FKCONSTR_ACTION_CASCADE)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid ON UPDATE
action for foreign key constraint containing generated column")));
+ if (fkconstraint->fk_del_action ==
FKCONSTR_ACTION_SETNULL ||
+ fkconstraint->fk_del_action ==
FKCONSTR_ACTION_SETDEFAULT)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid ON DELETE
action for foreign key constraint containing generated column")));
+
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("foreign key constraints on
generated columns are not supported")));
+ }
+ }
+
/*
* Look up the equality operators to use in the constraint.
*
@@ -9031,7 +9070,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
*/
if (attTup->atthasdef)
{
- defaultexpr = build_column_default(rel, attnum);
+ defaultexpr = build_column_default(rel, attnum, true);
Assert(defaultexpr);
defaultexpr = strip_implicit_coercions(defaultexpr);
defaultexpr = coerce_to_target_type(NULL, /* no UNKNOWN
params */
@@ -9372,7 +9411,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
RemoveAttrDefault(RelationGetRelid(rel), attnum, DROP_RESTRICT,
true,
true);
- StoreAttrDefault(rel, attnum, defaultexpr, true);
+ StoreAttrDefault(rel, attnum, defaultexpr, true, false);
}
ObjectAddressSubSet(address, RelationRelationId,
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index da0850bfd6..853db162d2 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -43,6 +43,7 @@
#include "parser/parse_relation.h"
#include "parser/parsetree.h"
#include "pgstat.h"
+#include "rewrite/rewriteHandler.h"
#include "rewrite/rewriteManip.h"
#include "storage/bufmgr.h"
#include "storage/lmgr.h"
@@ -100,6 +101,7 @@ static void AfterTriggerSaveEvent(EState *estate,
ResultRelInfo *relinfo,
List *recheckIndexes, Bitmapset
*modifiedCols,
TransitionCaptureState
*transition_capture);
static void AfterTriggerEnlargeQueryState(void);
+static void check_modified_virtual_generated(TupleDesc tupdesc, HeapTuple
tuple);
/*
@@ -536,6 +538,11 @@ CreateTrigger(CreateTrigStmt *stmt, const char
*queryString,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("BEFORE
trigger's WHEN condition cannot reference NEW system columns"),
parser_errposition(pstate, var->location)));
+ if
(get_attgenerated(RelationGetRelid(rel), var->varattno) &&
TRIGGER_FOR_BEFORE(tgtype))
+ ereport(ERROR,
+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("BEFORE
trigger's WHEN condition cannot reference NEW generated columns"),
+
parser_errposition(pstate, var->location)));
break;
default:
/* can't happen without
add_missing_from, so just elog */
@@ -2401,6 +2408,8 @@ ExecBRInsertTriggers(EState *estate, ResultRelInfo
*relinfo,
TupleTableSlot *newslot = estate->es_trig_tuple_slot;
TupleDesc tupdesc =
RelationGetDescr(relinfo->ri_RelationDesc);
+ check_modified_virtual_generated(tupdesc, newtuple);
+
if (newslot->tts_tupleDescriptor != tupdesc)
ExecSetSlotDescriptor(newslot, tupdesc);
ExecStoreTuple(newtuple, newslot, InvalidBuffer, false);
@@ -2878,6 +2887,8 @@ ExecBRUpdateTriggers(EState *estate, EPQState *epqstate,
TupleTableSlot *newslot = estate->es_trig_tuple_slot;
TupleDesc tupdesc =
RelationGetDescr(relinfo->ri_RelationDesc);
+ check_modified_virtual_generated(tupdesc, newtuple);
+
if (newslot->tts_tupleDescriptor != tupdesc)
ExecSetSlotDescriptor(newslot, tupdesc);
ExecStoreTuple(newtuple, newslot, InvalidBuffer, false);
@@ -3270,6 +3281,7 @@ TriggerEnabled(EState *estate, ResultRelInfo *relinfo,
oldContext =
MemoryContextSwitchTo(estate->es_query_cxt);
tgqual = stringToNode(trigger->tgqual);
+ tgqual = (Node *)
expand_generated_columns_in_expr((Expr *) tgqual, relinfo->ri_RelationDesc);
/* Change references to OLD and NEW to INNER_VAR and
OUTER_VAR */
ChangeVarNodes(tgqual, PRS2_OLD_VARNO, INNER_VAR, 0);
ChangeVarNodes(tgqual, PRS2_NEW_VARNO, OUTER_VAR, 0);
@@ -5499,3 +5511,27 @@ pg_trigger_depth(PG_FUNCTION_ARGS)
{
PG_RETURN_INT32(MyTriggerDepth);
}
+
+/*
+ * Check whether a trigger modified a virtual generated column and error if
+ * so.
+ */
+static void
+check_modified_virtual_generated(TupleDesc tupdesc, HeapTuple tuple)
+{
+ int i;
+
+ for (i = 0; i < tupdesc->natts; i++)
+ {
+ if (TupleDescAttr(tupdesc, i)->attgenerated ==
ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ bool isnull;
+
+ fastgetattr(tuple, i + 1, tupdesc, &isnull);
+ if (!isnull)
+ ereport(ERROR,
+
(errcode(ERRCODE_E_R_I_E_TRIGGER_PROTOCOL_VIOLATED),
+ errmsg("trigger modified
virtual generated column value")));
+ }
+ }
+}
diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c
index 7ed16aeff4..25abcee86b 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -912,7 +912,8 @@ DefineDomain(CreateDomainStmt *stmt)
defaultExpr = cookDefault(pstate,
constr->raw_expr,
basetypeoid,
basetypeMod,
-
domainName);
+
domainName,
+
0);
/*
* If the expression is just a NULL
constant, we treat it
@@ -2172,7 +2173,8 @@ AlterDomainDefault(List *names, Node *defaultRaw)
defaultExpr = cookDefault(pstate, defaultRaw,
typTup->typbasetype,
typTup->typtypmod,
-
NameStr(typTup->typname));
+
NameStr(typTup->typname),
+ 0);
/*
* If the expression is just a NULL constant, we treat the
command
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 2946a0edee..4ed838abec 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -53,7 +53,7 @@
#include "miscadmin.h"
#include "optimizer/clauses.h"
#include "parser/parsetree.h"
-#include "rewrite/rewriteManip.h"
+#include "rewrite/rewriteHandler.h"
#include "storage/bufmgr.h"
#include "storage/lmgr.h"
#include "tcop/utility.h"
@@ -1807,6 +1807,7 @@ ExecRelCheck(ResultRelInfo *resultRelInfo,
Expr *checkconstr;
checkconstr = stringToNode(check[i].ccbin);
+ checkconstr =
expand_generated_columns_in_expr(checkconstr, rel);
resultRelInfo->ri_ConstraintExprs[i] =
ExecPrepareExpr(checkconstr, estate);
}
@@ -2268,6 +2269,10 @@ ExecBuildSlotValueDescription(Oid reloid,
if (att->attisdropped)
continue;
+ /* ignore virtual generated columns; they are always null here
*/
+ if (att->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ continue;
+
if (!table_perm)
{
/*
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index f9ddf4ed76..8ceaa556bc 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2814,6 +2814,7 @@ _copyColumnDef(const ColumnDef *from)
COPY_NODE_FIELD(raw_default);
COPY_NODE_FIELD(cooked_default);
COPY_SCALAR_FIELD(identity);
+ COPY_SCALAR_FIELD(generated);
COPY_NODE_FIELD(collClause);
COPY_SCALAR_FIELD(collOid);
COPY_NODE_FIELD(constraints);
@@ -2837,6 +2838,7 @@ _copyConstraint(const Constraint *from)
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
COPY_SCALAR_FIELD(generated_when);
+ COPY_SCALAR_FIELD(generated_kind);
COPY_NODE_FIELD(keys);
COPY_NODE_FIELD(exclusions);
COPY_NODE_FIELD(options);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 8d92c03633..5be63d2f54 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2544,6 +2544,7 @@ _equalColumnDef(const ColumnDef *a, const ColumnDef *b)
COMPARE_NODE_FIELD(raw_default);
COMPARE_NODE_FIELD(cooked_default);
COMPARE_SCALAR_FIELD(identity);
+ COMPARE_SCALAR_FIELD(generated);
COMPARE_NODE_FIELD(collClause);
COMPARE_SCALAR_FIELD(collOid);
COMPARE_NODE_FIELD(constraints);
@@ -2565,6 +2566,7 @@ _equalConstraint(const Constraint *a, const Constraint *b)
COMPARE_NODE_FIELD(raw_expr);
COMPARE_STRING_FIELD(cooked_expr);
COMPARE_SCALAR_FIELD(generated_when);
+ COMPARE_SCALAR_FIELD(generated_kind);
COMPARE_NODE_FIELD(keys);
COMPARE_NODE_FIELD(exclusions);
COMPARE_NODE_FIELD(options);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 9ee3e23761..0ff1b00f24 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2801,6 +2801,7 @@ _outColumnDef(StringInfo str, const ColumnDef *node)
WRITE_NODE_FIELD(raw_default);
WRITE_NODE_FIELD(cooked_default);
WRITE_CHAR_FIELD(identity);
+ WRITE_CHAR_FIELD(generated);
WRITE_NODE_FIELD(collClause);
WRITE_OID_FIELD(collOid);
WRITE_NODE_FIELD(constraints);
@@ -3452,6 +3453,14 @@ _outConstraint(StringInfo str, const Constraint *node)
WRITE_CHAR_FIELD(generated_when);
break;
+ case CONSTR_GENERATED:
+ appendStringInfoString(str, "GENERATED");
+ WRITE_NODE_FIELD(raw_expr);
+ WRITE_STRING_FIELD(cooked_expr);
+ WRITE_CHAR_FIELD(generated_when);
+ WRITE_CHAR_FIELD(generated_kind);
+ break;
+
case CONSTR_CHECK:
appendStringInfoString(str, "CHECK");
WRITE_BOOL_FIELD(is_no_inherit);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 7d0de99baf..ff586533c6 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -570,7 +570,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
opt_frame_clause frame_extent frame_bound
%type <str> opt_existing_window_name
%type <boolean> opt_if_not_exists
-%type <ival> generated_when override_kind
+%type <ival> generated_when override_kind opt_virtual_or_stored
%type <partspec> PartitionSpec OptPartitionSpec
%type <str> part_strategy
%type <partelem> part_elem
@@ -669,7 +669,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT
SEQUENCE SEQUENCES
SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW
SIMILAR SIMPLE SKIP SMALLINT SNAPSHOT SOME SQL_P STABLE STANDALONE_P
- START STATEMENT STATISTICS STDIN STDOUT STORAGE STRICT_P STRIP_P
+ START STATEMENT STATISTICS STDIN STDOUT STORAGE STORED STRICT_P STRIP_P
SUBSCRIPTION SUBSTRING SYMMETRIC SYSID SYSTEM_P
TABLE TABLES TABLESAMPLE TABLESPACE TEMP TEMPLATE TEMPORARY TEXT_P THEN
@@ -680,7 +680,7 @@ static Node *makeRecursiveViewSelect(char *relname, List
*aliases, Node *query);
UNTIL UPDATE USER USING
VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING
- VERBOSE VERSION_P VIEW VIEWS VOLATILE
+ VERBOSE VERSION_P VIEW VIEWS VIRTUAL VOLATILE
WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE
@@ -3403,6 +3403,17 @@ ColConstraintElem:
n->location = @1;
$$ = (Node *)n;
}
+ | GENERATED generated_when AS '(' a_expr ')'
opt_virtual_or_stored
+ {
+ Constraint *n = makeNode(Constraint);
+ n->contype = CONSTR_GENERATED;
+ n->generated_when = $2;
+ n->raw_expr = $5;
+ n->cooked_expr = NULL;
+ n->generated_kind = $7;
+ n->location = @1;
+ $$ = (Node *)n;
+ }
| REFERENCES qualified_name opt_column_list key_match
key_actions
{
Constraint *n = makeNode(Constraint);
@@ -3425,6 +3436,12 @@ generated_when:
| BY DEFAULT { $$ = ATTRIBUTE_IDENTITY_BY_DEFAULT; }
;
+opt_virtual_or_stored:
+ STORED { $$ =
ATTRIBUTE_GENERATED_STORED; }
+ | VIRTUAL { $$ =
ATTRIBUTE_GENERATED_VIRTUAL; }
+ | /*EMPTY*/ { $$ =
ATTRIBUTE_GENERATED_VIRTUAL; }
+ ;
+
/*
* ConstraintAttr represents constraint attributes, which we parse as if
* they were independent constraint clauses, in order to avoid shift/reduce
@@ -3492,6 +3509,7 @@ TableLikeOption:
DEFAULTS { $$ =
CREATE_TABLE_LIKE_DEFAULTS; }
| CONSTRAINTS { $$ =
CREATE_TABLE_LIKE_CONSTRAINTS; }
| IDENTITY_P { $$ =
CREATE_TABLE_LIKE_IDENTITY; }
+ | GENERATED { $$ =
CREATE_TABLE_LIKE_GENERATED; }
| INDEXES { $$ =
CREATE_TABLE_LIKE_INDEXES; }
| STORAGE { $$ =
CREATE_TABLE_LIKE_STORAGE; }
| COMMENTS { $$ =
CREATE_TABLE_LIKE_COMMENTS; }
@@ -14820,6 +14838,7 @@ unreserved_keyword:
| STDIN
| STDOUT
| STORAGE
+ | STORED
| STRICT_P
| STRIP_P
| SUBSCRIPTION
@@ -14855,6 +14874,7 @@ unreserved_keyword:
| VERSION_P
| VIEW
| VIEWS
+ | VIRTUAL
| VOLATILE
| WHITESPACE_P
| WITHIN
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 64111f315e..dc77720e86 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -507,6 +507,14 @@ check_agglevels_and_constraints(ParseState *pstate, Node
*expr)
err = _("grouping operations are not allowed in
partition key expression");
break;
+ case EXPR_KIND_GENERATED_COLUMN:
+
+ if (isAgg)
+ err = _("aggregate functions are not allowed in
column generation expressions");
+ else
+ err = _("grouping operations are not allowed in
column generation expressions");
+
+ break;
/*
* There is intentionally no default: case here, so
that the
@@ -883,6 +891,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc
*wfunc,
case EXPR_KIND_PARTITION_EXPRESSION:
err = _("window functions are not allowed in partition
key expression");
break;
+ case EXPR_KIND_GENERATED_COLUMN:
+ err = _("window functions are not allowed in column
generation expressions");
+ break;
/*
* There is intentionally no default: case here, so
that the
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 6d8cb07766..cfaf4f03d6 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1840,6 +1840,9 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_PARTITION_EXPRESSION:
err = _("cannot use subquery in partition key
expression");
break;
+ case EXPR_KIND_GENERATED_COLUMN:
+ err = _("cannot use subquery in column generation
expression");
+ break;
/*
* There is intentionally no default: case here, so
that the
@@ -3462,6 +3465,8 @@ ParseExprKindName(ParseExprKind exprKind)
return "WHEN";
case EXPR_KIND_PARTITION_EXPRESSION:
return "PARTITION BY";
+ case EXPR_KIND_GENERATED_COLUMN:
+ return "GENERATED AS";
/*
* There is intentionally no default: case here, so
that the
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 2f2f2c7fb0..76d3e1b18e 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -528,6 +528,15 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List
*fargs,
parser_errposition(pstate, location)));
}
+ if (pstate->p_expr_kind == EXPR_KIND_GENERATED_COLUMN &&
+ func_volatile(funcid) != PROVOLATILE_IMMUTABLE)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot use function %s in column
generation expression",
+ func_signature_string(funcname,
nargs, argnames, actual_arg_types)),
+ errdetail("Functions used in a column
generation expression must be immutable."),
+ parser_errposition(pstate, location)));
+
/*
* If there are default arguments, we have to include their types in
* actual_arg_types for the purpose of checking generic type
consistency.
@@ -2235,6 +2244,9 @@ check_srf_call_placement(ParseState *pstate, Node
*last_srf, int location)
case EXPR_KIND_PARTITION_EXPRESSION:
err = _("set-returning functions are not allowed in
partition key expressions");
break;
+ case EXPR_KIND_GENERATED_COLUMN:
+ err = _("set-returning functions are not allowed in
column generation expressions");
+ break;
/*
* There is intentionally no default: case here, so
that the
diff --git a/src/backend/parser/parse_utilcmd.c
b/src/backend/parser/parse_utilcmd.c
index 20586797cc..5f6bec5ed1 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -505,6 +505,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef
*column)
bool saw_nullable;
bool saw_default;
bool saw_identity;
+ bool saw_generated;
ListCell *clist;
cxt->columns = lappend(cxt->columns, column);
@@ -612,6 +613,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef
*column)
saw_nullable = false;
saw_default = false;
saw_identity = false;
+ saw_generated = false;
foreach(clist, column->constraints)
{
@@ -683,6 +685,41 @@ transformColumnDefinition(CreateStmtContext *cxt,
ColumnDef *column)
break;
}
+ case CONSTR_GENERATED:
+ if (saw_generated)
+ ereport(ERROR,
+
(errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("multiple
generation clauses specified for column \"%s\" of table \"%s\"",
+
column->colname, cxt->relation->relname),
+
parser_errposition(cxt->pstate,
+
constraint->location)));
+ column->generated = constraint->generated_kind;
+ column->raw_default = constraint->raw_expr;
+ Assert(constraint->cooked_expr == NULL);
+ saw_generated = true;
+
+ /*
+ * Prevent virtual generated columns from
having a domain
+ * type. We would have to enforce domain
constraints when
+ * columns underlying the generated column
change. This could
+ * possibly be implemented, but it's not.
+ */
+ if (column->generated ==
ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ Type ctype;
+
+ ctype = typenameType(cxt->pstate,
column->typeName, NULL);
+ if (((Form_pg_type)
GETSTRUCT(ctype))->typtype == TYPTYPE_DOMAIN)
+ ereport(ERROR,
+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+
errmsg("virtual generated column \"%s\" cannot have a domain type",
+
column->colname),
+
parser_errposition(cxt->pstate,
+
column->location)));
+ ReleaseSysCache(ctype);
+ }
+ break;
+
case CONSTR_CHECK:
cxt->ckconstraints =
lappend(cxt->ckconstraints, constraint);
break;
@@ -767,6 +804,50 @@ transformColumnDefinition(CreateStmtContext *cxt,
ColumnDef *column)
column->colname,
cxt->relation->relname),
parser_errposition(cxt->pstate,
constraint->location)));
+
+ if (saw_default && saw_generated)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("both default and generation
expression specified for column \"%s\" of table \"%s\"",
+ column->colname,
cxt->relation->relname),
+ parser_errposition(cxt->pstate,
+
constraint->location)));
+
+ if (saw_identity && saw_generated)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("both identity and generation
expression specified for column \"%s\" of table \"%s\"",
+ column->colname,
cxt->relation->relname),
+ parser_errposition(cxt->pstate,
+
constraint->location)));
+
+ /*
+ * For a generated column, convert the not-null constraint into
a full
+ * check constraint, so that the generation expression can be
expanded
+ * at check time.
+ */
+ if (column->is_not_null && column->generated)
+ {
+ Constraint *chk = makeNode(Constraint);
+ NullTest *nt = makeNode(NullTest);
+ ColumnRef *cr = makeNode(ColumnRef);
+
+ cr->location = -1;
+ cr->fields = list_make1(makeString(column->colname));
+
+ nt->arg = (Expr *) cr;
+ nt->nulltesttype = IS_NOT_NULL;
+ nt->location = -1;
+
+ chk->contype = CONSTR_CHECK;
+ chk->location = -1;
+ chk->initially_valid = true;
+ chk->raw_expr = (Node *) nt;
+
+ cxt->ckconstraints = lappend(cxt->ckconstraints, chk);
+
+ column->is_not_null = false;
+ }
}
/*
@@ -1014,7 +1095,8 @@ transformTableLikeClause(CreateStmtContext *cxt,
TableLikeClause *table_like_cla
* Copy default, if present and the default has been requested
*/
if (attribute->atthasdef &&
- (table_like_clause->options &
CREATE_TABLE_LIKE_DEFAULTS))
+ (table_like_clause->options &
CREATE_TABLE_LIKE_DEFAULTS ||
+ table_like_clause->options &
CREATE_TABLE_LIKE_GENERATED))
{
Node *this_default = NULL;
AttrDefault *attrdef;
@@ -1039,6 +1121,9 @@ transformTableLikeClause(CreateStmtContext *cxt,
TableLikeClause *table_like_cla
*/
def->cooked_default = this_default;
+ if (attribute->attgenerated &&
+ (table_like_clause->options &
CREATE_TABLE_LIKE_GENERATED))
+ def->generated = attribute->attgenerated;
}
/*
diff --git a/src/backend/replication/logical/worker.c
b/src/backend/replication/logical/worker.c
index 041f3873b9..d63121ecf7 100644
--- a/src/backend/replication/logical/worker.c
+++ b/src/backend/replication/logical/worker.c
@@ -253,7 +253,7 @@ slot_fill_defaults(LogicalRepRelMapEntry *rel, EState
*estate,
if (rel->attrmap[attnum] >= 0)
continue;
- defexpr = (Expr *) build_column_default(rel->localrel, attnum +
1);
+ defexpr = (Expr *) build_column_default(rel->localrel, attnum +
1, true);
if (defexpr != NULL)
{
diff --git a/src/backend/rewrite/rewriteHandler.c
b/src/backend/rewrite/rewriteHandler.c
index ef52dd5b95..84af18ec8c 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -20,6 +20,7 @@
*/
#include "postgres.h"
+#include "access/htup_details.h"
#include "access/sysattr.h"
#include "catalog/dependency.h"
#include "catalog/pg_type.h"
@@ -37,6 +38,7 @@
#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
+#include "utils/syscache.h"
/* We use a list of these to detect recursion in RewriteQuery */
@@ -823,6 +825,13 @@ rewriteTargetListIU(List *targetList,
if (att_tup->attidentity ==
ATTRIBUTE_IDENTITY_BY_DEFAULT && override == OVERRIDING_USER_VALUE)
apply_default = true;
+
+ if (att_tup->attgenerated && !apply_default)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot insert into
column \"%s\"", NameStr(att_tup->attname)),
+ errdetail("Column \"%s\" is a
generated column.",
+
NameStr(att_tup->attname))));
}
if (commandType == CMD_UPDATE)
@@ -833,9 +842,28 @@ rewriteTargetListIU(List *targetList,
errmsg("column \"%s\" can only
be updated to DEFAULT", NameStr(att_tup->attname)),
errdetail("Column \"%s\" is an
identity column defined as GENERATED ALWAYS.",
NameStr(att_tup->attname))));
+
+ if (att_tup->attgenerated && new_tle && !apply_default)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("column \"%s\" can only
be updated to DEFAULT", NameStr(att_tup->attname)),
+ errdetail("Column \"%s\" is a
generated column.",
+
NameStr(att_tup->attname))));
}
- if (apply_default)
+ if (att_tup->attgenerated)
+ {
+ /*
+ * virtual generated column stores a null value
+ */
+ new_tle = NULL;
+
+ if (att_tup->attgenerated == ATTRIBUTE_GENERATED_STORED)
+ ereport(ERROR,
+
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("stored generated
columns are not yet implemented")));
+ }
+ else if (apply_default)
{
Node *new_expr;
@@ -849,7 +877,7 @@ rewriteTargetListIU(List *targetList,
new_expr = (Node *) nve;
}
else
- new_expr =
build_column_default(target_relation, attrno);
+ new_expr =
build_column_default(target_relation, attrno, true);
/*
* If there is no default (ie, default is effectively
NULL), we
@@ -1109,7 +1137,7 @@ get_assignment_input(Node *node)
* If there is no default, return a NULL instead.
*/
Node *
-build_column_default(Relation rel, int attrno)
+build_column_default(Relation rel, int attrno, bool allow_typdefault)
{
TupleDesc rd_att = rel->rd_att;
Form_pg_attribute att_tup = TupleDescAttr(rd_att, attrno - 1);
@@ -1139,7 +1167,7 @@ build_column_default(Relation rel, int attrno)
}
}
- if (expr == NULL)
+ if (expr == NULL && allow_typdefault)
{
/*
* No per-column default, so look for a default for the type
itself.
@@ -1250,7 +1278,7 @@ rewriteValuesRTE(RangeTblEntry *rte, Relation
target_relation, List *attrnos)
att_tup =
TupleDescAttr(target_relation->rd_att, attrno - 1);
if (!att_tup->attisdropped)
- new_expr =
build_column_default(target_relation, attrno);
+ new_expr =
build_column_default(target_relation, attrno, true);
else
new_expr = NULL; /* force a NULL
if dropped */
@@ -3563,6 +3591,75 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
}
+static Node *
+expand_generated_columns_in_expr_mutator(Node *node, Relation rel)
+{
+ if (node == NULL)
+ return NULL;
+
+ if (IsA(node, Var))
+ {
+ Var *v = (Var *) node;
+ Oid relid = RelationGetRelid(rel);
+ AttrNumber attnum = v->varattno;
+
+ if (relid && attnum && get_attgenerated(relid, attnum) ==
ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ node = build_column_default(rel, attnum, false);
+ ChangeVarNodes(node, 1, v->varno, 0);
+ }
+
+ return node;
+ }
+ else
+ return expression_tree_mutator(node,
expand_generated_columns_in_expr_mutator, rel);
+}
+
+
+Expr *
+expand_generated_columns_in_expr(Expr *expr, Relation rel)
+{
+ return (Expr *) expression_tree_mutator((Node *) expr,
+
expand_generated_columns_in_expr_mutator,
+
rel);
+}
+
+
+static Node *
+expand_generated_columns_in_query_mutator(Node *node, List *rtable)
+{
+ if (node == NULL)
+ return NULL;
+
+ if (IsA(node, Var))
+ {
+ Var *v = (Var *) node;
+ Oid relid;
+ AttrNumber attnum;
+
+ relid = getrelid(v->varno, rtable);
+ attnum = v->varattno;
+
+ if (!relid || !attnum)
+ return node;
+
+ if (get_attgenerated(relid, attnum) ==
ATTRIBUTE_GENERATED_VIRTUAL)
+ {
+ Relation rt_entry_relation = heap_open(relid, NoLock);
+
+ node = build_column_default(rt_entry_relation, attnum,
false);
+ ChangeVarNodes(node, 1, v->varno, 0);
+
+ heap_close(rt_entry_relation, NoLock);
+ }
+
+ return node;
+ }
+ else
+ return expression_tree_mutator(node,
expand_generated_columns_in_query_mutator, rtable);
+}
+
+
/*
* QueryRewrite -
* Primary entry point to the query rewriter.
@@ -3618,6 +3715,21 @@ QueryRewrite(Query *parsetree)
/*
* Step 3
*
+ * Expand generated columns.
+ */
+ foreach(l, querylist)
+ {
+ Query *query = (Query *) lfirst(l);
+
+ query = query_tree_mutator(query,
+
expand_generated_columns_in_query_mutator,
+
query->rtable,
+
QTW_DONT_COPY_QUERY);
+ }
+
+ /*
+ * Step 4
+ *
* Determine which, if any, of the resulting queries is supposed to set
* the command-result tag; and update the canSetTag fields accordingly.
*
diff --git a/src/backend/utils/cache/lsyscache.c
b/src/backend/utils/cache/lsyscache.c
index 82763f8013..e0fd0cf8b1 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -836,6 +836,38 @@ get_attnum(Oid relid, const char *attname)
return InvalidAttrNumber;
}
+/*
+ * get_attgenerated
+ *
+ * Given the relation id and the attribute name,
+ * return the "attgenerated" field from the attribute relation.
+ *
+ * Returns '\0' if not found.
+ *
+ * Since not generated is represented by '\0', this can also be
used as a
+ * Boolean test.
+ */
+char
+get_attgenerated(Oid relid, AttrNumber attnum)
+{
+ HeapTuple tp;
+
+ tp = SearchSysCache2(ATTNUM,
+ ObjectIdGetDatum(relid),
+ Int16GetDatum(attnum));
+ if (HeapTupleIsValid(tp))
+ {
+ Form_pg_attribute att_tup = (Form_pg_attribute) GETSTRUCT(tp);
+ char result;
+
+ result = att_tup->attgenerated;
+ ReleaseSysCache(tp);
+ return result;
+ }
+ else
+ return '\0';
+}
+
/*
* get_attidentity
*
diff --git a/src/backend/utils/cache/relcache.c
b/src/backend/utils/cache/relcache.c
index b8e37809b0..42cf22dd3b 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -3280,6 +3280,7 @@ RelationBuildLocalRelation(const char *relname,
Form_pg_attribute datt = TupleDescAttr(rel->rd_att, i);
datt->attidentity = satt->attidentity;
+ datt->attgenerated = satt->attgenerated;
datt->attnotnull = satt->attnotnull;
has_not_null |= satt->attnotnull;
}
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 75f08cd792..88215d14e6 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1970,6 +1970,11 @@ dumpTableData_insert(Archive *fout, void *dcontext)
{
if (field > 0)
archputs(", ", fout);
+ if (tbinfo->attgenerated[field])
+ {
+ archputs("DEFAULT", fout);
+ continue;
+ }
if (PQgetisnull(res, tuple, field))
{
archputs("NULL", fout);
@@ -7846,6 +7851,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int
numTables)
int i_attnotnull;
int i_atthasdef;
int i_attidentity;
+ int i_attgenerated;
int i_attisdropped;
int i_attlen;
int i_attalign;
@@ -7902,6 +7908,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int
numTables)
"CASE WHEN
a.attcollation <> t.typcollation "
"THEN a.attcollation
ELSE 0 END AS attcollation, "
"a.attidentity, "
+ "a.attgenerated, "
"pg_catalog.array_to_string(ARRAY("
"SELECT
pg_catalog.quote_ident(option_name) || "
"' ' ||
pg_catalog.quote_literal(option_value) "
@@ -8015,6 +8022,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int
numTables)
i_attnotnull = PQfnumber(res, "attnotnull");
i_atthasdef = PQfnumber(res, "atthasdef");
i_attidentity = PQfnumber(res, "attidentity");
+ i_attgenerated = PQfnumber(res, "attgenerated");
i_attisdropped = PQfnumber(res, "attisdropped");
i_attlen = PQfnumber(res, "attlen");
i_attalign = PQfnumber(res, "attalign");
@@ -8031,6 +8039,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int
numTables)
tbinfo->attstorage = (char *) pg_malloc(ntups * sizeof(char));
tbinfo->typstorage = (char *) pg_malloc(ntups * sizeof(char));
tbinfo->attidentity = (char *) pg_malloc(ntups * sizeof(char));
+ tbinfo->attgenerated = (char *) pg_malloc(ntups * sizeof(char));
tbinfo->attisdropped = (bool *) pg_malloc(ntups * sizeof(bool));
tbinfo->attlen = (int *) pg_malloc(ntups * sizeof(int));
tbinfo->attalign = (char *) pg_malloc(ntups * sizeof(char));
@@ -8056,6 +8065,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int
numTables)
tbinfo->attstorage[j] = *(PQgetvalue(res, j,
i_attstorage));
tbinfo->typstorage[j] = *(PQgetvalue(res, j,
i_typstorage));
tbinfo->attidentity[j] = (i_attidentity >= 0 ?
*(PQgetvalue(res, j, i_attidentity)) : '\0');
+ tbinfo->attgenerated[j] = (i_attgenerated >= 0 ?
*(PQgetvalue(res, j, i_attgenerated)) : '\0');
tbinfo->needs_override = tbinfo->needs_override ||
(tbinfo->attidentity[j] == ATTRIBUTE_IDENTITY_ALWAYS);
tbinfo->attisdropped[j] = (PQgetvalue(res, j,
i_attisdropped)[0] == 't');
tbinfo->attlen[j] = atoi(PQgetvalue(res, j, i_attlen));
@@ -8088,7 +8098,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int
numTables)
tbinfo->dobj.name);
printfPQExpBuffer(q, "SELECT tableoid, oid, adnum, "
-
"pg_catalog.pg_get_expr(adbin, adrelid) AS adsrc "
+
"pg_catalog.pg_get_expr(adbin, adrelid, true) AS adsrc "
"FROM
pg_catalog.pg_attrdef "
"WHERE adrelid =
'%u'::pg_catalog.oid",
tbinfo->dobj.catId.oid);
@@ -15364,6 +15374,23 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
tbinfo->atttypnames[j]);
}
+ if (has_default)
+ {
+ if (tbinfo->attgenerated[j] ==
ATTRIBUTE_GENERATED_STORED)
+ appendPQExpBuffer(q, "
GENERATED ALWAYS AS (%s) STORED",
+
tbinfo->attrdefs[j]->adef_expr);
+ else if
(tbinfo->attgenerated[j] == ATTRIBUTE_GENERATED_VIRTUAL)
+ appendPQExpBuffer(q, "
GENERATED ALWAYS AS (%s)",
+
tbinfo->attrdefs[j]->adef_expr);
+ else
+ appendPQExpBuffer(q, "
DEFAULT %s",
+
tbinfo->attrdefs[j]->adef_expr);
+ }
+
+
+ if (has_notnull)
+ appendPQExpBufferStr(q, " NOT
NULL");
+
/* Add collation if not default for the
type */
if (OidIsValid(tbinfo->attcollation[j]))
{
@@ -15378,13 +15405,6 @@ dumpTableSchema(Archive *fout, TableInfo *tbinfo)
appendPQExpBufferStr(q,
fmtId(coll->dobj.name));
}
}
-
- if (has_default)
- appendPQExpBuffer(q, " DEFAULT
%s",
-
tbinfo->attrdefs[j]->adef_expr);
-
- if (has_notnull)
- appendPQExpBufferStr(q, " NOT
NULL");
}
}
@@ -17892,6 +17912,7 @@ fmtCopyColumnList(const TableInfo *ti, PQExpBuffer
buffer)
int numatts = ti->numatts;
char **attnames = ti->attnames;
bool *attisdropped = ti->attisdropped;
+ char *attgenerated = ti->attgenerated;
bool needComma;
int i;
@@ -17901,6 +17922,8 @@ fmtCopyColumnList(const TableInfo *ti, PQExpBuffer
buffer)
{
if (attisdropped[i])
continue;
+ if (attgenerated[i])
+ continue;
if (needComma)
appendPQExpBufferStr(buffer, ", ");
appendPQExpBufferStr(buffer, fmtId(attnames[i]));
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index e7593e6da7..1afe7d87c3 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -309,6 +309,7 @@ typedef struct _tableInfo
char *typstorage; /* type storage scheme */
bool *attisdropped; /* true if attr is dropped; don't dump
it */
char *attidentity;
+ char *attgenerated;
int *attlen; /* attribute length,
used by binary_upgrade */
char *attalign; /* attribute align, used by
binary_upgrade */
bool *attislocal; /* true if attr has local definition */
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index c492fbdc24..d1dc01fcd6 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -4986,6 +4986,45 @@
role => 1,
section_post_data => 1, }, },
+ 'CREATE TABLE test_table_generated' => {
+ all_runs => 1,
+ catch_all => 'CREATE ... commands',
+ create_order => 3,
+ create_sql => 'CREATE TABLE dump_test.test_table_generated (
+ col1 int primary key,
+ col2 int generated always as
(col1 * 2)
+ );',
+ regexp => qr/^
+ \QCREATE TABLE test_table_generated (\E\n
+ \s+\Qcol1 integer NOT NULL,\E\n
+ \s+\Qcol2 integer GENERATED ALWAYS AS (col1 * 2)\E\n
+ \);
+ /xms,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_blobs => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ pg_dumpall_dbprivs => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ with_oids => 1, },
+ unlike => {
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ pg_dumpall_globals_clean => 1,
+ role => 1,
+ section_post_data => 1, }, },
+
'CREATE STATISTICS extended_stats_no_options' => {
all_runs => 1,
catch_all => 'CREATE ... commands',
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index f6049cc9e5..f6242babc8 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1594,7 +1594,7 @@ describeOneTableDetails(const char *schemaname,
*/
printfPQExpBuffer(&buf, "SELECT a.attname,");
appendPQExpBufferStr(&buf, "\n pg_catalog.format_type(a.atttypid,
a.atttypmod),"
- "\n (SELECT
substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)"
+ "\n (SELECT
substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128)"
"\n FROM
pg_catalog.pg_attrdef d"
"\n WHERE d.adrelid =
a.attrelid AND d.adnum = a.attnum AND a.atthasdef),"
"\n a.attnotnull, a.attnum,");
@@ -1607,6 +1607,10 @@ describeOneTableDetails(const char *schemaname,
appendPQExpBufferStr(&buf, ",\n a.attidentity");
else
appendPQExpBufferStr(&buf, ",\n ''::pg_catalog.char AS
attidentity");
+ if (pset.sversion >= 110000)
+ appendPQExpBufferStr(&buf, ",\n a.attgenerated");
+ else
+ appendPQExpBufferStr(&buf, ",\n ''::pg_catalog.char AS
attgenerated");
if (tableinfo.relkind == RELKIND_INDEX)
appendPQExpBufferStr(&buf, ",\n
pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef");
else
@@ -1794,6 +1798,7 @@ describeOneTableDetails(const char *schemaname,
if (show_column_details)
{
char *identity;
+ char *generated;
char *default_str = "";
printTableAddCell(&cont, PQgetvalue(res, i, 5), false,
false);
@@ -1801,16 +1806,21 @@ describeOneTableDetails(const char *schemaname,
printTableAddCell(&cont, strcmp(PQgetvalue(res, i, 3),
"t") == 0 ? "not null" : "", false, false);
identity = PQgetvalue(res, i, 6);
+ generated = PQgetvalue(res, i, 7);
- if (!identity[0])
- /* (note: above we cut off the 'default' string
at 128) */
- default_str = PQgetvalue(res, i, 2);
- else if (identity[0] == ATTRIBUTE_IDENTITY_ALWAYS)
+ if (identity[0] == ATTRIBUTE_IDENTITY_ALWAYS)
default_str = "generated always as identity";
else if (identity[0] == ATTRIBUTE_IDENTITY_BY_DEFAULT)
default_str = "generated by default as
identity";
+ else if (generated[0] == ATTRIBUTE_GENERATED_STORED)
+ default_str = psprintf("generated always as
(%s) stored", PQgetvalue(res, i, 2));
+ else if (generated[0] == ATTRIBUTE_GENERATED_VIRTUAL)
+ default_str = psprintf("generated always as
(%s)", PQgetvalue(res, i, 2));
+ else
+ /* (note: above we cut off the 'default' string
at 128) */
+ default_str = PQgetvalue(res, i, 2);
- printTableAddCell(&cont, default_str, false, false);
+ printTableAddCell(&cont, default_str, false,
generated[0] ? true : false);
}
/* Value: for sequences only */
@@ -1819,16 +1829,16 @@ describeOneTableDetails(const char *schemaname,
/* Expression for index column */
if (tableinfo.relkind == RELKIND_INDEX)
- printTableAddCell(&cont, PQgetvalue(res, i, 7), false,
false);
+ printTableAddCell(&cont, PQgetvalue(res, i, 8), false,
false);
/* FDW options for foreign table column, only for 9.2 or later
*/
if (tableinfo.relkind == RELKIND_FOREIGN_TABLE && pset.sversion
>= 90200)
- printTableAddCell(&cont, PQgetvalue(res, i, 8), false,
false);
+ printTableAddCell(&cont, PQgetvalue(res, i, 9), false,
false);
/* Storage and Description */
if (verbose)
{
- int firstvcol = 9;
+ int firstvcol = 10;
char *storage = PQgetvalue(res, i, firstvcol);
/* these strings are literal in our syntax, so not
translated. */
diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 0dafd6bf2a..0ee2f33d40 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 201707211
+#define CATALOG_VERSION_NO 201708175
#endif
diff --git a/src/include/catalog/heap.h b/src/include/catalog/heap.h
index cb1bc887f8..1a762a6e63 100644
--- a/src/include/catalog/heap.h
+++ b/src/include/catalog/heap.h
@@ -103,13 +103,14 @@ extern List *AddRelationNewConstraints(Relation rel,
bool is_internal);
extern Oid StoreAttrDefault(Relation rel, AttrNumber attnum,
- Node *expr, bool is_internal);
+ Node *expr, bool
is_internal, bool generated_col);
extern Node *cookDefault(ParseState *pstate,
Node *raw_default,
Oid atttypid,
int32 atttypmod,
- char *attname);
+ char *attname,
+ char attgenerated);
extern void DeleteRelationTuple(Oid relid);
extern void DeleteAttributeTuples(Oid relid);
diff --git a/src/include/catalog/pg_attribute.h
b/src/include/catalog/pg_attribute.h
index bcf28e8f04..5d0ae8d9d4 100644
--- a/src/include/catalog/pg_attribute.h
+++ b/src/include/catalog/pg_attribute.h
@@ -136,6 +136,9 @@ CATALOG(pg_attribute,1249) BKI_BOOTSTRAP BKI_WITHOUT_OIDS
BKI_ROWTYPE_OID(75) BK
/* One of the ATTRIBUTE_IDENTITY_* constants below, or '\0' */
char attidentity;
+ /* One of the ATTRIBUTE_GENERATED_* constants below, or '\0' */
+ char attgenerated;
+
/* Is dropped (ie, logically invisible) or not */
bool attisdropped;
@@ -191,7 +194,7 @@ typedef FormData_pg_attribute *Form_pg_attribute;
* ----------------
*/
-#define Natts_pg_attribute 22
+#define Natts_pg_attribute 23
#define Anum_pg_attribute_attrelid 1
#define Anum_pg_attribute_attname 2
#define Anum_pg_attribute_atttypid 3
@@ -207,13 +210,14 @@ typedef FormData_pg_attribute *Form_pg_attribute;
#define Anum_pg_attribute_attnotnull 13
#define Anum_pg_attribute_atthasdef 14
#define Anum_pg_attribute_attidentity 15
-#define Anum_pg_attribute_attisdropped 16
-#define Anum_pg_attribute_attislocal 17
-#define Anum_pg_attribute_attinhcount 18
-#define Anum_pg_attribute_attcollation 19
-#define Anum_pg_attribute_attacl 20
-#define Anum_pg_attribute_attoptions 21
-#define Anum_pg_attribute_attfdwoptions 22
+#define Anum_pg_attribute_attgenerated 16
+#define Anum_pg_attribute_attisdropped 17
+#define Anum_pg_attribute_attislocal 18
+#define Anum_pg_attribute_attinhcount 19
+#define Anum_pg_attribute_attcollation 20
+#define Anum_pg_attribute_attacl 21
+#define Anum_pg_attribute_attoptions 22
+#define Anum_pg_attribute_attfdwoptions 23
/* ----------------
@@ -228,4 +232,7 @@ typedef FormData_pg_attribute *Form_pg_attribute;
#define ATTRIBUTE_IDENTITY_ALWAYS 'a'
#define ATTRIBUTE_IDENTITY_BY_DEFAULT 'd'
+#define ATTRIBUTE_GENERATED_STORED 's'
+#define ATTRIBUTE_GENERATED_VIRTUAL 'v'
+
#endif /* PG_ATTRIBUTE_H */
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index b256657bda..40f5cc4f18 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -149,7 +149,7 @@ typedef FormData_pg_class *Form_pg_class;
*/
DATA(insert OID = 1247 ( pg_type PGNSP 71 0 PGUID 0 0 0 0 0 0 0
f f p r 30 0 t f f f f f f t n f 3 1 _null_ _null_ _null_));
DESCR("");
-DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r
22 0 f f f f f f f t n f 3 1 _null_ _null_ _null_));
+DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r
23 0 f f f f f f f t n f 3 1 _null_ _null_ _null_));
DESCR("");
DATA(insert OID = 1255 ( pg_proc PGNSP 81 0 PGUID 0 0 0 0 0 0 0
f f p r 29 0 t f f f f f f t n f 3 1 _null_ _null_ _null_));
DESCR("");
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 5f2a4a75da..fce66ee0b3 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -647,6 +647,7 @@ typedef struct ColumnDef
Node *raw_default; /* default value (untransformed parse
tree) */
Node *cooked_default; /* default value (transformed expr tree) */
char identity; /* attidentity setting */
+ char generated; /* attgenerated setting */
CollateClause *collClause; /* untransformed COLLATE spec, if any */
Oid collOid; /* collation OID
(InvalidOid if not set) */
List *constraints; /* other constraints on column */
@@ -669,9 +670,10 @@ typedef enum TableLikeOption
CREATE_TABLE_LIKE_DEFAULTS = 1 << 0,
CREATE_TABLE_LIKE_CONSTRAINTS = 1 << 1,
CREATE_TABLE_LIKE_IDENTITY = 1 << 2,
- CREATE_TABLE_LIKE_INDEXES = 1 << 3,
- CREATE_TABLE_LIKE_STORAGE = 1 << 4,
- CREATE_TABLE_LIKE_COMMENTS = 1 << 5,
+ CREATE_TABLE_LIKE_GENERATED = 1 << 3,
+ CREATE_TABLE_LIKE_INDEXES = 1 << 4,
+ CREATE_TABLE_LIKE_STORAGE = 1 << 5,
+ CREATE_TABLE_LIKE_COMMENTS = 1 << 6,
CREATE_TABLE_LIKE_ALL = PG_INT32_MAX
} TableLikeOption;
@@ -2044,6 +2046,7 @@ typedef enum ConstrType /* types of
constraints */
CONSTR_NOTNULL,
CONSTR_DEFAULT,
CONSTR_IDENTITY,
+ CONSTR_GENERATED,
CONSTR_CHECK,
CONSTR_PRIMARY,
CONSTR_UNIQUE,
@@ -2082,7 +2085,8 @@ typedef struct Constraint
bool is_no_inherit; /* is constraint non-inheritable? */
Node *raw_expr; /* expr, as untransformed parse tree */
char *cooked_expr; /* expr, as nodeToString representation
*/
- char generated_when;
+ char generated_when; /* ALWAYS or BY DEFAULT */
+ char generated_kind; /* STORED or VIRTUAL */
/* Fields used for unique constraints (UNIQUE and PRIMARY KEY): */
List *keys; /* String nodes naming
referenced column(s) */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index f50e45e886..7b28080186 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -377,6 +377,7 @@ PG_KEYWORD("statistics", STATISTICS, UNRESERVED_KEYWORD)
PG_KEYWORD("stdin", STDIN, UNRESERVED_KEYWORD)
PG_KEYWORD("stdout", STDOUT, UNRESERVED_KEYWORD)
PG_KEYWORD("storage", STORAGE, UNRESERVED_KEYWORD)
+PG_KEYWORD("stored", STORED, UNRESERVED_KEYWORD)
PG_KEYWORD("strict", STRICT_P, UNRESERVED_KEYWORD)
PG_KEYWORD("strip", STRIP_P, UNRESERVED_KEYWORD)
PG_KEYWORD("subscription", SUBSCRIPTION, UNRESERVED_KEYWORD)
@@ -432,6 +433,7 @@ PG_KEYWORD("verbose", VERBOSE, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("version", VERSION_P, UNRESERVED_KEYWORD)
PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD)
PG_KEYWORD("views", VIEWS, UNRESERVED_KEYWORD)
+PG_KEYWORD("virtual", VIRTUAL, UNRESERVED_KEYWORD)
PG_KEYWORD("volatile", VOLATILE, UNRESERVED_KEYWORD)
PG_KEYWORD("when", WHEN, RESERVED_KEYWORD)
PG_KEYWORD("where", WHERE, RESERVED_KEYWORD)
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 68930c1f4a..470f163da6 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -67,7 +67,8 @@ typedef enum ParseExprKind
EXPR_KIND_EXECUTE_PARAMETER, /* parameter value in EXECUTE */
EXPR_KIND_TRIGGER_WHEN, /* WHEN condition in CREATE TRIGGER */
EXPR_KIND_POLICY, /* USING or WITH CHECK expr in
policy */
- EXPR_KIND_PARTITION_EXPRESSION /* PARTITION BY expression */
+ EXPR_KIND_PARTITION_EXPRESSION, /* PARTITION BY expression */
+ EXPR_KIND_GENERATED_COLUMN /* generation expression for a column */
} ParseExprKind;
diff --git a/src/include/rewrite/rewriteHandler.h
b/src/include/rewrite/rewriteHandler.h
index 494fa29f10..3b7bb45068 100644
--- a/src/include/rewrite/rewriteHandler.h
+++ b/src/include/rewrite/rewriteHandler.h
@@ -22,12 +22,13 @@ extern void AcquireRewriteLocks(Query *parsetree,
bool forExecute,
bool forUpdatePushedDown);
-extern Node *build_column_default(Relation rel, int attrno);
+extern Node *build_column_default(Relation rel, int attrno, bool
allow_typdefault);
extern Query *get_view_query(Relation view);
extern const char *view_query_is_auto_updatable(Query *viewquery,
bool check_cols);
extern int relation_is_updatable(Oid reloid,
bool include_triggers,
Bitmapset *include_cols);
+extern Expr *expand_generated_columns_in_expr(Expr *expr, Relation rel);
#endif /* REWRITEHANDLER_H */
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 07208b56ce..a7e921ebad 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -86,6 +86,7 @@ extern Oid get_opfamily_proc(Oid opfamily, Oid lefttype, Oid
righttype,
extern char *get_attname(Oid relid, AttrNumber attnum);
extern char *get_relid_attribute_name(Oid relid, AttrNumber attnum);
extern AttrNumber get_attnum(Oid relid, const char *attname);
+extern char get_attgenerated(Oid relid, AttrNumber attnum);
extern char get_attidentity(Oid relid, AttrNumber attnum);
extern Oid get_atttype(Oid relid, AttrNumber attnum);
extern int32 get_atttypmod(Oid relid, AttrNumber attnum);
diff --git a/src/test/regress/expected/create_table_like.out
b/src/test/regress/expected/create_table_like.out
index 3f405c94ce..5d47e22981 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -113,6 +113,52 @@ SELECT * FROM test_like_id_3; -- identity was copied and
applied
(1 row)
DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3;
+CREATE TABLE test_like_gen_1 (a int, b int GENERATED ALWAYS AS (a * 2));
+\d test_like_gen_1
+ Table "public.test_like_gen_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2)
+
+INSERT INTO test_like_gen_1 (a) VALUES (1);
+SELECT * FROM test_like_gen_1;
+ a | b
+---+---
+ 1 | 2
+(1 row)
+
+CREATE TABLE test_like_gen_2 (LIKE test_like_gen_1);
+\d test_like_gen_2
+ Table "public.test_like_gen_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+
+INSERT INTO test_like_gen_2 (a) VALUES (1);
+SELECT * FROM test_like_gen_2;
+ a | b
+---+---
+ 1 |
+(1 row)
+
+CREATE TABLE test_like_gen_3 (LIKE test_like_gen_1 INCLUDING GENERATED);
+\d test_like_gen_3
+ Table "public.test_like_gen_3"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | |
+ b | integer | | | generated always as (a * 2)
+
+INSERT INTO test_like_gen_3 (a) VALUES (1);
+SELECT * FROM test_like_gen_3;
+ a | b
+---+---
+ 1 | 2
+(1 row)
+
+DROP TABLE test_like_gen_1, test_like_gen_2, test_like_gen_3;
CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies
indexes */
INSERT INTO inhg VALUES (5, 10);
INSERT INTO inhg VALUES (20, 10); -- should fail
diff --git a/src/test/regress/expected/generated.out
b/src/test/regress/expected/generated.out
new file mode 100644
index 0000000000..78536203e9
--- /dev/null
+++ b/src/test/regress/expected/generated.out
@@ -0,0 +1,309 @@
+CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55));
+CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2));
+SELECT table_name, column_name, column_default, is_nullable, is_generated,
generation_expression FROM information_schema.columns WHERE table_name LIKE
'gtest_' ORDER BY 1, 2;
+ table_name | column_name | column_default | is_nullable | is_generated |
generation_expression
+------------+-------------+----------------+-------------+--------------+-----------------------
+ gtest0 | a | | NO | NEVER |
+ gtest0 | b | | YES | ALWAYS | 55
+ gtest1 | a | | NO | NEVER |
+ gtest1 | b | | YES | ALWAYS | a * 2
+(4 rows)
+
+-- duplicate generated
+CREATE TABLE gtest_err_1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
GENERATED ALWAYS AS (a * 3));
+ERROR: multiple generation clauses specified for column "b" of table
"gtest_err_1"
+LINE 1: ...nt PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) GENERATED ...
+ ^
+-- references to other generated columns, including self-references
+CREATE TABLE gtest_err_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b *
2));
+ERROR: cannot use generated column "b" in column generation expression
+DETAIL: A generated column cannot reference another generated column.
+CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a *
2), c int GENERATED ALWAYS AS (b * 3));
+ERROR: cannot use generated column "b" in column generation expression
+DETAIL: A generated column cannot reference another generated column.
+-- invalid reference
+CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c *
2));
+ERROR: column "c" does not exist
+-- functions must be immutable
+CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED
ALWAYS AS (random()));
+ERROR: cannot use function random() in column generation expression
+DETAIL: Functions used in a column generation expression must be immutable.
+-- cannot have default/identity and generated
+CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS
AS (a * 2));
+ERROR: both default and generation expression specified for column "b" of
table "gtest_err_5a"
+LINE 1: ... gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ...
+ ^
+CREATE TABLE gtest_err_5b (a int PRIMARY KEY, b int GENERATED ALWAYS AS
identity GENERATED ALWAYS AS (a * 2));
+ERROR: both identity and generation expression specified for column "b" of
table "gtest_err_5b"
+LINE 1: ...t PRIMARY KEY, b int GENERATED ALWAYS AS identity GENERATED ...
+ ^
+INSERT INTO gtest1 VALUES (1);
+INSERT INTO gtest1 VALUES (2, DEFAULT);
+INSERT INTO gtest1 VALUES (3, 33); -- error
+ERROR: cannot insert into column "b"
+DETAIL: Column "b" is a generated column.
+SELECT * FROM gtest1 ORDER BY a;
+ a | b
+---+---
+ 1 | 2
+ 2 | 4
+(2 rows)
+
+UPDATE gtest1 SET b = DEFAULT WHERE a = 1;
+UPDATE gtest1 SET b = 11 WHERE a = 1; -- error
+ERROR: column "b" can only be updated to DEFAULT
+DETAIL: Column "b" is a generated column.
+SELECT * FROM gtest1 ORDER BY a;
+ a | b
+---+---
+ 1 | 2
+ 2 | 4
+(2 rows)
+
+SELECT a, b, b * 2 AS b2 FROM gtest1 ORDER BY a;
+ a | b | b2
+---+---+----
+ 1 | 2 | 4
+ 2 | 4 | 8
+(2 rows)
+
+SELECT a, b FROM gtest1 WHERE b = 4 ORDER BY a;
+ a | b
+---+---
+ 2 | 4
+(1 row)
+
+-- test with joins
+CREATE TABLE gtestx (x int, y int);
+INSERT INTO gtestx VALUES (11, 1), (22, 2), (33, 3);
+SELECT * FROM gtestx, gtest1 WHERE gtestx.y = gtest1.a;
+ x | y | a | b
+----+---+---+---
+ 11 | 1 | 1 | 2
+ 22 | 2 | 2 | 4
+(2 rows)
+
+DROP TABLE gtestx;
+-- test UPDATE/DELETE quals
+SELECT * FROM gtest1 ORDER BY a;
+ a | b
+---+---
+ 1 | 2
+ 2 | 4
+(2 rows)
+
+UPDATE gtest1 SET a = 3 WHERE b = 4;
+SELECT * FROM gtest1 ORDER BY a;
+ a | b
+---+---
+ 1 | 2
+ 3 | 6
+(2 rows)
+
+DELETE FROM gtest1 WHERE b = 2;
+SELECT * FROM gtest1 ORDER BY a;
+ a | b
+---+---
+ 3 | 6
+(1 row)
+
+-- stored
+CREATE TABLE gtest3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 3)
STORED);
+INSERT INTO gtest3 (a) VALUES (1), (2), (3);
+ERROR: stored generated columns are not yet implemented
+SELECT * FROM gtest3 ORDER BY a;
+ a | b
+---+---
+(0 rows)
+
+UPDATE gtest3 SET a = 22 WHERE a = 2;
+ERROR: stored generated columns are not yet implemented
+SELECT * FROM gtest3 ORDER BY a;
+ a | b
+---+---
+(0 rows)
+
+-- COPY
+TRUNCATE gtest1;
+INSERT INTO gtest1 (a) VALUES (1), (2);
+COPY gtest1 TO stdout;
+1
+2
+COPY gtest1 (a, b) TO stdout;
+ERROR: column "b" is a generated column
+DETAIL: Generated columns cannot be used in COPY.
+COPY gtest1 FROM stdin;
+COPY gtest1 (a, b) FROM stdin;
+ERROR: column "b" is a generated column
+DETAIL: Generated columns cannot be used in COPY.
+SELECT * FROM gtest1 ORDER BY a;
+ a | b
+---+---
+ 1 | 2
+ 2 | 4
+ 3 | 6
+(3 rows)
+
+-- drop column behavior
+CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b *
2));
+ALTER TABLE gtest10 DROP COLUMN b;
+\d gtest10
+ Table "public.gtest10"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | not null |
+Indexes:
+ "gtest10_pkey" PRIMARY KEY, btree (a)
+
+-- privileges
+CREATE USER regress_user11;
+CREATE TABLE gtest11 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b *
2));
+INSERT INTO gtest11 VALUES (1, 10), (2, 20);
+GRANT SELECT (a, c) ON gtest11 TO regress_user11;
+CREATE FUNCTION gf1(a int) RETURNS int AS $$ SELECT a * 3 $$ IMMUTABLE
LANGUAGE SQL;
+REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC;
+CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS
(gf1(b)));
+INSERT INTO gtest12 VALUES (1, 10), (2, 20);
+GRANT SELECT (a, c) ON gtest12 TO regress_user11;
+SET ROLE regress_user11;
+SELECT a, b FROM gtest11; -- not allowed
+ERROR: permission denied for relation gtest11
+SELECT a, c FROM gtest11; -- allowed
+ a | c
+---+----
+ 1 | 20
+ 2 | 40
+(2 rows)
+
+SELECT gf1(10); -- not allowed
+ERROR: permission denied for function gf1
+SELECT a, c FROM gtest12; -- FIXME: should be allowed
+ERROR: permission denied for function gf1
+RESET ROLE;
+DROP TABLE gtest11, gtest12;
+DROP FUNCTION gf1(int);
+DROP USER regress_user11;
+-- check constraints
+CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
CHECK (b < 50));
+INSERT INTO gtest20 (a) VALUES (10); -- ok
+INSERT INTO gtest20 (a) VALUES (30); -- violates constraint
+ERROR: new row for relation "gtest20" violates check constraint
"gtest20_b_check"
+DETAIL: Failing row contains (30).
+-- not-null constraints
+CREATE TABLE gtest21 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a,
0)) not null);
+INSERT INTO gtest21 (a) VALUES (1); -- ok
+INSERT INTO gtest21 (a) VALUES (0); -- violates constraint
+ERROR: new row for relation "gtest21" violates check constraint
"gtest21_b_check"
+DETAIL: Failing row contains (0).
+-- index constraints
+CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
unique);
+ERROR: index creation on generated columns is not supported
+CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a * 2), PRIMARY KEY
(a, b));
+ERROR: index creation on generated columns is not supported
+CREATE TABLE gtest22c (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2));
+CREATE INDEX ON gtest22c (b);
+ERROR: index creation on generated columns is not supported
+CREATE INDEX ON gtest22c ((b * 2));
+ERROR: index creation on generated columns is not supported
+CREATE INDEX ON gtest22c (a) WHERE b > 0;
+ERROR: index creation on generated columns is not supported
+-- foreign keys
+CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
REFERENCES gtest23a (x) ON UPDATE CASCADE);
+ERROR: invalid ON UPDATE action for foreign key constraint containing
generated column
+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
REFERENCES gtest23a (x) ON DELETE SET NULL);
+ERROR: invalid ON DELETE action for foreign key constraint containing
generated column
+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
REFERENCES gtest23a (x));
+ERROR: foreign key constraints on generated columns are not supported
+DROP TABLE gtest23a;
+-- domains
+CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10);
+CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a
* 2)); -- prohibited
+ERROR: virtual generated column "b" cannot have a domain type
+LINE 1: CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENE...
+ ^
+-- ALTER TABLE
+CREATE TABLE gtest25 (a int PRIMARY KEY);
+INSERT INTO gtest25 VALUES (3), (4);
+ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (a * 3);
+SELECT * FROM gtest25 ORDER BY a;
+ a | b
+---+----
+ 3 | 9
+ 4 | 12
+(2 rows)
+
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4); -- error
+ERROR: cannot use generated column "b" in column generation expression
+DETAIL: A generated column cannot reference another generated column.
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4); -- error
+ERROR: column "z" does not exist
+-- triggers
+CREATE TABLE gtest26 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2));
+CREATE FUNCTION gtest_trigger_func() RETURNS trigger
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+ RAISE INFO '%: old = %', TG_NAME, OLD;
+ RAISE INFO '%: new = %', TG_NAME, NEW;
+ RETURN NEW;
+END
+$$;
+CREATE TRIGGER gtest1 BEFORE UPDATE ON gtest26
+ FOR EACH ROW
+ WHEN (OLD.b < 0) -- ok
+ EXECUTE PROCEDURE gtest_trigger_func();
+CREATE TRIGGER gtest2 BEFORE UPDATE ON gtest26
+ FOR EACH ROW
+ WHEN (NEW.b < 0) -- error
+ EXECUTE PROCEDURE gtest_trigger_func();
+ERROR: BEFORE trigger's WHEN condition cannot reference NEW generated columns
+LINE 3: WHEN (NEW.b < 0)
+ ^
+CREATE TRIGGER gtest3 AFTER UPDATE ON gtest26
+ FOR EACH ROW
+ WHEN (OLD.b < 0) -- ok
+ EXECUTE PROCEDURE gtest_trigger_func();
+CREATE TRIGGER gtest4 AFTER UPDATE ON gtest26
+ FOR EACH ROW
+ WHEN (NEW.b < 0) -- ok
+ EXECUTE PROCEDURE gtest_trigger_func();
+INSERT INTO gtest26 (a) VALUES (-2), (0), (3);
+SELECT * FROM gtest26 ORDER BY a;
+ a | b
+----+----
+ -2 | -4
+ 0 | 0
+ 3 | 6
+(3 rows)
+
+UPDATE gtest26 SET a = a * -2;
+INFO: gtest1: old = (-2,)
+INFO: gtest1: new = (4,)
+INFO: gtest3: old = (-2,)
+INFO: gtest3: new = (4,)
+INFO: gtest4: old = (3,)
+INFO: gtest4: new = (-6,)
+SELECT * FROM gtest26 ORDER BY a;
+ a | b
+----+-----
+ -6 | -12
+ 0 | 0
+ 4 | 8
+(3 rows)
+
+CREATE FUNCTION gtest_trigger_func2() RETURNS trigger
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+ NEW.b = 5;
+ RETURN NEW;
+END
+$$;
+CREATE TRIGGER gtest10 BEFORE INSERT OR UPDATE ON gtest26
+ FOR EACH ROW
+ EXECUTE PROCEDURE gtest_trigger_func2();
+INSERT INTO gtest26 (a) VALUES (10);
+ERROR: trigger modified virtual generated column value
+UPDATE gtest26 SET a = 1 WHERE a = 0;
+ERROR: trigger modified virtual generated column value
diff --git a/src/test/regress/parallel_schedule
b/src/test/regress/parallel_schedule
index eefdeeacae..e8f2fbe659 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -114,7 +114,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs
prepare without_oid c
# ----------
# Another group of parallel tests
# ----------
-test: identity
+test: identity generated
# event triggers cannot run concurrently with any test that runs DDL
test: event_trigger
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 76b0de30a7..3493c63824 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -171,6 +171,7 @@ test: truncate
test: alter_table
test: sequence
test: identity
+test: generated
test: polymorphism
test: rowtypes
test: returning
diff --git a/src/test/regress/sql/create_table_like.sql
b/src/test/regress/sql/create_table_like.sql
index 557040bbe7..2ae96e3d68 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -51,6 +51,20 @@ CREATE TABLE test_like_id_3 (LIKE test_like_id_1 INCLUDING
IDENTITY);
SELECT * FROM test_like_id_3; -- identity was copied and applied
DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3;
+CREATE TABLE test_like_gen_1 (a int, b int GENERATED ALWAYS AS (a * 2));
+\d test_like_gen_1
+INSERT INTO test_like_gen_1 (a) VALUES (1);
+SELECT * FROM test_like_gen_1;
+CREATE TABLE test_like_gen_2 (LIKE test_like_gen_1);
+\d test_like_gen_2
+INSERT INTO test_like_gen_2 (a) VALUES (1);
+SELECT * FROM test_like_gen_2;
+CREATE TABLE test_like_gen_3 (LIKE test_like_gen_1 INCLUDING GENERATED);
+\d test_like_gen_3
+INSERT INTO test_like_gen_3 (a) VALUES (1);
+SELECT * FROM test_like_gen_3;
+DROP TABLE test_like_gen_1, test_like_gen_2, test_like_gen_3;
+
CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies
indexes */
INSERT INTO inhg VALUES (5, 10);
INSERT INTO inhg VALUES (20, 10); -- should fail
diff --git a/src/test/regress/sql/generated.sql
b/src/test/regress/sql/generated.sql
new file mode 100644
index 0000000000..9dc96ad93d
--- /dev/null
+++ b/src/test/regress/sql/generated.sql
@@ -0,0 +1,192 @@
+CREATE TABLE gtest0 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (55));
+CREATE TABLE gtest1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2));
+
+SELECT table_name, column_name, column_default, is_nullable, is_generated,
generation_expression FROM information_schema.columns WHERE table_name LIKE
'gtest_' ORDER BY 1, 2;
+
+-- duplicate generated
+CREATE TABLE gtest_err_1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
GENERATED ALWAYS AS (a * 3));
+
+-- references to other generated columns, including self-references
+CREATE TABLE gtest_err_2a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (b *
2));
+CREATE TABLE gtest_err_2b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a *
2), c int GENERATED ALWAYS AS (b * 3));
+
+-- invalid reference
+CREATE TABLE gtest_err_3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (c *
2));
+
+-- functions must be immutable
+CREATE TABLE gtest_err_4 (a int PRIMARY KEY, b double precision GENERATED
ALWAYS AS (random()));
+
+-- cannot have default/identity and generated
+CREATE TABLE gtest_err_5a (a int PRIMARY KEY, b int DEFAULT 5 GENERATED ALWAYS
AS (a * 2));
+CREATE TABLE gtest_err_5b (a int PRIMARY KEY, b int GENERATED ALWAYS AS
identity GENERATED ALWAYS AS (a * 2));
+
+INSERT INTO gtest1 VALUES (1);
+INSERT INTO gtest1 VALUES (2, DEFAULT);
+INSERT INTO gtest1 VALUES (3, 33); -- error
+
+SELECT * FROM gtest1 ORDER BY a;
+
+UPDATE gtest1 SET b = DEFAULT WHERE a = 1;
+UPDATE gtest1 SET b = 11 WHERE a = 1; -- error
+
+SELECT * FROM gtest1 ORDER BY a;
+
+SELECT a, b, b * 2 AS b2 FROM gtest1 ORDER BY a;
+SELECT a, b FROM gtest1 WHERE b = 4 ORDER BY a;
+
+-- test with joins
+CREATE TABLE gtestx (x int, y int);
+INSERT INTO gtestx VALUES (11, 1), (22, 2), (33, 3);
+SELECT * FROM gtestx, gtest1 WHERE gtestx.y = gtest1.a;
+DROP TABLE gtestx;
+
+-- test UPDATE/DELETE quals
+SELECT * FROM gtest1 ORDER BY a;
+UPDATE gtest1 SET a = 3 WHERE b = 4;
+SELECT * FROM gtest1 ORDER BY a;
+DELETE FROM gtest1 WHERE b = 2;
+SELECT * FROM gtest1 ORDER BY a;
+
+-- stored
+CREATE TABLE gtest3 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 3)
STORED);
+INSERT INTO gtest3 (a) VALUES (1), (2), (3);
+SELECT * FROM gtest3 ORDER BY a;
+UPDATE gtest3 SET a = 22 WHERE a = 2;
+SELECT * FROM gtest3 ORDER BY a;
+
+-- COPY
+TRUNCATE gtest1;
+INSERT INTO gtest1 (a) VALUES (1), (2);
+
+COPY gtest1 TO stdout;
+
+COPY gtest1 (a, b) TO stdout;
+
+COPY gtest1 FROM stdin;
+3
+\.
+
+COPY gtest1 (a, b) FROM stdin;
+
+SELECT * FROM gtest1 ORDER BY a;
+
+-- drop column behavior
+CREATE TABLE gtest10 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b *
2));
+ALTER TABLE gtest10 DROP COLUMN b;
+
+\d gtest10
+
+-- privileges
+CREATE USER regress_user11;
+CREATE TABLE gtest11 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS (b *
2));
+INSERT INTO gtest11 VALUES (1, 10), (2, 20);
+GRANT SELECT (a, c) ON gtest11 TO regress_user11;
+
+CREATE FUNCTION gf1(a int) RETURNS int AS $$ SELECT a * 3 $$ IMMUTABLE
LANGUAGE SQL;
+REVOKE ALL ON FUNCTION gf1(int) FROM PUBLIC;
+CREATE TABLE gtest12 (a int PRIMARY KEY, b int, c int GENERATED ALWAYS AS
(gf1(b)));
+INSERT INTO gtest12 VALUES (1, 10), (2, 20);
+GRANT SELECT (a, c) ON gtest12 TO regress_user11;
+
+SET ROLE regress_user11;
+SELECT a, b FROM gtest11; -- not allowed
+SELECT a, c FROM gtest11; -- allowed
+SELECT gf1(10); -- not allowed
+SELECT a, c FROM gtest12; -- FIXME: should be allowed
+RESET ROLE;
+
+DROP TABLE gtest11, gtest12;
+DROP FUNCTION gf1(int);
+DROP USER regress_user11;
+
+-- check constraints
+CREATE TABLE gtest20 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
CHECK (b < 50));
+INSERT INTO gtest20 (a) VALUES (10); -- ok
+INSERT INTO gtest20 (a) VALUES (30); -- violates constraint
+
+-- not-null constraints
+CREATE TABLE gtest21 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a,
0)) not null);
+INSERT INTO gtest21 (a) VALUES (1); -- ok
+INSERT INTO gtest21 (a) VALUES (0); -- violates constraint
+
+-- index constraints
+CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
unique);
+CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a * 2), PRIMARY KEY
(a, b));
+CREATE TABLE gtest22c (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2));
+CREATE INDEX ON gtest22c (b);
+CREATE INDEX ON gtest22c ((b * 2));
+CREATE INDEX ON gtest22c (a) WHERE b > 0;
+
+-- foreign keys
+CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
REFERENCES gtest23a (x) ON UPDATE CASCADE);
+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
REFERENCES gtest23a (x) ON DELETE SET NULL);
+CREATE TABLE gtest23b (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
REFERENCES gtest23a (x));
+DROP TABLE gtest23a;
+
+-- domains
+CREATE DOMAIN gtestdomain1 AS int CHECK (VALUE < 10);
+CREATE TABLE gtest24 (a int PRIMARY KEY, b gtestdomain1 GENERATED ALWAYS AS (a
* 2)); -- prohibited
+
+-- ALTER TABLE
+CREATE TABLE gtest25 (a int PRIMARY KEY);
+INSERT INTO gtest25 VALUES (3), (4);
+ALTER TABLE gtest25 ADD COLUMN b int GENERATED ALWAYS AS (a * 3);
+SELECT * FROM gtest25 ORDER BY a;
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (b * 4); -- error
+ALTER TABLE gtest25 ADD COLUMN x int GENERATED ALWAYS AS (z * 4); -- error
+
+-- triggers
+CREATE TABLE gtest26 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2));
+
+CREATE FUNCTION gtest_trigger_func() RETURNS trigger
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+ RAISE INFO '%: old = %', TG_NAME, OLD;
+ RAISE INFO '%: new = %', TG_NAME, NEW;
+ RETURN NEW;
+END
+$$;
+
+CREATE TRIGGER gtest1 BEFORE UPDATE ON gtest26
+ FOR EACH ROW
+ WHEN (OLD.b < 0) -- ok
+ EXECUTE PROCEDURE gtest_trigger_func();
+
+CREATE TRIGGER gtest2 BEFORE UPDATE ON gtest26
+ FOR EACH ROW
+ WHEN (NEW.b < 0) -- error
+ EXECUTE PROCEDURE gtest_trigger_func();
+
+CREATE TRIGGER gtest3 AFTER UPDATE ON gtest26
+ FOR EACH ROW
+ WHEN (OLD.b < 0) -- ok
+ EXECUTE PROCEDURE gtest_trigger_func();
+
+CREATE TRIGGER gtest4 AFTER UPDATE ON gtest26
+ FOR EACH ROW
+ WHEN (NEW.b < 0) -- ok
+ EXECUTE PROCEDURE gtest_trigger_func();
+
+INSERT INTO gtest26 (a) VALUES (-2), (0), (3);
+SELECT * FROM gtest26 ORDER BY a;
+UPDATE gtest26 SET a = a * -2;
+SELECT * FROM gtest26 ORDER BY a;
+
+
+CREATE FUNCTION gtest_trigger_func2() RETURNS trigger
+ LANGUAGE plpgsql
+AS $$
+BEGIN
+ NEW.b = 5;
+ RETURN NEW;
+END
+$$;
+
+CREATE TRIGGER gtest10 BEFORE INSERT OR UPDATE ON gtest26
+ FOR EACH ROW
+ EXECUTE PROCEDURE gtest_trigger_func2();
+
+INSERT INTO gtest26 (a) VALUES (10);
+UPDATE gtest26 SET a = 1 WHERE a = 0;
base-commit: 04e9678614ec64ad9043174ac99a25b1dc45233a
--
2.14.1
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
