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

Commit7e64dbc

Browse files
committed
Support assignment to subfields of composite columns in UPDATE and INSERT.
As a side effect, cause subscripts in INSERT targetlists to do somethingmore or less sensible; previously we evaluated such subscripts and theneffectively ignored them. Another side effect is that UPDATE-ing anelement or slice of an array value that is NULL now produces a non-nullresult, namely an array containing just the assigned-to positions.
1 parent3a0df65 commit7e64dbc

File tree

27 files changed

+1466
-572
lines changed

27 files changed

+1466
-572
lines changed

‎doc/src/sgml/ref/insert.sgml

Lines changed: 7 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.26 2003/11/29 19:51:39 pgsql Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/insert.sgml,v 1.27 2004/06/09 19:08:13 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -73,6 +73,9 @@ INSERT INTO <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable
7373
<listitem>
7474
<para>
7575
The name of a column in <replaceable class="PARAMETER">table</replaceable>.
76+
The column name can be qualified with a subfield name or array
77+
subscript, if needed. (Inserting into only some fields of a
78+
composite column leaves the other fields null.)
7679
</para>
7780
</listitem>
7881
</varlistentry>
@@ -184,13 +187,11 @@ INSERT INTO films SELECT * FROM tmp;
184187

185188
<programlisting>
186189
-- Create an empty 3x3 gameboard for noughts-and-crosses
187-
-- (all ofthese commands create the same board)
190+
-- (these commands create the same board)
188191
INSERT INTO tictactoe (game, board[1:3][1:3])
189-
VALUES (1,'{{"","",""},{},{"",""}}');
190-
INSERT INTO tictactoe (game, board[3][3])
191-
VALUES (2,'{}');
192+
VALUES (1,'{{"","",""},{"","",""},{"","",""}}');
192193
INSERT INTO tictactoe (game, board)
193-
VALUES (3,'{{,,},{,,},{,,}}');
194+
VALUES (2,'{{,,},{,,},{,,}}');
194195
</programlisting>
195196
</para>
196197
</refsect1>

‎doc/src/sgml/ref/update.sgml

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.28 2004/03/03 22:22:24 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/update.sgml,v 1.29 2004/06/09 19:08:13 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -77,7 +77,10 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> SET <replacea
7777
<term><replaceable class="PARAMETER">column</replaceable></term>
7878
<listitem>
7979
<para>
80-
The name of a column in <replaceable class="PARAMETER">table</replaceable>.
80+
The name of a column in <replaceable
81+
class="PARAMETER">table</replaceable>.
82+
The column name can be qualified with a subfield name or array
83+
subscript, if needed.
8184
</para>
8285
</listitem>
8386
</varlistentry>

‎doc/src/sgml/rowtypes.sgml

Lines changed: 65 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/rowtypes.sgml,v 2.1 2004/06/07 04:04:47 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/rowtypes.sgml,v 2.2 2004/06/09 19:08:14 tgl Exp $ -->
22

33
<sect1 id="rowtypes">
44
<title>Composite Types</title>
@@ -66,6 +66,27 @@ SELECT price_extension(item, 10) FROM on_hand;
6666
</programlisting>
6767

6868
</para>
69+
70+
<para>
71+
Whenever you create a table, a composite type is also automatically
72+
created, with the same name as the table, to represent the table's
73+
row type. For example, had we said
74+
<programlisting>
75+
CREATE TABLE inventory_item (
76+
name text,
77+
supplier_id integer REFERENCES suppliers,
78+
price numeric CHECK (price > 0)
79+
);
80+
</programlisting>
81+
then the same <literal>inventory_item</> composite type shown above would
82+
come into being as a
83+
byproduct, and could be used just as above. Note however an important
84+
restriction of the current implementation: since no constraints are
85+
associated with a composite type, the constraints shown in the table
86+
definition <emphasis>do not apply</> to values of the composite type
87+
outside the table. (A partial workaround is to use domain
88+
types as members of composite types.)
89+
</para>
6990
</sect2>
7091

7192
<sect2>
@@ -178,6 +199,49 @@ SELECT (my_func(...)).field FROM ...
178199
</para>
179200
</sect2>
180201

202+
<sect2>
203+
<title>Modifying Composite Types</title>
204+
205+
<para>
206+
Here are some examples of the proper syntax for inserting and updating
207+
composite columns.
208+
First, inserting or updating a whole column:
209+
210+
<programlisting>
211+
INSERT INTO mytab (complex_col) VALUES((1.1,2.2));
212+
213+
UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;
214+
</programlisting>
215+
216+
The first example omits <literal>ROW</>, the second uses it; we
217+
could have done it either way.
218+
</para>
219+
220+
<para>
221+
We can update an individual subfield of a composite column:
222+
223+
<programlisting>
224+
UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;
225+
</programlisting>
226+
227+
Notice here that we don't need to (and indeed cannot)
228+
put parentheses around the column name appearing just after
229+
<literal>SET</>, but we do need parentheses when referencing the same
230+
column in the expression to the right of the equal sign.
231+
</para>
232+
233+
<para>
234+
And we can specify subfields as targets for <command>INSERT</>, too:
235+
236+
<programlisting>
237+
INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);
238+
</programlisting>
239+
240+
Had we not supplied values for all the subfields of the column, the
241+
remaining subfields would have been filled with NULLs.
242+
</para>
243+
</sect2>
244+
181245
<sect2>
182246
<title>Composite Type Input and Output Syntax</title>
183247

0 commit comments

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