codekingpro/portable-devtools

1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>F.19. intagg — integer aggregator and enumerator</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="hstore.html" title="F.18. hstore — hstore key/value datatype" /><link rel="next" href="intarray.html" title="F.20. intarray — manipulate arrays of integers" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.19. intagg — integer aggregator and enumerator</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="hstore.html" title="F.18. hstore — hstore key/value datatype">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules and Extensions</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="intarray.html" title="F.20. intarray — manipulate arrays of integers">Next</a></td></tr></table><hr /></div><div class="sect1" id="INTAGG"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.19. intagg — integer aggregator and enumerator <a href="#INTAGG" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="intagg.html#INTAGG-FUNCTIONS">F.19.1. Functions</a></span></dt><dt><span class="sect2"><a href="intagg.html#INTAGG-SAMPLES">F.19.2. Sample Uses</a></span></dt></dl></div><a id="id-1.11.7.29.2" class="indexterm"></a><p>3  The <code class="filename">intagg</code> module provides an integer aggregator and an4  enumerator.  <code class="filename">intagg</code> is now obsolete, because there5  are built-in functions that provide a superset of its capabilities.6  However, the module is still provided as a compatibility wrapper around7  the built-in functions.8 </p><div class="sect2" id="INTAGG-FUNCTIONS"><div class="titlepage"><div><div><h3 class="title">F.19.1. Functions <a href="#INTAGG-FUNCTIONS" class="id_link">#</a></h3></div></div></div><a id="id-1.11.7.29.4.2" class="indexterm"></a><a id="id-1.11.7.29.4.3" class="indexterm"></a><p>9  The aggregator is an aggregate function10  <code class="function">int_array_aggregate(integer)</code>11  that produces an integer array12  containing exactly the integers it is fed.13  This is a wrapper around <code class="function">array_agg</code>,14  which does the same thing for any array type.15 </p><a id="id-1.11.7.29.4.5" class="indexterm"></a><p>16  The enumerator is a function17  <code class="function">int_array_enum(integer[])</code>18  that returns <code class="type">setof integer</code>.  It is essentially the reverse19  operation of the aggregator: given an array of integers, expand it20  into a set of rows.  This is a wrapper around <code class="function">unnest</code>,21  which does the same thing for any array type.22 </p></div><div class="sect2" id="INTAGG-SAMPLES"><div class="titlepage"><div><div><h3 class="title">F.19.2. Sample Uses <a href="#INTAGG-SAMPLES" class="id_link">#</a></h3></div></div></div><p>23   Many database systems have the notion of a one to many table. Such a table24   usually sits between two indexed tables, for example:25 26</p><pre class="programlisting">27CREATE TABLE left (id INT PRIMARY KEY, ...);28CREATE TABLE right (id INT PRIMARY KEY, ...);29CREATE TABLE one_to_many(left INT REFERENCES left, right INT REFERENCES right);30</pre><p>31 32  It is typically used like this:33 34</p><pre class="programlisting">35SELECT right.* from right JOIN one_to_many ON (right.id = one_to_many.right)36  WHERE one_to_many.left = <em class="replaceable"><code>item</code></em>;37</pre><p>38 39  This will return all the items in the right hand table for an entry40  in the left hand table. This is a very common construct in SQL.41 </p><p>42  Now, this methodology can be cumbersome with a very large number of43  entries in the <code class="structname">one_to_many</code> table.  Often,44  a join like this would result in an index scan45  and a fetch for each right hand entry in the table for a particular46  left hand entry. If you have a very dynamic system, there is not much you47  can do. However, if you have some data which is fairly static, you can48  create a summary table with the aggregator.49 50</p><pre class="programlisting">51CREATE TABLE summary AS52  SELECT left, int_array_aggregate(right) AS right53  FROM one_to_many54  GROUP BY left;55</pre><p>56 57  This will create a table with one row per left item, and an array58  of right items. Now this is pretty useless without some way of using59  the array; that's why there is an array enumerator.  You can do60 61</p><pre class="programlisting">62SELECT left, int_array_enum(right) FROM summary WHERE left = <em class="replaceable"><code>item</code></em>;63</pre><p>64 65  The above query using <code class="function">int_array_enum</code> produces the same results66  as67 68</p><pre class="programlisting">69SELECT left, right FROM one_to_many WHERE left = <em class="replaceable"><code>item</code></em>;70</pre><p>71 72  The difference is that the query against the summary table has to get73  only one row from the table, whereas the direct query against74  <code class="structname">one_to_many</code> must index scan and fetch a row for each entry.75 </p><p>76  On one system, an <code class="command">EXPLAIN</code> showed a query with a cost of 8488 was77  reduced to a cost of 329.  The original query was a join involving the78  <code class="structname">one_to_many</code> table, which was replaced by:79 80</p><pre class="programlisting">81SELECT right, count(right) FROM82  ( SELECT left, int_array_enum(right) AS right83    FROM summary JOIN (SELECT left FROM left_table WHERE left = <em class="replaceable"><code>item</code></em>) AS lefts84         ON (summary.left = lefts.left)85  ) AS list86  GROUP BY right87  ORDER BY count DESC;88</pre><p>89 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="hstore.html" title="F.18. hstore — hstore key/value datatype">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="intarray.html" title="F.20. intarray — manipulate arrays of integers">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.18. hstore — hstore key/value datatype </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> F.20. intarray — manipulate arrays of integers</td></tr></table></div></body></html>