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
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
|
<!-- manual page source format generated by PolyglotMan v3.0.4, -->
<!-- available via anonymous ftp from ftp.cs.berkeley.edu:/ucb/people/phelps/tcltk/rman.tar.Z -->
<HTML>
<HEAD>
<TITLE>INTRODUCTION(SQL) manual page</TITLE>
</HEAD>
<BODY>
<A HREF="sql.html">SQL Reference Contents</A>
<H2><A NAME="sect0" HREF="#toc0">Section 4 - SQL Commands (COMMANDS) </A></H2>
<H2><A NAME="sect1" HREF="#toc1">General Information </A></H2>
<H2><A NAME="sect2" HREF="#toc2">DESCRIPTION
</A></H2>
The following is a description of the general syntax of SQL. Individual
SQL statements and commands are treated separately in the document; this
section describes the syntactic classes from which the constituent parts
of SQL statements are drawn.
<H2><A NAME="sect3" HREF="#toc3">Comments </A></H2>
A <I>comment</I> is an arbitrary sequence
of characters following double dashes up to the end of the line. We also
support double-slashes as comments, e.g.: -- This is a standard SQL comment
<BR>
// And this is another supported comment style, like C++ <BR>
<P>
We also support
C-style comments, e.g.: /* multi <BR>
line <BR>
comment */ <BR>
<H2><A NAME="sect4" HREF="#toc4">Names </A></H2>
<I>Names</I> in
SQL are sequences of less than NAMEDATALEN alphanumeric characters, starting
with an alphabetic character. By default, NAMEDATALEN is set to 32, but
at the time the system is built, NAMEDATALEN can be changed by changing
the #ifdef in src/backend/include/postgres.h. Underscore (`_') is considered
an alphabetic character.
<H2><A NAME="sect5" HREF="#toc5">Keywords </A></H2>
The following identifiers are reserved
for use as <I>keywords</I> and may not be used otherwise: <P>
<B><P>
</B><B><P>
<P>
</B>In addition, all
Postgres classes have several predefined attributes used by the system.
<H2><A NAME="sect6" HREF="#toc6">Constants </A></H2>
There are six types of <I>constants</I> for use in SQL. They are
described below.
<H2><A NAME="sect7" HREF="#toc7">String Constants </A></H2>
<I>Strings</I> in SQL are arbitrary sequences
of ASCII characters bounded by single quotes (' '). Uppercase alphabetics
within strings are accepted literally. Non-printing characters may be embedded
within strings by prepending them with a backslash, e.g., `\n'. Also, in order
to embed quotes within strings, it is necessary to prefix them with `\' .
The same convention applies to `\' itself. Because of the limitations on
instance sizes, string constants are currently limited to a length of
a little less than 8192 bytes. Larger objects may be created using the
Postgres Large Object interface.
<H2><A NAME="sect8" HREF="#toc8">Integer Constants </A></H2>
<I>Integer constants</I> in
SQL are collection of ASCII digits with no decimal point. Legal values
range from -2147483647 to +2147483647. This will vary depending on the
operating system and host machine.
<H2><A NAME="sect9" HREF="#toc9">Floating Point Constants </A></H2>
<I>Floating point
constants</I> consist of an integer part, a decimal point, and a fraction
part or scientific notation of the following format: {<dig>} .{<dig>} [e [+-]
{<dig>}] <BR>
Where <dig> is a digit. You must include at least one <dig> after
the period and after the [+-] if you use those options. An exponent with
a missing mantissa has a mantissa of 1 inserted. There may be no extra
characters embedded in the string. Floating point constaints are of type
float4.
<H2><A NAME="sect10" HREF="#toc10">Constants of Postgres User-Defined Types </A></H2>
A constant of an <I>arbitrary</I>
type can be entered using the notation: or CAST 'string' AS type-name <BR>
The
value inside the string is passed to the input conversion routine for
the type called type-name. The result is a constant of the indicated type.
The explicit typecast may be omitted if there is no ambiguity as to the
type the constant must be, in which case it is automatically coerced.
<H2><A NAME="sect11" HREF="#toc11">Array constants </A></H2>
<I>Array constants</I> are arrays of any Postgres type, including
other arrays, string constants, etc. The general format of an array constant
is the following: {<val1><delim><val2><delim>} <BR>
Where <I><delim></I> is the delimiter for
the type stored in the `pg_type' class. (For built-in types, this is the comma
character, `,'.) An example of an array constant is {{1,2,3},{4,5,6},{7,8,9}}
<BR>
This constant is a two-dimensional, 3 by 3 array consisting of three sub-arrays
of integers. <P>
Individual array elements can and should be placed between
quotation marks whenever possible to avoid ambiguity problems with respect
to leading white space. <P>
<H2><A NAME="sect12" HREF="#toc12">FIELDS AND COLUMNS </A></H2>
<H2><A NAME="sect13" HREF="#toc13">Fields </A></H2>
A <I>field</I> is either
an attribute of a given class or one of the following: oid <BR>
xmin <BR>
xmax
<BR>
cmin <BR>
cmax <BR>
<P>
<I>Oid</I> stands for the unique identifier of an instance which
is added by Postgres to all instances automatically. Oids are not reused
and are 32 bit quantities. <P>
<I>Xmin, cmin, xmax</I> and <I>cmax</I> stand respectively
for the identity of the inserting transaction, the command identifier
within the transaction, the identity of the deleting transaction and its
associated deleting command. For further information on these fields consult
[STON87]. Times are represented internally as instances of the `abstime'
data type. Transaction and command identifiers are 32 bit quantities. Transactions
are assigned sequentially starting at 512.
<H2><A NAME="sect14" HREF="#toc14">Columns </A></H2>
A <I>column</I> is a construct
of the form: Instance-variable{.composite_field}.field `['number`]' <BR>
<I>Instance-variable</I>
identifies a particular class and can be thought of as standing for the
instances of that class. An instance variable is either a class name,
a surrogate for a class defined by means of a <I>from</I> clause, or the keyword
<B>new</B> or <B>current.</B> New and current can only appear in the action portion
of a rule, while other instance variables can be used in any SQL statement.
<I>Composite_field</I> is a field of of one of the Postgres composite types indicated
in the <I><A HREF="information.l.html">information</I>(l)</A>
section, while successive composite fields address
attributes in the class(s) to which the composite field evaluates. Lastly,
<I>field</I> is a normal (base type) field in the class(s) last addressed. If
<I>field</I> is of type array, then the optional <I>number</I> designator indicates
a specific element in the array. If no number is indicated, then all array
elements are returned.
<H2><A NAME="sect15" HREF="#toc15">Operators </A></H2>
Any built-in system, or user-defined operator
may be used in SQL. For the list of built-in and system operators consult
<B><A HREF="pgbuiltin.3.html">pgbuiltin</B>(3)</A>
. For a list of user-defined operators consult your system administrator
or run a query on the pg_operator class. Parentheses may be used for arbitrary
grouping of operators.
<H2><A NAME="sect16" HREF="#toc16">Expressions (a_expr) </A></H2>
An <I>expression</I> is one of the
following: ( a_expr ) <BR>
constant <BR>
attribute <BR>
a_expr binary_operator a_expr
<BR>
a_expr right_unary_operator <BR>
left_unary_operator a_expr <BR>
parameter <BR>
functional
expressions <BR>
aggregate expressions <BR>
We have already discussed constants
and attributes. The two kinds of operator expressions indicate respectively
binary and left_unary expressions. The following sections discuss the
remaining options.
<H2><A NAME="sect17" HREF="#toc17">Parameters </A></H2>
A <I>parameter</I> is used to indicate a parameter
in a SQL function. Typically this is used in SQL function definition statement.
The form of a parameter is: '$' number <BR>
For example, consider the definition
of a function, DEPT, as create function DEPT (char16) <BR>
<tt> </tt><tt> </tt>returns dept <BR>
<tt> </tt><tt> </tt>as 'select * from <BR>
<tt> </tt><tt> </tt> dept where name=$1' <BR>
<tt> </tt><tt> </tt>language 'sql' <BR>
<H2><A NAME="sect18" HREF="#toc18">Functional Expressions
</A></H2>
A <I>functional expression</I> is the name of a legal SQL function, followed
by its argument list enclosed in parentheses, e.g.: fn-name (a_expr{ , a_expr})
<BR>
For example, the following computes the square root of an employee salary.
sqrt(emp.salary) <BR>
<H2><A NAME="sect19" HREF="#toc19">Aggregate Expression </A></H2>
An <I>aggregate expression</I> represents
a simple aggregate (i.e., one that computes a single value) or an aggregate
function (i.e., one that computes a set of values). The syntax is the following:
aggregate.name (attribute) <BR>
Here, <I>aggregate_name</I> must be a previously
defined aggregate.
<H2><A NAME="sect20" HREF="#toc20">Target_list </A></H2>
A <I>target list</I> is a parenthesized, comma-separated
list of one or more elements, each of which must be of the form: a_expr[AS
result_attname] <BR>
Here, result_attname is the name of the attribute to
be created (or an already existing attribute name in the case of update
statements.) If <I>result_attname</I> is not present, then <I>a_expr</I> must contain
only one attribute name which is assumed to be the name of the result
field. In Postgres default naming is only used if <I>a_expr</I> is an attribute.
A <I>qualification</I> consists of any number of clauses connected by the logical
operators: not <BR>
and <BR>
or <BR>
A clause is an <I>a_expr</I> that evaluates to a Boolean
over a set of instances.
<H2><A NAME="sect21" HREF="#toc21">From List </A></H2>
The <I>from list</I> is a comma-separated
list of <I>from expressions</I>. <P>
Each <I>from expression</I> is of the form: [class_reference]
instance_variable <BR>
<tt> </tt><tt> </tt>{, [class_ref] instance_variable...} <BR>
where <I>class_reference</I>
is of the form class_name [*] <BR>
The <I>from expression</I> defines one or more
instance variables to range over the class indicated in <I>class_reference</I>.
One can also request the instance variable to range over all classes
that are beneath the indicated class in the inheritance hierarchy by postpending
the designator `*'.
<H2><A NAME="sect22" HREF="#toc22">SEE ALSO </A></H2>
<A HREF="insert.l.html">insert(l)</A>
, <A HREF="delete.l.html">delete(l)</A>
, <A HREF="execute.l.html">execute(l)</A>
, <A HREF="update.l.html">update(l)</A>
,
<A HREF="select.l.html">select(l)</A>
, <A HREF="monitor.1.html">monitor(1)</A>
. <P>
<HR><P>
<A NAME="toc"><B>Table of Contents</B></A><P>
<UL>
<LI><A NAME="toc0" HREF="#sect0">Section 4 - SQL Commands (COMMANDS)</A></LI>
<LI><A NAME="toc1" HREF="#sect1">General Information</A></LI>
<LI><A NAME="toc2" HREF="#sect2">DESCRIPTION</A></LI>
<LI><A NAME="toc3" HREF="#sect3">Comments</A></LI>
<LI><A NAME="toc4" HREF="#sect4">Names</A></LI>
<LI><A NAME="toc5" HREF="#sect5">Keywords</A></LI>
<LI><A NAME="toc6" HREF="#sect6">Constants</A></LI>
<LI><A NAME="toc7" HREF="#sect7">String Constants</A></LI>
<LI><A NAME="toc8" HREF="#sect8">Integer Constants</A></LI>
<LI><A NAME="toc9" HREF="#sect9">Floating Point Constants</A></LI>
<LI><A NAME="toc10" HREF="#sect10">Constants of Postgres User-Defined Types</A></LI>
<LI><A NAME="toc11" HREF="#sect11">Array constants</A></LI>
<LI><A NAME="toc12" HREF="#sect12">FIELDS AND COLUMNS</A></LI>
<LI><A NAME="toc13" HREF="#sect13">Fields</A></LI>
<LI><A NAME="toc14" HREF="#sect14">Columns</A></LI>
<LI><A NAME="toc15" HREF="#sect15">Operators</A></LI>
<LI><A NAME="toc16" HREF="#sect16">Expressions (a_expr)</A></LI>
<LI><A NAME="toc17" HREF="#sect17">Parameters</A></LI>
<LI><A NAME="toc18" HREF="#sect18">Functional Expressions</A></LI>
<LI><A NAME="toc19" HREF="#sect19">Aggregate Expression</A></LI>
<LI><A NAME="toc20" HREF="#sect20">Target_list</A></LI>
<LI><A NAME="toc21" HREF="#sect21">From List</A></LI>
<LI><A NAME="toc22" HREF="#sect22">SEE ALSO</A></LI>
</UL>
</BODY></HTML>
|
