From 4fc09ad00c3cc95003a5523d85999da1dd4f9d75 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Wed, 24 Nov 2010 00:00:34 +0200 Subject: [PATCH] Add index entries for more functions Also, move index entries into the tables, closer to the function description, for easier editing in the future. Resort some tables to be more alphabetical. Remove the entries for count, max, min, and sum in the tutorial area, because that was felt to be confusing. Thom Brown --- doc/src/sgml/func.sgml | 1781 +++++++++++++++++++++++++-------------- doc/src/sgml/query.sgml | 6 - 2 files changed, 1141 insertions(+), 646 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 6992aaa281..21f1ddfa50 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -610,67 +610,6 @@ boundary cases can therefore vary depending on the host system. - - abs - - - cbrt - - - ceiling - - - degrees - - - div - - - exp - - - floor - - - ln - - - log - - - mod - - - pi - - - power - - - radians - - - random - - - round - - - setseed - - - sign - - - sqrt - - - trunc - - - width_bucket - - Mathematical Functions @@ -686,7 +625,12 @@ - abs(x) + + + abs + + abs(x) + (same as input) absolute value abs(-17.4) @@ -694,7 +638,12 @@ - cbrt(dp) + + + cbrt + + cbrt(dp) + dp cube root cbrt(27.0) @@ -702,7 +651,12 @@ - ceil(dp or numeric) + + + ceil + + ceil(dp or numeric) + (same as input) smallest integer not less than argument ceil(-42.8) @@ -710,7 +664,12 @@ - ceiling(dp or numeric) + + + ceiling + + ceiling(dp or numeric) + (same as input) smallest integer not less than argument (alias for ceil) ceiling(-95.3) @@ -718,7 +677,12 @@ - degrees(dp) + + + degrees + + degrees(dp) + dp radians to degrees degrees(0.5) @@ -726,8 +690,13 @@ - div(y numeric, - x numeric) + + + div + + div(y numeric, + x numeric) + numeric integer quotient of y/x div(9,4) @@ -735,7 +704,12 @@ - exp(dp or numeric) + + + exp + + exp(dp or numeric) + (same as input) exponential exp(1.0) @@ -743,7 +717,12 @@ - floor(dp or numeric) + + + floor + + floor(dp or numeric) + (same as input) largest integer not greater than argument floor(-42.8) @@ -751,7 +730,12 @@ - ln(dp or numeric) + + + ln + + ln(dp or numeric) + (same as input) natural logarithm ln(2.0) @@ -759,7 +743,12 @@ - log(dp or numeric) + + + log + + log(dp or numeric) + (same as input) base 10 logarithm log(100.0) @@ -776,8 +765,13 @@ - mod(y, - x) + + + mod + + mod(y, + x) + (same as argument types) remainder of y/x mod(9,4) @@ -785,7 +779,12 @@ - pi() + + + pi + + pi() + dp π constant pi() @@ -793,8 +792,13 @@ - power(a dp, - b dp) + + + power + + power(a dp, + b dp) + dp a raised to the power of b power(9.0, 3.0) @@ -811,7 +815,12 @@ - radians(dp) + + + radians + + radians(dp) + dp degrees to radians radians(45.0) @@ -819,7 +828,12 @@ - random() + + + random + + random() + dp random value in the range 0.0 <= x < 1.0 random() @@ -827,7 +841,12 @@ - round(dp or numeric) + + + round + + round(dp or numeric) + (same as input) round to nearest integer round(42.4) @@ -843,7 +862,12 @@ - setseed(dp) + + + setseed + + setseed(dp) + void set seed for subsequent random() calls (value between -1.0 and 1.0, inclusive) @@ -852,7 +876,12 @@ - sign(dp or numeric) + + + sign + + sign(dp or numeric) + (same as input) sign of the argument (-1, 0, +1) sign(-8.4) @@ -860,7 +889,12 @@ - sqrt(dp or numeric) + + + sqrt + + sqrt(dp or numeric) + (same as input) square root sqrt(2.0) @@ -868,7 +902,12 @@ - trunc(dp or numeric) + + + trunc + + trunc(dp or numeric) + (same as input) truncate toward zero trunc(42.8) @@ -884,7 +923,12 @@ - width_bucket(op numeric, b1 numeric, b2 numeric, count int) + + + width_bucket + + width_bucket(op numeric, b1 numeric, b2 numeric, count int) + int return the bucket to which operand would be assigned in an equidepth histogram with count @@ -917,31 +961,6 @@ degrees() above. - - acos - - - asin - - - atan - - - atan2 - - - cos - - - cot - - - sin - - - tan - -
Trigonometric Functions @@ -955,44 +974,83 @@ - acos(x) + + + acos + acos(x) + inverse cosine - asin(x) + + + asin + + asin(x) + inverse sine - atan(x) + + + atan + + atan(x) + inverse tangent - atan2(y, - x) + + + atan2 + + atan2(y, + x) + inverse tangent of y/x - cos(x) + + + cos + + cos(x) + cosine - cot(x) + + + cot + + cot(x) + cotangent - sin(x) + + + sin + + sin(x) + sine - tan(x) + + + tan + + tan(x) + tangent @@ -1040,34 +1098,6 @@ - - bit_length - - - char_length - - - lower - - - octet_length - - - overlay - - - position - - - substring - - - trim - - - upper - -
<acronym>SQL</acronym> String Functions and Operators @@ -1114,7 +1144,12 @@ - bit_length(string) + + + bit_length + + bit_length(string) + int Number of bits in string bit_length('jose') @@ -1122,7 +1157,12 @@ - char_length(string) or character_length(string) + + + char_length + + char_length(string) or character_length(string) + int Number of characters in string @@ -1141,7 +1181,12 @@ - lower(string) + + + lower + + lower(string) + text Convert string to lower case lower('TOM') @@ -1149,7 +1194,12 @@ - octet_length(string) + + + octet_length + + octet_length(string) + int Number of bytes in string octet_length('jose') @@ -1157,7 +1207,12 @@ - overlay(string placing string from int for int) + + + overlay + + overlay(string placing string from int for int) + text Replace substring @@ -1167,7 +1222,12 @@ - position(substring in string) + + + position + + position(substring in string) + int Location of specified substring position('om' in 'Thomas') @@ -1175,7 +1235,12 @@ - substring(string from int for int) + + + substring + + substring(string from int for int) + text Extract substring @@ -1210,6 +1275,9 @@ + + trim + trim(leading | trailing | both characters from string) @@ -1225,7 +1293,12 @@ - upper(string) + + + upper + + upper(string) + text Convert string to upper case upper('tom') @@ -1241,103 +1314,6 @@ SQL-standard string functions listed in . - - ascii - - - btrim - - - chr - - - concat - - - concat_ws - - - convert - - - convert_from - - - convert_to - - - decode - - - encode - - - format - - - initcap - - - left - - - lpad - - - ltrim - - - md5 - - - pg_client_encoding - - - quote_ident - - - quote_literal - - - quote_nullable - - - repeat - - - replace - - - reverse - - - right - - - rpad - - - rtrim - - - split_part - - - strpos - - - substr - - - to_ascii - - - to_hex - - - translate - -
Other String Functions @@ -1353,7 +1329,12 @@ - ascii(string) + + + ascii + + ascii(string) + int ASCII code of the first character of the @@ -1366,8 +1347,13 @@ - btrim(string text - , characters text) + + + btrim + + btrim(string text + , characters text) + text Remove the longest string consisting only of characters @@ -1379,7 +1365,12 @@ - chr(int) + + + chr + + chr(int) + text Character with the given code. For UTF8 the @@ -1394,6 +1385,9 @@ + + concat + concat(str "any" [, str "any" [, ...] ]) @@ -1407,6 +1401,9 @@ + + concat_ws + concat_ws(sep text, str "any" [, str "any" [, ...] ]) @@ -1422,6 +1419,9 @@ + + convert + convert(string bytea, src_encoding name, dest_encoding name) @@ -1443,6 +1443,9 @@ + + convert_from + convert_from(string bytea, src_encoding name) @@ -1458,6 +1461,9 @@ + + convert_to + convert_to(string text, dest_encoding name) @@ -1471,6 +1477,9 @@ + + decode + decode(string text, type text) @@ -1485,6 +1494,9 @@ + + encode + encode(data bytea, type text) @@ -1501,6 +1513,9 @@ + + format + format(formatstr text [, str "any" [, ...] ]) @@ -1522,7 +1537,12 @@ - initcap(string) + + + initcap + + initcap(string) + text Convert the first letter of each word to upper case and the @@ -1535,6 +1555,9 @@ + + left + left(str text, n int) @@ -1548,7 +1571,12 @@ - length(string) + + + length + + length(string) + int Number of characters in string @@ -1572,6 +1600,9 @@ + + lpad + lpad(string text, length int , fill text) @@ -1590,7 +1621,11 @@ - ltrim(string text + + + ltrim + + ltrim(string text , characters text) text @@ -1604,7 +1639,12 @@ - md5(string) + + + md5 + + md5(string) + text Calculates the MD5 hash of string, @@ -1615,7 +1655,12 @@ - pg_client_encoding() + + + pg_client_encoding + + pg_client_encoding() + name Current client encoding name @@ -1625,7 +1670,12 @@ - quote_ident(string text) + + + quote_ident + + quote_ident(string text) + text Return the given string suitably quoted to be used as an identifier @@ -1640,7 +1690,12 @@ - quote_literal(string text) + + + quote_literal + + quote_literal(string text) + text Return the given string suitably quoted to be used as a string literal @@ -1667,7 +1722,12 @@ - quote_nullable(string text) + + + quote_nullable + + quote_nullable(string text) + text Return the given string suitably quoted to be used as a string literal @@ -1693,7 +1753,12 @@ - regexp_matches(string text, pattern text [, flags text]) + + + regexp_matches + + regexp_matches(string text, pattern text [, flags text]) + setof text[] Return all captured substrings resulting from matching a POSIX regular @@ -1705,7 +1770,12 @@ - regexp_replace(string text, pattern text, replacement text [, flags text]) + + + regexp_replace + + regexp_replace(string text, pattern text, replacement text [, flags text]) + text Replace substring(s) matching a POSIX regular expression. See @@ -1716,7 +1786,12 @@ - regexp_split_to_array(string text, pattern text [, flags text ]) + + + regexp_split_to_array + + regexp_split_to_array(string text, pattern text [, flags text ]) + text[] Split string using a POSIX regular expression as @@ -1728,7 +1803,12 @@ - regexp_split_to_table(string text, pattern text [, flags text]) + + + regexp_split_to_table + + regexp_split_to_table(string text, pattern text [, flags text]) + setof text Split string using a POSIX regular expression as @@ -1740,7 +1820,12 @@ - repeat(string text, number int) + + + repeat + + repeat(string text, number int) + text Repeat string the specified number of times @@ -1749,9 +1834,14 @@ - replace(string text, - from text, - to text) + + + replace + + replace(string text, + from text, + to text) + text Replace all occurrences in string of substring from with substring to @@ -1762,6 +1852,9 @@ + + reverse + reverse(str) text @@ -1774,6 +1867,9 @@ + + right + right(str text, n int) @@ -1788,6 +1884,9 @@ + + rpad + rpad(string text, length int , fill text) @@ -1805,8 +1904,12 @@ - rtrim(string text - , characters text) + + + rtrim + + rtrim(string text + , characters text) text @@ -1819,9 +1922,14 @@ - split_part(string text, - delimiter text, - field int) + + + split_part + + split_part(string text, + delimiter text, + field int) + text Split string on delimiter and return the given field (counting from one) @@ -1831,7 +1939,12 @@ - strpos(string, substring) + + + strpos + + strpos(string, substring) + int Location of specified substring (same as @@ -1844,7 +1957,12 @@ - substr(string, from , count) + + + substr + + substr(string, from , count) + text Extract substring (same as @@ -1855,23 +1973,31 @@ - to_ascii(string text - , encoding text) + + + to_ascii + + to_ascii(string text + , encoding text) + text - Convert string to ASCII from another encoding (only supports conversion from LATIN1, LATIN2, LATIN9, and WIN1250 encodings) - to_ascii('Karel') Karel - to_hex(number int - or bigint) + + + to_hex + + to_hex(number int + or bigint) + text Convert number to its equivalent hexadecimal representation @@ -1882,6 +2008,9 @@ + + translate + translate(string text, from text, to text) @@ -2759,7 +2888,12 @@ - octet_length(string) + + + octet_length + + octet_length(string) + int Number of bytes in binary string octet_length(E'jo\\000se'::bytea) @@ -2767,7 +2901,12 @@ - overlay(string placing string from int for int) + + + overlay + + overlay(string placing string from int for int) + bytea Replace substring @@ -2777,7 +2916,12 @@ - position(substring in string) + + + position + + position(substring in string) + int Location of specified substring position(E'\\000om'::bytea in E'Th\\000omas'::bytea) @@ -2785,13 +2929,15 @@ - substring(string from int for int) - bytea - Extract substring substring + substring(string from int for int) + + bytea + + Extract substring substring(E'Th\\000omas'::bytea from 2 for 3) h\000o @@ -2799,6 +2945,9 @@ + + trim + trim(both bytes from string) @@ -2839,8 +2988,13 @@ - btrim(string - bytea, bytes bytea) + + + btrim + + btrim(string + bytea, bytes bytea) + bytea Remove the longest string consisting only of bytes @@ -2853,6 +3007,9 @@ + + decode + decode(string text, type text) @@ -2867,6 +3024,9 @@ + + encode + encode(string bytea, type text) @@ -2881,14 +3041,14 @@ + + get_bit + get_bit(string, offset) int Extract bit from string - - get_bit - get_bit(E'Th\\000omas'::bytea, 45) 1 @@ -2896,21 +3056,26 @@ + + get_byte + get_byte(string, offset) int Extract byte from string - - get_byte - get_byte(E'Th\\000omas'::bytea, 4) 109 - length(string) + + + length + + length(string) + int Length of binary string @@ -2929,7 +3094,12 @@ - md5(string) + + + md5 + + md5(string) + text Calculates the MD5 hash of string, @@ -2941,15 +3111,15 @@ + + set_bit + set_bit(string, offset, newvalue) bytea Set bit in string - - set_bit - set_bit(E'Th\\000omas'::bytea, 45, 0) Th\000omAs @@ -2957,15 +3127,15 @@ + + set_byte + set_byte(string, offset, newvalue) bytea Set byte in string - - set_byte - set_byte(E'Th\\000omas'::bytea, 4, 64) Th\000o@as @@ -4915,19 +5085,6 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); formatting - - to_char - - - to_date - - - to_number - - - to_timestamp - - The PostgreSQL formatting functions provide a powerful set of tools for converting various data types @@ -4961,7 +5118,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - to_char(timestamp, text) + + + to_char + + to_char(timestamp, text) + text convert time stamp to string to_char(current_timestamp, 'HH12:MI:SS') @@ -4992,19 +5154,34 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); to_char(-125.8, '999D99S') - to_date(text, text) + + + to_date + + to_date(text, text) + date convert string to date to_date('05 Dec 2000', 'DD Mon YYYY') - to_number(text, text) + + + to_number + + to_number(text, text) + numeric convert string to numeric to_number('12,454.8-', '99G999D9S') - to_timestamp(text, text) + + + to_timestamp + + to_timestamp(text, text) + timestamp with time zone convert string to time stamp to_timestamp('05 Dec 2000', 'DD Mon YYYY') @@ -5977,61 +6154,6 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
- - age - - - clock_timestamp - - - current_date - - - current_time - - - current_timestamp - - - date_part - - - date_trunc - - - extract - - - isfinite - - - justify_days - - - justify_hours - - - justify_interval - - - localtime - - - localtimestamp - - - now - - - statement_timestamp - - - timeofday - - - transaction_timestamp - - Date/Time Functions @@ -6047,7 +6169,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - age(timestamp, timestamp) + + + age + + age(timestamp, timestamp) + interval Subtract arguments, producing a symbolic result that uses years and months @@ -6064,7 +6191,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - clock_timestamp() + + + clock_timestamp + + clock_timestamp() + timestamp with time zone Current date and time (changes during statement execution); see @@ -6074,7 +6206,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - current_date + + + current_date + + current_date + date Current date; see @@ -6084,7 +6221,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - current_time + + + current_time + + current_time + time with time zone Current time of day; see @@ -6094,7 +6236,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - current_timestamp + + + current_timestamp + + current_timestamp + timestamp with time zone Current date and time (start of current transaction); see @@ -6104,7 +6251,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - date_part(text, timestamp) + + + date_part + + date_part(text, timestamp) + double precision Get subfield (equivalent to extract); see @@ -6124,7 +6276,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - date_trunc(text, timestamp) + + + date_trunc + + date_trunc(text, timestamp) + timestamp Truncate to specified precision; see also @@ -6133,8 +6290,13 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - extract(field from - timestamp) + + + extract + + extract(field from + timestamp) + double precision Get subfield; see @@ -6153,7 +6315,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - isfinite(date) + + + isfinite + + isfinite(date) + boolean Test for finite date (not +/-infinity) isfinite(date '2001-02-16') @@ -6177,7 +6344,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - justify_days(interval) + + + justify_days + + justify_days(interval) + interval Adjust interval so 30-day time periods are represented as months justify_days(interval '35 days') @@ -6185,7 +6357,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - justify_hours(interval) + + + justify_hours + + justify_hours(interval) + interval Adjust interval so 24-hour time periods are represented as days justify_hours(interval '27 hours') @@ -6193,7 +6370,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - justify_interval(interval) + + + justify_interval + + justify_interval(interval) + interval Adjust interval using justify_days and justify_hours, with additional sign adjustments justify_interval(interval '1 mon -1 hour') @@ -6201,7 +6383,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - localtime + + + localtime + + localtime + time Current time of day; see @@ -6211,7 +6398,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - localtimestamp + + + localtimestamp + + localtimestamp + timestamp Current date and time (start of current transaction); see @@ -6221,7 +6413,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - now() + + + now + + now() + timestamp with time zone Current date and time (start of current transaction); see @@ -6231,7 +6428,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - statement_timestamp() + + + statement_timestamp + + statement_timestamp() + timestamp with time zone Current date and time (start of current statement); see @@ -6241,7 +6443,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - timeofday() + + + timeofday + + timeofday() + text Current date and time (like clock_timestamp, but as a text string); @@ -6252,7 +6459,12 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); - transaction_timestamp() + + + transaction_timestamp + + transaction_timestamp() + timestamp with time zone Current date and time (start of current transaction); see @@ -7140,19 +7352,34 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple - enum_first(anyenum) + + + enum_first + + enum_first(anyenum) + Returns the first value of the input enum type enum_first(null::rainbow) red - enum_last(anyenum) + + + enum_last + + enum_last(anyenum) + Returns the last value of the input enum type enum_last(null::rainbow) purple - enum_range(anyenum) + + + enum_range + + enum_range(anyenum) + Returns all values of the input enum type in an ordered array enum_range(null::rainbow) {red,orange,yellow,green,blue,purple} @@ -7549,7 +7776,12 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple - box(circle) + + + box + + box(circle) + box circle to box box(circle '((0,0),2.0)') @@ -7567,7 +7799,12 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple box(polygon '((0,0),(1,1),(2,0))') - circle(box) + + + circle + + circle(box) + circle box to circle circle(box '((0,0),(1,1))') @@ -7585,7 +7822,12 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple circle(polygon '((0,0),(1,1),(2,0))') - lseg(box) + + + lseg + + lseg(box) + lseg box diagonal to line segment lseg(box '((-1,0),(1,0))') @@ -7597,14 +7839,24 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple lseg(point '(-1,0)', point '(1,0)') - path(polygon) + + + path + + path(polygon) + point polygon to path path(polygon '((0,0),(1,1),(2,0))') - point(double - precision, double precision) + + + point + + point(double + precision, double precision) + point construct point point(23.4, -44.5) @@ -7634,7 +7886,12 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple point(polygon '((0,0),(1,1),(2,0))') - polygon(box) + + + polygon + + polygon(box) + polygon box to 4-point polygon polygon(box '((0,0),(1,1))') @@ -7822,7 +8079,12 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple - abbrev(inet) + + + abbrev + + abbrev(inet) + text abbreviated display format as text abbrev(inet '10.1.0.0/16') @@ -7836,14 +8098,24 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple 10.1/16 - broadcast(inet) + + + broadcast + + broadcast(inet) + inet broadcast address for network broadcast('192.168.1.5/24') 192.168.1.255/24 - family(inet) + + + family + + family(inet) + int extract family of address; 4 for IPv4, 6 for IPv6 @@ -7851,42 +8123,72 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple 6 - host(inet) + + + host + + host(inet) + text extract IP address as text host('192.168.1.5/24') 192.168.1.5 - hostmask(inet) + + + hostmask + + hostmask(inet) + inet construct host mask for network hostmask('192.168.23.20/30') 0.0.0.3 - masklen(inet) + + + masklen + + masklen(inet) + int extract netmask length masklen('192.168.1.5/24') 24 - netmask(inet) + + + netmask + + netmask(inet) + inet construct netmask for network netmask('192.168.1.5/24') 255.255.255.0 - network(inet) + + + network + + network(inet) + cidr extract network part of address network('192.168.1.5/24') 192.168.1.0/24 - set_masklen(inet, int) + + + set_masklen + + set_masklen(inet, int) + inet set netmask length for inet value set_masklen('192.168.1.5/24', 16) @@ -7900,7 +8202,12 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple 192.168.0.0/16 - text(inet) + + + text + + text(inet) + text extract IP address and netmask length as text text(inet '192.168.1.5') @@ -7948,7 +8255,12 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple - trunc(macaddr) + + + trunc + + trunc(macaddr) + macaddr set last 3 bytes to zero trunc(macaddr '12:34:56:78:90:ab') @@ -8083,84 +8395,156 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple - to_tsvector( config regconfig , document text) - tsvector - reduce document text to tsvector - to_tsvector('english', 'The Fat Rats') - 'fat':2 'rat':3 + + + get_current_ts_config + + get_current_ts_config() + + regconfig + get default text search configuration + get_current_ts_config() + english - length(tsvector) + + + length + + length(tsvector) + integer number of lexemes in tsvector length('fat:2,4 cat:3 rat:5A'::tsvector) 3 - setweight(tsvector, "char") + + + numnode + + numnode(tsquery) + + integer + number of lexemes plus operators in tsquery + numnode('(fat & rat) | cat'::tsquery) + 5 + + + + + plainto_tsquery + + plainto_tsquery( config regconfig , query text) + + tsquery + produce tsquery ignoring punctuation + plainto_tsquery('english', 'The Fat Rats') + 'fat' & 'rat' + + + + + querytree + + querytree(query tsquery) + + text + get indexable part of a tsquery + querytree('foo & ! bar'::tsquery) + 'foo' + + + + + setweight + + setweight(tsvector, "char") + tsvector assign weight to each element of tsvector setweight('fat:2,4 cat:3 rat:5B'::tsvector, 'A') 'cat':3A 'fat':2A,4A 'rat':5A - strip(tsvector) + + + strip + + strip(tsvector) + tsvector remove positions and weights from tsvector strip('fat:2,4 cat:3 rat:5A'::tsvector) 'cat' 'fat' 'rat' - to_tsquery( config regconfig , query text) + + + to_tsquery + + to_tsquery( config regconfig , query text) + tsquery normalize words and convert to tsquery to_tsquery('english', 'The & Fat & Rats') 'fat' & 'rat' - plainto_tsquery( config regconfig , query text) - tsquery - produce tsquery ignoring punctuation - plainto_tsquery('english', 'The Fat Rats') - 'fat' & 'rat' - - - numnode(tsquery) - integer - number of lexemes plus operators in tsquery - numnode('(fat & rat) | cat'::tsquery) - 5 + + + to_tsvector + + to_tsvector( config regconfig , document text) + + tsvector + reduce document text to tsvector + to_tsvector('english', 'The Fat Rats') + 'fat':2 'rat':3 - querytree(query tsquery) + + + ts_headline + + ts_headline( config regconfig, document text, query tsquery , options text ) + text - get indexable part of a tsquery - querytree('foo & ! bar'::tsquery) - 'foo' + display a query match + ts_headline('x y z', 'z'::tsquery) + x y <b>z</b> - ts_rank( weights float4[], vector tsvector, query tsquery , normalization integer ) + + + ts_rank + + ts_rank( weights float4[], vector tsvector, query tsquery , normalization integer ) + float4 rank document for query ts_rank(textsearch, query) 0.818 - ts_rank_cd( weights float4[], vector tsvector, query tsquery , normalization integer ) + + + ts_rank_cd + + ts_rank_cd( weights float4[], vector tsvector, query tsquery , normalization integer ) + float4 rank document for query using cover density ts_rank_cd('{0.1, 0.2, 0.4, 1.0}', textsearch, query) 2.01317 - ts_headline( config regconfig, document text, query tsquery , options text ) - text - display a query match - ts_headline('x y z', 'z'::tsquery) - x y <b>z</b> - - - ts_rewrite(query tsquery, target tsquery, substitute tsquery) + + + ts_rewrite + + ts_rewrite(query tsquery, target tsquery, substitute tsquery) + tsquery replace target with substitute within query ts_rewrite('a & b'::tsquery, 'a'::tsquery, 'foo|bar'::tsquery) @@ -8174,21 +8558,24 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple 'b' & ( 'foo' | 'bar' ) - get_current_ts_config() - regconfig - get default text search configuration - get_current_ts_config() - english - - - tsvector_update_trigger() + + + tsvector_update_trigger + + tsvector_update_trigger() + trigger trigger function for automatic tsvector column update CREATE TRIGGER ... tsvector_update_trigger(tsvcol, 'pg_catalog.swedish', title, body) - tsvector_update_trigger_column() + + + tsvector_update_trigger_column + + tsvector_update_trigger_column() + trigger trigger function for automatic tsvector column update CREATE TRIGGER ... tsvector_update_trigger_column(tsvcol, configcol, title, body) @@ -8229,21 +8616,36 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple - ts_debug( config regconfig, document text, OUT alias text, OUT description text, OUT token text, OUT dictionaries regdictionary[], OUT dictionary regdictionary, OUT lexemes text[]) + + + ts_debug + + ts_debug( config regconfig, document text, OUT alias text, OUT description text, OUT token text, OUT dictionaries regdictionary[], OUT dictionary regdictionary, OUT lexemes text[]) + setof record test a configuration ts_debug('english', 'The Brightest supernovaes') (asciiword,"Word, all ASCII",The,{english_stem},english_stem,{}) ... - ts_lexize(dict regdictionary, token text) + + + ts_lexize + + ts_lexize(dict regdictionary, token text) + text[] test a dictionary ts_lexize('english_stem', 'stars') {star} - ts_parse(parser_name text, document text, OUT tokid integer, OUT token text) + + + ts_parse + + ts_parse(parser_name text, document text, OUT tokid integer, OUT token text) + setof record test a parser ts_parse('default', 'foo - bar') @@ -8257,7 +8659,12 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple (1,foo) ... - ts_token_type(parser_name text, OUT tokid integer, OUT alias text, OUT description text) + + + ts_token_type + + ts_token_type(parser_name text, OUT tokid integer, OUT alias text, OUT description text) + setof record get token types defined by parser ts_token_type('default') @@ -8271,7 +8678,12 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow', 'green', 'blue', 'purple (1,asciiword,"Word, all ASCII") ... - ts_stat(sqlquery text, weights text, OUT word text, OUT ndoc integer, OUT nentry integer) + + + ts_stat + + ts_stat(sqlquery text, weights text, OUT word text, OUT ndoc integer, OUT nentry integer) + setof record get statistics of a tsvector column ts_stat('SELECT vector from apod') @@ -10068,6 +10480,9 @@ SELECT NULLIF(value, '(none)') ... average + + avg + avg(expression) @@ -10150,7 +10565,12 @@ SELECT NULLIF(value, '(none)') ... - count(*) + + + count + + count(*) + bigint number of input rows @@ -10183,7 +10603,12 @@ SELECT NULLIF(value, '(none)') ... - max(expression) + + + max + + max(expression) + any array, numeric, string, or date/time type same as argument type @@ -10194,7 +10619,12 @@ SELECT NULLIF(value, '(none)') ... - min(expression) + + + min + + min(expression) + any array, numeric, string, or date/time type same as argument type @@ -10224,7 +10654,12 @@ SELECT NULLIF(value, '(none)') ... - sum(expression) + + + sum + + sum(expression) + smallint, int, bigint, real, double @@ -10366,6 +10801,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; correlation + + corr + corr(Y, X) @@ -10383,6 +10821,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; covariance population + + covar_pop + covar_pop(Y, X) @@ -10400,6 +10841,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; covariance sample + + covar_samp + covar_samp(Y, X) @@ -10413,6 +10857,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; + + regr_avgx + regr_avgx(Y, X) @@ -10427,6 +10874,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; + + regr_avgy + regr_avgy(Y, X) @@ -10441,6 +10891,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; + + regr_count + regr_count(Y, X) @@ -10457,6 +10910,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; regression intercept + + regr_intercept + regr_intercept(Y, X) @@ -10473,6 +10929,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; + + regr_r2 + regr_r2(Y, X) @@ -10489,6 +10948,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; regression slope + + regr_slope + regr_slope(Y, X) @@ -10504,6 +10966,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; + + regr_sxx + regr_sxx(Y, X) @@ -10521,6 +10986,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; + + regr_sxy + regr_sxy(Y, X) @@ -10541,6 +11009,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; + + regr_syy + regr_syy(Y, X) @@ -10561,6 +11032,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; standard deviation + + stddev + stddev(expression) @@ -10581,6 +11055,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; standard deviation population + + stddev_pop + stddev_pop(expression) @@ -10601,6 +11078,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; standard deviation sample + + stddev_samp + stddev_samp(expression) @@ -10640,6 +11120,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; variance population + + var_pop + var_pop(expression) @@ -10660,6 +11143,9 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab; variance sample + + var_samp + var_samp(expression) @@ -11856,6 +12342,13 @@ postgres=# SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]); name of current database + + current_query() + text + text of the currently executing query, as submitted + by the client (might contain more than one statement) + + current_schema[()] name @@ -11874,28 +12367,6 @@ postgres=# SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]); user name of current execution context - - current_query() - text - text of the currently executing query, as submitted - by the client (might contain more than one statement) - - - - - pg_backend_pid() - int - - Process ID of the server process attached to the current session - - - - - pg_listening_channels() - setof text - channel names that the session is currently listening on - - inet_client_addr() inet @@ -11921,9 +12392,18 @@ postgres=# SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]); - pg_my_temp_schema() - oid - OID of session's temporary schema, or 0 if none + + pg_backend_pid() + int + + Process ID of the server process attached to the current session + + + + + pg_conf_load_time() + timestamp with time zone + configuration load time @@ -11933,15 +12413,21 @@ postgres=# SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]); - pg_postmaster_start_time() - timestamp with time zone - server start time + pg_listening_channels() + setof text + channel names that the session is currently listening on - pg_conf_load_time() + pg_my_temp_schema() + oid + OID of session's temporary schema, or 0 if none + + + + pg_postmaster_start_time() timestamp with time zone - configuration load time + server start time @@ -11977,34 +12463,50 @@ postgres=# SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]); - user - current + current_catalog - schema - current + current_database - search path - current + current_query - current_catalog + current_schema - current_database + current_schemas - current_schema + current_user + + + + pg_backend_pid + + + + schema + current + + + + search path + current + + + + user + current - current_user + user @@ -12569,16 +13071,16 @@ SELECT relname FROM pg_class WHERE pg_table_is_visible(oid); is function visible in search path - pg_operator_is_visible(operator_oid) + pg_opclass_is_visible(opclass_oid) boolean - is operator visible in search path + is operator class visible in search path - pg_opclass_is_visible(opclass_oid) + pg_operator_is_visible(operator_oid) boolean - is operator class visible in search path + is operator visible in search path pg_table_is_visible(table_oid) @@ -12627,10 +13129,10 @@ SELECT relname FROM pg_class WHERE pg_table_is_visible(oid); pg_function_is_visible - pg_operator_is_visible + pg_opclass_is_visible - pg_opclass_is_visible + pg_operator_is_visible pg_table_is_visible @@ -12680,15 +13182,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); - pg_get_keywords - - - - pg_get_viewdef + pg_get_constraintdef - pg_get_ruledef + pg_get_expr @@ -12712,15 +13210,19 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); - pg_get_triggerdef + pg_get_keywords - pg_get_constraintdef + pg_get_ruledef - pg_get_expr + pg_get_serial_sequence + + + + pg_get_triggerdef @@ -12728,7 +13230,7 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); - pg_get_serial_sequence + pg_get_viewdef @@ -12757,11 +13259,6 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); text get SQL name of a data type - - pg_get_keywords() - setof record - get list of SQL keywords and their categories - pg_get_constraintdef(constraint_oid) text @@ -12816,6 +13313,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); or definition of just one index column when column_no is not zero + + pg_get_keywords() + setof record + get list of SQL keywords and their categories + pg_get_ruledef(rule_oid) text @@ -13092,7 +13594,7 @@ SELECT typlen FROM pg_type WHERE oid = pg_typeof(33); - txid_snapshot_xmin + txid_snapshot_xip @@ -13100,7 +13602,7 @@ SELECT typlen FROM pg_type WHERE oid = pg_typeof(33); - txid_snapshot_xip + txid_snapshot_xmin @@ -13133,9 +13635,9 @@ SELECT typlen FROM pg_type WHERE oid = pg_typeof(33); get current snapshot - txid_snapshot_xmin(txid_snapshot) - bigint - get xmin of snapshot + txid_snapshot_xip(txid_snapshot) + setof bigint + get in-progress transaction IDs in snapshot txid_snapshot_xmax(txid_snapshot) @@ -13143,9 +13645,9 @@ SELECT typlen FROM pg_type WHERE oid = pg_typeof(33); get xmax of snapshot - txid_snapshot_xip(txid_snapshot) - setof bigint - get in-progress transaction IDs in snapshot + txid_snapshot_xmin(txid_snapshot) + bigint + get xmin of snapshot txid_visible_in_snapshot(bigint, txid_snapshot) @@ -13240,6 +13742,9 @@ SELECT typlen FROM pg_type WHERE oid = pg_typeof(33); + + current_setting + current_setting(setting_name) text @@ -13247,6 +13752,9 @@ SELECT typlen FROM pg_type WHERE oid = pg_typeof(33); + + set_config + set_config(setting_name, new_value, is_local) @@ -13309,15 +13817,15 @@ SELECT set_config('log_statement_stats', 'off', false); pg_cancel_backend - - pg_terminate_backend - pg_reload_conf pg_rotate_logfile + + pg_terminate_backend + signal @@ -13349,24 +13857,24 @@ SELECT set_config('log_statement_stats', 'off', false); - pg_terminate_backend(pid int) + pg_reload_conf() boolean - Terminate a backend + Cause server processes to reload their configuration files - pg_reload_conf() + pg_rotate_logfile() boolean - Cause server processes to reload their configuration files + Rotate server's log file - pg_rotate_logfile() + pg_terminate_backend(pid int) boolean - Rotate server's log file + Terminate a backend @@ -13403,28 +13911,28 @@ SELECT set_config('log_statement_stats', 'off', false); - pg_start_backup + backup - pg_stop_backup + pg_current_xlog_insert_location - pg_switch_xlog + pg_current_xlog_location - pg_current_xlog_location + pg_start_backup - pg_current_xlog_insert_location + pg_stop_backup - pg_xlogfile_name_offset + pg_switch_xlog pg_xlogfile_name - backup + pg_xlogfile_name_offset @@ -13445,52 +13953,52 @@ SELECT set_config('log_statement_stats', 'off', false); - pg_start_backup(label text , fast boolean ) + pg_current_xlog_insert_location() text - Prepare for performing on-line backup + Get current transaction log insert location - pg_stop_backup() + pg_current_xlog_location() text - Finish performing on-line backup + Get current transaction log write location - pg_switch_xlog() + pg_start_backup(label text , fast boolean ) text - Force switch to a new transaction log file + Prepare for performing on-line backup - pg_current_xlog_location() + pg_stop_backup() text - Get current transaction log write location + Finish performing on-line backup - pg_current_xlog_insert_location() + pg_switch_xlog() text - Get current transaction log insert location + Force switch to a new transaction log file - pg_xlogfile_name_offset(location text) + pg_xlogfile_name(location text) - text, integer - Convert transaction log location string to file name and decimal byte offset within file + text + Convert transaction log location string to file name - pg_xlogfile_name(location text) + pg_xlogfile_name_offset(location text) - text - Convert transaction log location string to file name + text, integer + Convert transaction log location string to file name and decimal byte offset within file @@ -13674,25 +14182,25 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); pg_column_size - pg_total_relation_size + pg_database_size - pg_table_size + pg_indexes_size - pg_indexes_size + pg_relation_size - pg_database_size + pg_size_pretty - pg_tablespace_size + pg_table_size - pg_relation_size + pg_tablespace_size - pg_size_pretty + pg_total_relation_size
@@ -13711,24 +14219,17 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); - pg_total_relation_size(regclass) + pg_database_size(oid) bigint - - Total disk space used by the table with the specified OID or name, - including all indexes and TOAST data - + Disk space used by the database with the specified OID - pg_table_size(regclass) + pg_database_size(name) bigint - - Disk space used by the table with the specified OID or name, - excluding indexes (but including TOAST, free space map, and visibility - map) - + Disk space used by the database with the specified name @@ -13742,58 +14243,65 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup()); - pg_database_size(oid) + pg_relation_size(relation regclass, fork text) bigint - Disk space used by the database with the specified OID + + Disk space used by the specified fork ('main', + 'fsm' or 'vm') + of the table or index with the specified OID or name + - pg_database_size(name) + pg_relation_size(relation regclass) bigint - Disk space used by the database with the specified name + + Shorthand for pg_relation_size(..., 'main') + - pg_tablespace_size(oid) + pg_size_pretty(bigint) - bigint - Disk space used by the tablespace with the specified OID + text + Converts a size in bytes into a human-readable format with size units - pg_tablespace_size(name) + pg_table_size(regclass) bigint - Disk space used by the tablespace with the specified name + + Disk space used by the table with the specified OID or name, + excluding indexes (but including TOAST, free space map, and visibility + map) + - pg_relation_size(relation regclass, fork text) + pg_tablespace_size(oid) bigint - - Disk space used by the specified fork ('main', - 'fsm' or 'vm') - of the table or index with the specified OID or name - + Disk space used by the tablespace with the specified OID - pg_relation_size(relation regclass) + pg_tablespace_size(name) bigint - - Shorthand for pg_relation_size(..., 'main') - + Disk space used by the tablespace with the specified name - pg_size_pretty(bigint) + pg_total_relation_size(regclass) - text - Converts a size in bytes into a human-readable format with size units + bigint + + Total disk space used by the table with the specified OID or name, + including all indexes and TOAST data + @@ -14020,7 +14528,6 @@ SELECT (pg_stat_file('filename')).modification; voidObtain exclusive advisory lock - pg_advisory_lock_shared(key bigint) @@ -14035,75 +14542,69 @@ SELECT (pg_stat_file('filename')).modification; void Obtain shared advisory lock - - pg_try_advisory_lock(key bigint) + pg_advisory_unlock(key bigint) boolean - Obtain exclusive advisory lock if available + Release an exclusive advisory lock - pg_try_advisory_lock(key1 int, key2 int) + pg_advisory_unlock(key1 int, key2 int) boolean - Obtain exclusive advisory lock if available + Release an exclusive advisory lock - - pg_try_advisory_lock_shared(key bigint) + pg_advisory_unlock_all() - boolean - Obtain shared advisory lock if available + void + Release all advisory locks held by the current session - pg_try_advisory_lock_shared(key1 int, key2 int) + pg_advisory_unlock_shared(key bigint) boolean - Obtain shared advisory lock if available + Release a shared advisory lock - - pg_advisory_unlock(key bigint) + pg_advisory_unlock_shared(key1 int, key2 int) boolean - Release an exclusive advisory lock + Release a shared advisory lock - pg_advisory_unlock(key1 int, key2 int) + pg_try_advisory_lock(key bigint) boolean - Release an exclusive advisory lock + Obtain exclusive advisory lock if available - - pg_advisory_unlock_shared(key bigint) + pg_try_advisory_lock(key1 int, key2 int) boolean - Release a shared advisory lock + Obtain exclusive advisory lock if available - pg_advisory_unlock_shared(key1 int, key2 int) + pg_try_advisory_lock_shared(key bigint) boolean - Release a shared advisory lock + Obtain shared advisory lock if available - - pg_advisory_unlock_all() + pg_try_advisory_lock_shared(key1 int, key2 int) - void - Release all advisory locks held by the current session + boolean + Obtain shared advisory lock if available -
diff --git a/doc/src/sgml/query.sgml b/doc/src/sgml/query.sgml index 005ab581fa..681d08abed 100644 --- a/doc/src/sgml/query.sgml +++ b/doc/src/sgml/query.sgml @@ -642,12 +642,6 @@ SELECT * - average - count - max - min - sum - Like most other relational database products, PostgreSQL supports aggregate functions. -- 2.40.0