aboutsummaryrefslogtreecommitdiff
path: root/doc/src
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src')
-rw-r--r--doc/src/sgml/catalogs.sgml6
-rw-r--r--doc/src/sgml/ddl.sgml46
-rw-r--r--doc/src/sgml/ref/alter_table.sgml15
-rw-r--r--doc/src/sgml/ref/create_foreign_table.sgml11
-rw-r--r--doc/src/sgml/ref/create_table.sgml22
-rw-r--r--doc/src/sgml/trigger.sgml4
6 files changed, 74 insertions, 30 deletions
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 088fb175cce..ee59a7e15d0 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1307,8 +1307,10 @@
</para>
<para>
If a zero byte (<literal>''</literal>), then not a generated column.
- Otherwise, <literal>s</literal> = stored. (Other values might be added
- in the future.)
+ Otherwise, <literal>s</literal> = stored, <literal>v</literal> =
+ virtual. A stored generated column is physically stored like a normal
+ column. A virtual generated column is physically stored as a null
+ value, with the actual value being computed at run time.
</para></entry>
</row>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 7ff39ae8c67..ae156b6b1cd 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -361,7 +361,6 @@ INSERT INTO people (id, name, address) VALUES (<emphasis>DEFAULT</emphasis>, 'C'
storage and is computed when it is read. Thus, a virtual generated column
is similar to a view and a stored generated column is similar to a
materialized view (except that it is always updated automatically).
- <productname>PostgreSQL</productname> currently implements only stored generated columns.
</para>
<para>
@@ -371,12 +370,12 @@ INSERT INTO people (id, name, address) VALUES (<emphasis>DEFAULT</emphasis>, 'C'
CREATE TABLE people (
...,
height_cm numeric,
- height_in numeric <emphasis>GENERATED ALWAYS AS (height_cm / 2.54) STORED</emphasis>
+ height_in numeric <emphasis>GENERATED ALWAYS AS (height_cm / 2.54)</emphasis>
);
</programlisting>
- The keyword <literal>STORED</literal> must be specified to choose the
- stored kind of generated column. See <xref linkend="sql-createtable"/> for
- more details.
+ A generated column is by default of the virtual kind. Use the keywords
+ <literal>VIRTUAL</literal> or <literal>STORED</literal> to make the choice
+ explicit. See <xref linkend="sql-createtable"/> for more details.
</para>
<para>
@@ -442,12 +441,18 @@ CREATE TABLE people (
<listitem>
<para>
If a parent column is a generated column, its child column must also
- be a generated column; however, the child column can have a
- different generation expression. The generation expression that is
+ be a generated column of the same kind (stored or virtual); however,
+ the child column can have a different generation expression.
+ </para>
+
+ <para>
+ For stored generated columns, the generation expression that is
actually applied during insert or update of a row is the one
- associated with the table that the row is physically in.
- (This is unlike the behavior for column defaults: for those, the
- default value associated with the table named in the query applies.)
+ associated with the table that the row is physically in. (This is
+ unlike the behavior for column defaults: for those, the default value
+ associated with the table named in the query applies.) For virtual
+ generated columns, the generation expression of the table named in the
+ query applies when a table is read.
</para>
</listitem>
<listitem>
@@ -502,6 +507,26 @@ CREATE TABLE people (
particular role can read from a generated column but not from the
underlying base columns.
</para>
+
+ <para>
+ For virtual generated columns, this is only fully secure if the
+ generation expression uses only leakproof functions (see <xref
+ linkend="sql-createfunction"/>), but this is not enforced by the system.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ Privileges of functions used in generation expressions are checked when
+ the expression is actually executed, on write or read respectively, as
+ if the generation expression had been called directly from the query
+ using the generated column. The user of a generated column must have
+ permissions to call all functions used by the generation expression.
+ Functions in the generation expression are executed with the privileges
+ of the user executing the query or the function owner, depending on
+ whether the functions are defined as <literal>SECURITY INVOKER</literal>
+ or <literal>SECURITY DEFINER</literal>.
+ <!-- matches create_view.sgml -->
+ </para>
</listitem>
<listitem>
<para>
@@ -519,6 +544,7 @@ CREATE TABLE people (
<link linkend="sql-createpublication-params-with-publish-generated-columns">
<literal>publish_generated_columns</literal></link> or by including them
in the column list of the <command>CREATE PUBLICATION</command> command.
+ This is currently only supported for stored generated columns.
See <xref linkend="logical-replication-gencols"/> for details.
</para>
</listitem>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index f9576da435e..8e56b8e59b0 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -102,7 +102,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
NULL |
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
DEFAULT <replaceable>default_expr</replaceable> |
- GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED |
+ GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ STORED | VIRTUAL ] |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
UNIQUE [ NULLS [ NOT ] DISTINCT ] <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
@@ -264,8 +264,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
This form replaces the expression of a generated column. Existing data
- in the column is rewritten and all the future changes will apply the new
- generation expression.
+ in a stored generated column is rewritten and all the future changes
+ will apply the new generation expression.
</para>
</listitem>
</varlistentry>
@@ -280,9 +280,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
+ This form is currently only supported for stored generated columns (not
+ virtual ones).
+ </para>
+
+ <para>
If <literal>DROP EXPRESSION IF EXISTS</literal> is specified and the
- column is not a stored generated column, no error is thrown. In this
- case a notice is issued instead.
+ column is not a generated column, no error is thrown. In this case a
+ notice is issued instead.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 0dcd9ca6f87..e0b0e075c2c 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -47,7 +47,7 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
NULL |
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
DEFAULT <replaceable>default_expr</replaceable> |
- GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED }
+ GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ STORED | VIRTUAL ] }
[ ENFORCED | NOT ENFORCED ]
<phrase>and <replaceable class="parameter">table_constraint</replaceable> is:</phrase>
@@ -283,7 +283,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</varlistentry>
<varlistentry>
- <term><literal>GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED</literal><indexterm><primary>generated column</primary></indexterm></term>
+ <term><literal>GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ STORED | VIRTUAL ]</literal><indexterm><primary>generated column</primary></indexterm></term>
<listitem>
<para>
This clause creates the column as a <firstterm>generated
@@ -292,10 +292,13 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- The keyword <literal>STORED</literal> is required to signify that the
+ When <literal>VIRTUAL</literal> is specified, the column will be
+ computed when it is read. (The foreign-data wrapper will see it as a
+ null value in new rows and may choose to store it as a null value or
+ ignore it altogether.) When <literal>STORED</literal> is specified, the
column will be computed on write. (The computed value will be presented
to the foreign-data wrapper for storage and must be returned on
- reading.)
+ reading.) <literal>VIRTUAL</literal> is the default.
</para>
<para>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 417498f71db..9acbc4dd34d 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -65,7 +65,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
NULL |
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
DEFAULT <replaceable>default_expr</replaceable> |
- GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED |
+ GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ STORED | VIRTUAL ] |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
UNIQUE [ NULLS [ NOT ] DISTINCT ] <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
@@ -725,8 +725,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<term><literal>INCLUDING GENERATED</literal></term>
<listitem>
<para>
- Any generation expressions of copied column definitions will be
- copied. By default, new columns will be regular base columns.
+ Any generation expressions as well as the stored/virtual choice of
+ copied column definitions will be copied. By default, new columns
+ will be regular base columns.
</para>
</listitem>
</varlistentry>
@@ -907,7 +908,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</varlistentry>
<varlistentry id="sql-createtable-parms-generated-stored">
- <term><literal>GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED</literal><indexterm><primary>generated column</primary></indexterm></term>
+ <term><literal>GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) [ STORED | VIRTUAL ]</literal><indexterm><primary>generated column</primary></indexterm></term>
<listitem>
<para>
This clause creates the column as a <firstterm>generated
@@ -916,8 +917,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</para>
<para>
- The keyword <literal>STORED</literal> is required to signify that the
- column will be computed on write and will be stored on disk.
+ 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>
@@ -2504,9 +2508,9 @@ CREATE TABLE cities_partdef
<title>Generated Columns</title>
<para>
- The option <literal>STORED</literal> is not standard but is also used by
- other SQL implementations. The SQL standard does not specify the storage
- of generated columns.
+ The options <literal>STORED</literal> and <literal>VIRTUAL</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>
diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
index c3c0faf7a1b..e9214dcf1b1 100644
--- a/doc/src/sgml/trigger.sgml
+++ b/doc/src/sgml/trigger.sgml
@@ -293,6 +293,10 @@
<literal>BEFORE</literal> trigger. Changes to the value of a generated
column in a <literal>BEFORE</literal> trigger are ignored and will be
overwritten.
+ Virtual generated columns are never computed when triggers fire. In the C
+ language interface, their content is undefined in a trigger function.
+ Higher-level programming languages should prevent access to virtual
+ generated columns in triggers.
</para>
<para>
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-20 10:49:52 +0000