From 3b0453b24fcbc81d33c9e59ce369a2ffb2be9d11 Mon Sep 17 00:00:00 2001 From: Neil Conway Date: Wed, 14 Sep 2005 21:14:26 +0000 Subject: [PATCH] Various documentation improvements. Add a lot of index entries for the builtin functions (although some more entries are still needed), and remove the duplicate index entries that have been causing collateindex.pl warnings. Consistently use "int" and "bigint", rather than a mix of "int", "integer", "int4", "bigint", and "int8". Make parenthesis style in syntax examples more consistent. Various copy-editing for newly-added documentation and SGML markup fixes. --- doc/src/sgml/dfunc.sgml | 11 +- doc/src/sgml/func.sgml | 490 +++++++++++++++++++++++++++++--------- doc/src/sgml/plpgsql.sgml | 43 ++-- doc/src/sgml/xfunc.sgml | 7 +- 4 files changed, 415 insertions(+), 136 deletions(-) diff --git a/doc/src/sgml/dfunc.sgml b/doc/src/sgml/dfunc.sgml index 202584e0f7..3471698e25 100644 --- a/doc/src/sgml/dfunc.sgml +++ b/doc/src/sgml/dfunc.sgml @@ -1,5 +1,5 @@ @@ -28,11 +28,10 @@ $PostgreSQL: pgsql/doc/src/sgml/dfunc.sgml,v 1.31 2005/04/09 03:52:43 momjian Ex - PIC Creating shared libraries is generally - analogous to linking executables: first the source files are - compiled into object files, then the object files are linked - together. The object files need to be created as - position-independent code + Creating shared libraries is generally analogous to linking + executables: first the source files are compiled into object files, + then the object files are linked together. The object files need to + be created as position-independent code (PIC),PIC which conceptually means that they can be placed at an arbitrary location in memory when they are loaded by the executable. (Object files diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 41ca4a8cc9..eaebce24c7 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,5 +1,5 @@ @@ -263,7 +263,7 @@ PostgreSQL documentation BETWEEN In addition to the comparison operators, the special - BETWEEN construct is available.BETWEEN + BETWEEN construct is available. a BETWEEN x AND y @@ -567,6 +567,64 @@ PostgreSQL documentation boundary cases may therefore vary depending on the host system. + + abs + + + cbrt + + + ceiling + + + degrees + + + exp + + + floor + + + ln + + + log + + + mod + + + π + + + power + + + radians + + + random + + + round + + + setseed + + + sign + + + sqrt + + + trunc + + + width_bucket + + Mathematical Functions @@ -722,7 +780,7 @@ PostgreSQL documentation - round(v numeric, s integer) + round(v numeric, s int) numeric round to s decimal places round(42.4382, 2) @@ -731,7 +789,7 @@ PostgreSQL documentation setseed(dp) - integer + int set seed for subsequent random() calls setseed(0.54823) 1177314959 @@ -762,7 +820,7 @@ PostgreSQL documentation - trunc(v numeric, s integer) + trunc(v numeric, s int) numeric truncate to s decimal places trunc(42.4382, 2) @@ -770,8 +828,8 @@ PostgreSQL documentation - width_bucket(op numeric, b1 numeric, b2 numeric, count integer) - integer + 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 buckets, an upper bound of b1, and a lower bound @@ -790,6 +848,31 @@ PostgreSQL documentation precision. + + acos + + + asin + + + atan + + + atan2 + + + cos + + + cot + + + sin + + + tan + +
Trigonometric Functions @@ -874,6 +957,37 @@ PostgreSQL documentation function invocation. (See .) + + bit_length + + + char_length + + + convert + + + lower + + + octet_length + + + overlay + + + position + + + substring + + + trim + + + upper + +
<acronym>SQL</acronym> String Functions and Operators @@ -905,7 +1019,7 @@ PostgreSQL documentation bit_length(string) - integer + int Number of bits in string bit_length('jose') 32 @@ -913,7 +1027,7 @@ PostgreSQL documentation char_length(string) or character_length(string) - integer + int Number of characters in string @@ -923,7 +1037,7 @@ PostgreSQL documentation length of a character string - character strings, length + character string, length char_length('jose') @@ -955,20 +1069,17 @@ PostgreSQL documentation octet_length(string) - integer + int Number of bytes in string octet_length('jose') 4 - overlay(string placing string from integer for integer) + overlay(string placing string from int for int) text Replace substring - - overlay - overlay('Txxxxas' placing 'hom' from 2 for 4) Thomas @@ -976,20 +1087,17 @@ PostgreSQL documentation position(substring in string) - integer + int Location of specified substring position('om' in 'Thomas') 3 - substring(string from integer for integer) + substring(string from int for int) text Extract substring - - substring - substring('Thomas' from 2 for 3) hom @@ -1000,9 +1108,6 @@ PostgreSQL documentation text Extract substring matching POSIX regular expression - - substring - substring('Thomas' from '...$') mas @@ -1014,9 +1119,6 @@ PostgreSQL documentation Extract substring matching SQL regular expression - - substring - substring('Thomas' from '%#"o_a#"_' for '#') oma @@ -1055,6 +1157,76 @@ PostgreSQL documentation SQL-standard string functions listed in . + + ascii + + + btrim + + + chr + + + decode + + + encode + + + initcap + + + lpad + + + ltrim + + + md5 + + + pg_client_encoding + + + quote_ident + + + quote_literal + + + regexp_replace + + + repeat + + + replace + + + rpad + + + rtrim + + + split_part + + + strpos + + + substr + + + to_ascii + + + to_hex + + + translate + +
Other String Functions @@ -1071,7 +1243,7 @@ PostgreSQL documentation ascii(text) - integer + int ASCII code of the first character of the argument ascii('x') 120 @@ -1091,7 +1263,7 @@ PostgreSQL documentation - chr(integer) + chr(int) text Character with the given ASCII code chr(65) @@ -1159,18 +1331,9 @@ PostgreSQL documentation length(string text) - integer + int Number of characters in string - - character string - length - - - length - of a character string - character strings, length - length('jose') 4 @@ -1179,7 +1342,7 @@ PostgreSQL documentation lpad(string text, - length integer + length int , fill text) text @@ -1231,7 +1394,7 @@ PostgreSQL documentation - quote_ident(string text)quote_ident + quote_ident(string text) text Return the given string suitably quoted to be used as an identifier @@ -1245,7 +1408,7 @@ PostgreSQL documentation - quote_literal(string text)quote_literal + quote_literal(string text) text Return the given string suitably quoted to be used as a string literal @@ -1277,7 +1440,7 @@ PostgreSQL documentation - repeat(string text, number integer) + repeat(string text, number int) text Repeat string the specified number of times @@ -1300,7 +1463,7 @@ PostgreSQL documentation rpad(string text, - length integer + length int , fill text) text @@ -1332,12 +1495,12 @@ PostgreSQL documentation split_part(string text, delimiter text, - field integer) + field int) text Split string on delimiter and return the given field (counting from one) - split_part( 'abc~@~def~@~ghi', '~@~', 2) + split_part('abc~@~def~@~ghi', '~@~', 2) def @@ -1386,7 +1549,7 @@ PostgreSQL documentation - to_hex(number integer + to_hex(number int or bigint) text Convert number to its equivalent hexadecimal @@ -2200,7 +2363,7 @@ PostgreSQL documentation octet_length(string) - integer + int Number of bytes in binary string octet_length( 'jo\\000se'::bytea) 5 @@ -2208,14 +2371,14 @@ PostgreSQL documentation position(substring in string) - integer + int Location of specified substring position('\\000om'::bytea in 'Th\\000omas'::bytea) 3 - substring(string from integer for integer) + substring(string from int for int) bytea Extract substring @@ -2245,7 +2408,7 @@ PostgreSQL documentation get_byte(string, offset) - integer + int Extract byte from string @@ -2272,7 +2435,7 @@ PostgreSQL documentation get_bit(string, offset) - integer + int Extract bit from string @@ -2337,7 +2500,7 @@ PostgreSQL documentation length(string) - integer + int Length of binary string @@ -2553,7 +2716,7 @@ cast(-44 as bit(12)) 111111010100 <function>LIKE</function> - + LIKE @@ -2650,7 +2813,7 @@ cast(-44 as bit(12)) 111111010100 <function>SIMILAR TO</function> Regular Expressions - + regular expression @@ -4090,13 +4253,22 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); Data Type Formatting Functions - + formatting - + to_char + + to_date + + + to_timestamp + + + to_number + The PostgreSQL formatting functions @@ -5008,6 +5180,49 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
+ + age + + + current_date + + + current_time + + + current_timestamp + + + date_part + + + date_trunc + + + extract + + + isfinite + + + justify_hours + + + justify_days + + + localtime + + + localtimestamp + + + now + + + timeofday + + Date/Time Functions @@ -5198,8 +5413,8 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})'); In addition to these functions, the SQL OVERLAPS operator is supported: -( start1, end1 ) OVERLAPS ( start2, end2 ) -( start1, length1 ) OVERLAPS ( start2, length2 ) +(start1, end1) OVERLAPS (start2, end2) +(start1, length1) OVERLAPS (start2, length2) This expression yields true when two time periods (defined by their endpoints) overlap, false when they do not overlap. The endpoints @@ -5237,8 +5452,15 @@ SELECT (DATE '2001-02-16', INTERVAL '100 days') OVERLAPS <function>EXTRACT</function>, <function>date_part</function> + + date_part + + + extract + + -EXTRACT (field FROM source) +EXTRACT(field FROM source) @@ -5586,7 +5808,6 @@ SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40'); - @@ -5621,6 +5842,10 @@ SELECT date_part('hour', INTERVAL '4 hours 3 minutes'); <function>date_trunc</function> + + date_trunc + + The function date_trunc is conceptually similar to the trunc function for numbers. @@ -5680,6 +5905,10 @@ SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40'); conversion + + AT TIME ZONE + + The AT TIME ZONE construct allows conversions of time stamps to different time zones. precision ) -CURRENT_TIMESTAMP ( precision ) +CURRENT_TIME (precision) +CURRENT_TIMESTAMP (precision) LOCALTIME LOCALTIMESTAMP -LOCALTIME ( precision ) -LOCALTIMESTAMP ( precision ) +LOCALTIME (precision) +LOCALTIMESTAMP (precision) @@ -6090,6 +6319,42 @@ SELECT TIMESTAMP 'now'; -- incorrect for use with DEFAULT
+ + area + + + center + + + diameter + + + height + + + isclosed + + + isopen + + + length + + + npoints + + + pclose + + + popen + + + radius + + + width + Geometric Functions @@ -6147,13 +6412,13 @@ SELECT TIMESTAMP 'now'; -- incorrect for use with DEFAULT npoints(path) - integer + int number of points npoints(path '[(0,0),(1,1),(2,0)]') npoints(polygon) - integer + int number of points npoints(polygon '((1,1),(0,0))') @@ -6194,7 +6459,6 @@ SELECT TIMESTAMP 'now'; -- incorrect for use with DEFAULT
- Geometric Type Conversion Functions @@ -6468,13 +6732,13 @@ SELECT TIMESTAMP 'now'; -- incorrect for use with DEFAULT masklen(inet) - integer + int extract netmask length masklen('192.168.1.5/24') 24 - set_masklen(inet, integer) + set_masklen(inet, int) inet set netmask length for inet value set_masklen('192.168.1.5/24', 16) @@ -6517,7 +6781,7 @@ SELECT TIMESTAMP 'now'; -- incorrect for use with DEFAULT family(inet) - integer + int extract family of address; 4 for IPv4, 6 for IPv6 family('::1') @@ -6982,7 +7246,6 @@ SELECT NULLIF(value, '(none)') ... the SQL standard, but are a common extension. - @@ -7155,10 +7418,10 @@ SELECT NULLIF(value, '(none)') ... array_lower - (anyarray, integer) + (anyarray, int) - integer + int returns lower bound of the requested array dimension array_lower(array_prepend(0, ARRAY[1,2,3]), 1) 0 @@ -7167,10 +7430,10 @@ SELECT NULLIF(value, '(none)') ... array_upper - (anyarray, integer) + (anyarray, int) - integer + int returns upper bound of the requested array dimension array_upper(ARRAY[1,2,3,4], 1) 4 @@ -7244,7 +7507,7 @@ SELECT NULLIF(value, '(none)') ... avg(expression) - smallint, integer, + smallint, int, bigint, real, double precision, numeric, or interval @@ -7264,7 +7527,7 @@ SELECT NULLIF(value, '(none)') ... bit_and(expression) - smallint, integer, bigint, or + smallint, int, bigint, or bit @@ -7281,7 +7544,7 @@ SELECT NULLIF(value, '(none)') ... bit_or(expression) - smallint, integer, bigint, or + smallint, int, bigint, or bit @@ -7385,7 +7648,7 @@ SELECT NULLIF(value, '(none)') ... stddev(expression) - smallint, integer, + smallint, int, bigint, real, double precision, or numeric @@ -7399,14 +7662,14 @@ SELECT NULLIF(value, '(none)') ... sum(expression) - smallint, integer, + smallint, int, bigint, real, double precision, numeric, or interval bigint for smallint or - integer arguments, numeric for + int arguments, numeric for bigint arguments, double precision for floating-point arguments, otherwise the same as the argument data type @@ -7422,7 +7685,7 @@ SELECT NULLIF(value, '(none)') ... variance(expression) - smallint, integer, + smallint, int, bigint, real, double precision, or numeric @@ -7526,7 +7789,7 @@ SELECT count(*) FROM sometable; <literal>EXISTS</literal> -EXISTS ( subquery ) +EXISTS (subquery) @@ -8049,6 +8312,10 @@ AND otherwise the result of the row comparison is unknown (null). + + IS DISTINCT FROM + + row_constructor IS DISTINCT FROM row_constructor @@ -8082,6 +8349,10 @@ AND functions + + generate_series + + This section describes functions that possibly return more than one row. Currently the only functions in this class are series generating functions, @@ -8213,7 +8484,7 @@ select current_date + s.a as dates from generate_series(0,14,7) as s(a); inet_client_port() - int4 + int port of the remote connection @@ -8225,7 +8496,7 @@ select current_date + s.a as dates from generate_series(0,14,7) as s(a); inet_server_port() - int4 + int port of the local connection @@ -8345,8 +8616,9 @@ SET search_path TO schema , schema, .. - pg_postmaster_start_time returns the timestamp with time zone - when the postmaster started. + pg_postmaster_start_time returns the + timestamp with time zone when the + postmaster started. @@ -8851,7 +9123,7 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); pg_tablespace_databases(tablespace_oid) setof oid - get set of database OIDs that have objects in the tablespace + get the set of database OIDs that have objects in the tablespace @@ -8886,20 +9158,22 @@ SELECT pg_type_is_visible('myschema.widget'::regtype); - pg_get_userbyid - extracts a role's name given its OID. - pg_get_serial_sequence - fetches the name of the sequence associated with a serial or - bigserial column. The name is suitably formatted - for passing to the sequence functions (see ). - NULL is returned if the column does not have a sequence attached. + pg_get_userbyid extracts a role's name given + its OID. - pg_tablespace_databases allows usage examination of a - tablespace. It will return a set of OIDs of databases that have objects - stored in the tablespace. If this function returns any row, the + pg_get_serial_sequence fetches the name of the + sequence associated with a serial or bigserial + column. The name is suitably formatted for passing to the sequence + functions (see ). NULL is + returned if the column does not have an associated sequence. + + + + pg_tablespace_databases allows a tablespace to + be examined. It returns the set of OIDs of databases that have objects + stored in the tablespace. If this function returns any rows, the tablespace is not empty and cannot be dropped. To display the specific objects populating the tablespace, you will need to connect to the databases identified by @@ -9120,19 +9394,19 @@ SELECT set_config('log_statement_stats', 'off', false); - pg_cancel_backend sends a Query Cancel (SIGINT) signal - to a backend process identified by process ID (pid). - The process ID of an active backend can be found from the - procpid column in the + pg_cancel_backend sends a query cancel + (SIGINT) signal to a backend process identified by + process ID. The process ID of an active backend can be found from + the procpid column in the pg_stat_activity view, or by listing the postgres processes on the server with ps. - pg_reload_conf sends a SIGHUP signal to the - postmaster, causing reload of the configuration files - in all server processes. + pg_reload_conf sends a SIGHUP signal + to the postmaster, causing the configuration files + to be reloaded by all server processes. @@ -9245,7 +9519,7 @@ SELECT set_config('log_statement_stats', 'off', false); pg_column_size(any) - integer + int Number of bytes used to store a particular value (possibly compressed) @@ -9375,7 +9649,7 @@ SELECT set_config('log_statement_stats', 'off', false); - pg_read_file(filename text, offset int8, length int8) + pg_read_file(filename text, offset bigint, length bigint) text Return the contents of a text file diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index ad6b1c8494..a447dc8f1e 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1,5 +1,5 @@ @@ -1320,20 +1320,27 @@ EXECUTE 'UPDATE tbl SET ' + + quote_ident + use in PL/PgSQL + + + + quote_literal + use in PL/PgSQL + + - This example shows use of the functions - quote_ident(text) and - quote_literal(text).quote_identuse - in - PL/pgSQLquote_literaluse - in PL/pgSQL For safety, variables containing column and - table identifiers should be passed to function - quote_ident. Variables containing values - that should be literal strings in the constructed command should - be passed to quote_literal. Both take the - appropriate steps to return the input text enclosed in double or - single quotes respectively, with any embedded special characters - properly escaped. + This example demonstrates the use of the + quote_ident and + quote_literal functions. For safety, + expressions containing column and table identifiers should be + passed to quote_ident. Expressions containing + values that should be literal strings in the constructed command + should be passed to quote_literal. Both + take the appropriate steps to return the input text enclosed in + double or single quotes respectively, with any embedded special + characters properly escaped. @@ -3599,11 +3606,11 @@ $$ LANGUAGE plpgsql; The PL/pgSQL version of EXECUTE works similarly to the PL/SQL version, but you have to remember to use - quote_literal(text) and - quote_string(text) as described in quote_literal and + quote_ident as described in . Constructs of the - type EXECUTE 'SELECT * FROM $1'; will not - work unless you use these functions. + type EXECUTE 'SELECT * FROM $1'; will not work + unless you use these functions. diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 0781bf4dba..15ca5a21ce 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -1,5 +1,5 @@ @@ -99,9 +99,8 @@ $PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.103 2005/05/30 23:09:07 tgl Exp $ SETOFfunction Alternatively, an SQL function may be declared to return a set, by specifying the function's return type as SETOF - sometype.SETOF - In this case all rows of the last query's result are returned. - Further details appear below. + sometype. In this case all rows of the + last query's result are returned. Further details appear below. -- 2.40.0