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