.\" This is -*-nroff-*-
.\" XXX standard disclaimer belongs here....
.\" /usr/local/devel/postgres-4.2-devel/src/ref/RCS/built-in.3pqsrc,v 1.23 1994/02/18 11:26:23 aoki Exp
.TH INTRODUCTION BUILT-INS 02/18/94
.XA 0 "Section 3 \(em What comes with \*(PG (BUILT-INS)"
.BH "SECTION 3 \(em WHAT COMES WITH \*(PG (BUILT-INS)"
.SH "DESCRIPTION"
This section describes the data types, functions and operators
available to users in \*(PG as it is distributed.
.XA 1 "Built-in and System Types"
.SH "BUILT-IN AND SYSTEM TYPES"
This section describes both 
.BR built-in
and
.BR system
data types.  Built-in types are required for \*(PG to run.  System
types are installed in every database, but are not strictly required.
Built-in types are marked with asterisks in the table below.
.PP
Users may add new types to \*(PG using the
.IR "define type"
command described in this manual.  User-defined types are not
described in this section.
.XA 2 "List of built-in types"
.PP
.if n .ta 5 +15 +40
.if t .ta 0.5i +1.5i +3.0i
.in 0
.nf
	\fBPOSTGRES Type\fP	\fBMeaning\fP	\fBRequired\fP
	abstime	absolute date and time	*
	aclitem	access control list item	*
	bool	boolean	*
	box	2-dimensional rectangle
	bytea	variable length array of bytes	*
	char	character	*
	char2	array of 2 characters	*
	char4	array of 4 characters	*
	char8	array of 8 characters	*
	char16	array of 16 characters	*
	cid	command identifier type	*
	filename	large object filename	*
	int2	two-byte signed integer	*
	int28	array of 8 int2	*
	int4	four-byte signed integer	*
	float4	single-precision floating-point number	*
	float8	double-precision floating-point number	*
	lseg	2-dimensional line segment
	oid	object identifier type	*
	oid8	array of 8 oid	*
	oidchar16	oid and char16 composed	*
	oidint2	oid and int2 composed	*
	oidint4	oid and int4 composed	*
	path	variable-length array of lseg
	point	2-dimensional geometric point
	polygon	2-dimensional polygon
	regproc	registered procedure	*
	reltime	relative date and time	*
	smgr	storage manager	*
	text	variable length array of characters	*
	tid	tuple identifier type	*
	tinterval	time interval	*
	xid	transaction identifier type	*
.fi
.in
.PP
As a rule, the built-in types are all either (1) internal types, in
which case the user should not worry about their external format, or
(2) have obvious formats.  The exceptions to this rule are the three
time types.
.XA 2 "Syntax of date and time types"
.SP "DATE AND TIME" BUILT-INS 02/18/94
.SH "ABSOLUTE TIME"
Absolute time is specified using the following syntax:
.(C
Month  Day [ Hour : Minute : Second ]  Year [ Timezone ]
.)C
.(l
where	Month is Jan, Feb, ..., Dec
	Day is 1, 2, ..., 31
	Hour is 01, 02, ..., 24
	Minute is 00, 01, ..., 59
	Second is 00, 01, ..., 59
	Year is 1901, 1902, ..., 2038
.)l
Valid dates are from Dec 13 20:45:53 1901 GMT to Jan 19 03:14:04
2038 GMT.  As of Version 3.0, times are no longer read and written
using Greenwich Mean Time; the input and output routines default to
the local time zone.
.PP
The special absolute time values \*(lqcurrent\*(rq,
\*(lqinfinity\*(rq and \*(lq-infinity\*(rq are also provided.
\*(lqinfinity\*(rq specifies a time later than any valid time, and
\*(lq-infinity\*(rq specifies a time earlier than any valid time.
\*(lqcurrent\*(rq indicates that the current time should be
substituted whenever this value appears in a computation.
.PP
The strings \*(lqnow\*(rq and \*(lqepoch\*(rq can be used to specify
time values.  \*(lqnow\*(rq means the current time, and differs from
\*(lqcurrent\*(rq in that the current time is immediately substituted
for it.  \*(lqepoch\*(rq means Jan 1 00:00:00 1970 GMT.
.SH "RELATIVE TIME"
Relative time is specified with the following syntax:
.(C
@ Quantity Unit [Direction]
.)C
.(l
where 	Quantity is `1', `2', ...
	Unit is ``second'', ``minute'', ``hour'', ``day'', ``week'',
	``month'' (30-days), or ``year'' (365-days),
	or PLURAL of these units.
	Direction is ``ago''
.)l
.PP
.RB ( Note :
Valid relative times are less than or equal to 68 years.)
In addition, the special relative time \*(lqUndefined RelTime\*(rq is
provided.
.SH "TIME RANGES"
Time ranges are specified as:
.(C
[ 'abstime' 'abstime']
.)C
where 
.IR abstime
is a time in the absolute time format.  Special abstime values such as 
\*(lqcurrent\*(rq, \*(lqinfinity\*(rq and \*(lq-infinity\*(rq can be used.
.XA 1 "Built-in operators and functions"
.SP OPERATORS BUILT-INS 02/18/94
.SH OPERATORS
\*(PG provides a large number of built-in operators on system types.
These operators are declared in the system catalog
\*(lqpg_operator\*(rq.  Every entry in \*(lqpg_operator\*(rq includes
the object ID of the procedure that implements the operator.
.PP
Users may invoke operators using the operator name, as in
.(C
retrieve (emp.all) where emp.salary < 40000
.)C
Alternatively, users may call the functions that implement the
operators directly.  In this case, the query above would be expressed
as
.(C
retrieve (emp.all) where int4lt(emp.salary, 40000)
.)C
The rest of this section provides a list of the built-in operators and
the functions that implement them.  Binary operators are listed first,
followed by unary operators.
.XA 2 "Binary operators"
.SH "BINARY OPERATORS"
This list was generated from the \*(PG system catalogs with the query
.(C
retrieve (argtype = t1.typname, o.oprname,
	t0.typname, p.proname,
	ltype=t1.typname, rtype=t2.typname)
    from p in pg_proc, t0 in pg_type, t1 in pg_type,
	t2 in pg_type, o in pg_operator
    where p.prorettype = t0.oid
	and RegprocToOid(o.oprcode) = p.oid
	and p.pronargs = 2
	and o.oprleft = t1.oid
	and o.oprright = t2.oid
.)C
The list is sorted by the built-in type name of the first operand.
The
.IR "function prototype"
column gives the return type, function name, and argument types for
the procedure that implements the operator.  Note that these function
prototypes are cast in terms of \*(PQ types and so are
.BR not
directly usable as C function prototypes.
.ie t \{
.so postquel/binary.tbl
\}
.el \{
.so obj/binary.nr
\}
.XA 2 "Unary operators"
.SH "UNARY OPERATORS"
The tables below give right and left unary operators.
Left unary operators have the operator precede the operand;
right unary operators have the operator follow the operand.
.PP
.SS "Right Unary Operators"
.ie t \{
.so postquel/right_unary.tbl
\}
.el \{
.so obj/right_unary.nr
\}
.PP
.SS "Left Unary Operators"
.\" we want soelim to include the .nr table but not the .tbl table
.ie t \{
.so postquel/left_unary.tbl
\}
.el \{
.so obj/left_unary.nr
\}
.XA 1 "Built-in aggregate functions"
.SP AGGREGATES BUILT-INS 02/18/94
.SH "AGGREGATE FUNCTIONS"
The table below gives the aggregate functions that are normally
registered in the system catalogs.  None of them are required for
\*(PG to operate.
.PP
.if n .ta 5 +15
.if t .ta 0.5i +1.5i
.in 0
.nf
	\fBName\fP	\fBOperation\fP	
	int2ave	int2 average
	int4ave	int4 average
	float4ave	float4 average
	float8ave	float8 average
	int2sum	int2 sum (total)
	int4sum	int4 sum (total)
	float4sum	float4 sum (total)
	float8sum	float8 sum (total)
	int2max	int2 maximum (high value)
	int4max	int4 maximum (high value)
	float4max	float4 maximum (high value)
	float8max	float8 maximum (high value)
	int2min	int2 minimum (low value)
	int4min	int4 minimum (low value)
	float4min	float4 minimum (low value)
	float8min	float8 minimum (low value)
	count	any count
.fi
.in
.SH "SEE ALSO"
For examples on specifying literals of built-in types, see
.IR postquel (commands).
.SH BUGS
The lists of types, functions, and operators are accurate only for
Version \*(PV.  The lists will be incomplete and contain extraneous
entries in future versions of \*(PG.
.PP
Although most of the input and output functions correponding to the
base types (e.g., integers and floating point numbers) do some
error-checking, none of them are particularly rigorous about it.  More
importantly, almost none of the operators and functions (e.g.,
addition and multiplication) perform any error-checking at all.
Consequently, many of the numeric operations will (for example)
silently underflow or overflow.
.PP
Some of the input and output functions are not invertible.  That is,
the result of an output function may lose precision when compared to
the original input.