.lf 1 tmac.an.nr .\" This is -*-nroff-*- .\" XXX standard disclaimer belongs here.... .\" $Header: /data/01/postgres/src/ref/RCS/tmac.an.nr,v 1.1 1993/01/26 02:43:13 aoki Exp $ .ds II INGRES .ds PG POSTGRES .ds UU UNIX .ds PQ POSTQUEL .ds LI LIBPQ .ds PV 4.1 .de (l \" fake "-me"-style lists .nf .ie '\\$1'M' .in +0n .el .in +5n .. .de )l .fi .in .. .de (C \" constant-width font blocks .(l \\$1 .sp .. .de )C .sp .)l .. .de BH \" reference manual "big header" for sections .SH NAME \&\\$1 .. .de SB \" section/index stuff .. \" no-op .de SE .. \" no-op .de XA .. \" no-op .de XP .. \" no-op .de SP .. .ds lq "" .ds rq "" .lf 1 built-in.3pqsrc .\" This is -*-nroff-*- .\" XXX standard disclaimer belongs here.... .\" $Header: /data/01/postgres/src/ref/RCS/built-in.3pqsrc,v 1.19 1993/05/28 09:06:44 aoki Exp $ .TH INTRODUCTION BUILT-INS 05/28/93 .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 * 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 * 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 These types all have obvious formats except for the three time types, explained in the following. .XA 2 "Syntax of date and time types" .SP "DATE AND TIME" BUILT-INS 01/23/93 .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 1970, 1971, ..., 2038 .)l Valid dates are, therefore, Jan 1 00:00:00 1970 GMT to Jan 1 00:00:00 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 \*(lqnow\*(rq is provided as a convenience. The special absolute time \*(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] [ , abstime] [abstime, \*(lq\*(rq] [\*(lq\*(rq, \*(lq\*(rq] .)C where .IR abstime is a time in the absolute time format. Pairs of double-quotes ("") will cause the time interval to either start or end at the least or greatest time allowable, that is, either Jan 1 00:00:00 1902 or Jan 1 00:00:00 2038, respectively. .XA 1 "Built-in operators and functions" .SP OPERATORS BUILT-INS 01/23/93 .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 \{ .lf 1 postquel/binary.tbl .\" This is -*-nroff-*- .TS center expand; l l c l c l l lw(2i). \fBType\fP \fBOperator\fP \fBPOSTGRES Function Prototype\fP \fBOperation\fP _ abstime !\(eq bool abstimene(abstime, abstime) inequality + abstime timepl(abstime, reltime) addition \(mi abstime timemi(abstime, reltime) subtraction <\(eq bool abstimele(abstime, abstime) less or equal bool ininterval(abstime, tinterval) abstime in tinterval? < bool abstimelt(abstime, abstime) less than \(eq bool abstimeeq(abstime, abstime) equality >\(eq bool abstimege(abstime, abstime) greater or equal > bool abstimegt(abstime, abstime) greater than _ bool \(eq bool booleq(bool, bool) equality !\(eq bool boolne(bool, bool) inequality _ box && bool box_overlap(box, box) boxes overlap &< bool box_overleft(box, box) T{ box A overlaps box B, but does not extend to right of box B T} &> bool box_overright(box, box) T{ box A overlaps box B, but does not extend to left of box B T} << bool box_left(box, box) A is left of B <\(eq bool box_le(box, box) area less or equal < bool box_lt(box, box) area less than \(eq bool box_eq(box, box) area equal >\(eq bool box_ge(box, box) area greater or equal >> bool box_right(box, box) A is right of B > bool box_gt(box, box) area greater than @ bool box_contained(box, box) A is contained in B ~\(eq bool box_same(box, box) box equality ~ bool box_contain(box, box) A contains B _ char !\(eq bool charne(char, char) inequality * bool charmul(char, char) multiplication + bool charpl(char, char) addition \(mi bool charmi(char, char) subtraction / bool chardiv(char, char) division <\(eq bool charle(char, char) less or equal < bool charlt(char, char) less than \(eq bool chareq(char, char) equality >\(eq bool charge(char, char) greater or equal > bool chargt(char, char) greater than _ char16 !\(eq bool char16ne(char16, char16) inequality !~ bool char16regexne(char16, char16) T{ A does not match regular expression B (\*(PG uses the libc regexp calls for this operation) T} <\(eq bool char16le(char16, char16) less or equal < bool char16lt(char16, char16) less than \(eq bool char16eq(char16, char16) equality >\(eq bool char16ge(char16, char16) greater or equal > bool char16gt(char16, char16) greater than ~ bool char16regexeq(char16, char16) T{ A matches regular expression B (\*(PG uses the libc regexp calls for this operation) T} _ float4 !\(eq bool float4ne(float4, float4) inequality * float4 float4mul(float4, float4) multiplication + float4 float4pl(float4, float4) addition \(mi float4 float4mi(float4, float4) subtraction / float4 float4div(float4, float4) division <\(eq bool float4le(float4, float4) less or equal < bool float4lt(float4, float4) less than \(eq bool float4eq(float4, float4) equality >\(eq bool float4ge(float4, float4) greater or equal > bool float4gt(float4, float4) greater than _ float8 !\(eq bool float8ne(float8, float8) inequality * float8 float8mul(float8, float8) multiplication + float8 float8pl(float8, float8) addition \(mi float8 float8mi(float8, float8) subtraction / float8 float8div(float8, float8) division <\(eq bool float8le(float8, float8) less or equal < bool float8lt(float8, float8) less than1 \(eq bool float8eq(float8, float8) equality >\(eq bool float8ge(float8, float8) greater or equal > bool float8gt(float8, float8) greater than ^ float8 dpow(float8, float8) exponentiation _ int2 !\(eq bool int2ne(int2, int2) inequality !\(eq int4 int24ne(int2, int4) inequality % int2 int2mod(int2, int2) modulus % int4 int24mod(int2, int4) modulus * int2 int2mul(int2, int2) multiplication * int4 int24mul(int2, int4) multiplication + int2 int2pl(int2, int2) addition + int4 int24pl(int2, int4) addition \(mi int2 int2mi(int2, int2) subtraction \(mi int4 int24mi(int2, int4) subtraction / int2 int2div(int2, int2) division / int4 int24div(int2, int4) division <\(eq bool int2le(int2, int2) less or equal <\(eq int4 int24le(int2, int4) less or equal < bool int2lt(int2, int2) less than < int4 int24lt(int2, int4) less than \(eq bool int2eq(int2, int2) equality \(eq int4 int24eq(int2, int4) equality >\(eq bool int2ge(int2, int2) greater or equal >\(eq int4 int24ge(int2, int4) greater or equal > bool int2gt(int2, int2) greater than > int4 int24gt(int2, int4) greater than int2 int2inc(int2) increment _ int4 !!\(eq bool int4notin(int4, char16) T{ This is the relational ``not in'' operator, and is not intended for public use. T} !\(eq bool int4ne(int4, int4) inequality !\(eq int4 int42ne(int4, int2) inequality % int4 int42mod(int4, int2) modulus % int4 int4mod(int4, int4) modulus * int4 int42mul(int4, int2) multiplication * int4 int4mul(int4, int4) multiplication + int4 int42pl(int4, int2) addition + int4 int4pl(int4, int4) addition \(mi int4 int42mi(int4, int2) subtraction \(mi int4 int4mi(int4, int4) subtraction / int4 int42div(int4, int2) division / int4 int4div(int4, int4) division <\(eq bool int4le(int4, int4) less or equal <\(eq int4 int42le(int4, int2) less or equal < bool int4lt(int4, int4) less than < int4 int42lt(int4, int2) less than \(eq bool int4eq(int4, int4) equality \(eq int4 int42eq(int4, int2) equality >\(eq bool int4ge(int4, int4) greater or equal >\(eq int4 int42ge(int4, int2) greater or equal > bool int4gt(int4, int4) greater than > int4 int42lt(int4, int2) less than int4 int4inc(int4) increment _ oid !!\(eq bool oidnotin(oid, char16) T{ This is the relational ``not in'' operator, and is not intended for public use. T} !\(eq bool oidne(oid, oid) inequality !\(eq bool oidne(oid, regproc) inequality <\(eq bool oidle(oid, oid) less or equal < bool oidlt(oid, oid) less than \(eq bool oideq(oid, oid) equality \(eq bool oideq(oid, regproc) equality >\(eq bool oidge(oid, oid) greater or equal > bool oidgt(oid, oid) greater than _ oidchar16 !\(eq bool oidchar16ne(oidchar16, oidchar16) inequality < bool oidchar16lt(oidchar16, oidchar16) less than <\(eq bool oidchar16le(oidchar16, oidchar16) less or equal \(eq bool oidchar16eq(oidchar16, oidchar16) equality > bool oidchar16gt(oidchar16, oidchar16) greater than >\(eq bool oidchar16ge(oidchar16, oidchar16) greater or equal _ oidint4 !\(eq bool oidint4ne(oidint4, oidint4) inequality < bool oidint4lt(oidint4, oidint4) less than <\(eq bool oidint4le(oidint4, oidint4) less or equal \(eq bool oidint4eq(oidint4, oidint4) equality > bool oidint4gt(oidint4, oidint4) greater than >\(eq bool oidint4ge(oidint4, oidint4) greater or equal _ point !< bool point_left(point, point) A is left of B !> bool point_right(point, point) A is right of B !^ bool point_above(point, point) A is above B !| bool point_below(point, point) A is below B \(eq|\(eq bool point_eq(point, point) equality ---> bool on_pb(point, box) point inside box ---` bool on_ppath(point, path) point on path <---> int4 pointdist(point, point) distance between points _ polygon && bool poly_overlap(polygon, polygon) polygons overlap &< bool poly_overleft(polygon, polygon) T{ A overlaps B but does not extend to right of B T} &> bool poly_overright(polygon, polygon) T{ A overlaps B but does not extend to left of B T} << bool poly_left(polygon, polygon) A is left of B >> bool poly_right(polygon, polygon) A is right of B @ bool poly_contained(polygon, polygon) A is contained by B ~\(eq bool poly_same(polygon, polygon) equality ~ bool poly_contain(polygon, polygon) A contains B _ reltime !\(eq bool reltimene(reltime, reltime) inequality <\(eq bool reltimele(reltime, reltime) less or equal < bool reltimelt(reltime, reltime) less than \(eq bool reltimeeq(reltime, reltime) equality >\(eq bool reltimege(reltime, reltime) greater or equal > bool reltimegt(reltime, reltime) greater than _ text !\(eq bool textne(text, text) inequality !~ bool textregexne(text, text) T{ A does not contain the regular expression B. \*(PG uses the libc regexp interface for this operator. T} <\(eq bool text_le(text, text) less or equal < bool text_lt(text, text) less than \(eq bool texteq(text, text) equality >\(eq bool text_ge(text, text) greater or equal > bool text_gt(text, text) greater than ~ bool textregexeq(text, text) T{ A contains the regular expression B. \*(PG uses the libc regexp interface for this operator. T} _ tinterval #!\(eq bool intervallenne(tinterval, reltime) T{ interval length not equal to reltime. T} #<\(eq bool intervallenle(tinterval, reltime) T{ interval length less or equal reltime T} #< bool intervallenlt(tinterval, reltime) T{ interval length less than reltime T} #\(eq bool intervalleneq(tinterval, reltime) T{ interval length not equal to reltime T} #>\(eq bool intervallenge(tinterval, reltime) T{ interval length greater or equal reltime T} #> bool intervallengt(tinterval, reltime) T{ interval length greater than reltime T} && bool intervalov(tinterval, tinterval) intervals overlap << bool intervalct(tinterval, tinterval) A contains B \(eq bool intervaleq(tinterval, tinterval) equality .TE .lf 165 built-in.3pqsrc \} .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 \{ .lf 1 postquel/right_unary.tbl .\" This is -*-nroff-*- .TS center expand; l l c l c l l lw(2i). \fBType\fP \fBOperator\fP \fBPOSTGRES Function Prototype\fP \fBOperation\fP _ float8 % float8 dround(float8) round to nearest integer .TE .lf 178 built-in.3pqsrc \} .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 \{ .lf 1 postquel/left_unary.tbl .\" This is -*-nroff-*- .TS center expand; l l c l c l l lw(2i). \fBType\fP \fBOperator\fP \fBPOSTGRES Function Prototype\fP \fBOperation\fP _ box @@ point box_center(box) center of box _ float4 @ float4 float4abs(float4) absolute value _ float8 @ float8 float8abs(float8) absolute value % float8 dtrunc(float8) truncate to integer |/ float8 dsqrt(float8) square root ||/ float8 dcbrt(float8) cube root : float8 dexp(float8) exponential function ; float8 dlog1(float8) natural logarithm _ tinterval | abstime intervalstart(tinterval) start of interval .TE .lf 187 built-in.3pqsrc \} .el \{ .so obj/left_unary.nr \} .XA 1 "Built-in aggregate functions" .SP AGGREGATES BUILT-INS 05/28/93 .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 int4 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.