summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/hstore.sgml
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--doc/src/sgml/hstore.sgml981
1 files changed, 981 insertions, 0 deletions
diff --git a/doc/src/sgml/hstore.sgml b/doc/src/sgml/hstore.sgml
new file mode 100644
index 0000000..870063c
--- /dev/null
+++ b/doc/src/sgml/hstore.sgml
@@ -0,0 +1,981 @@
+<!-- doc/src/sgml/hstore.sgml -->
+
+<sect1 id="hstore" xreflabel="hstore">
+ <title>hstore</title>
+
+ <indexterm zone="hstore">
+ <primary>hstore</primary>
+ </indexterm>
+
+ <para>
+ This module implements the <type>hstore</type> data type for storing sets of
+ key/value pairs within a single <productname>PostgreSQL</productname> value.
+ This can be useful in various scenarios, such as rows with many attributes
+ that are rarely examined, or semi-structured data. Keys and values are
+ simply text strings.
+ </para>
+
+ <para>
+ This module is considered <quote>trusted</quote>, that is, it can be
+ installed by non-superusers who have <literal>CREATE</literal> privilege
+ on the current database.
+ </para>
+
+ <sect2>
+ <title><type>hstore</type> External Representation</title>
+
+ <para>
+
+ The text representation of an <type>hstore</type>, used for input and output,
+ includes zero or more <replaceable>key</replaceable> <literal>=&gt;</literal>
+ <replaceable>value</replaceable> pairs separated by commas. Some examples:
+
+<synopsis>
+k =&gt; v
+foo =&gt; bar, baz =&gt; whatever
+"1-a" =&gt; "anything at all"
+</synopsis>
+
+ The order of the pairs is not significant (and may not be reproduced on
+ output). Whitespace between pairs or around the <literal>=&gt;</literal> sign is
+ ignored. Double-quote keys and values that include whitespace, commas,
+ <literal>=</literal>s or <literal>&gt;</literal>s. To include a double quote or a
+ backslash in a key or value, escape it with a backslash.
+ </para>
+
+ <para>
+ Each key in an <type>hstore</type> is unique. If you declare an <type>hstore</type>
+ with duplicate keys, only one will be stored in the <type>hstore</type> and
+ there is no guarantee as to which will be kept:
+
+<programlisting>
+SELECT 'a=&gt;1,a=&gt;2'::hstore;
+ hstore
+----------
+ "a"=&gt;"1"
+</programlisting>
+ </para>
+
+ <para>
+ A value (but not a key) can be an SQL <literal>NULL</literal>. For example:
+
+<programlisting>
+key =&gt; NULL
+</programlisting>
+
+ The <literal>NULL</literal> keyword is case-insensitive. Double-quote the
+ <literal>NULL</literal> to treat it as the ordinary string <quote>NULL</quote>.
+ </para>
+
+ <note>
+ <para>
+ Keep in mind that the <type>hstore</type> text format, when used for input,
+ applies <emphasis>before</emphasis> any required quoting or escaping. If you are
+ passing an <type>hstore</type> literal via a parameter, then no additional
+ processing is needed. But if you're passing it as a quoted literal
+ constant, then any single-quote characters and (depending on the setting of
+ the <varname>standard_conforming_strings</varname> configuration parameter)
+ backslash characters need to be escaped correctly. See
+ <xref linkend="sql-syntax-strings"/> for more on the handling of string
+ constants.
+ </para>
+ </note>
+
+ <para>
+ On output, double quotes always surround keys and values, even when it's
+ not strictly necessary.
+ </para>
+
+ </sect2>
+
+ <sect2>
+ <title><type>hstore</type> Operators and Functions</title>
+
+ <para>
+ The operators provided by the <literal>hstore</literal> module are
+ shown in <xref linkend="hstore-op-table"/>, the functions
+ in <xref linkend="hstore-func-table"/>.
+ </para>
+
+ <table id="hstore-op-table">
+ <title><type>hstore</type> Operators</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Operator
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>hstore</type> <literal>-&gt;</literal> <type>text</type>
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Returns value associated with given key, or <literal>NULL</literal> if
+ not present.
+ </para>
+ <para>
+ <literal>'a=&gt;x, b=&gt;y'::hstore -&gt; 'a'</literal>
+ <returnvalue>x</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>hstore</type> <literal>-&gt;</literal> <type>text[]</type>
+ <returnvalue>text[]</returnvalue>
+ </para>
+ <para>
+ Returns values associated with given keys, or <literal>NULL</literal>
+ if not present.
+ </para>
+ <para>
+ <literal>'a=&gt;x, b=&gt;y, c=&gt;z'::hstore -&gt; ARRAY['c','a']</literal>
+ <returnvalue>{"z","x"}</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>hstore</type> <literal>||</literal> <type>hstore</type>
+ <returnvalue>hstore</returnvalue>
+ </para>
+ <para>
+ Concatenates two <type>hstore</type>s.
+ </para>
+ <para>
+ <literal>'a=&gt;b, c=&gt;d'::hstore || 'c=&gt;x, d=&gt;q'::hstore</literal>
+ <returnvalue>"a"=&gt;"b", "c"=&gt;"x", "d"=&gt;"q"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>hstore</type> <literal>?</literal> <type>text</type>
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Does <type>hstore</type> contain key?
+ </para>
+ <para>
+ <literal>'a=&gt;1'::hstore ? 'a'</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>hstore</type> <literal>?&amp;</literal> <type>text[]</type>
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Does <type>hstore</type> contain all the specified keys?
+ </para>
+ <para>
+ <literal>'a=&gt;1,b=&gt;2'::hstore ?&amp; ARRAY['a','b']</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>hstore</type> <literal>?|</literal> <type>text[]</type>
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Does <type>hstore</type> contain any of the specified keys?
+ </para>
+ <para>
+ <literal>'a=&gt;1,b=&gt;2'::hstore ?| ARRAY['b','c']</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>hstore</type> <literal>@&gt;</literal> <type>hstore</type>
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Does left operand contain right?
+ </para>
+ <para>
+ <literal>'a=&gt;b, b=&gt;1, c=&gt;NULL'::hstore @&gt; 'b=&gt;1'</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>hstore</type> <literal>&lt;@</literal> <type>hstore</type>
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Is left operand contained in right?
+ </para>
+ <para>
+ <literal>'a=&gt;c'::hstore &lt;@ 'a=&gt;b, b=&gt;1, c=&gt;NULL'</literal>
+ <returnvalue>f</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>hstore</type> <literal>-</literal> <type>text</type>
+ <returnvalue>hstore</returnvalue>
+ </para>
+ <para>
+ Deletes key from left operand.
+ </para>
+ <para>
+ <literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - 'b'::text</literal>
+ <returnvalue>"a"=&gt;"1", "c"=&gt;"3"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>hstore</type> <literal>-</literal> <type>text[]</type>
+ <returnvalue>hstore</returnvalue>
+ </para>
+ <para>
+ Deletes keys from left operand.
+ </para>
+ <para>
+ <literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - ARRAY['a','b']</literal>
+ <returnvalue>"c"=&gt;"3"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>hstore</type> <literal>-</literal> <type>hstore</type>
+ <returnvalue>hstore</returnvalue>
+ </para>
+ <para>
+ Deletes pairs from left operand that match pairs in the right operand.
+ </para>
+ <para>
+ <literal>'a=&gt;1, b=&gt;2, c=&gt;3'::hstore - 'a=&gt;4, b=&gt;2'::hstore</literal>
+ <returnvalue>"a"=&gt;"1", "c"=&gt;"3"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <type>anyelement</type> <literal>#=</literal> <type>hstore</type>
+ <returnvalue>anyelement</returnvalue>
+ </para>
+ <para>
+ Replaces fields in the left operand (which must be a composite type)
+ with matching values from <type>hstore</type>.
+ </para>
+ <para>
+ <literal>ROW(1,3) #= 'f1=>11'::hstore</literal>
+ <returnvalue>(11,3)</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <literal>%%</literal> <type>hstore</type>
+ <returnvalue>text[]</returnvalue>
+ </para>
+ <para>
+ Converts <type>hstore</type> to an array of alternating keys and
+ values.
+ </para>
+ <para>
+ <literal>%% 'a=&gt;foo, b=&gt;bar'::hstore</literal>
+ <returnvalue>{a,foo,b,bar}</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <literal>%#</literal> <type>hstore</type>
+ <returnvalue>text[]</returnvalue>
+ </para>
+ <para>
+ Converts <type>hstore</type> to a two-dimensional key/value array.
+ </para>
+ <para>
+ <literal>%# 'a=&gt;foo, b=&gt;bar'::hstore</literal>
+ <returnvalue>{{a,foo},{b,bar}}</returnvalue>
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <table id="hstore-func-table">
+ <title><type>hstore</type> Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>hstore</primary></indexterm>
+ <function>hstore</function> ( <type>record</type> )
+ <returnvalue>hstore</returnvalue>
+ </para>
+ <para>
+ Constructs an <type>hstore</type> from a record or row.
+ </para>
+ <para>
+ <literal>hstore(ROW(1,2))</literal>
+ <returnvalue>"f1"=&gt;"1", "f2"=&gt;"2"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>hstore</function> ( <type>text[]</type> )
+ <returnvalue>hstore</returnvalue>
+ </para>
+ <para>
+ Constructs an <type>hstore</type> from an array, which may be either
+ a key/value array, or a two-dimensional array.
+ </para>
+ <para>
+ <literal>hstore(ARRAY['a','1','b','2'])</literal>
+ <returnvalue>"a"=&gt;"1", "b"=&gt;"2"</returnvalue>
+ </para>
+ <para>
+ <literal>hstore(ARRAY[['c','3'],['d','4']])</literal>
+ <returnvalue>"c"=&gt;"3", "d"=&gt;"4"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>hstore</function> ( <type>text[]</type>, <type>text[]</type> )
+ <returnvalue>hstore</returnvalue>
+ </para>
+ <para>
+ Constructs an <type>hstore</type> from separate key and value arrays.
+ </para>
+ <para>
+ <literal>hstore(ARRAY['a','b'], ARRAY['1','2'])</literal>
+ <returnvalue>"a"=&gt;"1", "b"=&gt;"2"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>hstore</function> ( <type>text</type>, <type>text</type> )
+ <returnvalue>hstore</returnvalue>
+ </para>
+ <para>
+ Makes a single-item <type>hstore</type>.
+ </para>
+ <para>
+ <literal>hstore('a', 'b')</literal>
+ <returnvalue>"a"=&gt;"b"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>akeys</primary></indexterm>
+ <function>akeys</function> ( <type>hstore</type> )
+ <returnvalue>text[]</returnvalue>
+ </para>
+ <para>
+ Extracts an <type>hstore</type>'s keys as an array.
+ </para>
+ <para>
+ <literal>akeys('a=&gt;1,b=&gt;2')</literal>
+ <returnvalue>{a,b}</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>skeys</primary></indexterm>
+ <function>skeys</function> ( <type>hstore</type> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ Extracts an <type>hstore</type>'s keys as a set.
+ </para>
+ <para>
+ <literal>skeys('a=&gt;1,b=&gt;2')</literal>
+ <returnvalue></returnvalue>
+<programlisting>
+a
+b
+</programlisting>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>avals</primary></indexterm>
+ <function>avals</function> ( <type>hstore</type> )
+ <returnvalue>text[]</returnvalue>
+ </para>
+ <para>
+ Extracts an <type>hstore</type>'s values as an array.
+ </para>
+ <para>
+ <literal>avals('a=&gt;1,b=&gt;2')</literal>
+ <returnvalue>{1,2}</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>svals</primary></indexterm>
+ <function>svals</function> ( <type>hstore</type> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ Extracts an <type>hstore</type>'s values as a set.
+ </para>
+ <para>
+ <literal>svals('a=&gt;1,b=&gt;2')</literal>
+ <returnvalue></returnvalue>
+<programlisting>
+1
+2
+</programlisting>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>hstore_to_array</primary></indexterm>
+ <function>hstore_to_array</function> ( <type>hstore</type> )
+ <returnvalue>text[]</returnvalue>
+ </para>
+ <para>
+ Extracts an <type>hstore</type>'s keys and values as an array of
+ alternating keys and values.
+ </para>
+ <para>
+ <literal>hstore_to_array('a=&gt;1,b=&gt;2')</literal>
+ <returnvalue>{a,1,b,2}</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>hstore_to_matrix</primary></indexterm>
+ <function>hstore_to_matrix</function> ( <type>hstore</type> )
+ <returnvalue>text[]</returnvalue>
+ </para>
+ <para>
+ Extracts an <type>hstore</type>'s keys and values as a two-dimensional
+ array.
+ </para>
+ <para>
+ <literal>hstore_to_matrix('a=&gt;1,b=&gt;2')</literal>
+ <returnvalue>{{a,1},{b,2}}</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>hstore_to_json</primary></indexterm>
+ <function>hstore_to_json</function> ( <type>hstore</type> )
+ <returnvalue>json</returnvalue>
+ </para>
+ <para>
+ Converts an <type>hstore</type> to a <type>json</type> value,
+ converting all non-null values to JSON strings.
+ </para>
+ <para>
+ This function is used implicitly when an <type>hstore</type> value is
+ cast to <type>json</type>.
+ </para>
+ <para>
+ <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>
+ <returnvalue>{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>hstore_to_jsonb</primary></indexterm>
+ <function>hstore_to_jsonb</function> ( <type>hstore</type> )
+ <returnvalue>jsonb</returnvalue>
+ </para>
+ <para>
+ Converts an <type>hstore</type> to a <type>jsonb</type> value,
+ converting all non-null values to JSON strings.
+ </para>
+ <para>
+ This function is used implicitly when an <type>hstore</type> value is
+ cast to <type>jsonb</type>.
+ </para>
+ <para>
+ <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>
+ <returnvalue>{"a key": "1", "b": "t", "c": null, "d": "12345", "e": "012345", "f": "1.234", "g": "2.345e+4"}</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>hstore_to_json_loose</primary></indexterm>
+ <function>hstore_to_json_loose</function> ( <type>hstore</type> )
+ <returnvalue>json</returnvalue>
+ </para>
+ <para>
+ Converts an <type>hstore</type> to a <type>json</type> value, but
+ attempts to distinguish numerical and Boolean values so they are
+ unquoted in the JSON.
+ </para>
+ <para>
+ <literal>hstore_to_json_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>
+ <returnvalue>{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>hstore_to_jsonb_loose</primary></indexterm>
+ <function>hstore_to_jsonb_loose</function> ( <type>hstore</type> )
+ <returnvalue>jsonb</returnvalue>
+ </para>
+ <para>
+ Converts an <type>hstore</type> to a <type>jsonb</type> value, but
+ attempts to distinguish numerical and Boolean values so they are
+ unquoted in the JSON.
+ </para>
+ <para>
+ <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>
+ <returnvalue>{"a key": 1, "b": true, "c": null, "d": 12345, "e": "012345", "f": 1.234, "g": 2.345e+4}</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>slice</primary></indexterm>
+ <function>slice</function> ( <type>hstore</type>, <type>text[]</type> )
+ <returnvalue>hstore</returnvalue>
+ </para>
+ <para>
+ Extracts a subset of an <type>hstore</type> containing only the
+ specified keys.
+ </para>
+ <para>
+ <literal>slice('a=&gt;1,b=&gt;2,c=&gt;3'::hstore, ARRAY['b','c','x'])</literal>
+ <returnvalue>"b"=&gt;"2", "c"=&gt;"3"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>each</primary></indexterm>
+ <function>each</function> ( <type>hstore</type> )
+ <returnvalue>setof record</returnvalue>
+ ( <parameter>key</parameter> <type>text</type>,
+ <parameter>value</parameter> <type>text</type> )
+ </para>
+ <para>
+ Extracts an <type>hstore</type>'s keys and values as a set of records.
+ </para>
+ <para>
+ <literal>select * from each('a=&gt;1,b=&gt;2')</literal>
+ <returnvalue></returnvalue>
+<programlisting>
+ key | value
+-----+-------
+ a | 1
+ b | 2
+</programlisting>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>exist</primary></indexterm>
+ <function>exist</function> ( <type>hstore</type>, <type>text</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Does <type>hstore</type> contain key?
+ </para>
+ <para>
+ <literal>exist('a=&gt;1', 'a')</literal>
+ <returnvalue>t</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>defined</primary></indexterm>
+ <function>defined</function> ( <type>hstore</type>, <type>text</type> )
+ <returnvalue>boolean</returnvalue>
+ </para>
+ <para>
+ Does <type>hstore</type> contain a non-<literal>NULL</literal> value
+ for key?
+ </para>
+ <para>
+ <literal>defined('a=&gt;NULL', 'a')</literal>
+ <returnvalue>f</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>delete</primary></indexterm>
+ <function>delete</function> ( <type>hstore</type>, <type>text</type> )
+ <returnvalue>hstore</returnvalue>
+ </para>
+ <para>
+ Deletes pair with matching key.
+ </para>
+ <para>
+ <literal>delete('a=&gt;1,b=&gt;2', 'b')</literal>
+ <returnvalue>"a"=&gt;"1"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>delete</function> ( <type>hstore</type>, <type>text[]</type> )
+ <returnvalue>hstore</returnvalue>
+ </para>
+ <para>
+ Deletes pairs with matching keys.
+ </para>
+ <para>
+ <literal>delete('a=&gt;1,b=&gt;2,c=&gt;3', ARRAY['a','b'])</literal>
+ <returnvalue>"c"=&gt;"3"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>delete</function> ( <type>hstore</type>, <type>hstore</type> )
+ <returnvalue>hstore</returnvalue>
+ </para>
+ <para>
+ Deletes pairs matching those in the second argument.
+ </para>
+ <para>
+ <literal>delete('a=&gt;1,b=&gt;2', 'a=&gt;4,b=&gt;2'::hstore)</literal>
+ <returnvalue>"a"=&gt;"1"</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm><primary>populate_record</primary></indexterm>
+ <function>populate_record</function> ( <type>anyelement</type>, <type>hstore</type> )
+ <returnvalue>anyelement</returnvalue>
+ </para>
+ <para>
+ Replaces fields in the left operand (which must be a composite type)
+ with matching values from <type>hstore</type>.
+ </para>
+ <para>
+ <literal>populate_record(ROW(1,2), 'f1=>42'::hstore)</literal>
+ <returnvalue>(42,2)</returnvalue>
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ In addition to these operators and functions, values of
+ the <type>hstore</type> type can be subscripted, allowing them to act
+ like associative arrays. Only a single subscript of type <type>text</type>
+ can be specified; it is interpreted as a key and the corresponding
+ value is fetched or stored. For example,
+
+<programlisting>
+CREATE TABLE mytable (h hstore);
+INSERT INTO mytable VALUES ('a=>b, c=>d');
+SELECT h['a'] FROM mytable;
+ h
+---
+ b
+(1 row)
+
+UPDATE mytable SET h['c'] = 'new';
+SELECT h FROM mytable;
+ h
+----------------------
+ "a"=>"b", "c"=>"new"
+(1 row)
+</programlisting>
+
+ A subscripted fetch returns <literal>NULL</literal> if the subscript
+ is <literal>NULL</literal> or that key does not exist in
+ the <type>hstore</type>. (Thus, a subscripted fetch is not greatly
+ different from the <literal>-&gt;</literal> operator.)
+ A subscripted update fails if the subscript is <literal>NULL</literal>;
+ otherwise, it replaces the value for that key, adding an entry to
+ the <type>hstore</type> if the key does not already exist.
+ </para>
+ </sect2>
+
+ <sect2>
+ <title>Indexes</title>
+
+ <para>
+ <type>hstore</type> has GiST and GIN index support for the <literal>@&gt;</literal>,
+ <literal>?</literal>, <literal>?&amp;</literal> and <literal>?|</literal> operators. For example:
+ </para>
+<programlisting>
+CREATE INDEX hidx ON testhstore USING GIST (h);
+
+CREATE INDEX hidx ON testhstore USING GIN (h);
+</programlisting>
+
+ <para>
+ <literal>gist_hstore_ops</literal> GiST opclass approximates a set of
+ key/value pairs as a bitmap signature. Its optional integer parameter
+ <literal>siglen</literal> determines the
+ signature length in bytes. The default length is 16 bytes.
+ Valid values of signature length are between 1 and 2024 bytes. Longer
+ signatures lead to a more precise search (scanning a smaller fraction of the index and
+ fewer heap pages), at the cost of a larger index.
+ </para>
+
+ <para>
+ Example of creating such an index with a signature length of 32 bytes:
+<programlisting>
+CREATE INDEX hidx ON testhstore USING GIST (h gist_hstore_ops(siglen=32));
+</programlisting>
+ </para>
+
+ <para>
+ <type>hstore</type> also supports <type>btree</type> or <type>hash</type> indexes for
+ the <literal>=</literal> operator. This allows <type>hstore</type> columns to be
+ declared <literal>UNIQUE</literal>, or to be used in <literal>GROUP BY</literal>,
+ <literal>ORDER BY</literal> or <literal>DISTINCT</literal> expressions. The sort ordering
+ for <type>hstore</type> values is not particularly useful, but these indexes
+ may be useful for equivalence lookups. Create indexes for <literal>=</literal>
+ comparisons as follows:
+ </para>
+<programlisting>
+CREATE INDEX hidx ON testhstore USING BTREE (h);
+
+CREATE INDEX hidx ON testhstore USING HASH (h);
+</programlisting>
+ </sect2>
+
+ <sect2>
+ <title>Examples</title>
+
+ <para>
+ Add a key, or update an existing key with a new value:
+<programlisting>
+UPDATE tab SET h['c'] = '3';
+</programlisting>
+ Another way to do the same thing is:
+<programlisting>
+UPDATE tab SET h = h || hstore('c', '3');
+</programlisting>
+ If multiple keys are to be added or changed in one operation,
+ the concatenation approach is more efficient than subscripting:
+<programlisting>
+UPDATE tab SET h = h || hstore(array['q', 'w'], array['11', '12']);
+</programlisting>
+ </para>
+
+ <para>
+ Delete a key:
+<programlisting>
+UPDATE tab SET h = delete(h, 'k1');
+</programlisting>
+ </para>
+
+ <para>
+ Convert a <type>record</type> to an <type>hstore</type>:
+<programlisting>
+CREATE TABLE test (col1 integer, col2 text, col3 text);
+INSERT INTO test VALUES (123, 'foo', 'bar');
+
+SELECT hstore(t) FROM test AS t;
+ hstore
+---------------------------------------------
+ "col1"=&gt;"123", "col2"=&gt;"foo", "col3"=&gt;"bar"
+(1 row)
+</programlisting>
+ </para>
+
+ <para>
+ Convert an <type>hstore</type> to a predefined <type>record</type> type:
+<programlisting>
+CREATE TABLE test (col1 integer, col2 text, col3 text);
+
+SELECT * FROM populate_record(null::test,
+ '"col1"=&gt;"456", "col2"=&gt;"zzz"');
+ col1 | col2 | col3
+------+------+------
+ 456 | zzz |
+(1 row)
+</programlisting>
+ </para>
+
+ <para>
+ Modify an existing record using the values from an <type>hstore</type>:
+<programlisting>
+CREATE TABLE test (col1 integer, col2 text, col3 text);
+INSERT INTO test VALUES (123, 'foo', 'bar');
+
+SELECT (r).* FROM (SELECT t #= '"col3"=&gt;"baz"' AS r FROM test t) s;
+ col1 | col2 | col3
+------+------+------
+ 123 | foo | baz
+(1 row)
+</programlisting>
+ </para>
+ </sect2>
+
+ <sect2>
+ <title>Statistics</title>
+
+ <para>
+ The <type>hstore</type> type, because of its intrinsic liberality, could
+ contain a lot of different keys. Checking for valid keys is the task of the
+ application. The following examples demonstrate several techniques for
+ checking keys and obtaining statistics.
+ </para>
+
+ <para>
+ Simple example:
+<programlisting>
+SELECT * FROM each('aaa=&gt;bq, b=&gt;NULL, ""=&gt;1');
+</programlisting>
+ </para>
+
+ <para>
+ Using a table:
+<programlisting>
+CREATE TABLE stat AS SELECT (each(h)).key, (each(h)).value FROM testhstore;
+</programlisting>
+ </para>
+
+ <para>
+ Online statistics:
+<programlisting>
+SELECT key, count(*) FROM
+ (SELECT (each(h)).key FROM testhstore) AS stat
+ GROUP BY key
+ ORDER BY count DESC, key;
+ key | count
+-----------+-------
+ line | 883
+ query | 207
+ pos | 203
+ node | 202
+ space | 197
+ status | 195
+ public | 194
+ title | 190
+ org | 189
+...................
+</programlisting>
+ </para>
+ </sect2>
+
+ <sect2>
+ <title>Compatibility</title>
+
+ <para>
+ As of PostgreSQL 9.0, <type>hstore</type> uses a different internal
+ representation than previous versions. This presents no obstacle for
+ dump/restore upgrades since the text representation (used in the dump) is
+ unchanged.
+ </para>
+
+ <para>
+ In the event of a binary upgrade, upward compatibility is maintained by
+ having the new code recognize old-format data. This will entail a slight
+ performance penalty when processing data that has not yet been modified by
+ the new code. It is possible to force an upgrade of all values in a table
+ column by doing an <literal>UPDATE</literal> statement as follows:
+<programlisting>
+UPDATE tablename SET hstorecol = hstorecol || '';
+</programlisting>
+ </para>
+
+ <para>
+ Another way to do it is:
+<programlisting>
+ALTER TABLE tablename ALTER hstorecol TYPE hstore USING hstorecol || '';
+</programlisting>
+ The <command>ALTER TABLE</command> method requires an
+ <literal>ACCESS EXCLUSIVE</literal> lock on the table,
+ but does not result in bloating the table with old row versions.
+ </para>
+
+ </sect2>
+
+ <sect2>
+ <title>Transforms</title>
+
+ <para>
+ Additional extensions are available that implement transforms for
+ the <type>hstore</type> type for the languages PL/Perl and PL/Python. The
+ extensions for PL/Perl are called <literal>hstore_plperl</literal>
+ and <literal>hstore_plperlu</literal>, for trusted and untrusted PL/Perl.
+ If you install these transforms and specify them when creating a
+ function, <type>hstore</type> values are mapped to Perl hashes. The
+ extensions for PL/Python are
+ called <literal>hstore_plpythonu</literal>, <literal>hstore_plpython2u</literal>,
+ and <literal>hstore_plpython3u</literal>
+ (see <xref linkend="plpython-python23"/> for the PL/Python naming
+ convention). If you use them, <type>hstore</type> values are mapped to
+ Python dictionaries.
+ </para>
+
+ <caution>
+ <para>
+ It is strongly recommended that the transform extensions be installed in
+ the same schema as <filename>hstore</filename>. Otherwise there are
+ installation-time security hazards if a transform extension's schema
+ contains objects defined by a hostile user.
+ </para>
+ </caution>
+ </sect2>
+
+ <sect2>
+ <title>Authors</title>
+
+ <para>
+ Oleg Bartunov <email>oleg@sai.msu.su</email>, Moscow, Moscow University, Russia
+ </para>
+
+ <para>
+ Teodor Sigaev <email>teodor@sigaev.ru</email>, Moscow, Delta-Soft Ltd., Russia
+ </para>
+
+ <para>
+ Additional enhancements by Andrew Gierth <email>andrew@tao11.riddles.org.uk</email>,
+ United Kingdom
+ </para>
+ </sect2>
+
+</sect1>