This text was automatically converted from troff me macros to HTML. Information may have been lost, added, or changed in the process. Lars Aronsson and Lysator do not guarantee the correctness of this HTML document.
retrieve *- retrieve instances from a class
retrieve
[ (into classname [ archive_mode ] | portal portal_name ) | iportal portal_name ]
[unique]
( [ attr_name1 =] expression1 {, [attr_name-i =] expression-i} )
[ from from_list ]
[ where qual ]
[ sort by attr_name-1 [using operator] { , attr_name-j [using operator] } ]
Retrieve will get all instances which satisfy the qualification, qual , compute the value of each element in the target list, and either return them to an application program through one of two different kinds of portals or store them in a new class.
If classname is specified, the result of the query will be stored in a new class with the indicated name. If an archive specification, archive_mode of light , heavy , or none is not specifed, then it defaults to 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 res_target_list. A class with this name owned by the user must not already exist. The keyword all can be used when it is desired to retrieve all fields of a class.
If no result 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 iportal keyword, retrieve passes data to an application without conversion to external format. For a portal specified with the portal keyword, 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 unique tag is appended, in which case the instances in the res_target_list are sorted according to the sort clause and duplicates are removed before being returned.
Instances retrieved into a portal may be fetched in subsequent queries by using the fetch command. Since the results of a "retrieve portal" span queries, "retrieve portal" may only be executed inside of a 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.
The 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.
/* 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
/*
* Retrieve all fields for those employees who make
* more than the average salary
*/
retrieve into avgsal(ave = float8ave {emp.sal}) \g
retrieve (e.all)
from e in emp
where e.sal > avgsal.ave \g
/* retrieve employees's names sorted */
retrieve unique (emp.name)
sort by name using <
/* retrieve all employees's 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 <
/* construct a new class, raise, containing 1.1 */
/* times all employee's salaries */
retrieve into raise (salary = 1.1 * emp.salary)
/* do a retrieve into a portal */
begin \g
retrieve portal myportal (pg_user.all) \g
fetch 2 in myportal \g
fetch all in myportal \g
close myportal \g
end \g
postquel(commands), fetch(commands), close(commands), create(commands).
Retrieve into does not delete duplicates in Version 4.0.
Archive_mode is not implemented in Version 4.0.
If the backend crashes in the course of executing a Retrieve into, the class file will remain on disk. It can be safely removed by the database DBA, but a subsequent retrieve into to the same name will fail with a cryptic error message about "BlockExtend". A solution to this problem is being investigated and will be released in later version.
Retrieve iportal returns data in an architecture dependent format, namely that of the server on which the backend is running. A standard data format should be adopted, most likely XDR. At that point, there will be no need to distinguish among external and internal data.
Aggregate functions must appear in the target list.