Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitfc22b66

Browse files
committed
Generated columns
This is an SQL-standard feature that allows creating columns that arecomputed from expressions rather than assigned, similar to a view ormaterialized view but on a column basis.This implements one kind of generated column: stored (computed onwrite). Another kind, virtual (computed on read), is planned for thefuture, and some room is left for it.Reviewed-by: Michael Paquier <michael@paquier.xyz>Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com>Discussion:https://www.postgresql.org/message-id/flat/b151f851-4019-bdb1-699e-ebab07d2f40a@2ndquadrant.com
1 parent6b8b536 commitfc22b66

File tree

84 files changed

+3065
-155
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

84 files changed

+3065
-155
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6431,6 +6431,31 @@ select * from rem1;
64316431
11 | bye remote
64326432
(4 rows)
64336433

6434+
-- ===================================================================
6435+
-- test generated columns
6436+
-- ===================================================================
6437+
create table gloc1 (a int, b int);
6438+
alter table gloc1 set (autovacuum_enabled = 'false');
6439+
create foreign table grem1 (
6440+
a int,
6441+
b int generated always as (a * 2) stored)
6442+
server loopback options(table_name 'gloc1');
6443+
insert into grem1 (a) values (1), (2);
6444+
update grem1 set a = 22 where a = 2;
6445+
select * from gloc1;
6446+
a | b
6447+
----+----
6448+
1 | 2
6449+
22 | 44
6450+
(2 rows)
6451+
6452+
select * from grem1;
6453+
a | b
6454+
----+----
6455+
1 | 2
6456+
22 | 44
6457+
(2 rows)
6458+
64346459
-- ===================================================================
64356460
-- test local triggers
64366461
-- ===================================================================

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1644,9 +1644,10 @@ postgresPlanForeignModify(PlannerInfo *root,
16441644
elseif (operation==CMD_UPDATE)
16451645
{
16461646
intcol;
1647+
Bitmapset*allUpdatedCols=bms_union(rte->updatedCols,rte->extraUpdatedCols);
16471648

16481649
col=-1;
1649-
while ((col=bms_next_member(rte->updatedCols,col)) >=0)
1650+
while ((col=bms_next_member(allUpdatedCols,col)) >=0)
16501651
{
16511652
/* bit numbers are offset by FirstLowInvalidHeapAttributeNumber */
16521653
AttrNumberattno=col+FirstLowInvalidHeapAttributeNumber;

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1363,6 +1363,20 @@ insert into rem1(f2) values('bye remote');
13631363
select*from loc1;
13641364
select*from rem1;
13651365

1366+
-- ===================================================================
1367+
-- test generated columns
1368+
-- ===================================================================
1369+
createtablegloc1 (aint, bint);
1370+
altertable gloc1set (autovacuum_enabled='false');
1371+
create foreign table grem1 (
1372+
aint,
1373+
bint generated alwaysas (a*2) stored)
1374+
server loopback options(table_name'gloc1');
1375+
insert into grem1 (a)values (1), (2);
1376+
update grem1set a=22where a=2;
1377+
select*from gloc1;
1378+
select*from grem1;
1379+
13661380
-- ===================================================================
13671381
-- test local triggers
13681382
-- ===================================================================

‎doc/src/sgml/catalogs.sgml

Lines changed: 16 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1129,9 +1129,11 @@
11291129
<entry><type>bool</type></entry>
11301130
<entry></entry>
11311131
<entry>
1132-
This column has a default value, in which case there will be a
1133-
corresponding entry in the <structname>pg_attrdef</structname>
1134-
catalog that actually defines the value.
1132+
This column has a default expression or generation expression, in which
1133+
case there will be a corresponding entry in the
1134+
<structname>pg_attrdef</structname> catalog that actually defines the
1135+
expression. (Check <structfield>attgenerated</structfield> to
1136+
determine whether this is a default or a generation expression.)
11351137
</entry>
11361138
</row>
11371139

@@ -1159,6 +1161,17 @@
11591161
</entry>
11601162
</row>
11611163

1164+
<row>
1165+
<entry><structfield>attgenerated</structfield></entry>
1166+
<entry><type>char</type></entry>
1167+
<entry></entry>
1168+
<entry>
1169+
If a zero byte (<literal>''</literal>), then not a generated column.
1170+
Otherwise, <literal>s</literal> = stored. (Other values might be added
1171+
in the future.)
1172+
</entry>
1173+
</row>
1174+
11621175
<row>
11631176
<entry><structfield>attisdropped</structfield></entry>
11641177
<entry><type>bool</type></entry>

‎doc/src/sgml/ddl.sgml

Lines changed: 118 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -233,6 +233,124 @@ CREATE TABLE products (
233233
</para>
234234
</sect1>
235235

236+
<sect1 id="ddl-generated-columns">
237+
<title>Generated Columns</title>
238+
239+
<indexterm zone="ddl-generated-columns">
240+
<primary>generated column</primary>
241+
</indexterm>
242+
243+
<para>
244+
A generated column is a special column that is always computed from other
245+
columns. Thus, it is for columns what a view is for tables. There are two
246+
kinds of generated columns: stored and virtual. A stored generated column
247+
is computed when it is written (inserted or updated) and occupies storage
248+
as if it were a normal column. A virtual generated column occupies no
249+
storage and is computed when it is read. Thus, a virtual generated column
250+
is similar to a view and a stored generated column is similar to a
251+
materialized view (except that it is always updated automatically).
252+
PostgreSQL currently implements only stored generated columns.
253+
</para>
254+
255+
<para>
256+
To create a generated column, use the <literal>GENERATED ALWAYS
257+
AS</literal> clause in <command>CREATE TABLE</command>, for example:
258+
<programlisting>
259+
CREATE TABLE people (
260+
...,
261+
height_cm numeric,
262+
height_in numeric <emphasis>GENERATED ALWAYS AS (height_cm * 2.54) STORED</emphasis>
263+
);
264+
</programlisting>
265+
The keyword <literal>STORED</literal> must be specified to choose the
266+
stored kind of generated column. See <xref linkend="sql-createtable"/> for
267+
more details.
268+
</para>
269+
270+
<para>
271+
A generated column cannot be written to directly. In
272+
<command>INSERT</command> or <command>UPDATE</command> commands, a value
273+
cannot be specified for a generated column, but the keyword
274+
<literal>DEFAULT</literal> may be specified.
275+
</para>
276+
277+
<para>
278+
Consider the differences between a column with a default and a generated
279+
column. The column default is evaluated once when the row is first
280+
inserted if no other value was provided; a generated column is updated
281+
whenever the row changes and cannot be overridden. A column default may
282+
not refer to other columns of the table; a generation expression would
283+
normally do so. A column default can use volatile functions, for example
284+
<literal>random()</literal> or functions referring to the current time;
285+
this is not allowed for generated columns.
286+
</para>
287+
288+
<para>
289+
Several restrictions apply to the definition of generated columns and
290+
tables involving generated columns:
291+
292+
<itemizedlist>
293+
<listitem>
294+
<para>
295+
The generation expression can only use immutable functions and cannot
296+
use subqueries or reference anything other than the current row in any
297+
way.
298+
</para>
299+
</listitem>
300+
<listitem>
301+
<para>
302+
A generation expression cannot reference another generated column.
303+
</para>
304+
</listitem>
305+
<listitem>
306+
<para>
307+
A generation expression cannot reference a system column, except
308+
<varname>tableoid</varname>.
309+
</para>
310+
</listitem>
311+
<listitem>
312+
<para>
313+
A generated column cannot have a column default or an identity definition.
314+
</para>
315+
</listitem>
316+
<listitem>
317+
<para>
318+
A generated column cannot be part of a partition key.
319+
</para>
320+
</listitem>
321+
<listitem>
322+
<para>
323+
Foreign tables can have generated columns. See <xref
324+
linkend="sql-createforeigntable"/> for details.
325+
</para>
326+
</listitem>
327+
</itemizedlist>
328+
</para>
329+
330+
<para>
331+
Additional considerations apply to the use of generated columns.
332+
<itemizedlist>
333+
<listitem>
334+
<para>
335+
Generated columns maintain access privileges separately from their
336+
underlying base columns. So, it is possible to arrange it so that a
337+
particular role can read from a generated column but not from the
338+
underlying base columns.
339+
</para>
340+
</listitem>
341+
<listitem>
342+
<para>
343+
Generated columns are, conceptually, updated after
344+
<literal>BEFORE</literal> triggers have run. Therefore, changes made to
345+
base columns in a <literal>BEFORE</literal> trigger will be reflected in
346+
generated columns. But conversely, it is not allowed to access
347+
generated columns in <literal>BEFORE</literal> triggers.
348+
</para>
349+
</listitem>
350+
</itemizedlist>
351+
</para>
352+
</sect1>
353+
236354
<sect1 id="ddl-constraints">
237355
<title>Constraints</title>
238356

‎doc/src/sgml/information_schema.sgml

Lines changed: 64 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -952,6 +952,62 @@
952952
</table>
953953
</sect1>
954954

955+
<sect1 id="infoschema-column-column-usage">
956+
<title><literal>column_column_usage</literal></title>
957+
958+
<para>
959+
The view <literal>column_column_usage</literal> identifies all generated
960+
columns that depend on another base column in the same table. Only tables
961+
owned by a currently enabled role are included.
962+
</para>
963+
964+
<table>
965+
<title><literal>column_column_usage</literal> Columns</title>
966+
967+
<tgroup cols="3">
968+
<thead>
969+
<row>
970+
<entry>Name</entry>
971+
<entry>Data Type</entry>
972+
<entry>Description</entry>
973+
</row>
974+
</thead>
975+
976+
<tbody>
977+
<row>
978+
<entry><literal>table_catalog</literal></entry>
979+
<entry><type>sql_identifier</type></entry>
980+
<entry>Name of the database containing the table (always the current database)</entry>
981+
</row>
982+
983+
<row>
984+
<entry><literal>table_schema</literal></entry>
985+
<entry><type>sql_identifier</type></entry>
986+
<entry>Name of the schema containing the table</entry>
987+
</row>
988+
989+
<row>
990+
<entry><literal>table_name</literal></entry>
991+
<entry><type>sql_identifier</type></entry>
992+
<entry>Name of the table</entry>
993+
</row>
994+
995+
<row>
996+
<entry><literal>column_name</literal></entry>
997+
<entry><type>sql_identifier</type></entry>
998+
<entry>Name of the base column that a generated column depends on</entry>
999+
</row>
1000+
1001+
<row>
1002+
<entry><literal>dependent_column</literal></entry>
1003+
<entry><type>sql_identifier</type></entry>
1004+
<entry>Name of the generated column</entry>
1005+
</row>
1006+
</tbody>
1007+
</tgroup>
1008+
</table>
1009+
</sect1>
1010+
9551011
<sect1 id="infoschema-column-domain-usage">
9561012
<title><literal>column_domain_usage</literal></title>
9571013

@@ -1648,13 +1704,19 @@
16481704
<row>
16491705
<entry><literal>is_generated</literal></entry>
16501706
<entry><type>character_data</type></entry>
1651-
<entry>Applies to a feature not available in <productname>PostgreSQL</productname></entry>
1707+
<entry>
1708+
If the column is a generated column, then <literal>ALWAYS</literal>,
1709+
else <literal>NEVER</literal>.
1710+
</entry>
16521711
</row>
16531712

16541713
<row>
16551714
<entry><literal>generation_expression</literal></entry>
16561715
<entry><type>character_data</type></entry>
1657-
<entry>Applies to a feature not available in <productname>PostgreSQL</productname></entry>
1716+
<entry>
1717+
If the column is a generated column, then the generation expression,
1718+
else null.
1719+
</entry>
16581720
</row>
16591721

16601722
<row>

‎doc/src/sgml/protocol.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -6450,7 +6450,7 @@ Relation
64506450
</listitem>
64516451
</varlistentry>
64526452
</variablelist>
6453-
Next, the following message part appears for each column:
6453+
Next, the following message part appears for each column (except generated columns):
64546454
<variablelist>
64556455
<varlistentry>
64566456
<term>
@@ -6875,7 +6875,7 @@ TupleData
68756875
</listitem>
68766876
</varlistentry>
68776877
</variablelist>
6878-
Next, one of the following submessages appears for each column:
6878+
Next, one of the following submessages appears for each column (except generated columns):
68796879
<variablelist>
68806880
<varlistentry>
68816881
<term>

‎doc/src/sgml/ref/copy.sgml

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -103,7 +103,8 @@ COPY { <replaceable class="parameter">table_name</replaceable> [ ( <replaceable
103103
<listitem>
104104
<para>
105105
An optional list of columns to be copied. If no column list is
106-
specified, all columns of the table will be copied.
106+
specified, all columns of the table except generated columns will be
107+
copied.
107108
</para>
108109
</listitem>
109110
</varlistentry>

‎doc/src/sgml/ref/create_foreign_table.sgml

Lines changed: 26 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -42,7 +42,8 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
4242
{ NOT NULL |
4343
NULL |
4444
CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
45-
DEFAULT <replaceable>default_expr</replaceable> }
45+
DEFAULT <replaceable>default_expr</replaceable> |
46+
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED }
4647

4748
<phrase>and <replaceable class="parameter">table_constraint</replaceable> is:</phrase>
4849

@@ -258,6 +259,30 @@ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ]
258259
</listitem>
259260
</varlistentry>
260261

262+
<varlistentry>
263+
<term><literal>GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED</literal><indexterm><primary>generated column</primary></indexterm></term>
264+
<listitem>
265+
<para>
266+
This clause creates the column as a <firstterm>generated
267+
column</firstterm>. The column cannot be written to, and when read it
268+
will be computed from the specified expression.
269+
</para>
270+
271+
<para>
272+
The keyword <literal>STORED</literal> is required to signify that the
273+
column will be computed on write. (The computed value will be presented
274+
to the foreign-data wrapper for storage and must be returned on
275+
reading.)
276+
</para>
277+
278+
<para>
279+
The generation expression can refer to other columns in the table, but
280+
not other generated columns. Any functions and operators used must be
281+
immutable. References to other tables are not allowed.
282+
</para>
283+
</listitem>
284+
</varlistentry>
285+
261286
<varlistentry>
262287
<term><replaceable class="parameter">server_name</replaceable></term>
263288
<listitem>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp