SGML source for new documentation.

1 files changed, 362 insertions, 0 deletions

diff --git a/doc/src/sgml/query.sgml b/doc/src/sgml/query.sgml
new file mode 100644
index 00000000000..df119fd365a
--- /dev/null
+++ b/doc/src/sgml/query.sgml
@@ -0,0 +1,362 @@
+<Chapter ID="QUERY">
+<TITLE>The Query Language</TITLE>
+
+<Para>
+     The  <ProductName>Postgres</ProductName>  query language is a variant of
+the <Acronym>SQL3</Acronym> draft next-generation standard. It
+     has many extensions such as an extensible type  system,
+     inheritance,  functions and production rules. These are
+     features carried over from the original <ProductName>Postgres</ProductName>  query
+     language,  <ProductName>PostQuel</ProductName>.  This section provides an overview
+     of how to use <ProductName>Postgres</ProductName> <Acronym>SQL</Acronym>  to  perform  simple  operations.
+     This manual is only intended to give you an idea of our
+     flavor of <Acronym>SQL</Acronym> and is in no way a complete  tutorial  on
+     <Acronym>SQL</Acronym>.  Numerous  books  have  been  written  on <Acronym>SQL</Acronym>, including
+<!--
+<XRef LinkEnd="MELT93"> and <XRef LinkEnd="DATE97">.
+-->
+[MELT93] and [DATE97].
+     You should be  aware  that  some language features 
+are not part of the <Acronym>ANSI</Acronym> standard.
+</Para>
+
+<Sect1>
+<Title>Interactive Monitor</Title>
+
+<Para>
+     In the examples that follow, we assume  that  you  have
+     created  the mydb database as described in the previous
+     subsection and have started <Application>psql</Application>.
+     Examples  in  this  manual  can  also   be   found   in
+     <FileName>/usr/local/pgsql/src/tutorial/</FileName>.    Refer   to   the
+     <FileName>README</FileName> file in that directory for how to use them.   To
+     start the tutorial, do the following:
+
+<ProgramListing>
+% cd /usr/local/pgsql/src/tutorial
+% psql -s mydb
+Welcome to the POSTGRESQL interactive sql monitor:
+  Please read the file COPYRIGHT for copyright terms of POSTGRESQL
+
+   type \? for help on slash commands
+   type \q to quit
+   type \g or terminate with semicolon to execute query
+ You are currently connected to the database: postgres
+
+mydb=> \i basics.sql
+</ProgramListing>
+</Para>
+
+<Para>
+     The  <Literal>\i</Literal>  command  read  in  queries  from the specified
+     files. The <Literal>-s</Literal> option puts you in single step mode which
+     pauses  before  sending a query to the backend. Queries
+     in this section are in the file <FileName>basics.sql</FileName>.
+</Para>
+
+<Para>
+<Application>psql</Application>
+has a variety of <Literal>\d</Literal> commands for showing system information.
+Consult these commands for more details;
+for a listing, type <Literal>\?</Literal> at the <Application>psql</Application> prompt.
+</Para>
+
+<Sect1>
+<Title>Concepts</Title>
+
+<Para>
+     The fundamental notion in <ProductName>Postgres</ProductName> is that of a  class,
+     which  is a named collection of object instances.  Each
+     instance has the same collection of  named  attributes,
+     and each attribute is of a specific type.  Furthermore,
+     each instance has a permanent <FirstTerm>object  identifier</FirstTerm> (<Acronym>OID</Acronym>)
+     that  is  unique  throughout the installation.  Because
+     <Acronym>SQL</Acronym> syntax refers to tables,  we  will use  the  terms
+     <FirstTerm>table</FirstTerm> and <FirstTerm>class</FirstTerm> interchangeably.
+  Likewise, an <Acronym>SQL</Acronym> <FirstTerm>row</FirstTerm> is an
+     <FirstTerm>instance</FirstTerm> and <Acronym>SQL</Acronym> <FirstTerm>columns</FirstTerm>
+ are <FirstTerm>attributes</FirstTerm>.
+     As  previously  discussed,  classes  are  grouped  into
+     databases,  and  a collection of databases managed by a
+     single <Application>postmaster</Application> process constitutes  an  installation
+     or site.
+</Para>
+
+<Sect1>
+<Title>Creating a New Class</Title>
+
+<Para>
+     You  can  create  a  new  class by specifying the class
+     name, along with all attribute names and their types:
+
+<ProgramListing>
+CREATE TABLE weather (
+    city            varchar(80),
+    temp_lo         int,           -- low temperature
+    temp_hi         int,           -- high temperature
+    prcp            real,          -- precipitation
+    date            date
+);
+</ProgramListing>
+
+<Para>
+     Note that both keywords and identifiers are case-insensitive; identifiers can become
+case-sensitive by surrounding them with double-quotes as allowed by <Acronym>SQL92</Acronym>.
+     <ProductName>Postgres</ProductName>  <Acronym>SQL</Acronym> supports the usual
+     <Acronym>SQL</Acronym> types <Type>int</Type>,
+ <Type>float</Type>,  <Type>real</Type>,  <Type>smallint</Type>,  <Type>char(N)</Type>,  
+     <Type>varchar(N)</Type>,  <Type>date</Type>, <Type>time</Type>,
+and <Type>timestamp</Type>, as well as other types of general utility and
+a rich set of geometric types.  As we will 
+     see later, <ProductName>Postgres</ProductName> can be customized  with  an  
+     arbitrary  number  of
+     user-defined  data types.  Consequently, type names are
+     not syntactical keywords, except where required to support special cases in the <Acronym>SQL92</Acronym> standard.
+     So far, the <ProductName>Postgres</ProductName> create command looks exactly  like
+     the  command  used  to  create a table in a traditional
+     relational system.  However, we will presently see that
+     classes  have  properties  that  are  extensions of the
+     relational model.
+</Para>
+
+<Sect1>
+<Title>Populating a Class with Instances</Title>
+
+<Para>
+     The <Command>insert</Command> statement is used to populate a  class  with
+     instances:
+
+<ProgramListing>
+INSERT INTO weather
+    VALUES ('San Francisco', 46, 50, 0.25, '11/27/1994')
+</ProgramListing>
+
+<Para>
+     You can also use the <Command>copy</Command> command to perform load large
+     amounts of data from flat (<Acronym>ASCII</Acronym>) files.
+</Para>
+
+<Sect1>
+<Title>Querying a Class</Title>
+
+<Para>
+     The weather class can be queried with normal relational
+     selection  and projection queries.  A <Acronym>SQL</Acronym> <Command>select</Command> 
+     statement is used to do this.  The statement is divided into
+     a target list (the part that lists the attributes to be
+     returned) and a qualification (the part that  specifies
+     any  restrictions).   For  example, to retrieve all the
+     rows of weather, type:
+<ProgramListing>
+SELECT * FROM WEATHER;
+</ProgramListing>
+
+     and the output should be:
+<ProgramListing>
++--------------+---------+---------+------+------------+
+|city          | temp_lo | temp_hi | prcp | date       |
++--------------+---------+---------+------+------------+
+|San Francisco | 46      | 50      | 0.25 | 11-27-1994 |
++--------------+---------+---------+------+------------+
+|San Francisco | 43      | 57      | 0    | 11-29-1994 |
++--------------+---------+---------+------+------------+
+|Hayward       | 37      | 54      |      | 11-29-1994 |
++--------------+---------+---------+------+------------+
+</ProgramListing>
+     You may specify any arbitrary expressions in the  target list. For example, you can do:
+<ProgramListing>
+SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
+</ProgramListing>
+
+<Para>
+     Arbitrary  Boolean  operators
+ (<Command>and</Command>,  <Command>or</Command> and <Command>not</Command>) are
+     allowed in the qualification of any query.   For  example,
+
+<ProgramListing>
+SELECT * FROM weather
+    WHERE city = 'San Francisco'
+    AND prcp > 0.0;
+
++--------------+---------+---------+------+------------+
+|city          | temp_lo | temp_hi | prcp | date       |
++--------------+---------+---------+------+------------+
+|San Francisco | 46      | 50      | 0.25 | 11-27-1994 |
++--------------+---------+---------+------+------------+
+</ProgramListing>
+</Para>
+
+<Para>
+     As  a final note, you can specify that the results of a
+     select can be returned in a <FirstTerm>sorted order</FirstTerm>
+ or with <FirstTerm>duplicate instances</FirstTerm> removed.
+
+<ProgramListing>
+SELECT DISTINCT city
+    FROM weather
+    ORDER BY city;
+</ProgramListing>
+</Para>
+
+<Sect1>
+<Title>Redirecting SELECT Queries</Title>
+
+<Para>
+     Any select query can be redirected to a new class
+<ProgramListing>
+SELECT * INTO TABLE temp FROM weather;
+</ProgramListing>
+
+<Para>
+     This forms an implicit <Command>create</Command> command, creating a new
+     class temp with the attribute names and types specified
+     in  the target list of the <Command>select into</Command> command.  We can
+     then, of course, perform any operations on the  resulting 
+     class that we can perform on other classes.
+</Para>
+
+<Sect1>
+<Title>Joins Between Classes</Title>
+
+<Para>
+     Thus far, our queries have only accessed one class at a
+     time.  Queries can access multiple classes at once,  or
+     access  the  same  class  in  such  a way that multiple
+     instances of the class are being processed at the  same
+     time.   A query that accesses multiple instances of the
+     same or different classes at one time is called a  join
+     query.
+     As an example, say we wish to find all the records that
+     are in the  temperature  range  of  other  records.  In
+     effect,  we  need  to  compare  the temp_lo and temp_hi
+     attributes of each EMP  instance  to  the  temp_lo  and
+     temp_hi  attributes of all other EMP instances.
+<Note>
+<Para>
+This  is only a conceptual model.  The actual join may
+   be performed in a more efficient manner, but this is invisible to the user.
+</Para>
+</Note>
+
+ We can do this with the following query:
+
+<ProgramListing>
+SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
+    W2.city, W2.temp_lo AS low, W2.temp_hi AS high
+    FROM weather W1, weather W2
+    WHERE W1.temp_lo < W2.temp_lo
+    AND W1.temp_hi > W2.temp_hi;
+
++--------------+-----+------+---------------+-----+------+
+|city          | low | high | city          | low | high |
++--------------+-----+------+---------------+-----+------+
+|San Francisco | 43  | 57   | San Francisco | 46  | 50   |
++--------------+-----+------+---------------+-----+------+
+|San Francisco | 37  | 54   | San Francisco | 46  | 50   |
++--------------+-----+------+---------------+-----+------+
+</ProgramListing>     
+
+<Note>
+<Para>
+The semantics of such a join are 
+   that the qualification
+   is a truth expression defined for the Cartesian  product  of
+   the  classes indicated in the query.  For those instances in
+   the Cartesian product for which the qualification  is  true,
+   <ProductName>Postgres</ProductName>  computes  and  returns the values specified in the
+   target list.  <ProductName>Postgres</ProductName> <Acronym>SQL</Acronym> does not assign  any  meaning  to
+   duplicate values in such expressions.  This means that <ProductName>Postgres</ProductName> 
+   sometimes recomputes the same target list several times;
+   this frequently happens when Boolean expressions are connected 
+   with an "or".  To remove such duplicates, you must  use
+   the <Command>select distinct</Command> statement.
+</Para>
+</Note>
+
+<Para>
+     In this case, both W1 and  W2  are  surrogates for  an
+     instance  of the class weather, and both range over all
+     instances of the class.  (In the  terminology  of  most
+     database  systems,  W1 and W2 are known as <FirstTerm>range variables</FirstTerm>.)  
+     A query can contain an  arbitrary  number  of
+     class names and surrogates.
+</Para>
+
+<Sect1>
+<Title>Updates</Title>
+
+<Para>
+     You can update existing instances using the update command. 
+     Suppose you discover the temperature readings are
+     all  off  by 2 degrees as of Nov 28, you may update the
+     data as follow:
+
+<ProgramListing>
+UPDATE weather
+    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
+    WHERE date > '11/28/1994';
+</ProgramListing>
+</Para>
+
+<Sect1>
+<Title>Deletions</Title>
+
+<Para>
+     Deletions are performed using the <Command>delete</Command> command:
+<ProgramListing>
+DELETE FROM weather WHERE city = 'Hayward';
+</ProgramListing>
+
+     All weather recording belongs to Hayward is removed.
+     One should be wary of queries of the form
+<ProgramListing>
+DELETE FROM classname;
+</ProgramListing>
+
+     Without a qualification, <Command>delete</Command> will simply
+     remove  all  instances  of  the given class, leaving it
+     empty.  The system will not request confirmation before
+     doing this.
+</Para>
+
+<Sect1>
+<Title>Using Aggregate Functions</Title>
+
+<Para>
+     Like  most  other  query  languages,  <ProductName>PostgreSQL</ProductName> supports
+     aggregate functions.
+The current  implementation  of  <ProductName>Postgres</ProductName> aggregate functions have some limitations.
+     Specifically, while there  are  aggregates  to  compute
+     such  functions as the <Function>count</Function>, <Function>sum</Function>,
+ <Function>avg</Function> (average), <Function>max</Function> (maximum) and
+     <Function>min</Function> (minimum) over a set of instances,  aggregates  can  only
+     appear  in  the  target  list of a query and not directly in the
+     qualification (the where clause). As an example,
+
+<ProgramListing>
+SELECT max(temp_lo) FROM weather;
+</ProgramListing>
+
+is allowed, while
+
+<ProgramListing>
+SELECT city FROM weather WHERE temp_lo = max(temp_lo);
+</ProgramListing>
+
+is not. However, as is often the case the query can be restated to accomplish 
+the intended result; here by using a <FirstTerm>subselect</FirstTerm>:
+<ProgramListing>
+SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
+</ProgramListing>
+</Para>
+
+<Para>
+     Aggregates may also have <FirstTerm>group by</FirstTerm> clauses:
+<ProgramListing>
+SELECT city, max(temp_lo)
+    FROM weather
+    GROUP BY city;
+</ProgramListing>
+</Para>
+
+</Chapter>