.lf 1 tmac.an.nr
.\" This is -*-nroff-*-
.\" XXX standard disclaimer belongs here....
.\" /usr/local/devel/postgres-4.2-devel/src/ref/RCS/tmac.an.nr,v 1.2 1993/08/17 21:39:54 aoki Exp
.ds II INGRES
.ds PG POSTGRES
.ds UU UNIX
.ds PQ POSTQUEL
.ds LI LIBPQ
.ds PV 4.2
.de (l 			\" fake "-me"-style lists
.nf
.ie '\\$1'M' .in +0n
.el .in +5n
..
.de )l
.fi
.in
..
.de (C			\" constant-width font blocks
.(l \\$1
.sp
..
.de )C
.sp
.)l
..
.de BH			\" reference manual "big header" for sections
.SH NAME
\&\\$1
..
.de SB			\" section/index stuff
..	\" no-op
.de SE
..	\" no-op
.de XA
..	\" no-op
.de XP
..	\" no-op
.de SP
..
.ds lq ""
.ds rq ""
.lf 1 postquel/retrieve.cmdsrc
.\" This is -*-nroff-*-
.\" XXX standard disclaimer belongs here....
.\" /usr/local/devel/postgres-4.2-devel/src/ref/postquel/RCS/retrieve.cmdsrc,v 1.14 1994/03/12 11:15:42 aoki Exp
.TH RETRIEVE COMMANDS 03/13/94
.XA 2 Retrieve
.SH NAME
retrieve \(em retrieve instances from a class
.SH SYNOPSIS
.(l M
\fBretrieve\fR
    [ (\fBinto\fR classname [ archive_mode ] | 
       \fBportal\fR portal_name |
       \fBiportal\fR portal_name ) ]
    [\fBunique\fR]
    \fB(\fR [ attr_name-1 \fB=\fR] expression-1 {\fB,\fR [attr_name-i \fB=\fR] expression-i} \fB)\fR
    [ \fBfrom\fR from_list ] 
    [ \fBwhere\fR qual ] 
    [ \fBsort\fR \fBby\fR attr_name\-1 [\fBusing\fR operator]
          { \fB,\fR attr_name-j [\fBusing\fR operator] } ]
.)l
.SH DESCRIPTION
.BR Retrieve 
will get all instances which satisfy the qualification,
.IR qual ,
compute the value of each element in the target list, and either (1)
return them to an application program through one of two different
kinds of portals or (2) store them in a new class.
.PP
If
.IR classname
is specified, the result of the query will be stored in a new class
with the indicated name.  If an archive specification,
.IR archive_mode
of
.IR light ,
.IR heavy ,
or
.IR none
is not specifed, then it
defaults to
.IR light
archiving.  (This default may be changed at a site by the DBA).  The
current user will be the owner of the new class.  The class will have
attribute names as specified in the target list.  A class with this
name owned by the user must not already exist.  The keyword
.BR all
can be used when it is desired to retrieve all fields of a class.
.PP
If no result
.IR classname
is specified, then the result of the query will be available on the
specified portal and will not be saved.  If no portal name is
specified, the blank portal is used by default.  For a portal
specified with the
.BR iportal
keyword, retrieve passes data to an application without conversion to
external format.  For a portal specified with the
.BR portal
keyword,
.BR retrieve
passes data to an application after first converting it to the
external representation.  For the blank portal, all data is converted
to external format.  Duplicate instances are not removed when the
result is displayed through a portal unless the optional
.BR unique
tag is appended, in which case the instances in the target list are
sorted according to the sort clause and duplicates are removed before
being returned.
.PP
Instances retrieved into a portal may be fetched in subsequent queries
by using the
.BR fetch
command.  Since the results of a
.BR "retrieve portal"
span queries,
.BR "retrieve portal"
may only be executed inside of a
.BR begin / end
transaction block.  Attempts to use named portals outside of a
transaction block will result in a warning message from the parser,
and the query will be discarded.
.PP
The
.BR sort
clause allows a user to specify that he wishes the instances sorted
according to the corresponding operator.  This operator must be a
binary one returning a boolean.  Multiple sort fields are allowed and
are applied from left to right.
.PP
The target list specifies the fields to be retrieved.  Each 
.IR attr_name
specifies the desired attribute or portion of an array attribute.
Thus, each 
.IR attr_name
takes the form
.(C
class_name.att_name
.)C
or, if the user only desires part of an array,
.(C
/*
 * Specify a lower and upper index for each dimension
 * (i.e., clip a range of array elements)
 */
class_name.att_name[lIndex-1:uIndex-1]..[lIndex-i:uIndex-i]

/*
 * Specify an exact array element
 */
class_name.att_name[uIndex-1]..[uIndex-i]
.)C
where each 
.IR lIndex
or
.IR uIndex
is an integer constant.
.PP
When you retrieve an attribute which is of a complex type, the behavior
of the system depends on whether you used "nested dots" to project
out attributes of the complex type or not.  See the examples below.
.PP
You must have read access to a class to read its values (see
.IR "change acl" (commands).
.SH EXAMPLES
.(C
/*
 * Find all employees who make more than their manager
 */
retrieve (e.name)
   from e, m in emp
   where e.mgr = m.name
   and e.sal > m.sal
.)C
.(C
/*
 * Retrieve all fields for those employees who make
 * more than the average salary
 */
retrieve into avgsal(ave = float8ave {emp.sal}) \eg

retrieve (e.all)
   from e in emp
   where e.sal > avgsal.ave \eg
.)C
.(C
/*
 * Retrieve all employee names in sorted order
 */
retrieve unique (emp.name)
   sort by name using <
.)C
.(C
/*
 * Retrieve all employee names that were valid on 1/7/85 
 * in sorted order
 */
retrieve (e.name)
   from e in emp["January 7 1985"]
   sort by name using <
.)C
.(C
/*
 * Construct a new class, raise, containing 1.1
 * times all employee's salaries
 */
retrieve into raise (salary = 1.1 * emp.salary)
.)C
.(C
/*
 * Do a retrieve into a portal
 */
begin \eg
   retrieve portal myportal (pg_user.all) \eg
   fetch 2 in myportal \eg
   fetch all in myportal \eg
   close myportal \eg
end \eg
.)C
.(C
/*
 * Retrieve an entire 3x3 array that represents
 * a particular noughts-and-crosses board.
 * This retrieves a 3x3 array of char.
 */
retrieve (tictactoe.board)
    where tictactoe.game = 2
.)C
.(C
/*
 * Retrieve the middle row of a 3x3 array that
 * represents a noughts-and-crosses board.
 * This retrieves a 1x3 array of char.
 */
retrieve (tictactoe.board[2:2][1:3])
    where tictactoe.game = 2
.)C
.(C
/*
 * Retrieve the middle element of a 3x3 array that
 * represents a noughts-and-crosses board.
 * This retrieves a single char.
 */
retrieve (tictactoe.board[2][2])
    where tictactoe.game = 2
.)C
.(C
/*
 * Retrieve all attributes of a class "newemp" that
 * contains two attributes, "name" and a complex type
 * "manager" which is of type "newemp".  Since each 
 * complex attribute represents a procedure recorded 
 * in "pg_proc", the system will return the object IDs
 * of each procedure.  In this example, POSTGRES will
 * return tuples of the form ("carol", 34562),
 * ("sunita", 45662), and so on.  The "manager" field
 * is represented as an object ID.
 */
retrieve (newemp.name, newemp.manager)
.)C
.(C
/*
 * In order to see the attributes of a complex type, they
 * must be explicitly projected.  The following query will
 * return tuples of the form
 * ("carol", "mike", 23434), ("sunita", "mike", 23434)
 */
retrieve (newemp.name, newemp.manager.name,
          newemp.manager.manager)
.)C
.SH "SEE ALSO"
append(commands),
close(commands),
create(commands),
fetch(commands),
postquel(commands),
replace(commands).
.SH BUGS
.BR "Retrieve into"
does not delete duplicates.
.PP
.IR Archive_mode 
is not implemented in Version \*(PV.
.PP
If the backend crashes in the course of executing a 
.BR "retrieve into" ,
the class file will remain on disk.  It can be safely removed by the
database DBA, but a subsequent
.BR "retrieve into"
to the same name will fail with a cryptic error message about
"BlockExtend".
.PP
.BR "Retrieve iportal"
returns data in an architecture dependent format, namely that of the
server on which the backend is running.  A standard data format, such
as XDR, should be adopted.
.PP
Aggregate functions can only appear in the target list.