1<!-- doc/src/sgml/intagg.sgml -->
2
3<sect1 id="intagg" xreflabel="intagg">
4 <title>intagg</title>
5
6 <indexterm zone="intagg">
7  <primary>intagg</primary>
8 </indexterm>
9
10 <para>
11  The <filename>intagg</filename> module provides an integer aggregator and an
12  enumerator.  <filename>intagg</filename> is now obsolete, because there
13  are built-in functions that provide a superset of its capabilities.
14  However, the module is still provided as a compatibility wrapper around
15  the built-in functions.
16 </para>
17
18 <sect2>
19  <title>Functions</title>
20
21 <indexterm>
22  <primary>int_array_aggregate</primary>
23 </indexterm>
24
25 <indexterm>
26  <primary>array_agg</primary>
27 </indexterm>
28
29 <para>
30  The aggregator is an aggregate function
31  <function>int_array_aggregate(integer)</function>
32  that produces an integer array
33  containing exactly the integers it is fed.
34  This is a wrapper around <function>array_agg</function>,
35  which does the same thing for any array type.
36 </para>
37
38 <indexterm>
39  <primary>int_array_enum</primary>
40 </indexterm>
41
42 <para>
43  The enumerator is a function
44  <function>int_array_enum(integer[])</function>
45  that returns <type>setof integer</type>.  It is essentially the reverse
46  operation of the aggregator: given an array of integers, expand it
47  into a set of rows.  This is a wrapper around <function>unnest</function>,
48  which does the same thing for any array type.
49 </para>
50
51 </sect2>
52
53 <sect2>
54  <title>Sample Uses</title>
55
56  <para>
57   Many database systems have the notion of a one to many table. Such a table
58   usually sits between two indexed tables, for example:
59
60<programlisting>
61CREATE TABLE left (id INT PRIMARY KEY, ...);
62CREATE TABLE right (id INT PRIMARY KEY, ...);
63CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right);
64</programlisting>
65
66  It is typically used like this:
67
68<programlisting>
69SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right)
70  WHERE one_to_many.left = <replaceable>item</replaceable>;
71</programlisting>
72
73  This will return all the items in the right hand table for an entry
74  in the left hand table. This is a very common construct in SQL.
75 </para>
76
77 <para>
78  Now, this methodology can be cumbersome with a very large number of
79  entries in the <structname>one_to_many</structname> table.  Often,
80  a join like this would result in an index scan
81  and a fetch for each right hand entry in the table for a particular
82  left hand entry. If you have a very dynamic system, there is not much you
83  can do. However, if you have some data which is fairly static, you can
84  create a summary table with the aggregator.
85
86<programlisting>
87CREATE TABLE summary AS
88  SELECT left, int_array_aggregate(right) AS right
89  FROM one_to_many
90  GROUP BY left;
91</programlisting>
92
93  This will create a table with one row per left item, and an array
94  of right items. Now this is pretty useless without some way of using
95  the array; that's why there is an array enumerator.  You can do
96
97<programlisting>
98SELECT left, int_array_enum(right) FROM summary WHERE left = <replaceable>item</replaceable>;
99</programlisting>
100
101  The above query using <function>int_array_enum</function> produces the same results
102  as
103
104<programlisting>
105SELECT left, right FROM one_to_many WHERE left = <replaceable>item</replaceable>;
106</programlisting>
107
108  The difference is that the query against the summary table has to get
109  only one row from the table, whereas the direct query against
110  <structname>one_to_many</structname> must index scan and fetch a row for each entry.
111 </para>
112
113 <para>
114  On one system, an <command>EXPLAIN</command> showed a query with a cost of 8488 was
115  reduced to a cost of 329.  The original query was a join involving the
116  <structname>one_to_many</structname> table, which was replaced by:
117
118<programlisting>
119SELECT right, count(right) FROM
120  ( SELECT left, int_array_enum(right) AS right
121    FROM summary JOIN (SELECT left FROM left_table WHERE left = <replaceable>item</replaceable>) AS lefts
122         ON (summary.left = lefts.left)
123  ) AS list
124  GROUP BY right
125  ORDER BY count DESC;
126</programlisting>
127 </para>
128
129 </sect2>
130
131</sect1>
132