<!-- $PostgreSQL: pgsql/doc/src/sgml/intagg.sgml,v 1.3 2007/12/10 05:32:51 tgl Exp $ -->

<sect1 id="intagg">

<title>intagg</title>

<indexterm zone="intagg">

<primary>intagg</primary>

</indexterm>

<para>

This section describes the <literal>intagg</literal> module which provides an integer aggregator and an enumerator.

The <filename>intagg</filename> module provides an integer aggregator and an

enumerator.

</para>

<sect2>

<title>Functions</title>

<para>

The aggregator is an aggregate function

<function>int_array_aggregate(integer)</>

that produces an integer array

containing exactly the integers it is fed.

Here is a not-tremendously-useful example:

</para>

<programlisting>

test=# select int_array_aggregate(i) from

test-# generate_series(1,10,2) i;

int_array_aggregate

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

{1,3,5,7,9}

(1 row)

</programlisting>

<para>

Many database systems have the notion of a one to many table. Such a table usually sits between two indexed tables, as:

The enumerator is a function

<function>int_array_enum(integer[])</>

that returns <type>setof integer</>. It is essentially the reverse

operation of the aggregator: given an array of integers, expand it

into a set of rows. For example,

</para>

<programlisting>

test=# select * from int_array_enum(array[1,3,5,7,9]);

int_array_enum

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

1

3

5

7

9

(5 rows)

</programlisting>

</sect2>

<sect2>

<title>Sample Uses</title>

<para>

Many database systems have the notion of a one to many table. Such a table

usually sits between two indexed tables, for example:

</para>

<programlisting>

CREATE TABLE one_to_many(left INT, right INT) ;

CREATE TABLE left (id INT PRIMARY KEY, ...);

CREATE TABLE right (id INT PRIMARY KEY, ...);

CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right);

</programlisting>

<para>

And it is used like this:

It is typically used like this:

</para>

<programlisting>

SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right)

WHEREone_to_many.left = item;

SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right)

WHERE one_to_many.left =<replaceable>item</>;

</programlisting>

<para>

This will return all the items in the right hand table for an entry

This will return all the items in the right hand table for an entry

in the left hand table. This is a very common construct in SQL.

</para>

<para>

Now, this methodology can be cumbersome with a very large number of

entries in the one_to_many table.Depending on the order in which

data was entered,a join like thiscould result in an index scan

entries in the<structname>one_to_many</> table. Often,

a join like thiswould result in an index scan

and a fetch for each right hand entry in the table for a particular

left hand entry. If you have a very dynamic system, there is not much you

left hand entry. If you have a very dynamic system, there is not much you

can do. However, if you have some data which is fairly static, you can

create a summary table with the aggregator.

</para>

<programlisting>

CREATE TABLE summary as SELECT left, int_array_aggregate(right)

AS right FROM one_to_many GROUP BY left;

CREATE TABLE summary as

SELECT left, int_array_aggregate(right) AS right

FROM one_to_many

GROUP BY left;

</programlisting>

<para>

This will create a table with one row per left item, and an array

of right items. Now this is pretty useless without some way of using

the array, thats why there is an array enumerator.

the array; that's why there is an array enumerator. You can do

</para>

<programlisting>

SELECT left, int_array_enum(right) FROM summary WHERE left = item;

SELECT left, int_array_enum(right) FROM summary WHERE left =<replaceable>item</>;

</programlisting>

<para>

The above query using int_array_enum, produces the same results as:

The above query using <function>int_array_enum</> produces the same results

as

</para>

<programlisting>

SELECT left, right FROM one_to_many WHERE left = item;

SELECT left, right FROM one_to_many WHERE left =<replaceable>item</>;

</programlisting>

<para>

The difference is that the query against the summary table has to get

only one row from the table,where asthe query against "one_to_many"

must index scan and fetch a row for each entry.

only one row from the table,whereasthedirectquery against

<structname>one_to_many</>must index scan and fetch a row for each entry.

</para>

<para>

On our system, an EXPLAIN shows a query with a cost of 8488 gets reduced

to a cost of 329. The query is a join between the one_to_many table,

On one system, an <command>EXPLAIN</> showed a query with a cost of 8488 was

reduced to a cost of 329. The original query was a join involving the

<structname>one_to_many</> table, which was replaced by:

</para>

<programlisting>

SELECT right, count(right) FROM

(

SELECT left, int_array_enum(right) AS right FROM summary JOIN

(SELECT left FROM left_table WHERE left = item) AS lefts

ON (summary.left = lefts.left )

) AS list GROUP BY right ORDER BY count DESC ;

SELECT right, count(right) FROM

( SELECT left, int_array_enum(right) AS right

FROM summary JOIN (SELECT left FROM left_table WHERE left = <replaceable>item</>) AS lefts

ON (summary.left = lefts.left)

) AS list

GROUP BY right

ORDER BY count DESC;

</programlisting>

</sect1>

</sect2>

</sect1>