GNU/Linux man pages
Livre :
Expressions régulières,
Syntaxe et mise en oeuvre :
GNU/Linux |
RedHat 6.2(Zoot) |
|
 |
sql(l) |
 |
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.
A
comment 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
// And this is another supported comment style, like C++
We also support
C-style comments, e.g.:
/* multi
line
comment */
Names 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.
The following identifiers are reserved for use as keywords and may not be used otherwise:
In addition, all Postgres classes have several predefined attributes used by the system.
There are six types of constants for use in SQL. They are described below.
Strings 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.
Integer constants 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.
Floating
point constants consist of an integer part, a decimal
point, and a fraction part or scientific notation of the
following format:
{<dig>} .{<dig>} [e [+-] {<dig>}]
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.
A constant of
an arbitrary type can be entered using the notation:
or
CAST ’string’ AS type-name
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.
Array
constants 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>}
Where <delim> 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}}
This constant is a two-dimensional, 3 by 3 array consisting
of three sub-arrays of integers.
Individual array elements can and should be placed between quotation marks whenever possible to avoid ambiguity problems with respect to leading white space.
A field
is either an attribute of a given class or one of the
following:
oid
xmin
xmax
cmin
cmax
Oid 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.
Xmin, cmin, xmax and cmax 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.
A column
is a construct of the form:
Instance-variable{.composite_field}.field
’[’number’]’
Instance-variable 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 from clause, or the
keyword new or current. New and current can
only appear in the action portion of a rule, while other
instance variables can be used in any SQL statement.
Composite_field is a field of of one of the Postgres
composite types indicated in the pgbuiltin(l)
section, while successive composite fields address
attributes in the class(s) to which the composite field
evaluates. Lastly, field is a normal (base type)
field in the class(s) last addressed. If field is of
type array, then the optional number designator
indicates a specific element in the array. If no number is
indicated, then all array elements are returned.
Any built-in system, or user-defined operator may be used in SQL. For the list of built-in and system operators consult pgbuiltin(3). 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.
An
expression is one of the following:
( a_expr )
constant
attribute
a_expr binary_operator a_expr
a_expr right_unary_operator
left_unary_operator a_expr
parameter
functional expressions
aggregate expressions
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.
A
parameter 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
For example, consider the definition of a function, DEPT, as
create function DEPT (name)
|
returns dept |
||
|
as ’select * from |
||
|
dept where name=$1’ |
||
|
language ’sql’ |
A functional
expression is the name of a legal SQL function, followed
by its argument list enclosed in parentheses, e.g.:
fn-name (a_expr{ , a_expr})
For example, the following computes the square root of an
employee salary.
sqrt(emp.salary)
An aggregate
expression 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)
Here, aggregate_name must be a previously defined
aggregate.
A target
list is a parenthesized, comma-separated list of one or
more elements, each of which must be of the form:
a_expr[AS result_attname]
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 result_attname is not
present, then a_expr 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 a_expr is an
attribute.
A
qualification consists of any number of clauses
connected by the logical operators:
not
and
or
A clause is an a_expr that evaluates to a Boolean
over a set of instances.
The from list is a comma-separated list of from expressions.
Each from
expression is of the form:
[class_reference] instance_variable
|
{, [class_ref] instance_variable...} |
where class_reference is
of the form
class_name [*]
The from expression defines one or more instance
variables to range over the class indicated in
class_reference. 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 “*”.
insert(l), delete(l), execute(l), update(l), select(l), psql(1).
|
sql(l) | |