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

Commitcecb607

Browse files
committed
Make SQL arrays support null elements. This commit fixes the core array
functionality, but I still need to make another pass looking at placesthat incidentally use arrays (such as ACL manipulation) to make sure theyare null-safe. Contrib needs work too.I have not changed the behaviors that are still under discussion aboutarray comparison and what to do with lower bounds.
1 parentc859308 commitcecb607

File tree

35 files changed

+2145
-946
lines changed

35 files changed

+2145
-946
lines changed

‎contrib/intagg/int_aggregate.c‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -87,7 +87,7 @@ GetPGArray(PGARRAY * p, AggState *aggstate, bool fAdd)
8787
p= (PGARRAY*)MemoryContextAlloc(aggstate->aggcontext,cb);
8888
p->a.size=cb;
8989
p->a.ndim=1;
90-
p->a.flags=0;
90+
p->a.dataoffset=0;/* we don't support nulls, for now */
9191
p->a.elemtype=INT4OID;
9292
p->items=0;
9393
p->lower=START_NUM;

‎contrib/intarray/_int_tool.c‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -208,12 +208,13 @@ ArrayType *
208208
new_intArrayType(intnum)
209209
{
210210
ArrayType*r;
211-
intnbytes=ARR_OVERHEAD(NDIM)+sizeof(int)*num;
211+
intnbytes=ARR_OVERHEAD_NONULLS(NDIM)+sizeof(int)*num;
212212

213213
r= (ArrayType*)palloc0(nbytes);
214214

215215
ARR_SIZE(r)=nbytes;
216216
ARR_NDIM(r)=NDIM;
217+
r->dataoffset=0;/* marker for no null bitmap */
217218
ARR_ELEMTYPE(r)=INT4OID;
218219
*((int*)ARR_DIMS(r))=num;
219220
*((int*)ARR_LBOUND(r))=1;
@@ -224,7 +225,7 @@ new_intArrayType(int num)
224225
ArrayType*
225226
resize_intArrayType(ArrayType*a,intnum)
226227
{
227-
intnbytes=ARR_OVERHEAD(NDIM)+sizeof(int)*num;
228+
intnbytes=ARR_OVERHEAD_NONULLS(NDIM)+sizeof(int)*num;
228229

229230
if (num==ARRNELEMS(a))
230231
returna;

‎contrib/tsearch2/query_rewrite.c‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -232,7 +232,7 @@ rewrite_accum(PG_FUNCTION_ARGS) {
232232
if (ARR_ELEMTYPE(qa)!=tsqOid)
233233
elog(ERROR,"array should contain tsquery type");
234234

235-
deconstruct_array(qa,tsqOid,-1, false,'i',&elemsp,&nelemsp);
235+
deconstruct_array(qa,tsqOid,-1, false,'i',&elemsp,NULL,&nelemsp);
236236

237237
q= (QUERYTYPE*)DatumGetPointer(elemsp[0] );
238238
if (q->size==0 ) {

‎doc/src/sgml/array.sgml‎

Lines changed: 58 additions & 52 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.46 2005/11/04 23:13:59 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/array.sgml,v 1.47 2005/11/17 22:14:50 tgl Exp $ -->
22

33
<sect1 id="arrays">
44
<title>Arrays</title>
@@ -110,6 +110,13 @@ CREATE TABLE tictactoe (
110110
three subarrays of integers.
111111
</para>
112112

113+
<para>
114+
To set an element of an array constant to NULL, write <literal>NULL</>
115+
for the element value. (Any upper- or lower-case variant of
116+
<literal>NULL</> will do.) If you want an actual string value
117+
<quote>NULL</>, you must put double quotes around it.
118+
</para>
119+
113120
<para>
114121
(These kinds of array constants are actually only a special case of
115122
the generic type constants discussed in <xref
@@ -121,17 +128,6 @@ CREATE TABLE tictactoe (
121128
<para>
122129
Now we can show some <command>INSERT</command> statements.
123130

124-
<programlisting>
125-
INSERT INTO sal_emp
126-
VALUES ('Bill',
127-
'{10000, 10000, 10000, 10000}',
128-
'{{"meeting", "lunch"}, {"meeting"}}');
129-
ERROR: multidimensional arrays must have array expressions with matching dimensions
130-
</programlisting>
131-
132-
Note that multidimensional arrays must have matching extents for each
133-
dimension. A mismatch causes an error report.
134-
135131
<programlisting>
136132
INSERT INTO sal_emp
137133
VALUES ('Bill',
@@ -145,15 +141,9 @@ INSERT INTO sal_emp
145141
</programlisting>
146142
</para>
147143

148-
<para>
149-
A limitation of the present array implementation is that individual
150-
elements of an array cannot be SQL null values. The entire array
151-
can be set to null, but you can't have an array with some elements
152-
null and some not. (This is likely to change in the future.)
153-
</para>
154-
155144
<para>
156145
The result of the previous two inserts looks like this:
146+
157147
<programlisting>
158148
SELECT * FROM sal_emp;
159149
name | pay_by_quarter | schedule
@@ -183,6 +173,19 @@ INSERT INTO sal_emp
183173
constructor syntax is discussed in more detail in
184174
<xref linkend="sql-syntax-array-constructors">.
185175
</para>
176+
177+
<para>
178+
Multidimensional arrays must have matching extents for each
179+
dimension. A mismatch causes an error report, for example:
180+
181+
<programlisting>
182+
INSERT INTO sal_emp
183+
VALUES ('Bill',
184+
'{10000, 10000, 10000, 10000}',
185+
'{{"meeting", "lunch"}, {"meeting"}}');
186+
ERROR: multidimensional arrays must have array expressions with matching dimensions
187+
</programlisting>
188+
</para>
186189
</sect2>
187190

188191
<sect2>
@@ -262,14 +265,22 @@ SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';
262265
</para>
263266

264267
<para>
265-
Fetching from outside the current bounds of an array yields a
266-
SQL null value, not an error. For example, if <literal>schedule</>
268+
An array subscript expression will return null if either the array itself or
269+
any of the subscript expressions are null. Also, null is returned if a
270+
subscript is outside the array bounds (this case does not raise an error).
271+
For example, if <literal>schedule</>
267272
currently has the dimensions <literal>[1:3][1:2]</> then referencing
268273
<literal>schedule[3][3]</> yields NULL. Similarly, an array reference
269274
with the wrong number of subscripts yields a null rather than an error.
270-
Fetching an array slice that
271-
is completely outside the current bounds likewise yields a null array;
272-
but if the requested slice partially overlaps the array bounds, then it
275+
</para>
276+
277+
<para>
278+
An array slice expression likewise yields null if the array itself or
279+
any of the subscript expressions are null. However, in other corner
280+
cases such as selecting an array slice that
281+
is completely outside the current array bounds, a slice expression
282+
yields an empty (zero-dimensional) array instead of null.
283+
If the requested slice partially overlaps the array bounds, then it
273284
is silently reduced to just the overlapping region.
274285
</para>
275286

@@ -349,7 +360,7 @@ UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
349360
</para>
350361

351362
<para>
352-
Array slice assignment allows creation of arrays that do not use one-based
363+
Subscripted assignment allows creation of arrays that do not use one-based
353364
subscripts. For example one might assign to <literal>myarray[-2:7]</> to
354365
create an array with subscript values running from -2 to 7.
355366
</para>
@@ -442,7 +453,7 @@ SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
442453
arrays, but <function>array_cat</function> supports multidimensional arrays.
443454

444455
Note that the concatenation operator discussed above is preferred over
445-
direct use of these functions. In fact, the functionsare primarily for use
456+
direct use of these functions. In fact, the functionsexist primarily for use
446457
in implementing the concatenation operator. However, they may be directly
447458
useful in the creation of user-defined aggregates. Some examples:
448459

@@ -544,8 +555,9 @@ SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
544555

545556
<para>
546557
The array output routine will put double quotes around element values
547-
if they are empty strings or contain curly braces, delimiter characters,
548-
double quotes, backslashes, or white space. Double quotes and backslashes
558+
if they are empty strings, contain curly braces, delimiter characters,
559+
double quotes, backslashes, or white space, or match the word
560+
<literal>NULL</>. Double quotes and backslashes
549561
embedded in element values will be backslash-escaped. For numeric
550562
data types it is safe to assume that double quotes will never appear, but
551563
for textual data types one should be prepared to cope with either presence
@@ -555,35 +567,15 @@ SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);
555567

556568
<para>
557569
By default, the lower bound index value of an array's dimensions is
558-
set to one.If any of an array's dimensions has alowerbound index not
559-
equal to one, an additional decoration that indicates the actual
560-
arraydimensions will precede the array structure decoration.
570+
set to one. To represent arrays with otherlowerbounds, the array
571+
subscript ranges can be specified explicitly before writing the
572+
arraycontents.
561573
This decoration consists of square brackets (<literal>[]</>)
562574
around each array dimension's lower and upper bounds, with
563575
a colon (<literal>:</>) delimiter character in between. The
564576
array dimension decoration is followed by an equal sign (<literal>=</>).
565577
For example:
566578
<programlisting>
567-
SELECT 1 || ARRAY[2,3] AS array;
568-
569-
array
570-
---------------
571-
[0:2]={1,2,3}
572-
(1 row)
573-
574-
SELECT ARRAY[1,2] || ARRAY[[3,4]] AS array;
575-
576-
array
577-
--------------------------
578-
[0:1][1:2]={{1,2},{3,4}}
579-
(1 row)
580-
</programlisting>
581-
</para>
582-
583-
<para>
584-
This syntax can also be used to specify non-default array subscripts
585-
in an array literal. For example:
586-
<programlisting>
587579
SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
588580
FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;
589581

@@ -592,6 +584,18 @@ SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
592584
1 | 6
593585
(1 row)
594586
</programlisting>
587+
The array output routine will include explicit dimensions in its result
588+
only when there are one or more lower bounds different from one.
589+
</para>
590+
591+
<para>
592+
If the value written for an element is <literal>NULL</> (in any case
593+
variant), the element is taken to be NULL. The presence of any quotes
594+
or backslashes disables this and allows the literal string value
595+
<quote>NULL</> to be entered. Also, for backwards compatibility with
596+
pre-8.2 versions of <productname>PostgreSQL</>, the <xref
597+
linkend="guc-array-nulls"> configuration parameter may be turned
598+
<literal>off</> to suppress recognition of <literal>NULL</> as a NULL.
595599
</para>
596600

597601
<para>
@@ -600,7 +604,9 @@ SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
600604
if the element value would otherwise confuse the array-value parser.
601605
For example, elements containing curly braces, commas (or whatever the
602606
delimiter character is), double quotes, backslashes, or leading or trailing
603-
whitespace must be double-quoted. To put a double quote or backslash in a
607+
whitespace must be double-quoted. Empty strings and strings matching the
608+
word <literal>NULL</> must be quoted, too. To put a double quote or
609+
backslash in a
604610
quoted array element value, precede it with a backslash. Alternatively, you
605611
can use backslash-escaping to protect all data characters that would
606612
otherwise be taken as array syntax.

‎doc/src/sgml/config.sgml‎

Lines changed: 55 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.36 2005/11/04 23:53:18 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.37 2005/11/17 22:14:50 tgl Exp $
33
-->
44
<chapter Id="runtime-config">
55
<title>Server Configuration</title>
@@ -3614,6 +3614,7 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
36143614

36153615
<sect2 id="runtime-config-compatible-version">
36163616
<title>Previous PostgreSQL Versions</title>
3617+
36173618
<variablelist>
36183619

36193620
<varlistentry id="guc-add-missing-from" xreflabel="add_missing_from">
@@ -3647,40 +3648,27 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
36473648
</listitem>
36483649
</varlistentry>
36493650

3650-
<varlistentry id="guc-regex-flavor" xreflabel="regex_flavor">
3651-
<term><varname>regex_flavor</varname> (<type>string</type>)</term>
3652-
<indexterm><primary>regular expressions</></>
3651+
<varlistentry id="guc-array-nulls" xreflabel="array_nulls">
3652+
<term><varname>array_nulls</varname> (<type>boolean</type>)</term>
36533653
<indexterm>
3654-
<primary><varname>regex_flavor</> configuration parameter</primary>
3654+
<primary><varname>array_nulls</> configuration parameter</primary>
36553655
</indexterm>
36563656
<listitem>
36573657
<para>
3658-
The regular expression <quote>flavor</> can be set to
3659-
<literal>advanced</>, <literal>extended</>, or <literal>basic</>.
3660-
The default is <literal>advanced</>. The <literal>extended</>
3661-
setting may be useful for exact backwards compatibility with
3662-
pre-7.4 releases of <productname>PostgreSQL</>. See
3663-
<xref linkend="posix-syntax-details"> for details.
3658+
This controls whether the array input parser recognizes
3659+
unquoted <literal>NULL</> as specifying a NULL array element.
3660+
By default, this is <literal>on</>, allowing array values containing
3661+
NULLs to be entered. However, <productname>PostgreSQL</> versions
3662+
before 8.2 did not support NULLs in arrays, and therefore would
3663+
treat <literal>NULL</> as specifying a normal array element with
3664+
the string value <quote>NULL</>. For backwards compatibility with
3665+
applications that require the old behavior, this variable can be
3666+
turned <literal>off</>.
36643667
</para>
3665-
</listitem>
3666-
</varlistentry>
36673668

3668-
<varlistentry id="guc-sql-inheritance" xreflabel="sql_inheritance">
3669-
<term><varname>sql_inheritance</varname> (<type>boolean</type>)</term>
3670-
<indexterm>
3671-
<primary><varname>sql_inheritance</> configuration parameter</primary>
3672-
</indexterm>
3673-
<indexterm><primary>inheritance</></>
3674-
<listitem>
36753669
<para>
3676-
This controls the inheritance semantics, in particular whether
3677-
subtables are included by various commands by default. They were
3678-
not included in versions prior to 7.1. If you need the old
3679-
behavior you can set this variable to <literal>off</>, but in
3680-
the long run you are encouraged to change your applications to
3681-
use the <literal>ONLY</literal> key word to exclude subtables.
3682-
See <xref linkend="ddl-inherit"> for more information about
3683-
inheritance.
3670+
Note that it is possible to create array values containing NULLs
3671+
even when this variable is <literal>off</>.
36843672
</para>
36853673
</listitem>
36863674
</varlistentry>
@@ -3736,8 +3724,47 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir'
37363724
</listitem>
37373725
</varlistentry>
37383726

3727+
<varlistentry id="guc-regex-flavor" xreflabel="regex_flavor">
3728+
<term><varname>regex_flavor</varname> (<type>string</type>)</term>
3729+
<indexterm><primary>regular expressions</></>
3730+
<indexterm>
3731+
<primary><varname>regex_flavor</> configuration parameter</primary>
3732+
</indexterm>
3733+
<listitem>
3734+
<para>
3735+
The regular expression <quote>flavor</> can be set to
3736+
<literal>advanced</>, <literal>extended</>, or <literal>basic</>.
3737+
The default is <literal>advanced</>. The <literal>extended</>
3738+
setting may be useful for exact backwards compatibility with
3739+
pre-7.4 releases of <productname>PostgreSQL</>. See
3740+
<xref linkend="posix-syntax-details"> for details.
3741+
</para>
3742+
</listitem>
3743+
</varlistentry>
3744+
3745+
<varlistentry id="guc-sql-inheritance" xreflabel="sql_inheritance">
3746+
<term><varname>sql_inheritance</varname> (<type>boolean</type>)</term>
3747+
<indexterm>
3748+
<primary><varname>sql_inheritance</> configuration parameter</primary>
3749+
</indexterm>
3750+
<indexterm><primary>inheritance</></>
3751+
<listitem>
3752+
<para>
3753+
This controls the inheritance semantics, in particular whether
3754+
subtables are included by various commands by default. They were
3755+
not included in versions prior to 7.1. If you need the old
3756+
behavior you can set this variable to <literal>off</>, but in
3757+
the long run you are encouraged to change your applications to
3758+
use the <literal>ONLY</literal> key word to exclude subtables.
3759+
See <xref linkend="ddl-inherit"> for more information about
3760+
inheritance.
3761+
</para>
3762+
</listitem>
3763+
</varlistentry>
3764+
37393765
</variablelist>
37403766
</sect2>
3767+
37413768
<sect2 id="runtime-config-compatible-clients">
37423769
<title>Platform and Client Compatibility</title>
37433770
<variablelist>

0 commit comments

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