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

Commit9ab42f3

Browse files
committed
Merge branch 'REL9_5_STABLE' into PGPRO9_5
Merge pre-release of 9.5.1
2 parentsc5ba352 +65f1510 commit9ab42f3

File tree

34 files changed

+3511
-151
lines changed

34 files changed

+3511
-151
lines changed

‎contrib/hstore/expected/hstore.out

Lines changed: 8 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1466,10 +1466,10 @@ select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=
14661466
{"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"}
14671467
(1 row)
14681468

1469-
select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
1470-
hstore_to_json_loose
1471-
------------------------------------------------------------------------------------------
1472-
{"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4, "a key": 1}
1469+
select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4, h=> "2016-01-01"');
1470+
hstore_to_json_loose
1471+
-------------------------------------------------------------------------------------------------------------
1472+
{"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4, "h": "2016-01-01", "a key": 1}
14731473
(1 row)
14741474

14751475
select hstore_to_jsonb('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
@@ -1484,10 +1484,10 @@ select cast( hstore '"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=
14841484
{"b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4", "a key": "1"}
14851485
(1 row)
14861486

1487-
select hstore_to_jsonb_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
1488-
hstore_to_jsonb_loose
1489-
---------------------------------------------------------------------------------------
1490-
{"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 23450, "a key": 1}
1487+
select hstore_to_jsonb_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4, h=> "2016-01-01"');
1488+
hstore_to_jsonb_loose
1489+
----------------------------------------------------------------------------------------------------------
1490+
{"b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 23450, "h": "2016-01-01", "a key": 1}
14911491
(1 row)
14921492

14931493
create table test_json_agg (f1 text, f2 hstore);

‎contrib/hstore/sql/hstore.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -334,11 +334,11 @@ select count(*) from testhstore where h = 'pos=>98, line=>371, node=>CBA, indexe
334334
-- json and jsonb
335335
select hstore_to_json('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
336336
select cast( hstore'"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'as json);
337-
select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
337+
select hstore_to_json_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4, h=> "2016-01-01"');
338338

339339
select hstore_to_jsonb('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
340340
select cast( hstore'"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'as jsonb);
341-
select hstore_to_jsonb_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4');
341+
select hstore_to_jsonb_loose('"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4, h=> "2016-01-01"');
342342

343343
createtabletest_json_agg (f1text, f2 hstore);
344344
insert into test_json_aggvalues ('rec1','"a key" =>1, b => t, c => null, d=> 12345, e => 012345, f=> 1.234, g=> 2.345e+4'),

‎contrib/postgres_fdw/deparse.c

Lines changed: 11 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -111,6 +111,7 @@ static void deparseTargetList(StringInfo buf,
111111
PlannerInfo*root,
112112
Indexrtindex,
113113
Relationrel,
114+
boolis_returning,
114115
Bitmapset*attrs_used,
115116
List**retrieved_attrs);
116117
staticvoiddeparseReturningList(StringInfobuf,PlannerInfo*root,
@@ -721,7 +722,7 @@ deparseSelectSql(StringInfo buf,
721722
* Construct SELECT list
722723
*/
723724
appendStringInfoString(buf,"SELECT ");
724-
deparseTargetList(buf,root,baserel->relid,rel,attrs_used,
725+
deparseTargetList(buf,root,baserel->relid,rel,false,attrs_used,
725726
retrieved_attrs);
726727

727728
/*
@@ -735,7 +736,8 @@ deparseSelectSql(StringInfo buf,
735736

736737
/*
737738
* Emit a target list that retrieves the columns specified in attrs_used.
738-
* This is used for both SELECT and RETURNING targetlists.
739+
* This is used for both SELECT and RETURNING targetlists; the is_returning
740+
* parameter is true only for a RETURNING targetlist.
739741
*
740742
* The tlist text is appended to buf, and we also create an integer List
741743
* of the columns being retrieved, which is returned to *retrieved_attrs.
@@ -745,6 +747,7 @@ deparseTargetList(StringInfo buf,
745747
PlannerInfo*root,
746748
Indexrtindex,
747749
Relationrel,
750+
boolis_returning,
748751
Bitmapset*attrs_used,
749752
List**retrieved_attrs)
750753
{
@@ -774,6 +777,8 @@ deparseTargetList(StringInfo buf,
774777
{
775778
if (!first)
776779
appendStringInfoString(buf,", ");
780+
elseif (is_returning)
781+
appendStringInfoString(buf," RETURNING ");
777782
first= false;
778783

779784
deparseColumnRef(buf,rtindex,i,root);
@@ -791,6 +796,8 @@ deparseTargetList(StringInfo buf,
791796
{
792797
if (!first)
793798
appendStringInfoString(buf,", ");
799+
elseif (is_returning)
800+
appendStringInfoString(buf," RETURNING ");
794801
first= false;
795802

796803
appendStringInfoString(buf,"ctid");
@@ -800,7 +807,7 @@ deparseTargetList(StringInfo buf,
800807
}
801808

802809
/* Don't generate bad syntax if no undropped columns */
803-
if (first)
810+
if (first&& !is_returning)
804811
appendStringInfoString(buf,"NULL");
805812
}
806813

@@ -1019,11 +1026,8 @@ deparseReturningList(StringInfo buf, PlannerInfo *root,
10191026
}
10201027

10211028
if (attrs_used!=NULL)
1022-
{
1023-
appendStringInfoString(buf," RETURNING ");
1024-
deparseTargetList(buf,root,rtindex,rel,attrs_used,
1029+
deparseTargetList(buf,root,rtindex,rel, true,attrs_used,
10251030
retrieved_attrs);
1026-
}
10271031
else
10281032
*retrieved_attrs=NIL;
10291033
}

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2232,6 +2232,59 @@ SELECT c1,c2,c3,c4 FROM ft2 ORDER BY c1;
22322232
1104 | 204 | ddd |
22332233
(819 rows)
22342234

2235+
EXPLAIN (verbose, costs off)
2236+
INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass;
2237+
QUERY PLAN
2238+
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2239+
Insert on public.ft2
2240+
Output: (tableoid)::regclass
2241+
Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
2242+
-> Result
2243+
Output: 9999, 999, NULL::integer, 'foo'::text, NULL::timestamp with time zone, NULL::timestamp without time zone, NULL::character varying, 'ft2 '::character(10), NULL::user_enum
2244+
(5 rows)
2245+
2246+
INSERT INTO ft2 (c1,c2,c3) VALUES (9999,999,'foo') RETURNING tableoid::regclass;
2247+
tableoid
2248+
----------
2249+
ft2
2250+
(1 row)
2251+
2252+
EXPLAIN (verbose, costs off)
2253+
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass;
2254+
QUERY PLAN
2255+
-------------------------------------------------------------------------------------------------------------------
2256+
Update on public.ft2
2257+
Output: (tableoid)::regclass
2258+
Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1
2259+
-> Foreign Scan on public.ft2
2260+
Output: c1, c2, NULL::integer, 'bar'::text, c4, c5, c6, c7, c8, ctid
2261+
Remote SQL: SELECT "C 1", c2, c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" = 9999)) FOR UPDATE
2262+
(6 rows)
2263+
2264+
UPDATE ft2 SET c3 = 'bar' WHERE c1 = 9999 RETURNING tableoid::regclass;
2265+
tableoid
2266+
----------
2267+
ft2
2268+
(1 row)
2269+
2270+
EXPLAIN (verbose, costs off)
2271+
DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
2272+
QUERY PLAN
2273+
------------------------------------------------------------------------------------
2274+
Delete on public.ft2
2275+
Output: (tableoid)::regclass
2276+
Remote SQL: DELETE FROM "S 1"."T 1" WHERE ctid = $1
2277+
-> Foreign Scan on public.ft2
2278+
Output: ctid
2279+
Remote SQL: SELECT ctid FROM "S 1"."T 1" WHERE (("C 1" = 9999)) FOR UPDATE
2280+
(6 rows)
2281+
2282+
DELETE FROM ft2 WHERE c1 = 9999 RETURNING tableoid::regclass;
2283+
tableoid
2284+
----------
2285+
ft2
2286+
(1 row)
2287+
22352288
-- Test that trigger on remote table works as expected
22362289
CREATE OR REPLACE FUNCTION "S 1".F_BRTRIG() RETURNS trigger AS $$
22372290
BEGIN

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -358,6 +358,15 @@ EXPLAIN (verbose, costs off)
358358
DELETEFROM ft2 USING ft1WHEREft1.c1=ft2.c2ANDft1.c1 %10=2;
359359
DELETEFROM ft2 USING ft1WHEREft1.c1=ft2.c2ANDft1.c1 %10=2;
360360
SELECT c1,c2,c3,c4FROM ft2ORDER BY c1;
361+
EXPLAIN (verbose, costs off)
362+
INSERT INTO ft2 (c1,c2,c3)VALUES (9999,999,'foo') RETURNING tableoid::regclass;
363+
INSERT INTO ft2 (c1,c2,c3)VALUES (9999,999,'foo') RETURNING tableoid::regclass;
364+
EXPLAIN (verbose, costs off)
365+
UPDATE ft2SET c3='bar'WHERE c1=9999 RETURNING tableoid::regclass;
366+
UPDATE ft2SET c3='bar'WHERE c1=9999 RETURNING tableoid::regclass;
367+
EXPLAIN (verbose, costs off)
368+
DELETEFROM ft2WHERE c1=9999 RETURNING tableoid::regclass;
369+
DELETEFROM ft2WHERE c1=9999 RETURNING tableoid::regclass;
361370

362371
-- Test that trigger on remote table works as expected
363372
CREATEOR REPLACE FUNCTION"S 1".F_BRTRIG() RETURNS triggerAS $$

‎doc/src/sgml/ddl.sgml

Lines changed: 29 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -495,8 +495,8 @@ CREATE TABLE products (
495495
</indexterm>
496496

497497
<para>
498-
Unique constraints ensure that the data contained in a column or a
499-
group of columns is uniquewith respect to all the rows in the
498+
Unique constraints ensure that the data contained in a column, or a
499+
group of columns, is uniqueamong all the rows in the
500500
table. The syntax is:
501501
<programlisting>
502502
CREATE TABLE products (
@@ -518,8 +518,8 @@ CREATE TABLE products (
518518
</para>
519519

520520
<para>
521-
Ifa unique constraintrefers toa group of columns,the columns
522-
are listed separated by commas:
521+
To definea unique constraintfora group of columns,write it as a
522+
table constraint with the column names separated by commas:
523523
<programlisting>
524524
CREATE TABLE example (
525525
a integer,
@@ -546,9 +546,10 @@ CREATE TABLE products (
546546

547547
<para>
548548
Adding a unique constraint will automatically create a unique B-tree
549-
index on the column or group of columns used in the constraint.
550-
A uniqueness constraint on only some rows can be enforced by creating
551-
a <link linkend="indexes-partial">partial index</link>.
549+
index on the column or group of columns listed in the constraint.
550+
A uniqueness restriction covering only some rows cannot be written as
551+
a unique constraint, but it is possible to enforce such a restriction by
552+
creating a unique <link linkend="indexes-partial">partial index</link>.
552553
</para>
553554

554555
<indexterm>
@@ -557,10 +558,10 @@ CREATE TABLE products (
557558
</indexterm>
558559

559560
<para>
560-
In general, a unique constraint is violatedwhen there is more than
561+
In general, a unique constraint is violatedif there is more than
561562
one row in the table where the values of all of the
562563
columns included in the constraint are equal.
563-
However, two null values arenot considered equal in this
564+
However, two null values arenever considered equal in this
564565
comparison. That means even in the presence of a
565566
unique constraint it is possible to store duplicate
566567
rows that contain a null value in at least one of the constrained
@@ -584,8 +585,9 @@ CREATE TABLE products (
584585
</indexterm>
585586

586587
<para>
587-
Technically, a primary key constraint is simply a combination of a
588-
unique constraint and a not-null constraint. So, the following
588+
A primary key constraint indicates that a column, or group of columns,
589+
can be used as a unique identifier for rows in the table. This
590+
requires that the values be both unique and not null. So, the following
589591
two table definitions accept the same data:
590592
<programlisting>
591593
CREATE TABLE products (
@@ -605,7 +607,7 @@ CREATE TABLE products (
605607
</para>
606608

607609
<para>
608-
Primary keys canalso constrain more than one column; the syntax
610+
Primary keys canspan more than one column; the syntax
609611
is similar to unique constraints:
610612
<programlisting>
611613
CREATE TABLE example (
@@ -617,32 +619,32 @@ CREATE TABLE example (
617619
</programlisting>
618620
</para>
619621

620-
<para>
621-
A primary key indicates that a column or group of columns can be
622-
used as a unique identifier for rows in the table. (This is a
623-
direct consequence of the definition of a primary key. Note that
624-
a unique constraint does not, by itself, provide a unique identifier
625-
because it does not exclude null values.) This is useful both for
626-
documentation purposes and for client applications. For example,
627-
a GUI application that allows modifying row values probably needs
628-
to know the primary key of a table to be able to identify rows
629-
uniquely.
630-
</para>
631-
632622
<para>
633623
Adding a primary key will automatically create a unique B-tree index
634-
on the column or group of columns used in the primary key.
624+
on the column or group of columns listed in the primary key, and will
625+
force the column(s) to be marked <literal>NOT NULL</>.
635626
</para>
636627

637628
<para>
638629
A table can have at most one primary key. (There can be any number
639-
of unique and not-null constraints, which are functionallythe same
640-
thing, but only one can be identified as the primary key.)
630+
of unique and not-null constraints, which are functionallyalmost the
631+
samething, but only one can be identified as the primary key.)
641632
Relational database theory
642633
dictates that every table must have a primary key. This rule is
643634
not enforced by <productname>&productname;</productname>, but it is
644635
usually best to follow it.
645636
</para>
637+
638+
<para>
639+
Primary keys are useful both for
640+
documentation purposes and for client applications. For example,
641+
a GUI application that allows modifying row values probably needs
642+
to know the primary key of a table to be able to identify rows
643+
uniquely. There are also various ways in which the database system
644+
makes use of a primary key if one has been declared; for example,
645+
the primary key defines the default target column(s) for foreign keys
646+
referencing its table.
647+
</para>
646648
</sect2>
647649

648650
<sect2 id="ddl-constraints-fk">

‎doc/src/sgml/hstore.sgml

Lines changed: 23 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -325,11 +325,21 @@ b
325325
<row>
326326
<entry><function>hstore_to_json(hstore)</function><indexterm><primary>hstore_to_json</primary></indexterm></entry>
327327
<entry><type>json</type></entry>
328-
<entry>get <type>hstore</type> as a <type>json</type> value</entry>
328+
<entry>get <type>hstore</type> as a <type>json</type> value, converting
329+
all non-null values to JSON strings</entry>
329330
<entry><literal>hstore_to_json('"a key"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;2.345e+4')</literal></entry>
330331
<entry><literal>{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</literal></entry>
331332
</row>
332333

334+
<row>
335+
<entry><function>hstore_to_jsonb(hstore)</function><indexterm><primary>hstore_to_jsonb</primary></indexterm></entry>
336+
<entry><type>jsonb</type></entry>
337+
<entry>get <type>hstore</type> as a <type>jsonb</type> value, converting
338+
all non-null values to JSON strings</entry>
339+
<entry><literal>hstore_to_jsonb('"a key"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;2.345e+4')</literal></entry>
340+
<entry><literal>{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</literal></entry>
341+
</row>
342+
333343
<row>
334344
<entry><function>hstore_to_json_loose(hstore)</function><indexterm><primary>hstore_to_json_loose</primary></indexterm></entry>
335345
<entry><type>json</type></entry>
@@ -338,6 +348,14 @@ b
338348
<entry><literal>{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</literal></entry>
339349
</row>
340350

351+
<row>
352+
<entry><function>hstore_to_jsonb_loose(hstore)</function><indexterm><primary>hstore_to_jsonb_loose</primary></indexterm></entry>
353+
<entry><type>jsonb</type></entry>
354+
<entry>get <type>hstore</type> as a <type>jsonb</type> value, but attempt to distinguish numerical and Boolean values so they are unquoted in the JSON</entry>
355+
<entry><literal>hstore_to_jsonb_loose('"a key"=&gt;1, b=&gt;t, c=&gt;null, d=&gt;12345, e=&gt;012345, f=&gt;1.234, g=&gt;2.345e+4')</literal></entry>
356+
<entry><literal>{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</literal></entry>
357+
</row>
358+
341359
<row>
342360
<entry><function>slice(hstore, text[])</function><indexterm><primary>slice</primary></indexterm></entry>
343361
<entry><type>hstore</type></entry>
@@ -414,8 +432,10 @@ b
414432

415433
<note>
416434
<para>
417-
The function <function>hstore_to_json</function> is used when an <type>hstore</type>
418-
value is cast to <type>json</type>.
435+
The function <function>hstore_to_json</function> is used when
436+
an <type>hstore</type> value is cast to <type>json</type>.
437+
Likewise, <function>hstore_to_jsonb</function> is used when
438+
an <type>hstore</type> value is cast to <type>jsonb</type>.
419439
</para>
420440
</note>
421441

0 commit comments

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