~aglenyoung/+junk/postgres-9.3-dtrace : revision 5

4048

Some examples:

4049

4050

4051

SELECT foo FROM regexp_split_to_table('the quick brown fox jumped over the lazy dog', E'\\s+') AS foo;

4051

SELECT foo FROM regexp_split_to_table('the quick brown fox jumps over the lazy dog', E'\\s+') AS foo;

4052

foo

4053

--------

4053

-------

4054

the

4055

quick

4056

brown

4057

fox

4058

jumped

4058

jumps

4059

over

4060

the

4061

lazy

4062

dog

4063

(9 rows)

4064

4065

SELECT regexp_split_to_array('the quick brown fox jumped over the lazy dog', E'\\s+');

4065

SELECT regexp_split_to_array('the quick brown fox jumps over the lazy dog', E'\\s+');

4066

regexp_split_to_array

4067

------------------------------------------------

4068

{the,quick,brown,fox,jumped,over,the,lazy,dog}

4067

-----------------------------------------------

4068

{the,quick,brown,fox,jumps,over,the,lazy,dog}

4069

(1 row)

4070

4071

SELECT foo FROM regexp_split_to_table('the quick brown fox', E'\\s*') AS foo;

9928

</indexterm>

9929

<literal>array_to_json(anyarray [, pretty_bool])</literal>

9930

</entry>

9931

9931

9932

<entry>

9933

Returns the array as JSON. A PostgreSQL multidimensional array

9934

becomes a JSON array of arrays. Line feeds will be added between

9944

</indexterm>

9945

<literal>row_to_json(record [, pretty_bool])</literal>

9946

</entry>

9947

9947

9948

<entry>

9949

Returns the row as JSON. Line feeds will be added between level

9950

1 elements if <parameter>pretty_bool</parameter> is true.

9959

</indexterm>

9960

<literal>to_json(anyelement)</literal>

9961

</entry>

9962

9962

9963

<entry>

9964

Returns the value as JSON. If the data type is not builtin, and there

9965

is a cast from the type to json, the cast function will be used to

9964

Returns the value as JSON. If the data type is not built in, and there

9965

is a cast from the type to <type>json</type>, the cast function will be used to

9966

perform the conversion. Otherwise, for any value other than a number,

9967

a boolean or NULL, the text representation will be used, escaped and

9967

a Boolean, or a null value, the text representation will be used, escaped and

9968

quoted so that it is legal JSON.

9969

</entry>

9970

9977

</indexterm>

9978

<literal>json_array_length(json)</literal>

9979

</entry>

9980

9980

9981

<entry>

9982

Returns the number of elements in the outermost json array.

9982

Returns the number of elements in the outermost JSON array.

9983

</entry>

9984

<entry><literal>json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')</literal></entry>

9985

9991

</indexterm>

9992

9993

</entry>

9994

<entry>SETOF key text, value json</entry>

9994

<entry><type>SETOF key text, value json</type></entry>

9995

<entry>

9996

Expands the outermost json object into a set of key/value pairs.

9996

Expands the outermost JSON object into a set of key/value pairs.

9997

</entry>

9998

<entry><literal>select * from json_each('{"a":"foo", "b":"bar"}')</literal></entry>

9999

<entry>

10012

</indexterm>

10013

10014

</entry>

10015

<entry>SETOF key text, value text</entry>

10015

<entry><type>SETOF key text, value text</type></entry>

10016

<entry>

10017

Expands the outermost json object into a set of key/value pairs. The

10017

Expands the outermost JSON object into a set of key/value pairs. The

10018

returned value will be of type text.

10019

</entry>

10020

<entry><literal>select * from json_each_text('{"a":"foo", "b":"bar"}')</literal></entry>

10034

</indexterm>

10035

<literal>json_extract_path(from_json json, VARIADIC path_elems text[])</literal>

10036

</entry>

10037

10037

10038

<entry>

10039

Returns json object pointed to by <parameter>path_elems</parameter>.

10039

Returns JSON object pointed to by <parameter>path_elems</parameter>.

10040

</entry>

10041

<entry><literal>json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')</literal></entry>

10042

10048

</indexterm>

10049

<literal>json_extract_path_text(from_json json, VARIADIC path_elems text[])</literal>

10050

</entry>

10051

10051

10052

<entry>

10053

Returns json object pointed to by <parameter>path_elems</parameter>.

10053

Returns JSON object pointed to by <parameter>path_elems</parameter>.

10054

</entry>

10055

<entry><literal>json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4', 'f6')</literal></entry>

10056

10062

</indexterm>

10063

<literal>json_object_keys(json)</literal>

10064

</entry>

10065

<entry>SETOF text</entry>

10065

<entry><type>SETOF text</type></entry>

10066

<entry>

10067

Returns set of keys in the json object. Only the "outer" object will be displayed.

10067

Returns set of keys in the JSON object. Only the <quote>outer</quote> object will be displayed.

10068

</entry>

10069

<entry><literal>json_object_keys('{"f1":"abc","f2":{"f3":"a", "f4":"b"}}')</literal></entry>

10070

<entry>

10083

</indexterm>

10084

<literal>json_populate_record(base anyelement, from_json json, [, use_json_as_text bool=false]</literal>

10085

</entry>

10086

<entry>anyelement</entry>

10086

<entry><type>anyelement</type></entry>

10087

<entry>

10088

Expands the object in from_json to a row whose columns match

10088

Expands the object in <replaceable>from_json</replaceable> to a row whose columns match

10089

the record type defined by base. Conversion will be best

10090

effort; columns in base with no corresponding key in from_json

10090

effort; columns in base with no corresponding key in <replaceable>from_json</replaceable>

10091

will be left null. A column may only be specified once.

10092

</entry>

10093

<entry><literal>select * from json_populate_record(null::x, '{"a":1,"b":2}')</literal></entry>

10106

</indexterm>

10107

<literal>json_populate_recordset(base anyelement, from_json json, [, use_json_as_text bool=false]</literal>

10108

</entry>

10109

<entry>SETOF anyelement</entry>

10109

<entry><type>SETOF anyelement</type></entry>

10110

<entry>

10111

Expands the outermost set of objects in from_json to a set

10111

Expands the outermost set of objects in <replaceable>from_json</replaceable> to a set

10112

whose columns match the record type defined by base.

10113

Conversion will be best effort; columns in base with no

10114

corresponding key in from_json will be left null. A column

10114

corresponding key in <replaceable>from_json</replaceable> will be left null. A column

10115

may only be specified once.

10116

</entry>

10117

<entry><literal>select * from json_populate_recordset(null::x, '[{"a":1,"b":2},{"a":3,"b":4}]')</literal></entry>

10131

</indexterm>

10132

<literal>json_array_elements(json)</literal>

10133

</entry>

10134

<entry>SETOF json</entry>

10134

<entry><type>SETOF json</type></entry>

10135

<entry>

10136

Expands a json array to a set of json elements.

10136

Expands a JSON array to a set of JSON elements.

10137

</entry>

10138

<entry><literal>json_array_elements('[1,true, [2,false]]')</literal></entry>

10139

<entry>

10152

10153

<note>

10154

<para>

10155

The <xref linkend="hstore"> extension has a cast from hstore to

10156

json, so that converted hstore values are represented as json objects,

10155

The <type>json</type> functions and operators can impose stricter validity requirements

10156

than the type's input functions. In particular, they check much more closely that any use

10157

of Unicode surrogate pairs to designate characters outside the Unicode Basic Multilingual

10158

Plane is correct.

10159

</para>

10160

</note>

10161

10162

<note>

10163

<para>

10164

Many of these functions and operators will convert Unicode escapes

10165

in the JSON text to the appropriate UTF8 character when the database encoding is UTF8. In

10166

other encodings the escape sequence must be for an ASCII character, and any other code point

10167

in a Unicode escape sequence will result in an error.

10168

In general, it is best to avoid mixing Unicode escapes in JSON with a non-UTF8 database

10169

encoding, if possible.

10170

</para>

10171

</note>

10172

10173

<note>

10174

<para>

10175

The <xref linkend="hstore"> extension has a cast from <type>hstore</type> to

10176

<type>json</type>, so that converted <type>hstore</type> values are represented as JSON objects,

10157

10177

not as string values.

10158

10178

</para>

10159

10179

</note>

10161

10181

<para>

10162

10182

See also <xref linkend="functions-aggregate"> about the aggregate

10163

10183

function <function>json_agg</function> which aggregates record

10164

values as json efficiently.

10184

values as JSON efficiently.

10165

10185

</para>

10166

10186

</sect1>

10167

10187

11546

11566

<entry>

11547

11567

11548

11568

</entry>

11549

<entry>aggregates records as a json array of objects</entry>

11569

<entry>aggregates records as a JSON array of objects</entry>

11550

11570

</row>

11551

11571

11552

11572

<row>

14904

14924

</sect2>

14905

14925

14906

14926

14907

<title>Server Signalling Functions</title>

14927

<title>Server Signaling Functions</title>

14908

14928

14909

14929

14910

14930

<primary>pg_cancel_backend</primary>

14932

14952

</para>

14933

14953

14934

14954

14935

<title>Server Signalling Functions</title>

14955

<title>Server Signaling Functions</title>

14936

14956

14937

14957

<thead>

14938

14958

<row><entry>Name</entry> <entry>Return Type</entry> <entry>Description</entry>