1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
|
.\" This is -*-nroff-*-
.\" XXX standard disclaimer belongs here....
.\" $Header: /cvsroot/pgsql/src/man/Attic/select.l,v 1.13 1999/06/03 19:52:09 momjian Exp $
.TH SELECT SQL 11/05/95 PostgreSQL PostgreSQL
.SH NAME
select - retrieve instances from a class
.SH SYNOPSIS
.nf
\fBselect\fR [distinct [on attr_name]]
expression1 [\fBas\fR attr_name-1]
{, expression-1 [\fBas\fR attr_name-i]}
[\fBinto\fR [\fBtemp\fR] [\fBtable\fR] classname]
[\fBfrom\fR from-list]
[\fBwhere\fR where-clause]
[\fBgroup by\fR attr_name1 {, attr_name-i....}]
[\fBhaving\fR having-clause]
[ { \fBunion {all}\fR | \fBintersect\fR | \fBexcept\fR } \fBselect\fR ...]
[\fBorder by\fR attr_name1 [\fBasc\fR | \fBdesc\fR] [\fBusing op1\fR] {, attr_namei...}]
[\fBfor update\fR [\fBof\fR class_name...]]
[\fBlimit\fR count [\fBoffset\fR|, count]]
.fi
.SH DESCRIPTION
.BR Select
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
into table class name
is specified, the result of the query will be stored in a new class
with the indicated name.
.PP
The
.BR "order by"
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.
The
.BR "for update"
allows the select statement to perform exclusive locking of selected rows.
The
.BR "limit/offset"
allows control over which rows are returned by the query.
.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
.nf
class_name.att_name
.fi
or, if the user only desires part of an array,
.nf
--
--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]
.fi
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 grant/revoke(l).
.SH EXAMPLES
.nf
--
--Find all employees who make more than their manager
--
select e.name
from emp e, emp m
where e.mgr = m.name
and e.sal > m.sal
.fi
.nf
--
--Retrieve all fields for those employees who make
--more than the average salary
--
select avg(sal) as ave
into table avgsal from emp;
.fi
.nf
--
--Retrieve all employee names in sorted order
--
select distinct name
from emp
order by name using <
.fi
.nf
--
--Retrieve all employee names that were valid on 1/7/85
--in sorted order
--
selec name
from emp['January 7 1985'] e
order by name using <
.fi
.nf
--
--Construct a new class, raise, containing 1.1
--times all employee's salaries
--
select 1.1 * emp.salary as salary
into tables raise
from emp
.fi
.SH "SEE ALSO"
insert(l),
close(l),
create_table(l),
fetch(l),
update(l).
.SH BUGS
If the backend crashes in the course of executing a
.BR "select into" ,
the class file will remain on disk. It can be safely removed by the
database DBA, but a subsequent
.BR "select into"
to the same name will fail with a cryptic error message about
\*(lqBlockExtend\*(rq.
|
