Do some clean up of development files. Adjust converting script to
[working/Evergreen.git] / 1.6 / development / introduction_to_sql.xml
index 5362212..fad37b5 100644 (file)
                        worry if the INSERT statement is completely unfamiliar, we’ll talk more about\r
                        the syntax of the insert statement later.</simpara>\r
                        <formalpara><title><literal>actor.usr_note</literal> database table</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">evergreen=# INSERT INTO actor.usr_note (usr, creator, pub, title, value)\r
-                           VALUES (1, 1, TRUE, 'Who is this guy?', 'He''s the administrator!');\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+evergreen=# INSERT INTO actor.usr_note (usr, creator, pub, title, value)\r
+    VALUES (1, 1, TRUE, 'Who is this guy?', 'He''s the administrator!');\r
 \r
-                       evergreen=# select id, usr, creator, pub, title, value from actor.usr_note;\r
-                        id | usr | creator | pub |      title       |          value\r
-                       ----+-----+---------+-----+------------------+-------------------------\r
-                         1 |   1 |       1 | t   | Who is this guy? | He's the administrator!\r
-                       (1 rows)</programlisting>\r
+evergreen=# select id, usr, creator, pub, title, value from actor.usr_note;\r
+ id | usr | creator | pub |      title       |          value\r
+----+-----+---------+-----+------------------+-------------------------\r
+  1 |   1 |       1 | t   | Who is this guy? | He's the administrator!\r
+(1 rows)\r
+</programlisting>\r
                        </para></formalpara>\r
                        <simpara>PostgreSQL supports table inheritance, which lets you define tables that\r
                        inherit the column definitions of a given parent table. A search of the data in\r
                        the <literal>public</literal> schema. As a result, you might not find the object that you&#8217;re\r
                        looking for if you don&#8217;t use the appropriate schema.</simpara>\r
                        <formalpara><title>Example: Creating a table without a specific schema</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">evergreen=# CREATE TABLE foobar (foo TEXT, bar TEXT);\r
-                       CREATE TABLE\r
-                       evergreen=# \d foobar\r
-                          Table "public.foobar"\r
-                        Column | Type | Modifiers\r
-                       --------+------+-----------\r
-                        foo    | text |\r
-                        bar    | text |</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+evergreen=# CREATE TABLE foobar (foo TEXT, bar TEXT);\r
+CREATE TABLE\r
+evergreen=# \d foobar\r
+   Table "public.foobar"\r
+ Column | Type | Modifiers\r
+--------+------+-----------\r
+ foo    | text |\r
+ bar    | text |\r
+</programlisting>\r
                        </para></formalpara>\r
                        <formalpara><title>Example: Trying to access a unqualified table outside of the public schema</title><para>\r
                        <programlisting language="sql" linenumbering="unnumbered">evergreen=# SELECT * FROM usr_note;\r
                        <simpara>The <literal>actor.org_address</literal> table is a simple table in the Evergreen schema that\r
                        we can use as a concrete example of many of the properties of databases that\r
                        we have discussed so far.</simpara>\r
-                       <programlisting language="sql" linenumbering="unnumbered">CREATE TABLE actor.org_address (\r
-                         id            SERIAL  PRIMARY KEY,      <co id="sqlCO1-1"/>\r
-                         valid         BOOL    NOT NULL DEFAULT TRUE, <co id="sqlCO1-2"/>\r
-                         address_type  TEXT    NOT NULL DEFAULT 'MAILING', <co id="sqlCO1-3"/>\r
-                         org_unit      INT     NOT NULL REFERENCES actor.org_unit (id)  <co id="sqlCO1-4"/>\r
-                                                 DEFERRABLE INITIALLY DEFERRED,\r
-                         street1       TEXT    NOT NULL,\r
-                         street2       TEXT, <co id="sqlCO1-5"/>\r
-                         city          TEXT    NOT NULL,\r
-                         county        TEXT,\r
-                         state         TEXT    NOT NULL,\r
-                         country       TEXT    NOT NULL,\r
-                         post_code     TEXT    NOT NULL\r
-                       );</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+CREATE TABLE actor.org_address (\r
+  id            SERIAL  PRIMARY KEY,      <co id="sqlCO1-1"/>\r
+  valid         BOOL    NOT NULL DEFAULT TRUE, <co id="sqlCO1-2"/>\r
+  address_type  TEXT    NOT NULL DEFAULT 'MAILING', <co id="sqlCO1-3"/>\r
+  org_unit      INT     NOT NULL REFERENCES actor.org_unit (id)  <co id="sqlCO1-4"/>\r
+                         DEFERRABLE INITIALLY DEFERRED,\r
+  street1       TEXT    NOT NULL,\r
+  street2       TEXT, <co id="sqlCO1-5"/>\r
+  city          TEXT    NOT NULL,\r
+  county        TEXT,\r
+  state         TEXT    NOT NULL,\r
+  country       TEXT    NOT NULL,\r
+  post_code     TEXT    NOT NULL\r
+);\r
+</programlisting>\r
                        <calloutlist>\r
                        <callout arearefs="sqlCO1-1">\r
                        <simpara>\r
                        <simpara>To display the definition of a database object such as a table, issue the\r
                        command <literal>\d _object-name_</literal>. For example, to display the definition of the\r
                        actor.usr_note table:</simpara>\r
-                       <programlisting language="sh" linenumbering="unnumbered">$ psql evergreen <co id="sqlCO2-1"/>\r
-                       psql (8.4.1)\r
-                       Type "help" for help.\r
+<programlisting language="sh" linenumbering="unnumbered">\r
+$ psql evergreen <co id="sqlCO2-1"/>\r
+psql (8.4.1)\r
+Type "help" for help.\r
 \r
-                       evergreen=# \d actor.usr_note  <co id="sqlCO2-2"/>\r
-                                                               Table "actor.usr_note"\r
-                          Column    |           Type           |                          Modifiers\r
-                       -------------+--------------------------+-------------------------------------------------------------\r
                       id          | bigint                   | not null default nextval('actor.usr_note_id_seq'::regclass)\r
                       usr         | bigint                   | not null\r
                       creator     | bigint                   | not null\r
                       create_date | timestamp with time zone | default now()\r
                       pub         | boolean                  | not null default false\r
                       title       | text                     | not null\r
                       value       | text                     | not null\r
-                       Indexes:\r
-                           "usr_note_pkey" PRIMARY KEY, btree (id)\r
-                           "actor_usr_note_creator_idx" btree (creator)\r
-                           "actor_usr_note_usr_idx" btree (usr)\r
-                       Foreign-key constraints:\r
-                           "usr_note_creator_fkey" FOREIGN KEY (creator) REFERENCES actor.usr(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED\r
-                           "usr_note_usr_fkey" FOREIGN KEY (usr) REFERENCES actor.usr(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED\r
+evergreen=# \d actor.usr_note  <co id="sqlCO2-2"/>\r
+                                       Table "actor.usr_note"\r
+   Column    |           Type           |                          Modifiers\r
+-------------+--------------------------+-------------------------------------------------------------\r
+ id          | bigint                   | not null default nextval('actor.usr_note_id_seq'::regclass)\r
+ usr         | bigint                   | not null\r
+ creator     | bigint                   | not null\r
+ create_date | timestamp with time zone | default now()\r
+ pub         | boolean                  | not null default false\r
+ title       | text                     | not null\r
+ value       | text                     | not null\r
+Indexes:\r
+    "usr_note_pkey" PRIMARY KEY, btree (id)\r
+    "actor_usr_note_creator_idx" btree (creator)\r
+    "actor_usr_note_usr_idx" btree (usr)\r
+Foreign-key constraints:\r
+    "usr_note_creator_fkey" FOREIGN KEY (creator) REFERENCES actor.usr(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED\r
+    "usr_note_usr_fkey" FOREIGN KEY (usr) REFERENCES actor.usr(id) ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED\r
 \r
-                       evergreen=# \q    <co id="sqlCO2-3"/>\r
-                       $</programlisting>\r
+evergreen=# \q    <co id="sqlCO2-3"/>\r
+$\r
+</programlisting>\r
                        <calloutlist>\r
                        <callout arearefs="sqlCO2-1">\r
                        <simpara>\r
                        <simpara>For example, to sort the rows returned from your <literal>actor.usr_address</literal> query by\r
                        city, with county and then zip code as the tie breakers, issue the\r
                        following query:</simpara>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
-                         FROM actor.usr_address\r
-                         ORDER BY city, county, post_code\r
-                       ;</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT city, county, state\r
+  FROM actor.usr_address\r
+  ORDER BY city, county, post_code\r
+;\r
+</programlisting>\r
                </simplesect>\r
                <simplesect id="_filtering_results_with_the_where_clause">\r
                        <title>Filtering results with the WHERE clause</title>\r
                        <simpara>For example, to restrict the results returned from your <literal>actor.usr_address</literal>\r
                        query to only those rows containing a state value of <emphasis>Connecticut</emphasis>, issue the\r
                        following query:</simpara>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
-                         FROM actor.usr_address\r
-                         WHERE state = 'Connecticut'\r
-                         ORDER BY city, county, post_code\r
-                       ;</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT city, county, state\r
+  FROM actor.usr_address\r
+  WHERE state = 'Connecticut'\r
+  ORDER BY city, county, post_code\r
+;\r
+</programlisting>\r
                        <simpara>You can include more conditions in the <literal>WHERE</literal> clause with the <literal>OR</literal> and <literal>AND</literal>\r
                        operators. For example, to further restrict the results returned from your\r
                        <literal>actor.usr_address</literal> query to only those rows where the state column contains a\r
                        value of <emphasis>Connecticut</emphasis> and the city column contains a value of <emphasis>Hartford</emphasis>,\r
                        issue the following query:</simpara>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
-                         FROM actor.usr_address\r
-                         WHERE state = 'Connecticut'\r
-                           AND city = 'Hartford'\r
-                         ORDER BY city, county, post_code\r
-                       ;</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT city, county, state\r
+  FROM actor.usr_address\r
+  WHERE state = 'Connecticut'\r
+    AND city = 'Hartford'\r
+  ORDER BY city, county, post_code\r
+;\r
+</programlisting>\r
                        <note><simpara>To return rows where the state is <emphasis>Connecticut</emphasis> and the city is <emphasis>Hartford</emphasis> or\r
                        <emphasis>New Haven</emphasis>, you must use parentheses to explicitly group the city value\r
                        conditions together, or else the database will evaluate the <literal>OR city = 'New\r
                        Haven'</literal> clause entirely on its own and match all rows where the city column is\r
                        <emphasis>New Haven</emphasis>, even though the state might not be <emphasis>Connecticut</emphasis>.</simpara></note>\r
                        <formalpara><title>Trouble with OR</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
-                         FROM actor.usr_address\r
-                         WHERE state = 'Connecticut'\r
-                           AND city = 'Hartford' OR city = 'New Haven'\r
-                         ORDER BY city, county, post_code\r
-                       ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT city, county, state\r
+  FROM actor.usr_address\r
+  WHERE state = 'Connecticut'\r
+    AND city = 'Hartford' OR city = 'New Haven'\r
+  ORDER BY city, county, post_code\r
+;\r
 \r
-                       -- Can return unwanted rows because the OR is not grouped!</programlisting>\r
+-- Can return unwanted rows because the OR is not grouped!\r
+</programlisting>\r
                        </para></formalpara>\r
                        <formalpara><title>Grouped OR&#8217;ed conditions</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT city, county, state\r
-                         FROM actor.usr_address\r
-                         WHERE state = 'Connecticut'\r
-                           AND (city = 'Hartford' OR city = 'New Haven')\r
-                         ORDER BY city, county, post_code\r
-                       ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT city, county, state\r
+  FROM actor.usr_address\r
+  WHERE state = 'Connecticut'\r
+    AND (city = 'Hartford' OR city = 'New Haven')\r
+  ORDER BY city, county, post_code\r
+;\r
 \r
-                       -- The parentheses ensure that the OR is applied to the cities, and the\r
-                       -- state in either case must be 'Connecticut'</programlisting>\r
+-- The parentheses ensure that the OR is applied to the cities, and the\r
+-- state in either case must be 'Connecticut'\r
+</programlisting>\r
                        </para></formalpara>\r
                        <simplesect id="_comparison_operators">\r
                                <title>Comparison operators</title>\r
                        <simpara>To return rows from a table where a given column is not <literal>NULL</literal>, use the\r
                        <literal>IS NOT NULL</literal> comparison operator.</simpara>\r
                        <formalpara><title>Retrieving rows where a column is not <literal>NULL</literal></title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT id, first_given_name, family_name\r
-                         FROM actor.usr\r
-                         WHERE second_given_name IS NOT NULL\r
-                       ;</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT id, first_given_name, family_name\r
+  FROM actor.usr\r
+  WHERE second_given_name IS NOT NULL\r
+;\r
+</programlisting>\r
                        </para></formalpara>\r
                        <simpara>Similarly, to return rows from a table where a given column is <literal>NULL</literal>, use\r
                        the <literal>IS NULL</literal> comparison operator.</simpara>\r
                        <formalpara><title>Retrieving rows where a column is <literal>NULL</literal></title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT id, first_given_name, second_given_name, family_name\r
-                         FROM actor.usr\r
-                         WHERE second_given_name IS NULL\r
-                       ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT id, first_given_name, second_given_name, family_name\r
+  FROM actor.usr\r
+  WHERE second_given_name IS NULL\r
+;\r
 \r
-                        id | first_given_name | second_given_name |  family_name\r
-                       ----+------------------+-------------------+----------------\r
-                         1 | Administrator    |                   | System Account\r
-                       (1 row)</programlisting>\r
+ id | first_given_name | second_given_name |  family_name\r
+----+------------------+-------------------+----------------\r
+  1 | Administrator    |                   | System Account\r
+(1 row)\r
+</programlisting>\r
                        </para></formalpara>\r
                        <simpara>Notice that the <literal>NULL</literal> value in the output is displayed as empty space,\r
                        indistinguishable from an empty string; this is the default display method in\r
                        <literal>psql</literal>. You can change the behaviour of <literal>psql</literal> using the <literal>pset</literal> command:</simpara>\r
                        <formalpara><title>Changing the way <literal>NULL</literal> values are displayed in <literal>psql</literal></title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">evergreen=# \pset null '(null)'\r
-                       Null display is '(null)'.\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+evergreen=# \pset null '(null)'\r
+Null display is '(null)'.\r
 \r
-                       SELECT id, first_given_name, second_given_name, family_name\r
-                         FROM actor.usr\r
-                         WHERE second_given_name IS NULL\r
-                       ;\r
+SELECT id, first_given_name, second_given_name, family_name\r
+  FROM actor.usr\r
+  WHERE second_given_name IS NULL\r
+;\r
 \r
-                        id | first_given_name | second_given_name |  family_name\r
-                       ----+------------------+-------------------+----------------\r
-                         1 | Administrator    | (null)            | System Account\r
-                       (1 row)</programlisting>\r
+ id | first_given_name | second_given_name |  family_name\r
+----+------------------+-------------------+----------------\r
+  1 | Administrator    | (null)            | System Account\r
+(1 row)\r
+</programlisting>\r
                        </para></formalpara>\r
                        <simpara>Database queries within programming languages such as Perl and C have\r
                        special methods of checking for <literal>NULL</literal> values in returned results.</simpara>\r
                        <simpara>For example, to change the last name of a user in the <literal>actor.usr</literal> table to\r
                        <literal>L&#8217;estat</literal>, issue the following SQL:</simpara>\r
                        <formalpara><title>Escaping <literal>'</literal> in TEXT values</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">UPDATE actor.usr\r
-                         SET family_name = 'L''estat'\r
-                         WHERE profile IN (\r
-                           SELECT id\r
-                             FROM permission.grp_tree\r
-                             WHERE name = 'Vampire'\r
-                         )\r
-                               ;</programlisting>\r
-                               </para></formalpara>\r
-                               <simpara>When you retrieve the row from the database, the value is displayed with just\r
-                               a single <literal>'</literal> character:</simpara>\r
-                               <programlisting language="sql" linenumbering="unnumbered">SELECT id, family_name\r
-                                 FROM actor.usr\r
-                                 WHERE family_name = 'L''estat'\r
-                               ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+UPDATE actor.usr\r
+  SET family_name = 'L''estat'\r
+  WHERE profile IN (\r
+    SELECT id\r
+      FROM permission.grp_tree\r
+      WHERE name = 'Vampire'\r
+  )\r
+       ;</programlisting>\r
+                       </para></formalpara>\r
+                       <simpara>When you retrieve the row from the database, the value is displayed with just\r
+                       a single <literal>'</literal> character:</simpara>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT id, family_name\r
+  FROM actor.usr\r
+  WHERE family_name = 'L''estat'\r
+;\r
 \r
-                                id | family_name\r
-                               ----+-------------\r
-                                 1 | L'estat\r
-                               (1 row)</programlisting>\r
+ id | family_name\r
+----+-------------\r
+  1 | L'estat\r
+(1 row)\r
+</programlisting>\r
                </simplesect>\r
                <simplesect id="_grouping_and_eliminating_results_with_the_group_by_and_having_clauses">\r
                        <title>Grouping and eliminating results with the GROUP BY and HAVING clauses</title>\r
                        results for a range of values in a single query, rather than requiring you to\r
                        issue one query per target value.</simpara>\r
                        <formalpara><title>Returning unique results of a single column with <literal>GROUP BY</literal></title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT grp\r
-                         FROM permission.grp_perm_map\r
-                         GROUP BY grp\r
-                         ORDER BY grp;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT grp\r
+  FROM permission.grp_perm_map\r
+  GROUP BY grp\r
+  ORDER BY grp;\r
 \r
-                        grp\r
-                       -----+\r
-                          1\r
-                          2\r
-                          3\r
-                          4\r
-                          5\r
-                          6\r
-                          7\r
-                         10\r
-                       (8 rows)</programlisting>\r
+ grp\r
+-----+\r
+   1\r
+   2\r
+   3\r
+   4\r
+   5\r
+   6\r
+   7\r
+  10\r
+(8 rows)\r
+</programlisting>\r
                        </para></formalpara>\r
                        <simpara>While <literal>GROUP BY</literal> can be useful for a single column, it is more often used\r
                        to return the distinct results across multiple columns. For example, the\r
                        following query shows us which groups have permissions at each depth in\r
                        the library hierarchy:</simpara>\r
                        <formalpara><title>Returning unique results of multiple columns with <literal>GROUP BY</literal></title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT grp, depth\r
-                         FROM permission.grp_perm_map\r
-                         GROUP BY grp, depth\r
-                         ORDER BY depth, grp;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT grp, depth\r
+  FROM permission.grp_perm_map\r
+  GROUP BY grp, depth\r
+  ORDER BY depth, grp;\r
 \r
-                        grp | depth\r
-                       -----+-------\r
-                          1 |     0\r
-                          2 |     0\r
-                          3 |     0\r
-                          4 |     0\r
-                          5 |     0\r
-                         10 |     0\r
-                          3 |     1\r
-                          4 |     1\r
-                          5 |     1\r
-                          6 |     1\r
-                          7 |     1\r
-                         10 |     1\r
-                          3 |     2\r
-                          4 |     2\r
-                         10 |     2\r
-                       (15 rows)</programlisting>\r
+ grp | depth\r
+-----+-------\r
+   1 |     0\r
+   2 |     0\r
+   3 |     0\r
+   4 |     0\r
+   5 |     0\r
+  10 |     0\r
+   3 |     1\r
+   4 |     1\r
+   5 |     1\r
+   6 |     1\r
+   7 |     1\r
+  10 |     1\r
+   3 |     2\r
+   4 |     2\r
+  10 |     2\r
+(15 rows)\r
+</programlisting>\r
                        </para></formalpara>\r
                        <simpara>Extending this further, you can use the <literal>COUNT()</literal> aggregate function to\r
                        also return the number of times each unique combination of <literal>grp</literal> and <literal>depth</literal>\r
                        appears in the table. <emphasis>Yes, this is a sneak peek at the use of aggregate\r
                        functions! Keeners.</emphasis></simpara>\r
                        <formalpara><title>Counting unique column combinations with <literal>GROUP BY</literal></title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT grp, depth, COUNT(grp)\r
-                         FROM permission.grp_perm_map\r
-                         GROUP BY grp, depth\r
-                         ORDER BY depth, grp;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT grp, depth, COUNT(grp)\r
+  FROM permission.grp_perm_map\r
+  GROUP BY grp, depth\r
+  ORDER BY depth, grp;\r
 \r
-                        grp | depth | count\r
-                       -----+-------+-------\r
-                          1 |     0 |     6\r
-                          2 |     0 |     2\r
-                          3 |     0 |    45\r
-                          4 |     0 |     3\r
-                          5 |     0 |     5\r
-                         10 |     0 |     1\r
-                          3 |     1 |     3\r
-                          4 |     1 |     4\r
-                          5 |     1 |     1\r
-                          6 |     1 |     9\r
-                          7 |     1 |     5\r
-                         10 |     1 |    10\r
-                          3 |     2 |    24\r
-                          4 |     2 |     8\r
-                         10 |     2 |     7\r
-                       (15 rows)</programlisting>\r
+ grp | depth | count\r
+-----+-------+-------\r
+   1 |     0 |     6\r
+   2 |     0 |     2\r
+   3 |     0 |    45\r
+   4 |     0 |     3\r
+   5 |     0 |     5\r
+  10 |     0 |     1\r
+   3 |     1 |     3\r
+   4 |     1 |     4\r
+   5 |     1 |     1\r
+   6 |     1 |     9\r
+   7 |     1 |     5\r
+  10 |     1 |    10\r
+   3 |     2 |    24\r
+   4 |     2 |     8\r
+  10 |     2 |     7\r
+(15 rows)\r
+</programlisting>\r
                        </para></formalpara>\r
                        <simpara>You can use the <literal>WHERE</literal> clause to restrict the returned results before grouping\r
                        is applied to the results. The following query restricts the results to those\r
                        rows that have a depth of 0.</simpara>\r
                        <formalpara><title>Using the <literal>WHERE</literal> clause with <literal>GROUP BY</literal></title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT grp, COUNT(grp)\r
-                         FROM permission.grp_perm_map\r
-                         WHERE depth = 0\r
-                         GROUP BY grp\r
-                         ORDER BY 2 DESC\r
-                       ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT grp, COUNT(grp)\r
+  FROM permission.grp_perm_map\r
+  WHERE depth = 0\r
+  GROUP BY grp\r
+  ORDER BY 2 DESC\r
+;\r
 \r
-                        grp | count\r
-                       -----+-------\r
-                          3 |    45\r
-                          1 |     6\r
-                          5 |     5\r
-                          4 |     3\r
-                          2 |     2\r
-                         10 |     1\r
-                       (6 rows)</programlisting>\r
+ grp | count\r
+-----+-------\r
+   3 |    45\r
+   1 |     6\r
+   5 |     5\r
+   4 |     3\r
+   2 |     2\r
+  10 |     1\r
+(6 rows)\r
+</programlisting>\r
                        </para></formalpara>\r
                        <simpara>To restrict results after grouping has been applied to the rows, use the\r
                        <literal>HAVING</literal> clause; this is typically used to restrict results based on\r
                        the following query restricts the returned rows to those that have more than\r
                        5 occurrences of the same value for <literal>grp</literal> in the table.</simpara>\r
                        <formalpara><title><literal>GROUP BY</literal> restricted by a <literal>HAVING</literal> clause</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT grp, COUNT(grp)\r
-                         FROM permission.grp_perm_map\r
-                         GROUP BY grp\r
-                         HAVING COUNT(grp) &gt; 5\r
-                       ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT grp, COUNT(grp)\r
+  FROM permission.grp_perm_map\r
+  GROUP BY grp\r
+  HAVING COUNT(grp) &gt; 5\r
+;\r
 \r
-                        grp | count\r
-                       -----+-------\r
-                          6 |     9\r
-                          4 |    15\r
-                          5 |     6\r
-                          1 |     6\r
-                          3 |    72\r
-                         10 |    18\r
-                       (6 rows)</programlisting>\r
+ grp | count\r
+-----+-------\r
+   6 |     9\r
+   4 |    15\r
+   5 |     6\r
+   1 |     6\r
+   3 |    72\r
+  10 |    18\r
+(6 rows)\r
+</programlisting>\r
                        </para></formalpara>\r
                </simplesect>\r
                <simplesect id="_eliminating_duplicate_results_with_the_distinct_keyword">\r
                        them, then applying the <literal>DISTINCT</literal> keyword might be a sign that you are\r
                        papering over a real problem.</simpara>\r
                        <formalpara><title>Returning unique results of multiple columns with <literal>DISTINCT</literal></title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT DISTINCT grp, depth\r
-                         FROM permission.grp_perm_map\r
-                         ORDER BY depth, grp\r
-                       ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT DISTINCT grp, depth\r
+  FROM permission.grp_perm_map\r
+  ORDER BY depth, grp\r
+;\r
 \r
-                        grp | depth\r
-                       -----+-------\r
-                          1 |     0\r
-                          2 |     0\r
-                          3 |     0\r
-                          4 |     0\r
-                          5 |     0\r
-                         10 |     0\r
-                          3 |     1\r
-                          4 |     1\r
-                          5 |     1\r
-                          6 |     1\r
-                          7 |     1\r
-                         10 |     1\r
-                          3 |     2\r
-                          4 |     2\r
-                         10 |     2\r
-                       (15 rows)</programlisting>\r
+ grp | depth\r
+-----+-------\r
+   1 |     0\r
+   2 |     0\r
+   3 |     0\r
+   4 |     0\r
+   5 |     0\r
+  10 |     0\r
+   3 |     1\r
+   4 |     1\r
+   5 |     1\r
+   6 |     1\r
+   7 |     1\r
+  10 |     1\r
+   3 |     2\r
+   4 |     2\r
+  10 |     2\r
+(15 rows)\r
+</programlisting>\r
                        </para></formalpara>\r
                </simplesect>\r
                <simplesect id="_paging_through_results_with_the_limit_and_offset_clauses">\r
                        example, in the following query we list the five most frequently used\r
                        circulation modifiers:</simpara>\r
                        <formalpara><title>Using the <literal>LIMIT</literal> clause to restrict results</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT circ_modifier, COUNT(circ_modifier)\r
-                         FROM asset.copy\r
-                         GROUP BY circ_modifier\r
-                         ORDER BY 2 DESC\r
-                         LIMIT 5\r
-                       ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT circ_modifier, COUNT(circ_modifier)\r
+  FROM asset.copy\r
+  GROUP BY circ_modifier\r
+  ORDER BY 2 DESC\r
+  LIMIT 5\r
+;\r
 \r
-                        circ_modifier | count\r
-                       ---------------+--------\r
-                        CIRC          | 741995\r
-                        BOOK          | 636199\r
-                        SER           | 265906\r
-                        DOC           | 191598\r
-                        LAW MONO      | 126627\r
-                       (5 rows)</programlisting>\r
+ circ_modifier | count\r
+---------------+--------\r
+ CIRC          | 741995\r
+ BOOK          | 636199\r
+ SER           | 265906\r
+ DOC           | 191598\r
+ LAW MONO      | 126627\r
+(5 rows)\r
+</programlisting>\r
                        </para></formalpara>\r
                        <simpara>When you use the <literal>LIMIT</literal> clause to restrict the total number of rows returned\r
                        by your query, you can also use the <literal>OFFSET</literal> clause to determine which subset\r
                        <simpara>In the following example, we use the <literal>OFFSET</literal> clause to get results 6 through\r
                        10 from the same query that we prevously executed.</simpara>\r
                        <formalpara><title>Using the <literal>OFFSET</literal> clause to return a specific subset of rows</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT circ_modifier, COUNT(circ_modifier)\r
-                         FROM asset.copy\r
-                         GROUP BY circ_modifier\r
-                         ORDER BY 2 DESC\r
-                         LIMIT 5\r
-                         OFFSET 5\r
-                       ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT circ_modifier, COUNT(circ_modifier)\r
+  FROM asset.copy\r
+  GROUP BY circ_modifier\r
+  ORDER BY 2 DESC\r
+  LIMIT 5\r
+  OFFSET 5\r
+;\r
 \r
-                        circ_modifier | count\r
-                       ---------------+--------\r
-                        LAW SERIAL    | 102758\r
-                        DOCUMENTS     |  86215\r
-                        BOOK_WEB      |  63786\r
-                        MFORM SER     |  39917\r
-                        REF           |  34380\r
-                       (5 rows)</programlisting>\r
+ circ_modifier | count\r
+---------------+--------\r
+ LAW SERIAL    | 102758\r
+ DOCUMENTS     |  86215\r
+ BOOK_WEB      |  63786\r
+ MFORM SER     |  39917\r
+ REF           |  34380\r
+(5 rows)\r
+</programlisting>\r
                        </para></formalpara>\r
                </simplesect>\r
        </section>\r
                                used to convert text values to upper-case, modifies the results in the\r
                                following set of queries:</simpara>\r
                                <formalpara><title>Using the UPPER() scalar function to convert text values to upper-case</title><para>\r
-                               <programlisting language="sql" linenumbering="unnumbered">-- First, without the UPPER() function for comparison\r
-                               SELECT shortname, name\r
-                                 FROM actor.org_unit\r
-                                 WHERE id &lt; 4\r
-                               ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+-- First, without the UPPER() function for comparison\r
+SELECT shortname, name\r
+  FROM actor.org_unit\r
+  WHERE id &lt; 4\r
+;\r
 \r
                               shortname |         name\r
-                               -----------+-----------------------\r
                               CONS      | Example Consortium\r
                               SYS1      | Example System 1\r
                               SYS2      | Example System 2\r
-                               (3 rows)\r
+ shortname |         name\r
+-----------+-----------------------\r
+ CONS      | Example Consortium\r
+ SYS1      | Example System 1\r
+ SYS2      | Example System 2\r
+(3 rows)\r
 \r
-                               -- Now apply the UPPER() function to the name column\r
-                               SELECT shortname, UPPER(name)\r
-                                 FROM actor.org_unit\r
-                                 WHERE id &lt; 4\r
-                               ;\r
+-- Now apply the UPPER() function to the name column\r
+SELECT shortname, UPPER(name)\r
+  FROM actor.org_unit\r
+  WHERE id &lt; 4\r
+;\r
 \r
-                                shortname |       upper\r
-                               -----------+--------------------\r
-                                CONS      | EXAMPLE CONSORTIUM\r
-                                SYS1      | EXAMPLE SYSTEM 1\r
-                                SYS2      | EXAMPLE SYSTEM 2\r
-                               (3 rows)</programlisting>\r
+ shortname |       upper\r
+-----------+--------------------\r
+ CONS      | EXAMPLE CONSORTIUM\r
+ SYS1      | EXAMPLE SYSTEM 1\r
+ SYS2      | EXAMPLE SYSTEM 2\r
+(3 rows)\r
+</programlisting>\r
                                </para></formalpara>\r
                                <simpara>There are so many scalar functions in PostgreSQL that we cannot cover them\r
                                all here, but we can list some of the most commonly used functions:</simpara>\r
                        returned by the main SELECT statement to only those locations that have an\r
                        <literal>opac_visible</literal> value of <literal>TRUE</literal>:</simpara>\r
                        <formalpara><title>Sub-select example</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT call_number\r
-                         FROM asset.copy\r
-                         WHERE deleted IS FALSE\r
-                           AND location IN (\r
-                           SELECT id\r
-                             FROM asset.copy_location\r
-                             WHERE opac_visible IS TRUE\r
-                         )\r
-                       ;</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT call_number\r
+  FROM asset.copy\r
+  WHERE deleted IS FALSE\r
+    AND location IN (\r
+    SELECT id\r
+      FROM asset.copy_location\r
+      WHERE opac_visible IS TRUE\r
+  )\r
+;\r
+</programlisting>\r
                        </para></formalpara>\r
                        <simpara>Sub-selects can be an approachable way to breaking down a problem that\r
                        requires matching values between different tables, and often result in\r
                                we have to fully qualify the column names in our queries with the schema and\r
                                table names.</simpara>\r
                                <formalpara><title>A simple inner join</title><para>\r
-                               <programlisting language="sql" linenumbering="unnumbered">SELECT *\r
-                                 FROM actor.usr\r
-                                   INNER JOIN actor.org_unit ON actor.usr.home_ou = actor.org_unit.id\r
-                                   WHERE actor.org_unit.shortname = 'CONS'\r
-                               ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT *\r
+  FROM actor.usr\r
+    INNER JOIN actor.org_unit ON actor.usr.home_ou = actor.org_unit.id\r
+    WHERE actor.org_unit.shortname = 'CONS'\r
+;\r
 \r
-                               -[ RECORD 1 ]------------------+---------------------------------\r
-                               id                             | 1\r
-                               card                           | 1\r
-                               profile                        | 1\r
-                               usrname                        | admin\r
-                               email                          |\r
-                               ...\r
-                               mailing_address                |\r
-                               billing_address                |\r
-                               home_ou                        | 1\r
-                               ...\r
-                               claims_never_checked_out_count | 0\r
-                               id                             | 1\r
-                               parent_ou                      |\r
-                               ou_type                        | 1\r
-                               ill_address                    | 1\r
-                               holds_address                  | 1\r
-                               mailing_address                | 1\r
-                               billing_address                | 1\r
-                               shortname                      | CONS\r
-                               name                           | Example Consortium\r
-                               email                          |\r
-                               phone                          |\r
-                               opac_visible                   | t\r
-                               fiscal_calendar                | 1</programlisting>\r
+-[ RECORD 1 ]------------------+---------------------------------\r
+id                             | 1\r
+card                           | 1\r
+profile                        | 1\r
+usrname                        | admin\r
+email                          |\r
+...\r
+mailing_address                |\r
+billing_address                |\r
+home_ou                        | 1\r
+...\r
+claims_never_checked_out_count | 0\r
+id                             | 1\r
+parent_ou                      |\r
+ou_type                        | 1\r
+ill_address                    | 1\r
+holds_address                  | 1\r
+mailing_address                | 1\r
+billing_address                | 1\r
+shortname                      | CONS\r
+name                           | Example Consortium\r
+email                          |\r
+phone                          |\r
+opac_visible                   | t\r
+fiscal_calendar                | 1\r
+</programlisting>\r
                                </para></formalpara>\r
                                <simpara>Of course, you do not have to return every column from the joined tables;\r
                                you can (and should) continue to specify only the columns that you want to\r
                                joining the <literal>actor.org_unit</literal> table to give us access to the user&#8217;s home\r
                                library:</simpara>\r
                                <formalpara><title>Borrower Count by Profile (Adult, Child, etc)/Library</title><para>\r
-                               <programlisting language="sql" linenumbering="unnumbered">SELECT permission.grp_tree.name, actor.org_unit.name, COUNT(permission.grp_tree.name)\r
-                                 FROM actor.usr\r
-                                   INNER JOIN permission.grp_tree\r
-                                     ON actor.usr.profile = permission.grp_tree.id\r
-                                   INNER JOIN actor.org_unit\r
-                                     ON actor.org_unit.id = actor.usr.home_ou\r
-                                 WHERE actor.usr.deleted IS FALSE\r
-                                 GROUP BY permission.grp_tree.name, actor.org_unit.name\r
-                                 ORDER BY actor.org_unit.name, permission.grp_tree.name\r
-                               ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT permission.grp_tree.name, actor.org_unit.name, COUNT(permission.grp_tree.name)\r
+  FROM actor.usr\r
+    INNER JOIN permission.grp_tree\r
+      ON actor.usr.profile = permission.grp_tree.id\r
+    INNER JOIN actor.org_unit\r
+      ON actor.org_unit.id = actor.usr.home_ou\r
+  WHERE actor.usr.deleted IS FALSE\r
+  GROUP BY permission.grp_tree.name, actor.org_unit.name\r
+  ORDER BY actor.org_unit.name, permission.grp_tree.name\r
+;\r
 \r
-                                name  |        name        | count\r
-                               -------+--------------------+-------\r
-                                Users | Example Consortium |     1\r
-                               (1 row)</programlisting>\r
+ name  |        name        | count\r
+-------+--------------------+-------\r
+ Users | Example Consortium |     1\r
+(1 row)\r
+</programlisting>\r
                                </para></formalpara>\r
                        </simplesect>\r
                        <simplesect id="_aliases">\r
                                example, we can write the previous INNER JOIN statement example using aliases\r
                                instead of fully-qualified identifiers:</simpara>\r
                                <formalpara><title>Borrower Count by Profile (using aliases)</title><para>\r
-                               <programlisting language="sql" linenumbering="unnumbered">SELECT pgt.name AS "Profile", aou.name AS "Library", COUNT(pgt.name) AS "Count"\r
-                                 FROM actor.usr au\r
-                                   INNER JOIN permission.grp_tree pgt\r
-                                     ON au.profile = pgt.id\r
-                                   INNER JOIN actor.org_unit aou\r
-                                     ON aou.id = au.home_ou\r
-                                 WHERE au.deleted IS FALSE\r
-                                 GROUP BY pgt.name, aou.name\r
-                                 ORDER BY aou.name, pgt.name\r
-                               ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT pgt.name AS "Profile", aou.name AS "Library", COUNT(pgt.name) AS "Count"\r
+  FROM actor.usr au\r
+    INNER JOIN permission.grp_tree pgt\r
+      ON au.profile = pgt.id\r
+    INNER JOIN actor.org_unit aou\r
+      ON aou.id = au.home_ou\r
+  WHERE au.deleted IS FALSE\r
+  GROUP BY pgt.name, aou.name\r
+  ORDER BY aou.name, pgt.name\r
+;\r
 \r
-                                Profile |      Library       | Count\r
-                               ---------+--------------------+-------\r
-                                Users   | Example Consortium |     1\r
-                               (1 row)</programlisting>\r
+ Profile |      Library       | Count\r
+---------+--------------------+-------\r
+ Users   | Example Consortium |     1\r
+(1 row)\r
+</programlisting>\r
                                </para></formalpara>\r
                                <simpara>A nice side effect of declaring an alias for your columns is that the alias\r
                                is used as the column header in the results table. The previous version of\r
                                </listitem>\r
                                </itemizedlist>\r
                                <formalpara><title>Base tables for the OUTER JOIN examples</title><para>\r
-                               <programlisting language="sql" linenumbering="unnumbered">SELECT * FROM aaa;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT * FROM aaa;\r
 \r
                               id | stuff\r
-                               ----+-------\r
-                                 1 | one\r
-                                 2 | two\r
-                                 3 | three\r
-                                 4 | four\r
-                                 5 | five\r
-                               (5 rows)\r
+ id | stuff\r
+----+-------\r
+  1 | one\r
+  2 | two\r
+  3 | three\r
+  4 | four\r
+  5 | five\r
+(5 rows)\r
 \r
-                               SELECT * FROM bbb;\r
+SELECT * FROM bbb;\r
 \r
-                                id | stuff |   foo\r
-                               ----+-------+----------\r
-                                 1 | one   | oneone\r
-                                 2 | two   | twotwo\r
-                                 5 | five  | fivefive\r
-                                 6 | six   | sixsix\r
-                               (4 rows)</programlisting>\r
+ id | stuff |   foo\r
+----+-------+----------\r
+  1 | one   | oneone\r
+  2 | two   | twotwo\r
+  5 | five  | fivefive\r
+  6 | six   | sixsix\r
+(4 rows)\r
+</programlisting>\r
                                </para></formalpara>\r
                                <formalpara><title>Example of a LEFT OUTER JOIN</title><para>\r
-                               <programlisting language="sql" linenumbering="unnumbered">SELECT * FROM aaa\r
-                                 LEFT OUTER JOIN bbb ON aaa.id = bbb.id\r
-                               ;\r
-                                id | stuff | id | stuff |   foo\r
-                               ----+-------+----+-------+----------\r
-                                 1 | one   |  1 | one   | oneone\r
-                                 2 | two   |  2 | two   | twotwo\r
-                                 3 | three |    |       |\r
-                                 4 | four  |    |       |\r
-                                 5 | five  |  5 | five  | fivefive\r
-                               (5 rows)</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT * FROM aaa\r
+  LEFT OUTER JOIN bbb ON aaa.id = bbb.id\r
+;\r
+ id | stuff | id | stuff |   foo\r
+----+-------+----+-------+----------\r
+  1 | one   |  1 | one   | oneone\r
+  2 | two   |  2 | two   | twotwo\r
+  3 | three |    |       |\r
+  4 | four  |    |       |\r
+  5 | five  |  5 | five  | fivefive\r
+(5 rows)\r
+</programlisting>\r
                                </para></formalpara>\r
                                <formalpara><title>Example of a RIGHT OUTER JOIN</title><para>\r
-                               <programlisting language="sql" linenumbering="unnumbered">SELECT * FROM aaa\r
-                                 RIGHT OUTER JOIN bbb ON aaa.id = bbb.id\r
-                               ;\r
-                                id | stuff | id | stuff |   foo\r
-                               ----+-------+----+-------+----------\r
-                                 1 | one   |  1 | one   | oneone\r
-                                 2 | two   |  2 | two   | twotwo\r
-                                 5 | five  |  5 | five  | fivefive\r
-                                   |       |  6 | six   | sixsix\r
-                               (4 rows)</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT * FROM aaa\r
+  RIGHT OUTER JOIN bbb ON aaa.id = bbb.id\r
+;\r
+ id | stuff | id | stuff |   foo\r
+----+-------+----+-------+----------\r
+  1 | one   |  1 | one   | oneone\r
+  2 | two   |  2 | two   | twotwo\r
+  5 | five  |  5 | five  | fivefive\r
+    |       |  6 | six   | sixsix\r
+(4 rows)\r
+</programlisting>\r
                                </para></formalpara>\r
                                <formalpara><title>Example of a FULL OUTER JOIN</title><para>\r
-                               <programlisting language="sql" linenumbering="unnumbered">SELECT * FROM aaa\r
-                                 FULL OUTER JOIN bbb ON aaa.id = bbb.id\r
-                               ;\r
-                                id | stuff | id | stuff |   foo\r
-                               ----+-------+----+-------+----------\r
-                                 1 | one   |  1 | one   | oneone\r
-                                 2 | two   |  2 | two   | twotwo\r
-                                 3 | three |    |       |\r
-                                 4 | four  |    |       |\r
-                                 5 | five  |  5 | five  | fivefive\r
-                                   |       |  6 | six   | sixsix\r
-                               (6 rows)</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT * FROM aaa\r
+  FULL OUTER JOIN bbb ON aaa.id = bbb.id\r
+;\r
+ id | stuff | id | stuff |   foo\r
+----+-------+----+-------+----------\r
+  1 | one   |  1 | one   | oneone\r
+  2 | two   |  2 | two   | twotwo\r
+  3 | three |    |       |\r
+  4 | four  |    |       |\r
+  5 | five  |  5 | five  | fivefive\r
+    |       |  6 | six   | sixsix\r
+(6 rows)\r
+</programlisting>\r
                                </para></formalpara>\r
                        </simplesect>\r
                        <simplesect id="_self_joins">\r
                        of columns with compatible data types: the union, intersection, and difference\r
                        operators.</simpara>\r
                        <formalpara><title>Base tables for the set operation examples</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT * FROM aaa;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT * FROM aaa;\r
 \r
-                        id | stuff\r
-                       ----+-------\r
-                         1 | one\r
-                         2 | two\r
-                         3 | three\r
-                         4 | four\r
-                         5 | five\r
-                       (5 rows)\r
+       id | stuff\r
+       ----+-------\r
+       1 | one\r
+       2 | two\r
+       3 | three\r
+       4 | four\r
+       5 | five\r
+       (5 rows)\r
 \r
-                       SELECT * FROM bbb;\r
+SELECT * FROM bbb;\r
 \r
-                        id | stuff |   foo\r
-                       ----+-------+----------\r
-                         1 | one   | oneone\r
-                         2 | two   | twotwo\r
-                         5 | five  | fivefive\r
-                         6 | six   | sixsix\r
-                       (4 rows)</programlisting>\r
+       id | stuff |   foo\r
+       ----+-------+----------\r
+       1 | one   | oneone\r
+       2 | two   | twotwo\r
+       5 | five  | fivefive\r
+       6 | six   | sixsix\r
+(4 rows)\r
+</programlisting>\r
                        </para></formalpara>\r
                        <simplesect id="_union">\r
                                <title>Union</title>\r
                                does not return any duplicate rows. To return duplicate rows, use the\r
                                <literal>UNION ALL</literal> operator.</simpara>\r
                                <formalpara><title>Example of a UNION set operation</title><para>\r
-                               <programlisting language="sql" linenumbering="unnumbered">-- The parentheses are not required, but are intended to help\r
-                               -- illustrate the sets participating in the set operation\r
-                               (\r
-                                 SELECT id, stuff\r
-                                   FROM aaa\r
-                               )\r
-                               UNION\r
-                               (\r
-                                 SELECT id, stuff\r
-                                   FROM bbb\r
-                               )\r
-                               ORDER BY 1\r
-                               ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+-- The parentheses are not required, but are intended to help\r
+-- illustrate the sets participating in the set operation\r
+(\r
+  SELECT id, stuff\r
+    FROM aaa\r
+)\r
+UNION\r
+(\r
+  SELECT id, stuff\r
+    FROM bbb\r
+)\r
+ORDER BY 1\r
+;\r
 \r
-                                id | stuff\r
-                               ----+-------\r
-                                 1 | one\r
-                                 2 | two\r
-                                 3 | three\r
-                                 4 | four\r
-                                 5 | five\r
-                                 6 | six\r
-                               (6 rows)</programlisting>\r
+ id | stuff\r
+----+-------\r
+  1 | one\r
+  2 | two\r
+  3 | three\r
+  4 | four\r
+  5 | five\r
+  6 | six\r
+(6 rows)\r
+</programlisting>\r
                                </para></formalpara>\r
                        </simplesect>\r
                        <simplesect id="_intersection">\r
                                both the left-hand and right-hand tables. To return duplicate rows, use the\r
                                <literal>INTERSECT ALL</literal> operator.</simpara>\r
                                <formalpara><title>Example of an INTERSECT set operation</title><para>\r
-                               <programlisting language="sql" linenumbering="unnumbered">(\r
-                                 SELECT id, stuff\r
-                                   FROM aaa\r
-                               )\r
-                               INTERSECT\r
-                               (\r
-                                 SELECT id, stuff\r
-                                   FROM bbb\r
-                               )\r
-                               ORDER BY 1\r
-                               ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+(\r
+  SELECT id, stuff\r
+    FROM aaa\r
+)\r
+INTERSECT\r
+(\r
+  SELECT id, stuff\r
+    FROM bbb\r
+)\r
+ORDER BY 1\r
+;\r
 \r
-                                id | stuff\r
-                               ----+-------\r
-                                 1 | one\r
-                                 2 | two\r
-                                 5 | five\r
-                               (3 rows)</programlisting>\r
+ id | stuff\r
+----+-------\r
+  1 | one\r
+  2 | two\r
+  5 | five\r
+(3 rows)\r
+</programlisting>\r
                                </para></formalpara>\r
                        </simplesect>\r
                        <simplesect id="_difference">\r
                                exist in the right-hand table. You are effectively subtracting the common\r
                                rows from the left-hand table.</simpara>\r
                                <formalpara><title>Example of an EXCEPT set operation</title><para>\r
-                               <programlisting language="sql" linenumbering="unnumbered">(\r
-                                 SELECT id, stuff\r
-                                   FROM aaa\r
-                               )\r
-                               EXCEPT\r
-                               (\r
-                                 SELECT id, stuff\r
-                                   FROM bbb\r
-                               )\r
-                               ORDER BY 1\r
-                               ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+(\r
+  SELECT id, stuff\r
+    FROM aaa\r
+)\r
+EXCEPT\r
+(\r
+  SELECT id, stuff\r
+    FROM bbb\r
+)\r
+ORDER BY 1\r
+;\r
 \r
                               id | stuff\r
-                               ----+-------\r
-                                 3 | three\r
-                                 4 | four\r
-                               (2 rows)\r
+ id | stuff\r
+----+-------\r
+  3 | three\r
+  4 | four\r
+(2 rows)\r
 \r
-                               -- Order matters: switch the left-hand and right-hand tables\r
-                               -- and you get a different result\r
-                               (\r
-                                 SELECT id, stuff\r
-                                   FROM bbb\r
-                               )\r
-                               EXCEPT\r
-                               (\r
-                                 SELECT id, stuff\r
-                                   FROM aaa\r
-                               )\r
-                               ORDER BY 1\r
-                               ;\r
+-- Order matters: switch the left-hand and right-hand tables\r
+-- and you get a different result\r
+(\r
+  SELECT id, stuff\r
+    FROM bbb\r
+)\r
+EXCEPT\r
+(\r
+  SELECT id, stuff\r
+    FROM aaa\r
+)\r
+ORDER BY 1\r
+;\r
 \r
-                                id | stuff\r
-                               ----+-------\r
-                                 6 | six\r
-                               (1 row)</programlisting>\r
+ id | stuff\r
+----+-------\r
+  6 | six\r
+(1 row)\r
+</programlisting>\r
                                </para></formalpara>\r
                        </simplesect>\r
                </simplesect>\r
                        and a <literal>SELECT</literal> statement on which the view is built.</simpara>\r
                        <simpara>The following example creates a view based on our borrower profile count:</simpara>\r
                        <formalpara><title>Creating a view</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">CREATE VIEW actor.borrower_profile_count AS\r
-                         SELECT pgt.name AS "Profile", aou.name AS "Library", COUNT(pgt.name) AS "Count"\r
-                           FROM actor.usr au\r
-                             INNER JOIN permission.grp_tree pgt\r
-                               ON au.profile = pgt.id\r
-                             INNER JOIN actor.org_unit aou\r
-                               ON aou.id = au.home_ou\r
-                           WHERE au.deleted IS FALSE\r
-                           GROUP BY pgt.name, aou.name\r
-                           ORDER BY aou.name, pgt.name\r
-                       ;</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+CREATE VIEW actor.borrower_profile_count AS\r
+  SELECT pgt.name AS "Profile", aou.name AS "Library", COUNT(pgt.name) AS "Count"\r
+    FROM actor.usr au\r
+      INNER JOIN permission.grp_tree pgt\r
+       ON au.profile = pgt.id\r
+      INNER JOIN actor.org_unit aou\r
+       ON aou.id = au.home_ou\r
+    WHERE au.deleted IS FALSE\r
+    GROUP BY pgt.name, aou.name\r
+    ORDER BY aou.name, pgt.name\r
+;\r
+</programlisting>\r
                        </para></formalpara>\r
                        <simpara>When you subsequently select results from the view, you can apply additional\r
                        <literal>WHERE</literal> clauses to filter the results, or <literal>ORDER BY</literal> clauses to change the\r
                        Then we issue a <literal>SELECT</literal> statement with a <literal>WHERE</literal> clause to further filter the\r
                        results.</simpara>\r
                        <formalpara><title>Selecting results from a view</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT * FROM actor.borrower_profile_count;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT * FROM actor.borrower_profile_count;\r
 \r
-                                 Profile           | Library                    | Count\r
-                       ----------------------------+----------------------------+-------\r
                       Faculty                    | University Library         |   208\r
                       Graduate                   | University Library         |    16\r
                       Patrons                    | University Library         |    62\r
-                       ...\r
+         Profile           | Library                    | Count\r
+----------------------------+----------------------------+-------\r
+ Faculty                    | University Library         |   208\r
+ Graduate                   | University Library         |    16\r
+ Patrons                    | University Library         |    62\r
+...\r
 \r
-                       -- You can still filter your results with WHERE clauses\r
-                       SELECT *\r
-                         FROM actor.borrower_profile_count\r
-                         WHERE "Profile" = 'Faculty';\r
+-- You can still filter your results with WHERE clauses\r
+SELECT *\r
+  FROM actor.borrower_profile_count\r
+  WHERE "Profile" = 'Faculty';\r
 \r
-                        Profile | Library                    | Count\r
-                       ---------+----------------------------+-------\r
-                        Faculty | University Library         |   208\r
-                        Faculty | College Library            |    64\r
-                        Faculty | College Library 2          |   102\r
-                        Faculty | University Library 2       |   776\r
-                       (4 rows)</programlisting>\r
+ Profile | Library                    | Count\r
+---------+----------------------------+-------\r
+ Faculty | University Library         |   208\r
+ Faculty | College Library            |    64\r
+ Faculty | College Library 2          |   102\r
+ Faculty | University Library 2       |   776\r
+(4 rows)\r
+</programlisting>\r
                        </para></formalpara>\r
                </simplesect>\r
                <simplesect id="_inheritance">\r
                row in the <literal>biblio.record_entry</literal> table with every row in the <literal>metabib.full_rec</literal>\r
                view:</simpara>\r
                <formalpara><title>Query plan for a terrible query</title><para>\r
-               <programlisting language="sql" linenumbering="unnumbered">EXPLAIN SELECT *\r
-                 FROM biblio.record_entry\r
-                   FULL OUTER JOIN metabib.full_rec ON 1=1\r
-               ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+EXPLAIN SELECT *\r
+  FROM biblio.record_entry\r
+    FULL OUTER JOIN metabib.full_rec ON 1=1\r
+;\r
 \r
-                                                  QUERY PLAN\r
-               -------------------------------------------------------------------------------//\r
-                Merge Full Join  (cost=0.00..4959156437783.60 rows=132415734100864 width=1379)\r
-                  -&gt;  Seq Scan on record_entry  (cost=0.00..400634.16 rows=2013416 width=1292)\r
-                  -&gt;  Seq Scan on real_full_rec  (cost=0.00..1640972.04 rows=65766704 width=87)\r
-               (3 rows)</programlisting>\r
+                                  QUERY PLAN\r
+-------------------------------------------------------------------------------//\r
+ Merge Full Join  (cost=0.00..4959156437783.60 rows=132415734100864 width=1379)\r
+   -&gt;  Seq Scan on record_entry  (cost=0.00..400634.16 rows=2013416 width=1292)\r
+   -&gt;  Seq Scan on real_full_rec  (cost=0.00..1640972.04 rows=65766704 width=87)\r
+(3 rows)\r
+</programlisting>\r
                </para></formalpara>\r
                <simpara>This query plan shows that the query would return 132415734100864 rows, and it\r
                plans to accomplish what you asked for by sequentially scanning (<emphasis>Seq Scan</emphasis>)\r
                the left-hand table with every row in the right-hand table and take the saner\r
                approach of using an <literal>INNER JOIN</literal> where the join condition is on the record ID.</simpara>\r
                <formalpara><title>Query plan for a less terrible query</title><para>\r
-               <programlisting language="sql" linenumbering="unnumbered">EXPLAIN SELECT *\r
-                 FROM biblio.record_entry bre\r
-                   INNER JOIN metabib.full_rec mfr ON mfr.record = bre.id;\r
-                                                       QUERY PLAN\r
-               ----------------------------------------------------------------------------------------//\r
-                Hash Join  (cost=750229.86..5829273.98 rows=65766704 width=1379)\r
-                  Hash Cond: (real_full_rec.record = bre.id)\r
-                  -&gt;  Seq Scan on real_full_rec  (cost=0.00..1640972.04 rows=65766704 width=87)\r
-                  -&gt;  Hash  (cost=400634.16..400634.16 rows=2013416 width=1292)\r
-                        -&gt;  Seq Scan on record_entry bre  (cost=0.00..400634.16 rows=2013416 width=1292)\r
-               (5 rows)</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+EXPLAIN SELECT *\r
+  FROM biblio.record_entry bre\r
+    INNER JOIN metabib.full_rec mfr ON mfr.record = bre.id;\r
+                                       QUERY PLAN\r
+----------------------------------------------------------------------------------------//\r
+ Hash Join  (cost=750229.86..5829273.98 rows=65766704 width=1379)\r
+   Hash Cond: (real_full_rec.record = bre.id)\r
+   -&gt;  Seq Scan on real_full_rec  (cost=0.00..1640972.04 rows=65766704 width=87)\r
+   -&gt;  Hash  (cost=400634.16..400634.16 rows=2013416 width=1292)\r
+        -&gt;  Seq Scan on record_entry bre  (cost=0.00..400634.16 rows=2013416 width=1292)\r
+(5 rows)\r
+</programlisting>\r
                </para></formalpara>\r
                <simpara>This time, we will return 65766704 rows - still way too many rows. We forgot\r
                to include a <literal>WHERE</literal> clause to limit the results to something meaningful. In\r
                the following example, we will limit the results to deleted records that were\r
                modified in the last month.</simpara>\r
                <formalpara><title>Query plan for a realistic query</title><para>\r
-               <programlisting language="sql" linenumbering="unnumbered">EXPLAIN SELECT *\r
-                 FROM biblio.record_entry bre\r
-                   INNER JOIN metabib.full_rec mfr ON mfr.record = bre.id\r
-                 WHERE bre.deleted IS TRUE\r
-                   AND DATE_TRUNC('MONTH', bre.edit_date) &gt;\r
-                       DATE_TRUNC ('MONTH', NOW() - '1 MONTH'::INTERVAL)\r
-               ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+EXPLAIN SELECT *\r
+  FROM biblio.record_entry bre\r
+    INNER JOIN metabib.full_rec mfr ON mfr.record = bre.id\r
+  WHERE bre.deleted IS TRUE\r
+    AND DATE_TRUNC('MONTH', bre.edit_date) &gt;\r
+       DATE_TRUNC ('MONTH', NOW() - '1 MONTH'::INTERVAL)\r
+;\r
 \r
-                                                       QUERY PLAN\r
-               ----------------------------------------------------------------------------------------//\r
-                Hash Join  (cost=5058.86..2306218.81 rows=201669 width=1379)\r
-                  Hash Cond: (real_full_rec.record = bre.id)\r
-                  -&gt;  Seq Scan on real_full_rec  (cost=0.00..1640972.04 rows=65766704 width=87)\r
-                  -&gt;  Hash  (cost=4981.69..4981.69 rows=6174 width=1292)\r
-                        -&gt;  Index Scan using biblio_record_entry_deleted on record_entry bre\r
-                                (cost=0.00..4981.69 rows=6174 width=1292)\r
-                              Index Cond: (deleted = true)\r
-                              Filter: ((deleted IS TRUE) AND (date_trunc('MONTH'::text, edit_date)\r
-                                &gt; date_trunc('MONTH'::text, (now() - '1 mon'::interval))))\r
-               (7 rows)</programlisting>\r
+                                       QUERY PLAN\r
+----------------------------------------------------------------------------------------//\r
+ Hash Join  (cost=5058.86..2306218.81 rows=201669 width=1379)\r
+   Hash Cond: (real_full_rec.record = bre.id)\r
+   -&gt;  Seq Scan on real_full_rec  (cost=0.00..1640972.04 rows=65766704 width=87)\r
+   -&gt;  Hash  (cost=4981.69..4981.69 rows=6174 width=1292)\r
+        -&gt;  Index Scan using biblio_record_entry_deleted on record_entry bre\r
+                (cost=0.00..4981.69 rows=6174 width=1292)\r
+              Index Cond: (deleted = true)\r
+              Filter: ((deleted IS TRUE) AND (date_trunc('MONTH'::text, edit_date)\r
+                &gt; date_trunc('MONTH'::text, (now() - '1 mon'::interval))))\r
+(7 rows)\r
+</programlisting>\r
                </para></formalpara>\r
                <simpara>We can see that the number of rows returned is now only 201669; that&#8217;s\r
                something we can work with. Also, the overall cost of the query is 2306218,\r
                flattened MARC subfields is a fairly common operation, we could create a\r
                new index and see if that speeds up our query plan.</simpara>\r
                <formalpara><title>Query plan with optimized access via a new index</title><para>\r
-               <programlisting language="sql" linenumbering="unnumbered">-- This index will take a long time to create on a large database\r
-               -- of bibliographic records\r
-               CREATE INDEX bib_record_idx ON metabib.real_full_rec (record);\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+-- This index will take a long time to create on a large database\r
+-- of bibliographic records\r
+CREATE INDEX bib_record_idx ON metabib.real_full_rec (record);\r
 \r
-               EXPLAIN SELECT *\r
-                 FROM biblio.record_entry bre\r
-                   INNER JOIN metabib.full_rec mfr ON mfr.record = bre.id\r
-                 WHERE bre.deleted IS TRUE\r
-                   AND DATE_TRUNC('MONTH', bre.edit_date) &gt;\r
-                       DATE_TRUNC ('MONTH', NOW() - '1 MONTH'::INTERVAL)\r
-               ;\r
+EXPLAIN SELECT *\r
+  FROM biblio.record_entry bre\r
+    INNER JOIN metabib.full_rec mfr ON mfr.record = bre.id\r
+  WHERE bre.deleted IS TRUE\r
+    AND DATE_TRUNC('MONTH', bre.edit_date) &gt;\r
+       DATE_TRUNC ('MONTH', NOW() - '1 MONTH'::INTERVAL)\r
+;\r
 \r
-                                                       QUERY PLAN\r
-               ----------------------------------------------------------------------------------------//\r
-                Nested Loop  (cost=0.00..1558330.46 rows=201669 width=1379)\r
-                  -&gt;  Index Scan using biblio_record_entry_deleted on record_entry bre\r
-                          (cost=0.00..4981.69 rows=6174 width=1292)\r
-                        Index Cond: (deleted = true)\r
-                        Filter: ((deleted IS TRUE) AND (date_trunc('MONTH'::text, edit_date) &gt;\r
-                          date_trunc('MONTH'::text, (now() - '1 mon'::interval))))\r
-                  -&gt;  Index Scan using bib_record_idx on real_full_rec\r
-                         (cost=0.00..240.89 rows=850 width=87)\r
-                        Index Cond: (real_full_rec.record = bre.id)\r
-               (6 rows)</programlisting>\r
+                                       QUERY PLAN\r
+----------------------------------------------------------------------------------------//\r
+ Nested Loop  (cost=0.00..1558330.46 rows=201669 width=1379)\r
+   -&gt;  Index Scan using biblio_record_entry_deleted on record_entry bre\r
+          (cost=0.00..4981.69 rows=6174 width=1292)\r
+        Index Cond: (deleted = true)\r
+        Filter: ((deleted IS TRUE) AND (date_trunc('MONTH'::text, edit_date) &gt;\r
+          date_trunc('MONTH'::text, (now() - '1 mon'::interval))))\r
+   -&gt;  Index Scan using bib_record_idx on real_full_rec\r
+         (cost=0.00..240.89 rows=850 width=87)\r
+        Index Cond: (real_full_rec.record = bre.id)\r
+(6 rows)\r
+</programlisting>\r
                </para></formalpara>\r
                <simpara>We can see that the resulting number of rows is still the same (201669), but\r
                the execution estimate has dropped to 1558330 because the query planner can\r
                        <simpara>Of course, as with the rest of SQL, you can replace individual column values\r
                        with one or more use sub-selects:</simpara>\r
                        <formalpara><title>Inserting rows using sub-selects instead of integers</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">INSERT INTO permission.usr_grp_map (usr, grp)\r
-                         VALUES (\r
-                           (SELECT id FROM actor.usr\r
-                              WHERE family_name = 'Scott' AND first_given_name = 'Daniel'),\r
-                           (SELECT id FROM permission.grp_tree\r
-                              WHERE name = 'Local System Administrator')\r
-                         ), (\r
-                           (SELECT id FROM actor.usr\r
-                              WHERE family_name = 'Scott' AND first_given_name = 'Daniel'),\r
-                           (SELECT id FROM permission.grp_tree\r
-                              WHERE name = 'Circulator')\r
-                         )\r
-                       ;</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+INSERT INTO permission.usr_grp_map (usr, grp)\r
+  VALUES (\r
+    (SELECT id FROM actor.usr\r
+       WHERE family_name = 'Scott' AND first_given_name = 'Daniel'),\r
+    (SELECT id FROM permission.grp_tree\r
+       WHERE name = 'Local System Administrator')\r
+  ), (\r
+    (SELECT id FROM actor.usr\r
+       WHERE family_name = 'Scott' AND first_given_name = 'Daniel'),\r
+    (SELECT id FROM permission.grp_tree\r
+       WHERE name = 'Circulator')\r
+  )\r
+;\r
+</programlisting>\r
                        </para></formalpara>\r
                </simplesect>\r
                <simplesect id="_inserting_data_using_a_select_statement">\r
                        the <literal>grp</literal> column value based on the <literal>id</literal> column values returned from\r
                        <literal>permission.grp_tree</literal>:</simpara>\r
                        <formalpara><title>Inserting rows via a <literal>SELECT</literal> statement</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">INSERT INTO permission.usr_grp_map (usr, grp)\r
-                         SELECT 1, id\r
-                           FROM permission.grp_tree\r
-                           WHERE id &gt; 2\r
-                       ;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+INSERT INTO permission.usr_grp_map (usr, grp)\r
+  SELECT 1, id\r
+    FROM permission.grp_tree\r
+    WHERE id &gt; 2\r
+;\r
 \r
-                       INSERT 0 6</programlisting>\r
+INSERT 0 6\r
+</programlisting>\r
                        </para></formalpara>\r
                </simplesect>\r
                <simplesect id="_deleting_rows">\r
                        <literal>permission.grp_perm_map</literal> table where the permission maps to\r
                        <literal>UPDATE_ORG_UNIT_CLOSING</literal> and the group is anything other than administrators:</simpara>\r
                        <formalpara><title>Deleting rows from a table</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">DELETE FROM permission.grp_perm_map\r
-                         WHERE grp IN (\r
-                           SELECT id\r
-                             FROM permission.grp_tree\r
-                             WHERE name != 'Local System Administrator'\r
-                         ) AND perm = (\r
-                           SELECT id\r
-                             FROM permission.perm_list\r
-                                     WHERE code = 'UPDATE_ORG_UNIT_CLOSING'\r
-                                 )\r
-                       ;</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+DELETE FROM permission.grp_perm_map\r
+  WHERE grp IN (\r
+    SELECT id\r
+      FROM permission.grp_tree\r
+      WHERE name != 'Local System Administrator'\r
+  ) AND perm = (\r
+    SELECT id\r
+      FROM permission.perm_list\r
+             WHERE code = 'UPDATE_ORG_UNIT_CLOSING'\r
+         )\r
+;\r
+</programlisting>\r
                        </para></formalpara>\r
                        <note><simpara>There are two main reasons that a <literal>DELETE</literal> statement may not actually\r
                        delete rows from a table, even when the rows meet the conditional clause.</simpara></note>\r
                        <orderedlist numeration="arabic">\r
                        <listitem>\r
-\r
                        <simpara>\r
                        If the row contains a value that is the target of a relational constraint,\r
                        for example, if another table has a foreign key pointing at your target\r
                <simplesect id="_monthly_circulation_stats_by_collection_code_library">\r
                        <title>Monthly circulation stats by collection code / library</title>\r
                        <formalpara><title>Monthly Circulation Stats by Collection Code/Library</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT COUNT(acirc.id) AS "COUNT", aou.name AS "Library", acl.name AS "Copy Location"\r
-                         FROM asset.copy ac\r
-                           INNER JOIN asset.copy_location acl ON ac.location = acl.id\r
-                           INNER JOIN action.circulation acirc ON acirc.target_copy = ac.id\r
-                           INNER JOIN actor.org_unit aou ON acirc.circ_lib = aou.id\r
-                         WHERE DATE_TRUNC('MONTH', acirc.create_time) = DATE_TRUNC('MONTH', NOW() - INTERVAL '3 month')\r
-                           AND acirc.desk_renewal IS FALSE\r
-                           AND acirc.opac_renewal IS FALSE\r
-                           AND acirc.phone_renewal IS FALSE\r
-                         GROUP BY aou.name, acl.name\r
-                         ORDER BY aou.name, acl.name, 1\r
-                       ;</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT COUNT(acirc.id) AS "COUNT", aou.name AS "Library", acl.name AS "Copy Location"\r
+  FROM asset.copy ac\r
+    INNER JOIN asset.copy_location acl ON ac.location = acl.id\r
+    INNER JOIN action.circulation acirc ON acirc.target_copy = ac.id\r
+    INNER JOIN actor.org_unit aou ON acirc.circ_lib = aou.id\r
+  WHERE DATE_TRUNC('MONTH', acirc.create_time) = DATE_TRUNC('MONTH', NOW() - INTERVAL '3 month')\r
+    AND acirc.desk_renewal IS FALSE\r
+    AND acirc.opac_renewal IS FALSE\r
+    AND acirc.phone_renewal IS FALSE\r
+  GROUP BY aou.name, acl.name\r
+  ORDER BY aou.name, acl.name, 1\r
+;\r
+</programlisting>\r
                        </para></formalpara>\r
                </simplesect>\r
                <simplesect id="_monthly_circulation_stats_by_borrower_stat_library">\r
                        <title>Monthly circulation stats by borrower stat / library</title>\r
                        <formalpara><title>Monthly Circulation Stats by Borrower Stat/Library</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT COUNT(acirc.id) AS "COUNT", aou.name AS "Library", asceum.stat_cat_entry AS "Borrower Stat"\r
-                         FROM action.circulation acirc\r
-                           INNER JOIN actor.org_unit aou ON acirc.circ_lib = aou.id\r
-                           INNER JOIN actor.stat_cat_entry_usr_map asceum ON asceum.target_usr = acirc.usr\r
-                           INNER JOIN actor.stat_cat astat ON asceum.stat_cat = astat.id\r
-                         WHERE DATE_TRUNC('MONTH', acirc.create_time) = DATE_TRUNC('MONTH', NOW() - INTERVAL '3 month')\r
-                           AND astat.name = 'Preferred language'\r
-                           AND acirc.desk_renewal IS FALSE\r
-                           AND acirc.opac_renewal IS FALSE\r
-                           AND acirc.phone_renewal IS FALSE\r
-                         GROUP BY aou.name, asceum.stat_cat_entry\r
-                         ORDER BY aou.name, asceum.stat_cat_entry, 1\r
-                       ;</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT COUNT(acirc.id) AS "COUNT", aou.name AS "Library", asceum.stat_cat_entry AS "Borrower Stat"\r
+  FROM action.circulation acirc\r
+    INNER JOIN actor.org_unit aou ON acirc.circ_lib = aou.id\r
+    INNER JOIN actor.stat_cat_entry_usr_map asceum ON asceum.target_usr = acirc.usr\r
+    INNER JOIN actor.stat_cat astat ON asceum.stat_cat = astat.id\r
+  WHERE DATE_TRUNC('MONTH', acirc.create_time) = DATE_TRUNC('MONTH', NOW() - INTERVAL '3 month')\r
+    AND astat.name = 'Preferred language'\r
+    AND acirc.desk_renewal IS FALSE\r
+    AND acirc.opac_renewal IS FALSE\r
+    AND acirc.phone_renewal IS FALSE\r
+  GROUP BY aou.name, asceum.stat_cat_entry\r
+  ORDER BY aou.name, asceum.stat_cat_entry, 1\r
+;\r
+</programlisting>\r
                        </para></formalpara>\r
                </simplesect>\r
                <simplesect id="_monthly_intralibrary_loan_stats_by_library">\r
                        <title>Monthly intralibrary loan stats by library</title>\r
                        <formalpara><title>Monthly Intralibrary Loan Stats by Library</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT aou.name AS "Library", COUNT(acirc.id)\r
-                         FROM action.circulation acirc\r
-                           INNER JOIN actor.org_unit aou ON acirc.circ_lib = aou.id\r
-                           INNER JOIN asset.copy ac ON acirc.target_copy = ac.id\r
-                           INNER JOIN asset.call_number acn ON ac.call_number = acn.id\r
-                         WHERE acirc.circ_lib != acn.owning_lib\r
-                           AND DATE_TRUNC('MONTH', acirc.create_time) = DATE_TRUNC('MONTH', NOW() - INTERVAL '3 month')\r
-                           AND acirc.desk_renewal IS FALSE\r
-                           AND acirc.opac_renewal IS FALSE\r
-                           AND acirc.phone_renewal IS FALSE\r
-                         GROUP by aou.name\r
-                         ORDER BY aou.name, 2\r
-                       ;</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT aou.name AS "Library", COUNT(acirc.id)\r
+  FROM action.circulation acirc\r
+    INNER JOIN actor.org_unit aou ON acirc.circ_lib = aou.id\r
+    INNER JOIN asset.copy ac ON acirc.target_copy = ac.id\r
+    INNER JOIN asset.call_number acn ON ac.call_number = acn.id\r
+  WHERE acirc.circ_lib != acn.owning_lib\r
+    AND DATE_TRUNC('MONTH', acirc.create_time) = DATE_TRUNC('MONTH', NOW() - INTERVAL '3 month')\r
+    AND acirc.desk_renewal IS FALSE\r
+    AND acirc.opac_renewal IS FALSE\r
+    AND acirc.phone_renewal IS FALSE\r
+  GROUP by aou.name\r
+  ORDER BY aou.name, 2\r
+;\r
+</programlisting>\r
                        </para></formalpara>\r
                </simplesect>\r
                <simplesect id="_monthly_borrowers_added_by_profile_adult_child_etc_library">\r
                        <title>Monthly borrowers added by profile (adult, child, etc) / library</title>\r
                        <formalpara><title>Monthly Borrowers Added by Profile (Adult, Child, etc)/Library</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT pgt.name AS "Profile", aou.name AS "Library", COUNT(pgt.name) AS "Count"\r
-                         FROM actor.usr au\r
-                           INNER JOIN permission.grp_tree pgt\r
-                             ON au.profile = pgt.id\r
-                           INNER JOIN actor.org_unit aou\r
-                             ON aou.id = au.home_ou\r
-                         WHERE au.deleted IS FALSE\r
-                           AND DATE_TRUNC('MONTH', au.create_date) = DATE_TRUNC('MONTH', NOW() - '3 months'::interval)\r
-                         GROUP BY pgt.name, aou.name\r
-                         ORDER BY aou.name, pgt.name\r
-                       ;</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT pgt.name AS "Profile", aou.name AS "Library", COUNT(pgt.name) AS "Count"\r
+  FROM actor.usr au\r
+    INNER JOIN permission.grp_tree pgt\r
+      ON au.profile = pgt.id\r
+    INNER JOIN actor.org_unit aou\r
+      ON aou.id = au.home_ou\r
+  WHERE au.deleted IS FALSE\r
+    AND DATE_TRUNC('MONTH', au.create_date) = DATE_TRUNC('MONTH', NOW() - '3 months'::interval)\r
+  GROUP BY pgt.name, aou.name\r
+  ORDER BY aou.name, pgt.name\r
+;\r
+</programlisting>\r
                        </para></formalpara>\r
                </simplesect>\r
                <simplesect id="_borrower_count_by_profile_adult_child_etc_library">\r
                        <title>Borrower count by profile (adult, child, etc) / library</title>\r
                        <formalpara><title>Borrower Count by Profile (Adult, Child, etc)/Library</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT pgt.name AS "Profile", aou.name AS "Library", COUNT(pgt.name) AS "Count"\r
-                         FROM actor.usr au\r
-                           INNER JOIN permission.grp_tree pgt\r
-                             ON au.profile = pgt.id\r
-                           INNER JOIN actor.org_unit aou\r
-                             ON aou.id = au.home_ou\r
-                         WHERE au.deleted IS FALSE\r
-                         GROUP BY pgt.name, aou.name\r
-                         ORDER BY aou.name, pgt.name\r
-                       ;</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT pgt.name AS "Profile", aou.name AS "Library", COUNT(pgt.name) AS "Count"\r
+  FROM actor.usr au\r
+    INNER JOIN permission.grp_tree pgt\r
+      ON au.profile = pgt.id\r
+    INNER JOIN actor.org_unit aou\r
+      ON aou.id = au.home_ou\r
+  WHERE au.deleted IS FALSE\r
+  GROUP BY pgt.name, aou.name\r
+  ORDER BY aou.name, pgt.name\r
+;\r
+</programlisting>\r
                        </para></formalpara>\r
                </simplesect>\r
                <simplesect id="_monthly_items_added_by_collection_library">\r
                        <title>Monthly items added by collection / library</title>\r
-                       <simpara>We define a "collection" as a shelving location in Evergreen.</simpara>\r
+                       <simpara>We define a <quote>collection</quote> as a shelving location in Evergreen.</simpara>\r
                        <formalpara><title>Monthly Items Added by Collection/Library</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">SELECT aou.name AS "Library", acl.name, COUNT(ac.barcode)\r
-                         FROM actor.org_unit aou\r
-                           INNER JOIN asset.call_number acn ON acn.owning_lib = aou.id\r
-                           INNER JOIN asset.copy ac ON ac.call_number = acn.id\r
-                           INNER JOIN asset.copy_location acl ON ac.location = acl.id\r
-                         WHERE ac.deleted IS FALSE\r
-                           AND acn.deleted IS FALSE\r
-                           AND DATE_TRUNC('MONTH', ac.create_date) = DATE_TRUNC('MONTH', NOW() - '1 month'::interval)\r
-                         GROUP BY aou.name, acl.name\r
-                         ORDER BY aou.name, acl.name\r
-                       ;</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+SELECT aou.name AS "Library", acl.name, COUNT(ac.barcode)\r
+  FROM actor.org_unit aou\r
+    INNER JOIN asset.call_number acn ON acn.owning_lib = aou.id\r
+    INNER JOIN asset.copy ac ON ac.call_number = acn.id\r
+    INNER JOIN asset.copy_location acl ON ac.location = acl.id\r
+  WHERE ac.deleted IS FALSE\r
+    AND acn.deleted IS FALSE\r
+    AND DATE_TRUNC('MONTH', ac.create_date) = DATE_TRUNC('MONTH', NOW() - '1 month'::interval)\r
+  GROUP BY aou.name, acl.name\r
+  ORDER BY aou.name, acl.name\r
+;\r
+</programlisting>\r
                        </para></formalpara>\r
                </simplesect>\r
                <simplesect id="_hold_purchase_alert_by_library">\r
                        bibliographic record ID and display the title / author information for those\r
                        records that have more than a given threshold of holds.</simpara>\r
                        <formalpara><title>Hold Purchase Alert by Library</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">-- Title holds\r
-                       SELECT all_holds.bib_id, aou.name, rmsr.title, rmsr.author, COUNT(all_holds.bib_id)\r
-                         FROM\r
-                           (\r
-                             (\r
-                               SELECT target, request_lib\r
-                                 FROM action.hold_request\r
-                                 WHERE hold_type = 'T'\r
-                                   AND fulfillment_time IS NULL\r
-                                   AND cancel_time IS NULL\r
-                             )\r
-                             UNION ALL\r
-                             -- Volume holds\r
-                             (\r
-                               SELECT bre.id, request_lib\r
-                                 FROM action.hold_request ahr\r
-                                   INNER JOIN asset.call_number acn ON ahr.target = acn.id\r
-                                   INNER JOIN biblio.record_entry bre ON acn.record = bre.id\r
-                                 WHERE ahr.hold_type = 'V'\r
-                                   AND ahr.fulfillment_time IS NULL\r
-                                   AND ahr.cancel_time IS NULL\r
-                             )\r
-                             UNION ALL\r
-                             -- Copy holds\r
-                             (\r
-                               SELECT bre.id, request_lib\r
-                                 FROM action.hold_request ahr\r
-                                   INNER JOIN asset.copy ac ON ahr.target = ac.id\r
-                                   INNER JOIN asset.call_number acn ON ac.call_number = acn.id\r
-                                   INNER JOIN biblio.record_entry bre ON acn.record = bre.id\r
-                                 WHERE ahr.hold_type = 'C'\r
-                                   AND ahr.fulfillment_time IS NULL\r
-                                   AND ahr.cancel_time IS NULL\r
-                             )\r
-                           ) AS all_holds(bib_id, request_lib)\r
-                         INNER JOIN reporter.materialized_simple_record rmsr\r
-                         INNER JOIN actor.org_unit aou ON aou.id = all_holds.request_lib\r
-                           ON rmsr.id = all_holds.bib_id\r
-                         GROUP BY all_holds.bib_id, aou.name, rmsr.id, rmsr.title, rmsr.author\r
-                         HAVING COUNT(all_holds.bib_id) &gt; 2\r
-                         ORDER BY aou.name\r
-                       ;</programlisting>\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+-- Title holds\r
+SELECT all_holds.bib_id, aou.name, rmsr.title, rmsr.author, COUNT(all_holds.bib_id)\r
+  FROM\r
+    (\r
+      (\r
+       SELECT target, request_lib\r
+         FROM action.hold_request\r
+         WHERE hold_type = 'T'\r
+           AND fulfillment_time IS NULL\r
+           AND cancel_time IS NULL\r
+      )\r
+      UNION ALL\r
+      -- Volume holds\r
+      (\r
+       SELECT bre.id, request_lib\r
+         FROM action.hold_request ahr\r
+           INNER JOIN asset.call_number acn ON ahr.target = acn.id\r
+           INNER JOIN biblio.record_entry bre ON acn.record = bre.id\r
+         WHERE ahr.hold_type = 'V'\r
+           AND ahr.fulfillment_time IS NULL\r
+           AND ahr.cancel_time IS NULL\r
+      )\r
+      UNION ALL\r
+      -- Copy holds\r
+      (\r
+       SELECT bre.id, request_lib\r
+         FROM action.hold_request ahr\r
+           INNER JOIN asset.copy ac ON ahr.target = ac.id\r
+           INNER JOIN asset.call_number acn ON ac.call_number = acn.id\r
+           INNER JOIN biblio.record_entry bre ON acn.record = bre.id\r
+         WHERE ahr.hold_type = 'C'\r
+           AND ahr.fulfillment_time IS NULL\r
+           AND ahr.cancel_time IS NULL\r
+      )\r
+    ) AS all_holds(bib_id, request_lib)\r
+  INNER JOIN reporter.materialized_simple_record rmsr\r
+  INNER JOIN actor.org_unit aou ON aou.id = all_holds.request_lib\r
+    ON rmsr.id = all_holds.bib_id\r
+  GROUP BY all_holds.bib_id, aou.name, rmsr.id, rmsr.title, rmsr.author\r
+  HAVING COUNT(all_holds.bib_id) &gt; 2\r
+  ORDER BY aou.name\r
+;\r
+</programlisting>\r
                        </para></formalpara>\r
                </simplesect>\r
                <simplesect id="_update_borrower_records_with_a_different_home_library">\r
                        <simpara>Then we issue an <literal>UPDATE</literal> statement to set the home library for patrons with a\r
                        physical address with a city that matches the city names in our staging table.</simpara>\r
                        <formalpara><title>Update borrower records with a different home library</title><para>\r
-                       <programlisting language="sql" linenumbering="unnumbered">CREATE SCHEMA staging;\r
-                       CREATE TABLE staging.city_home_ou_map (city TEXT, ou_shortname TEXT,\r
-                         FOREIGN KEY (ou_shortname) REFERENCES actor.org_unit (shortname));\r
-                       INSERT INTO staging.city_home_ou_map (city, ou_shortname)\r
-                         VALUES ('Southbury', 'BR1'), ('Middlebury', 'BR2'), ('Hartford', 'BR3');\r
-                       BEGIN;\r
+<programlisting language="sql" linenumbering="unnumbered">\r
+CREATE SCHEMA staging;\r
+CREATE TABLE staging.city_home_ou_map (city TEXT, ou_shortname TEXT,\r
+  FOREIGN KEY (ou_shortname) REFERENCES actor.org_unit (shortname));\r
+INSERT INTO staging.city_home_ou_map (city, ou_shortname)\r
+  VALUES ('Southbury', 'BR1'), ('Middlebury', 'BR2'), ('Hartford', 'BR3');\r
+BEGIN;\r
 \r
-                       UPDATE actor.usr au SET home_ou = COALESCE(\r
-                         (\r
-                           SELECT aou.id\r
-                             FROM actor.org_unit aou\r
-                               INNER JOIN staging.city_home_ou_map schom ON schom.ou_shortname = aou.shortname\r
-                               INNER JOIN actor.usr_address aua ON aua.city = schom.city\r
-                             WHERE au.id = aua.usr\r
-                             GROUP BY aou.id\r
-                         ), home_ou)\r
-                       WHERE (\r
-                         SELECT aou.id\r
-                           FROM actor.org_unit aou\r
-                             INNER JOIN staging.city_home_ou_map schom ON schom.ou_shortname = aou.shortname\r
-                             INNER JOIN actor.usr_address aua ON aua.city = schom.city\r
-                           WHERE au.id = aua.usr\r
-                           GROUP BY aou.id\r
-                       ) IS NOT NULL;</programlisting>\r
+UPDATE actor.usr au SET home_ou = COALESCE(\r
+  (\r
+    SELECT aou.id\r
+      FROM actor.org_unit aou\r
+       INNER JOIN staging.city_home_ou_map schom ON schom.ou_shortname = aou.shortname\r
+       INNER JOIN actor.usr_address aua ON aua.city = schom.city\r
+      WHERE au.id = aua.usr\r
+      GROUP BY aou.id\r
+  ), home_ou)\r
+WHERE (\r
+  SELECT aou.id\r
+    FROM actor.org_unit aou\r
+      INNER JOIN staging.city_home_ou_map schom ON schom.ou_shortname = aou.shortname\r
+      INNER JOIN actor.usr_address aua ON aua.city = schom.city\r
+    WHERE au.id = aua.usr\r
+    GROUP BY aou.id\r
+) IS NOT NULL;\r
+</programlisting>\r
                        </para></formalpara>\r
                </simplesect>\r
        </section>\r