WARNING

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.

"SECTION 4 *- POSTQUEL COMMANDS (COMMANDS)"

DESCRIPTION

The following is a description of the general syntax of POSTQUEL. Individual POSTQUEL statements and commands are treated separately in the document; this section describes the syntactic classes from which the constituent parts of POSTQUEL statements are drawn.

Comments

A comment is an arbitrary sequence of characters bounded on the left by ``/*'' and on the right by ``*/'', e.g:

/* This is a comment */

Names

Names in POSTQUEL are sequences of not more than 16 alphanumeric characters, starting with an alphabetic. Underscore (^_^) is considered an alphabetic.

Keywords

The following identifiers are reserved for use as keywords and may not be used otherwise:

s3

xx abort delete key remove 

xx addattr demand leftouter rename 

xx after descending light replace

xx all destroy load retrieve

xx always destroydb merge returns

xx and do move rewrite

xx append empty never rightouter 

xx arch_store end new rule

xx archive execute none sort

xx arg fetch nonulls stdin

xx ascending forward not stdout

xx attachas from NULL store

xx backward function on to

xx before heavy once transaction

xx begin in operator type

xx binary index or union

xx by indexable output_proc unique

xx cfunction inherits parallel using

xx close input_proc pfunction vacuum

xx cluster instance portal variable

xx copy instead postquel version

xx create intersect priority view

xx createdb into purge where

xx current intotemp quel with

xx define is relation

In addition, all POSTGRES classes have several predefined attributes used by the system. For a list of these, see the section Fields , below.

Constants

There are six types of constants for use in POSTQUEL. They are described below.

Character Constants

Single "character constants" may be used in POSTQUEL by surrounding them by single quotes, e.g., `n'.

String Constants

Strings in POSTQUEL are arbitrary sequences of ASCII characters bounded by double quotes (" "). Upper case 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 8K bytes. Larger objects may be created using the POSTGRES Large Object interface.

Integer Constants

"Integer constants" in POSTQUEL are collection of ASCII digits with no decimal point. Legal values range from (mi2147483647 to +2147483647. This will vary depending on the operating system and host machine.

Floating Point Constants

"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 constants are taken to be double-precision quantities with a range of approximately and a precision of 17 decimal digits. This will vary depending on the operating system and host machine.

Constants of POSTGRES User Defined Types

A constant of an arbitrary type can be entered using the notation:

"string"::type-name

Array constants

"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>}"

An example of an array constant is

"{{1,2,3},{4,5},{6,7,8,9}}"

This constant is an array consisting of three sub-arrays of integers.

Fields

A field is one of the following:

attribute name in a given class
all
oid
tmin
tmax
xmin
xmax
cmin
cmax
vtype

"Tmin, tmax, xmin, cmin, xmax" and cmax stand respectively for the time that the instance was inserted, the time the instance was deleted, 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 identifiers are 32 bit quantities which are assigned sequentially starting at 512. Command identifiers are 16 bit objects; hence, it is an error to have more than 65535 POSTQUEL commands within one transaction.

Attributes

An attribute is a construct of the form:

Instance-variable{.composite_field}.field `['number`]'

Operators

Any built-in system, or user defined operator may be used in POSTQUEL. For the list of built-in and system operators consult built-in types (commands) and b. system types (commands). 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.

Expressions (a_expr)

An expression is one of the following:

( a_expr )
constant
attribute
a_expr  binary_operator  a_expr
left_unary_operator  a_expr
parameter
functional expressions 
aggregate expressions
set expressions
class expression (not in Version 4.0)

Parameters

A parameter is used to indicate a parameter in a POSTQUEL function. Typically this is used in POSTQUEL function definition statement. The form of a parameter is:

'$' number

For example, consider the definition of a function, DEPT, as

define function DEPT 
 (language="postquel", returntype = dept)
    arg is (char16) as 
    retrieve (dept.all) where dept.name = $1

Functional Expressions

A functional expression is the name of a legal POSTQUEL function, followed by its argument list enclosed in parentheses, e.g.:

fn-name (a_expr{ , a_expr})

sqrt(emp.salary)

Aggregate Expression

An aggregate expression represents a simple aggregate (i.e one which computes a single value) or an aggregate function (i.e. one which computes a set of values). The syntax is the following:

aggregate_name `{' [unique [using] opr] a_expr
  [from from_list]
  [where qualification]`}'

In the case that all instance variables used in the aggregate expression are defined in the from list, a simple aggregate has been defined. For example, to sum employee salaries whose age is greater than 30, one would write:

retrieve (total = sum {e.salary from e in emp
                                where e.age > 30} )

retrieve (total = sum {EMP.salary where emp.age > 30})

On the other hand, if there are variables used in the aggregate expression that are not defined in the from list, e.g:

avg {emp.salary where emp.age = e.age}

retrieve (e.age, avg {emp.salary where emp.age = e.age})
 from e in emp 
 where e.age > 18

Set Expressions

Set expressions are not supported in Version 4.0.

A set expression defines a collection of instances from some class and uses the following syntax:

 {target_list from from_list where qualification}

{emp.name where emp.age > 40}

{emp.name where emp.dept = dept.dname}

Class Expression

Class expressions are not supported in Version 4.0.

A class expression is an expression of the form:

class_constructor binary_class_operator class_constructor
unary_class_operator class_constructor

union  union of two classes
intersect intersection of two classes
-  difference of two classes
>>  left class contains right class
<<  right class contains left class
==  right class equals left class 

empty  right class is empty

An example of a query with a class expression is one to find all the departments with no employees:

retrieve (dept.dname)
    where empty {emp.name where emp.dept = dept.dname}

Target_list

A "target list" is a parenthesized, comma-separated list of one or more elements, each of which must be of the form:

[result_attname  =] a_expr

Qualification

A qualification consists of any number of clauses connected by the logical operators:

not
and
or

From List

The from list is a comma-separated list of from expressions.

Each from expression is of the form:

instance_variable-1 {, instance_variable-2}
 in class_reference

class_name [time_expression] [*]

Time Expressions

A time expression is in one of two forms:

 [date]
 [date-1, date-2]

In each case, the date is a character string of the form

[MON-FRI] "MMM DD [HH:MM:SS] YYYY" [Timezone]
fn

For example,

["Jan 1 1990"]
["Mar 3 00:00:00 1980", "Mar 3 23:59:59 1981"]
fn

SEE ALSO

append(commands), delete(commands), execute(commands), replace(commands), retrieve(commands), monitor(unix).

BUGS

The following constructs are not available in Version 4.0:

class expressions
set expressions