On 9/12/17 15:35, Jaime Casanova wrote:
> On 10 September 2017 at 00:08, Jaime Casanova
> <jaime.casan...@2ndquadrant.com> wrote:
>>
>> During my own tests, though, i found some problems:
Here is an updated patch that should address the problems you have found.
> also is interesting that in triggers, both before and after, the
> column has a null. that seems reasonable in a before trigger but not
> in an after trigger
Logically, you are correct. But it seems excessive to compute all
virtual columns for every trigger. I don't know how to consolidate
that, especially with the current trigger API that lets
you look more or less directly into the tuple.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 62f578fcd7f5cbf1dbafd43cbb6de0df2e6c148b Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Wed, 27 Dec 2017 11:49:35 -0500
Subject: [PATCH v2] Generated columns
This is an SQL-standard feature that allows creating columns that are
computed from expressions rather than assigned, similar to a view or
materialized view but on a column basis.
The plan to is implement two kinds of generated columns:
virtual (computed on read) and stored (computed on write). This patch
only implements the virtual kind, leaving stubs to implement the stored
kind later.
---
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 | 10 +-
src/backend/commands/indexcmds.c | 24 +-
src/backend/commands/tablecmds.c | 145 ++++++++-
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/rewrite/rewriteHandler.c | 149 ++++++++-
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/pg_dump_sort.c | 10 +
src/bin/pg_dump/t/002_pg_dump.pl | 39 +++
src/bin/psql/describe.c | 28 +-
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 | 1 +
src/include/utils/lsyscache.h | 1 +
src/test/regress/expected/create_table_like.out | 46 +++
src/test/regress/expected/generated.out | 407 ++++++++++++++++++++++++
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 | 233 ++++++++++++++
45 files changed, 1520 insertions(+), 93 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 3f02202caf..76237d9eb8 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1160,6 +1160,17 @@ <title><structname>pg_attribute</structname>
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 0faa72f1d3..6edf04cc69 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</productname></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</productname></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 af2a0e91b9..4ad5024694 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 a0c9a6d257..5dd8a5e5b1 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>
@@ -585,6 +586,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
@@ -617,7 +624,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
@@ -731,6 +738,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>
@@ -1938,6 +1970,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</literal> Clause</title>
diff --git a/src/backend/access/common/tupdesc.c
b/src/backend/access/common/tupdesc.c
index 9e37ca73a8..d810f8e1ee 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;
@@ -242,6 +243,7 @@ TupleDescCopyEntry(TupleDesc dst, AttrNumber dstAttno,
dstAtt->attnotnull = false;
dstAtt->atthasdef = false;
dstAtt->attidentity = '\0';
+ dstAtt->attgenerated = '\0';
}
/*
@@ -388,6 +390,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)
@@ -547,6 +551,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 5b5b04f41c..db76c4b077 100644
--- a/src/backend/catalog/genbki.pl
+++ b/src/backend/catalog/genbki.pl
@@ -451,6 +451,7 @@ sub emit_pgattr_row
atttypmod => '-1',
atthasdef => 'f',
attidentity => '',
+ attgenerated => '',
attisdropped => 'f',
attislocal => 't',
attinhcount => '0',
@@ -480,12 +481,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 4319fc6b8c..f95c0a91fc 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);
@@ -1927,7 +1929,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;
@@ -2014,7 +2016,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 to 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.
@@ -2179,7 +2196,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 =
@@ -2278,7 +2295,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
@@ -2295,7 +2313,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;
@@ -2643,6 +2662,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.
@@ -2660,7 +2719,8 @@ cookDefault(ParseState *pstate,
Node *raw_default,
Oid atttypid,
int32 atttypmod,
- const char *attname)
+ const char *attname,
+ char attgenerated)
{
Node *expr;
@@ -2669,17 +2729,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 0125c18bc1..0b00eebc08 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 360725d59a..e0f17b2894 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 254be28ae4..33c2e73517 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -3064,7 +3064,7 @@ 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 */
@@ -4755,6 +4755,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);
}
}
@@ -4779,6 +4781,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 97091dd9fb..1f5a373a6c 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -597,6 +597,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++)
{
@@ -606,10 +608,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)
{
@@ -618,14 +626,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 d979ce266d..6e012173f2 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -724,6 +724,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
if (colDef->identity)
attr->attidentity = colDef->identity;
+
+ if (colDef->generated)
+ attr->attgenerated = colDef->generated;
}
/*
@@ -1949,6 +1952,13 @@ MergeAttributes(List *schema, List *supers, char
relpersistence,
def->is_not_null |= attribute->attnotnull;
/* Default and other constraints are handled
below */
newattno[parent_attno - 1] = exist_attno;
+
+ /* Check for GENERATED conflicts */
+ if (def->generated != attribute->attgenerated)
+ ereport(ERROR,
+
(errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("inherited
column \"%s\" has a generation conflict",
+
attributeName)));
}
else
{
@@ -1967,6 +1977,7 @@ MergeAttributes(List *schema, List *supers, char
relpersistence,
def->storage = attribute->attstorage;
def->raw_default = NULL;
def->cooked_default = NULL;
+ def->generated = attribute->attgenerated;
def->collClause = NULL;
def->collOid = attribute->attcollation;
def->constraints = NIL;
@@ -4438,7 +4449,9 @@ ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap,
LOCKMODE lockmode)
{
case CONSTR_CHECK:
needscan = true;
- con->qualstate = ExecPrepareExpr((Expr *)
con->qual, estate);
+ con->qualstate =
ExecPrepareExpr(expand_generated_columns_in_expr((Expr *) con->qual,
+
newrel ? newrel : oldrel),
+
estate);
break;
case CONSTR_FOREIGN:
/* Nothing to do here */
@@ -5334,6 +5347,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;
@@ -5707,6 +5721,18 @@ ATExecDropNotNull(Relation rel, const char *colName,
LOCKMODE lockmode)
errmsg("cannot alter system column \"%s\"",
colName)));
+ /*
+ * Generated columns don't use the attnotnull field but use a full CHECK
+ * constraint instead. We could implement here that it finds that CHECK
+ * constraint and drops it, which is kind of what the SQL standard would
+ * require anyway, but that would be quite a bit more work.
+ */
+ if (((Form_pg_attribute) GETSTRUCT(tuple))->attgenerated)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot use DROP NOT NULL on generated
column \"%s\"",
+ colName)));
+
if (get_attidentity(RelationGetRelid(rel), attnum))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -5798,9 +5824,6 @@ ATExecDropNotNull(Relation rel, const char *colName,
LOCKMODE lockmode)
/*
* ALTER TABLE ALTER COLUMN SET NOT NULL
- *
- * Return the address of the modified column. If the column was already NOT
- * NULL, InvalidObjectAddress is returned.
*/
static void
@@ -5823,6 +5846,10 @@ ATPrepSetNotNull(Relation rel, bool recurse, bool
recursing)
}
}
+/*
+ * Return the address of the modified column. If the column was already NOT
+ * NULL, InvalidObjectAddress is returned.
+ */
static ObjectAddress
ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
const char *colName, LOCKMODE lockmode)
@@ -5854,6 +5881,17 @@ ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
errmsg("cannot alter system column \"%s\"",
colName)));
+ /*
+ * XXX We might want to convert this to a CHECK constraint like we do in
+ * transformColumnDefinition().
+ */
+ if (((Form_pg_attribute) GETSTRUCT(tuple))->attgenerated)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot use SET NOT NULL on generated
column \"%s\"",
+ colName),
+ errhint("Add a CHECK constraint instead.")));
+
/*
* Okay, actually perform the catalog change ... if needed
*/
@@ -5916,6 +5954,12 @@ ATExecColumnDefault(Relation rel, const char *colName,
colName,
RelationGetRelationName(rel)),
newDefault ? 0 : errhint("Use ALTER TABLE ...
ALTER COLUMN ... DROP IDENTITY instead.")));
+ if (get_attgenerated(RelationGetRelid(rel), attnum))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("column \"%s\" of relation \"%s\" is a
generated column",
+ colName,
RelationGetRelationName(rel))));
+
/*
* Remove any old default for the column. We use RESTRICT here for
* safety, but at present we do not expect anything to depend on the
@@ -7245,6 +7289,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.
*
@@ -8864,8 +8943,9 @@ ATPrepAlterColumnType(List **wqueue,
list_make1_oid(rel->rd_rel->reltype),
false);
- if (tab->relkind == RELKIND_RELATION ||
- tab->relkind == RELKIND_PARTITIONED_TABLE)
+ if ((tab->relkind == RELKIND_RELATION ||
+ tab->relkind == RELKIND_PARTITIONED_TABLE) &&
+ get_attgenerated(RelationGetRelid(rel), attnum) !=
ATTRIBUTE_GENERATED_VIRTUAL)
{
/*
* Set up an expression to transform the old data value to the
new
@@ -9139,10 +9219,18 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation
rel,
COERCE_IMPLICIT_CAST,
-1);
if (defaultexpr == NULL)
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("default for column \"%s\"
cannot be cast automatically to type %s",
- colName,
format_type_be(targettype))));
+ {
+ if (get_attgenerated(RelationGetRelid(rel), attnum))
+ ereport(ERROR,
+
(errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("generation expression
for column \"%s\" cannot be cast automatically to type %s",
+ colName,
format_type_be(targettype))));
+ else
+ ereport(ERROR,
+
(errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("default for column
\"%s\" cannot be cast automatically to type %s",
+ colName,
format_type_be(targettype))));
+ }
}
else
defaultexpr = NULL;
@@ -9217,6 +9305,21 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation
rel,
*/
Assert(foundObject.objectSubId
== 0);
}
+ else if (relKind == RELKIND_RELATION &&
+
foundObject.objectSubId != 0 &&
+
get_attgenerated(foundObject.objectId, foundObject.objectSubId))
+ {
+ /*
+ * Changing the type of a
column that is used by a
+ * generated column is not
allowed by SQL standard.
+ * It might be doable with some
thinking and effort.
+ */
+ ereport(ERROR,
+
(errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("cannot
alter type of a column used by a generated column"),
+
errdetail("Column \"%s\" is used by generated column \"%s\".",
+
colName, get_attname(foundObject.objectId, foundObject.objectSubId))));
+ }
else
{
/* Not expecting any other
direct dependencies... */
@@ -9382,7 +9485,8 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
/*
* Now scan for dependencies of this column on other things. The only
* thing we should find is the dependency on the column datatype, which
we
- * want to remove, and possibly a collation dependency.
+ * want to remove, possibly a collation dependency, and dependencies on
+ * other columns if it is a generated column.
*/
ScanKeyInit(&key[0],
Anum_pg_depend_classid,
@@ -9403,15 +9507,26 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation
rel,
while (HeapTupleIsValid(depTup = systable_getnext(scan)))
{
Form_pg_depend foundDep = (Form_pg_depend) GETSTRUCT(depTup);
+ ObjectAddress foundObject;
+
+ foundObject.classId = foundDep->refclassid;
+ foundObject.objectId = foundDep->refobjid;
+ foundObject.objectSubId = foundDep->refobjsubid;
- if (foundDep->deptype != DEPENDENCY_NORMAL)
+ if (foundDep->deptype != DEPENDENCY_NORMAL &&
+ foundDep->deptype != DEPENDENCY_AUTO)
elog(ERROR, "found unexpected dependency type '%c'",
foundDep->deptype);
if (!(foundDep->refclassid == TypeRelationId &&
foundDep->refobjid == attTup->atttypid) &&
!(foundDep->refclassid == CollationRelationId &&
- foundDep->refobjid == attTup->attcollation))
- elog(ERROR, "found unexpected dependency for column");
+ foundDep->refobjid == attTup->attcollation) &&
+ !(foundDep->refclassid == RelationRelationId &&
+ foundDep->refobjid == RelationGetRelid(rel) &&
+ foundDep->refobjsubid != 0)
+ )
+ elog(ERROR, "found unexpected dependency for column:
%s",
+ getObjectDescription(&foundObject));
CatalogTupleDelete(depRel, &depTup->t_self);
}
@@ -9470,7 +9585,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 92ae3822d8..4759b8bbd8 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"
@@ -101,6 +102,7 @@ static void AfterTriggerSaveEvent(EState *estate,
ResultRelInfo *relinfo,
TransitionCaptureState
*transition_capture);
static void AfterTriggerEnlargeQueryState(void);
static bool before_stmt_triggers_fired(Oid relid, CmdType cmdType);
+static void check_modified_virtual_generated(TupleDesc tupdesc, HeapTuple
tuple);
/*
@@ -562,6 +564,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 */
@@ -2342,6 +2349,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);
@@ -2829,6 +2838,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);
@@ -3221,6 +3232,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);
@@ -5839,3 +5851,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 f86af4c054..4d3133cb66 100644
--- a/src/backend/commands/typecmds.c
+++ b/src/backend/commands/typecmds.c
@@ -917,7 +917,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
@@ -2244,7 +2245,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 dbaa47f2d3..4137f3633d 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -52,7 +52,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"
@@ -1812,6 +1812,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);
}
@@ -2275,6 +2276,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 84d717102d..727f93ea13 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2817,6 +2817,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);
@@ -2840,6 +2841,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 2e869a9d5d..987020e712 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2563,6 +2563,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);
@@ -2584,6 +2585,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 e468d7cc41..4eb8023bc3 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2810,6 +2810,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);
@@ -3461,6 +3462,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 ebfc94f896..6fc8731420 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -572,7 +572,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
@@ -672,7 +672,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
@@ -683,7 +683,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
@@ -3521,6 +3521,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);
@@ -3543,6 +3554,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
@@ -3610,6 +3627,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; }
@@ -15149,6 +15167,7 @@ unreserved_keyword:
| STDIN
| STDOUT
| STORAGE
+ | STORED
| STRICT_P
| STRIP_P
| SUBSCRIPTION
@@ -15184,6 +15203,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 4c4f4cdc3d..6af2407cf2 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;
case EXPR_KIND_CALL:
if (isAgg)
@@ -894,6 +902,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc
*wfunc,
case EXPR_KIND_CALL:
err = _("window functions are not allowed in CALL
arguments");
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 29f9da796f..de680184e4 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1846,6 +1846,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
@@ -3470,6 +3473,8 @@ ParseExprKindName(ParseExprKind exprKind)
return "PARTITION BY";
case EXPR_KIND_CALL:
return "CALL";
+ 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 e6b085637b..58de8aab7f 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -557,6 +557,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.
@@ -2292,6 +2301,9 @@ check_srf_call_placement(ParseState *pstate, Node
*last_srf, int location)
case EXPR_KIND_CALL:
err = _("set-returning functions are not allowed in
CALL arguments");
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 f67379f8ed..686979db29 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -509,6 +509,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);
@@ -616,6 +617,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef
*column)
saw_nullable = false;
saw_default = false;
saw_identity = false;
+ saw_generated = false;
foreach(clist, column->constraints)
{
@@ -696,6 +698,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;
@@ -780,6 +817,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;
+ }
}
/*
@@ -1027,7 +1108,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;
@@ -1052,6 +1134,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/rewrite/rewriteHandler.c
b/src/backend/rewrite/rewriteHandler.c
index e93552a8f3..568fd54efd 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 */
@@ -828,6 +830,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)
@@ -838,9 +847,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;
@@ -1144,13 +1172,12 @@ build_column_default(Relation rel, int attrno)
}
}
- if (expr == NULL)
- {
- /*
- * No per-column default, so look for a default for the type
itself.
- */
+ /*
+ * No per-column default, so look for a default for the type itself.
But
+ * not for generated columns.
+ */
+ if (expr == NULL && !att_tup->attgenerated)
expr = get_typdefault(atttype);
- }
if (expr == NULL)
return NULL; /* No default anywhere */
@@ -3551,6 +3578,96 @@ 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);
+ 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);
+}
+
+typedef struct
+{
+ /* list of range tables, innermost last */
+ List *rtables;
+} expand_generated_context;
+
+static Node *
+expand_generated_columns_in_query_mutator(Node *node, expand_generated_context
*context)
+{
+ if (node == NULL)
+ return NULL;
+
+ if (IsA(node, Var))
+ {
+ Var *v = (Var *) node;
+ Oid relid;
+ AttrNumber attnum;
+ List *rtable = list_nth_node(List,
+
context->rtables,
+
list_length(context->rtables) - v->varlevelsup - 1);
+
+ 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);
+ ChangeVarNodes(node, 1, v->varno, v->varlevelsup);
+
+ heap_close(rt_entry_relation, NoLock);
+ }
+
+ return node;
+ }
+ else if (IsA(node, Query))
+ {
+ Query *query = (Query *) node;
+ Node *result;
+
+ context->rtables = lappend(context->rtables, query->rtable);
+ result = (Node *) query_tree_mutator(query,
+
expand_generated_columns_in_query_mutator,
+
context,
+
QTW_DONT_COPY_QUERY);
+ context->rtables = list_truncate(context->rtables,
list_length(context->rtables) - 1);
+ return result;
+ }
+ else
+ return expression_tree_mutator(node,
expand_generated_columns_in_query_mutator, context);
+}
+
+
/*
* QueryRewrite -
* Primary entry point to the query rewriter.
@@ -3606,6 +3723,24 @@ QueryRewrite(Query *parsetree)
/*
* Step 3
*
+ * Expand generated columns.
+ */
+ foreach(l, querylist)
+ {
+ Query *query = (Query *) lfirst(l);
+ expand_generated_context context;
+
+ context.rtables = list_make1(query->rtable);
+
+ query = query_tree_mutator(query,
+
expand_generated_columns_in_query_mutator,
+ &context,
+
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 5211360777..be63f842a6 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 e2760daac4..d5a0861325 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -3233,6 +3233,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 e6701aaa78..2174fbdbfd 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1973,6 +1973,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);
@@ -7888,6 +7893,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;
@@ -7944,6 +7950,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) "
@@ -8057,6 +8064,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");
@@ -8073,6 +8081,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));
@@ -8098,6 +8107,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));
@@ -8130,7 +8140,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);
@@ -15512,6 +15522,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]))
{
@@ -15526,13 +15553,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");
}
}
@@ -18043,6 +18063,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;
@@ -18052,6 +18073,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 da884ffd09..c655b5e7e1 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/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c
index 48b6dd594c..73c2d88f77 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -1160,6 +1160,16 @@ repairDependencyLoop(DumpableObject **loop,
}
}
+ /* Loop of table with itself, happens with generated columns */
+ if (nLoop == 1)
+ {
+ if (loop[0]->objType == DO_TABLE)
+ {
+ removeObjectDependency(loop[0], loop[0]->dumpId);
+ return;
+ }
+ }
+
/*
* If all the objects are TABLE_DATA items, what we must have is a
* circular set of foreign key constraints (or a single self-referential
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 7cf9bdadb2..8477480474 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -5026,6 +5026,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 3fc69c46c0..7de0ba7a44 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1692,7 +1692,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,");
@@ -1705,6 +1705,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
@@ -1886,6 +1890,7 @@ describeOneTableDetails(const char *schemaname,
if (show_column_details)
{
char *identity;
+ char *generated;
char *default_str = "";
printTableAddCell(&cont, PQgetvalue(res, i, 5), false,
false);
@@ -1893,30 +1898,35 @@ 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);
}
/* 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/heap.h b/src/include/catalog/heap.h
index 0fae02295b..a721000fe2 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,
- const char *attname);
+ const 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 2eaa6b2774..0d4f5e7e18 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;
@@ -2063,6 +2065,7 @@ typedef enum ConstrType /* types of
constraints */
CONSTR_NOTNULL,
CONSTR_DEFAULT,
CONSTR_IDENTITY,
+ CONSTR_GENERATED,
CONSTR_CHECK,
CONSTR_PRIMARY,
CONSTR_UNIQUE,
@@ -2101,7 +2104,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 a932400058..166aee746d 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -381,6 +381,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)
@@ -436,6 +437,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 565bb3dc6c..b22c789daf 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -68,7 +68,8 @@ typedef enum ParseExprKind
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_CALL /* CALL argument */
+ EXPR_KIND_CALL, /* CALL argument */
+ 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 86ae571eb1..3aeff05f6a 100644
--- a/src/include/rewrite/rewriteHandler.h
+++ b/src/include/rewrite/rewriteHandler.h
@@ -32,5 +32,6 @@ extern const char *view_query_is_auto_updatable(Query
*viewquery,
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 b316cc594c..1c15b4491e 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..afaba16fe2
--- /dev/null
+++ b/src/test/regress/expected/generated.out
@@ -0,0 +1,407 @@
+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)
+
+\d gtest1
+ Table "public.gtest1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | not null |
+ b | integer | | | generated always as (a * 2)
+Indexes:
+ "gtest1_pkey" PRIMARY KEY, btree (a)
+
+-- 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)
+
+-- views
+CREATE VIEW gtest1v AS SELECT * FROM gtest1;
+SELECT * FROM gtest1v;
+ a | b
+---+---
+ 3 | 6
+(1 row)
+
+INSERT INTO gtest1v VALUES (4, 8); -- fails
+ERROR: cannot insert into column "b"
+DETAIL: Column "b" is a generated column.
+-- inheritance
+CREATE TABLE gtest1_1 () INHERITS (gtest1);
+SELECT * FROM gtest1_1;
+ a | b
+---+---
+(0 rows)
+
+\d gtest1_1
+ Table "public.gtest1_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ a | integer | | not null |
+ b | integer | | | generated always as (a * 2)
+Inherits: gtest1
+
+INSERT INTO gtest1_1 VALUES (4);
+SELECT * FROM gtest1_1;
+ a | b
+---+---
+ 4 | 8
+(1 row)
+
+SELECT * FROM gtest1;
+ a | b
+---+---
+ 3 | 6
+ 4 | 8
+(2 rows)
+
+-- test inheritance mismatch
+CREATE TABLE gtesty (x int, b int);
+CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error
+NOTICE: merging multiple inherited definitions of column "b"
+ERROR: inherited column "b" has a generation conflict
+DROP TABLE gtesty;
+-- 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).
+CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2));
+INSERT INTO gtest20a (a) VALUES (10);
+INSERT INTO gtest20a (a) VALUES (30);
+ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row
+ERROR: check constraint "gtest20a_b_check" is violated by some row
+-- 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).
+CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a,
0)));
+ALTER TABLE gtest21a ALTER COLUMN b SET NOT NULL; -- error
+ERROR: cannot use SET NOT NULL on generated column "b"
+HINT: Add a CHECK constraint instead.
+ALTER TABLE gtest21a ALTER COLUMN b DROP NOT NULL; -- error
+ERROR: cannot use DROP NOT NULL on generated column "b"
+-- 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 ... ADD COLUMN
+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
+-- ALTER TABLE ... ALTER COLUMN
+CREATE TABLE gtest27 (a int, b int GENERATED ALWAYS AS (a * 2));
+INSERT INTO gtest27 (a) VALUES (3), (4);
+ALTER TABLE gtest27 ALTER COLUMN a TYPE text; -- error
+ERROR: cannot alter type of a column used by a generated column
+DETAIL: Column "a" is used by generated column "b".
+ALTER TABLE gtest27 ALTER COLUMN b TYPE numeric;
+\d gtest27
+ Table "public.gtest27"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------
+ a | integer | | |
+ b | numeric | | | generated always as ((a * 2))
+
+SELECT * FROM gtest27;
+ a | b
+---+---
+ 3 | 6
+ 4 | 8
+(2 rows)
+
+ALTER TABLE gtest27 ALTER COLUMN b TYPE boolean; -- error
+ERROR: generation expression for column "b" cannot be cast automatically to
type boolean
+ALTER TABLE gtest27 ALTER COLUMN b DROP DEFAULT; -- FIXME
+ERROR: column "b" of relation "gtest27" is a generated column
+\d gtest27
+ Table "public.gtest27"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-------------------------------
+ a | integer | | |
+ b | numeric | | | generated always as ((a * 2))
+
+-- 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 e224977791..30ce38dccf 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -116,7 +116,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs
prepare without_oid c
# ----------
# Another group of parallel tests
# ----------
-test: identity partition_join partition_prune reloptions hash_part
+test: identity generated partition_join partition_prune reloptions hash_part
# 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 9fc5f1a268..f0a71485bd 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -180,6 +180,7 @@ test: largeobject
test: with
test: xml
test: identity
+test: generated
test: partition_join
test: partition_prune
test: reloptions
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..e9ad664c61
--- /dev/null
+++ b/src/test/regress/sql/generated.sql
@@ -0,0 +1,233 @@
+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;
+
+\d gtest1
+
+-- 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;
+
+-- views
+CREATE VIEW gtest1v AS SELECT * FROM gtest1;
+SELECT * FROM gtest1v;
+INSERT INTO gtest1v VALUES (4, 8); -- fails
+
+-- inheritance
+CREATE TABLE gtest1_1 () INHERITS (gtest1);
+SELECT * FROM gtest1_1;
+\d gtest1_1
+INSERT INTO gtest1_1 VALUES (4);
+SELECT * FROM gtest1_1;
+SELECT * FROM gtest1;
+
+-- test inheritance mismatch
+CREATE TABLE gtesty (x int, b int);
+CREATE TABLE gtest1_2 () INHERITS (gtest1, gtesty); -- error
+DROP TABLE gtesty;
+
+-- 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
+
+CREATE TABLE gtest20a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2));
+INSERT INTO gtest20a (a) VALUES (10);
+INSERT INTO gtest20a (a) VALUES (30);
+ALTER TABLE gtest20a ADD CHECK (b < 50); -- fails on existing row
+
+-- 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
+
+CREATE TABLE gtest21a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (nullif(a,
0)));
+ALTER TABLE gtest21a ALTER COLUMN b SET NOT NULL; -- error
+ALTER TABLE gtest21a ALTER COLUMN b DROP NOT NULL; -- error
+
+-- 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 ... ADD COLUMN
+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
+
+-- ALTER TABLE ... ALTER COLUMN
+CREATE TABLE gtest27 (a int, b int GENERATED ALWAYS AS (a * 2));
+INSERT INTO gtest27 (a) VALUES (3), (4);
+ALTER TABLE gtest27 ALTER COLUMN a TYPE text; -- error
+ALTER TABLE gtest27 ALTER COLUMN b TYPE numeric;
+\d gtest27
+SELECT * FROM gtest27;
+ALTER TABLE gtest27 ALTER COLUMN b TYPE boolean; -- error
+
+ALTER TABLE gtest27 ALTER COLUMN b DROP DEFAULT; -- FIXME
+\d gtest27
+
+-- 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;
--
2.15.1
