From 4e94ea9fc92abff333a8d3cd99e7b667ab364442 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 4 Jan 2005 00:39:53 +0000 Subject: [PATCH] More minor updates and copy-editing. --- doc/src/sgml/ref/alter_database.sgml | 42 ++++++---- doc/src/sgml/ref/alter_group.sgml | 18 ++-- doc/src/sgml/ref/alter_table.sgml | 58 ++++++------- doc/src/sgml/ref/alter_user.sgml | 101 ++++++++++++---------- doc/src/sgml/ref/checkpoint.sgml | 4 +- doc/src/sgml/ref/close.sgml | 15 +++- doc/src/sgml/ref/cluster.sgml | 4 +- doc/src/sgml/ref/comment.sgml | 21 +++-- doc/src/sgml/ref/copy.sgml | 37 ++++----- doc/src/sgml/ref/create_aggregate.sgml | 4 +- doc/src/sgml/ref/create_domain.sgml | 7 +- doc/src/sgml/ref/create_function.sgml | 72 ++++++++-------- doc/src/sgml/ref/create_group.sgml | 32 +++++-- doc/src/sgml/ref/create_index.sgml | 27 ++++-- doc/src/sgml/ref/create_language.sgml | 13 +-- doc/src/sgml/ref/create_operator.sgml | 14 +++- doc/src/sgml/ref/create_rule.sgml | 10 +-- doc/src/sgml/ref/create_schema.sgml | 9 +- doc/src/sgml/ref/create_table.sgml | 104 +++++++++++------------ doc/src/sgml/ref/create_table_as.sgml | 20 ++--- doc/src/sgml/ref/create_type.sgml | 13 +-- doc/src/sgml/ref/create_user.sgml | 111 +++++++++++++------------ doc/src/sgml/ref/create_view.sgml | 12 ++- doc/src/sgml/ref/declare.sgml | 12 ++- doc/src/sgml/ref/delete.sgml | 6 +- doc/src/sgml/ref/drop_group.sgml | 13 ++- doc/src/sgml/ref/drop_sequence.sgml | 6 +- doc/src/sgml/ref/drop_table.sgml | 10 ++- doc/src/sgml/ref/drop_user.sgml | 9 +- doc/src/sgml/ref/drop_view.sgml | 5 +- doc/src/sgml/ref/explain.sgml | 18 ++-- doc/src/sgml/ref/fetch.sgml | 12 ++- doc/src/sgml/ref/insert.sgml | 46 ++++++---- doc/src/sgml/ref/load.sgml | 4 +- doc/src/sgml/ref/lock.sgml | 26 +++--- doc/src/sgml/ref/move.sgml | 14 +++- doc/src/sgml/ref/notify.sgml | 23 ++--- 37 files changed, 555 insertions(+), 397 deletions(-) diff --git a/doc/src/sgml/ref/alter_database.sgml b/doc/src/sgml/ref/alter_database.sgml index 89f9f8d3f24..6028706546c 100644 --- a/doc/src/sgml/ref/alter_database.sgml +++ b/doc/src/sgml/ref/alter_database.sgml @@ -1,5 +1,5 @@ @@ -25,7 +25,7 @@ ALTER DATABASE name RESET name RENAME TO newname -ALTER DATABASE name OWNER TO new_owner +ALTER DATABASE name OWNER TO newowner @@ -33,12 +33,12 @@ ALTER DATABASE name OWNER TO Description - ALTER DATABASE is used to change the attributes + ALTER DATABASE changes the attributes of a database. - The first two forms change the session default of a run-time + The first two forms change the session default for a run-time configuration variable for a PostgreSQL database. Whenever a new session is subsequently started in that database, the specified value becomes the session default value. @@ -46,12 +46,14 @@ ALTER DATABASE name OWNER TO postgresql.conf or has been received from the postmaster command line. Only the database owner or a superuser can change the session defaults for a - database. + database. Certain variables cannot be set this way, or can only be + set by a superuser. The third form changes the name of the database. Only the database - owner can rename a database, and only if he has the + owner or a superuser can rename a database; non-superuser owners must + also have the CREATEDB privilege. The current database cannot be renamed. (Connect to a different database if you need to do that.) @@ -71,7 +73,7 @@ ALTER DATABASE name OWNER TO name - The name of the database whose session defaults are to be altered. + The name of the database whose attributes are to be altered. @@ -81,14 +83,13 @@ ALTER DATABASE name OWNER TO value - Set the session default for this database of the specified - configuration parameter to the given value. If + Set this database's session default for the specified configuration + parameter to the given value. If value is DEFAULT or, equivalently, RESET is used, the - database-specific variable setting is removed and the system-wide - default + database-specific setting is removed, so the system-wide default setting will be inherited in new sessions. Use RESET - ALL to clear all settings. + ALL to clear all database-specific settings. @@ -107,6 +108,15 @@ ALTER DATABASE name OWNER TO + + + newowner + + + The new owner of the database. + + + @@ -114,9 +124,10 @@ ALTER DATABASE name OWNER TO Notes - Using , - it is also possible to tie a session default to a specific user - rather than a database. User-specific settings override database-specific + It is also possible to tie a session default to a specific user + rather than to a database; see + . + User-specific settings override database-specific ones if there is a conflict. @@ -147,7 +158,6 @@ ALTER DATABASE test SET enable_indexscan TO off; See Also - diff --git a/doc/src/sgml/ref/alter_group.sgml b/doc/src/sgml/ref/alter_group.sgml index 0b9af5f74e0..aa025e684a0 100644 --- a/doc/src/sgml/ref/alter_group.sgml +++ b/doc/src/sgml/ref/alter_group.sgml @@ -1,5 +1,5 @@ @@ -31,11 +31,12 @@ ALTER GROUP groupname RENAME TO Description - ALTER GROUP is used to change a user group. The - first two variants add or remove users from a group. Only database - superusers can use this command. Adding a user to a group does not - create the user. Similarly, removing a user from a group does not - drop the user itself. + ALTER GROUP changes the attributes of a user group. + + + + The first two variants add users to a group or remove them from a group. + Only database superusers can use this command. @@ -61,8 +62,9 @@ ALTER GROUP groupname RENAME TO username - Users which are to be added or removed from the group. The users - must exist. + Users that are to be added to or removed from the group. The users + must already exist; ALTER GROUP does not create or + drop users. diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index e5856d9bfb4..91793e03627 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -1,5 +1,5 @@ @@ -129,7 +129,8 @@ where action is one of: operations. The target can be set in the range 0 to 1000; alternatively, set it to -1 to revert to using the system default statistics - target. For more information on the use of statistics by the + target (). + For more information on the use of statistics by the PostgreSQL query planner, refer to . @@ -153,9 +154,9 @@ where action is one of: compressible data. EXTERNAL is for external, uncompressed data, and EXTENDED is for external, compressed data. EXTENDED is the default for all - data types that support it. The use of EXTERNAL will, for example, - make substring operations on a text column faster, at the penalty of - increased storage space. + data types that support it. Use of EXTERNAL will + make substring operations on text and bytea + columns faster, at the penalty of increased storage space. @@ -285,15 +286,15 @@ where action is one of: name - The name (possibly schema-qualified) of an existing table to - alter. If ONLY is specified, only that table is - altered. If ONLY is not specified, the table and all - its descendant tables (if any) are updated. * can be - appended to the table name to indicate that descendant tables are - to be altered, but in the current version, this is the default - behavior. (In releases before 7.1, ONLY was the - default behavior. The default can be altered by changing the - configuration parameter .) + The name (possibly schema-qualified) of an existing table to + alter. If ONLY is specified, only that table is + altered. If ONLY is not specified, the table and all + its descendant tables (if any) are updated. * can be + appended to the table name to indicate that descendant tables are + to be altered, but in the current version, this is the default + behavior. (In releases before 7.1, ONLY was the + default behavior. The default can be altered by changing the + configuration parameter .) @@ -302,7 +303,7 @@ where action is one of: column - Name of a new or existing column. + Name of a new or existing column. @@ -311,7 +312,7 @@ where action is one of: new_column - New name for an existing column. + New name for an existing column. @@ -320,7 +321,7 @@ where action is one of: new_name - New name for the table. + New name for the table. @@ -329,8 +330,8 @@ where action is one of: type - Data type of the new column, or new data type for an existing - column. + Data type of the new column, or new data type for an existing + column. @@ -339,7 +340,7 @@ where action is one of: table_constraint - New table constraint for the table. + New table constraint for the table. @@ -348,7 +349,7 @@ where action is one of: constraint_name - Name of an existing constraint to drop. + Name of an existing constraint to drop. @@ -358,7 +359,7 @@ where action is one of: Automatically drop objects that depend on the dropped column - or constraint (for example, views referencing the column). + or constraint (for example, views referencing the column). @@ -368,7 +369,7 @@ where action is one of: Refuse to drop the column or constraint if there are any dependent - objects. This is the default behavior. + objects. This is the default behavior. @@ -377,7 +378,7 @@ where action is one of: index_name - The index name on which the table should be marked for clustering. + The index name on which the table should be marked for clustering. @@ -386,7 +387,7 @@ where action is one of: new_owner - The user name of the new owner of the table. + The user name of the new owner of the table. @@ -395,7 +396,7 @@ where action is one of: tablespace_name - The tablespace name to which the table will be moved. + The tablespace name to which the table will be moved. @@ -472,7 +473,8 @@ ALTER TABLE table ALTER COLUMN anycol TYPE anytype; though a USING clause is supplied. In such cases, drop the default with DROP DEFAULT, perform the ALTER TYPE, and then use SET DEFAULT to add a suitable new - default. + default. Similar considerations apply to indexes and constraints involving + the column. @@ -593,7 +595,7 @@ ALTER TABLE distributors ADD PRIMARY KEY (dist_id); - To move a table to a different tablespace: + To move a table to a different tablespace: ALTER TABLE distributors SET TABLESPACE fasttablespace; diff --git a/doc/src/sgml/ref/alter_user.sgml b/doc/src/sgml/ref/alter_user.sgml index f552fe01a2a..0bf30d9bf9c 100644 --- a/doc/src/sgml/ref/alter_user.sgml +++ b/doc/src/sgml/ref/alter_user.sgml @@ -1,5 +1,5 @@ @@ -24,9 +24,9 @@ ALTER USER name [ [ WITH ] option can be: - [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' - | CREATEDB | NOCREATEDB + CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER + | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'abstime' ALTER USER name RENAME TO newname @@ -40,22 +40,21 @@ ALTER USER name RESET Description - ALTER USER is used to change the attributes of a + ALTER USER changes the attributes of a PostgreSQL user account. Attributes not mentioned in the command retain their previous settings. - The first variant of this command in the synopsis changes certain - global user privileges and authentication settings. (See below for - details.) Only a database superuser can change these privileges and - the password expiration with this command. Ordinary users can only - change their own password. + The first variant of this command listed in the synopsis changes certain + per-user privileges and authentication settings. (See below for + details.) Database superusers can change any of these settings for any + user. Ordinary users can only change their own password. The second variant changes the name of the user. Only a database - superuser can rename user accounts. The session user cannot be + superuser can rename user accounts. The current session user cannot be renamed. (Connect as a different user if you need to do that.) Because MD5-encrypted passwords use the user name as cryptographic salt, renaming a user clears their MD5 @@ -70,6 +69,8 @@ ALTER USER name RESET or has been received from the postmaster command line. Ordinary users can change their own session defaults. Superusers can change anyone's session defaults. + Certain variables cannot be set this way, or can only be + set by a superuser. @@ -81,55 +82,56 @@ ALTER USER name RESET name - The name of the user whose attributes are to be altered. + The name of the user whose attributes are to be altered. - password + CREATEDB + NOCREATEDB - The new password to be used for this account. + These clauses define a user's ability to create databases. If + CREATEDB is specified, the user + will be allowed to create his own databases. Using + NOCREATEDB will deny a user the ability to + create databases. (If the user is also a superuser, then this + setting has no real effect.) - ENCRYPTED - UNENCRYPTED + CREATEUSER + NOCREATEUSER - These key words control whether the password is stored - encrypted in pg_shadow. (See - - for more information about this choice.) + These clauses determine whether a user will be permitted to + create new users himself. CREATEUSER will also make + the user a superuser, who can override all access restrictions. - CREATEDB - NOCREATEDB + password - These clauses define a user's ability to create databases. If - CREATEDB is specified, the user - will be allowed to create his own databases. Using - NOCREATEDB will deny a user the ability to - create databases. + The new password to be used for this account. - CREATEUSER - NOCREATEUSER + ENCRYPTED + UNENCRYPTED - These clauses determine whether a user will be permitted to - create new users himself. This option will also make the user - a superuser who can override all access restrictions. + These key words control whether the password is stored + encrypted in pg_shadow. (See + + for more information about this choice.) @@ -138,9 +140,9 @@ ALTER USER name RESET abstime - The date (and, optionally, the time) - at which this user's password is to expire. To set the password - never to expire, use 'infinity'. + The date (and, optionally, the time) + at which this user's password is to expire. To set the password + never to expire, use 'infinity'. @@ -149,7 +151,7 @@ ALTER USER name RESET newname - The new name of the user. + The new name of the user. @@ -163,9 +165,9 @@ ALTER USER name RESET parameter to the given value. If value is DEFAULT or, equivalently, RESET is used, the - user-specific variable setting is removed and the user will + user-specific variable setting is removed, so the user will inherit the system-wide default setting in new sessions. Use - RESET ALL to clear all settings. + RESET ALL to clear all user-specific settings. @@ -194,9 +196,18 @@ ALTER USER name RESET - Using , it is also possible to tie a - session default to a specific database rather than a user. + The VALID UNTIL clause defines an expiration time for a + password only, not for the user account per se. In + particular, the expiration time is not enforced when logging in using + a non-password-based authentication method. + + + + It is also possible to tie a + session default to a specific database rather than to a user; see + . + User-specific settings override database-specific + ones if there is a conflict. @@ -204,7 +215,7 @@ ALTER USER name RESET Examples - Change a user password: + Change a user's password: ALTER USER davide WITH PASSWORD 'hu8jmn3'; @@ -212,7 +223,7 @@ ALTER USER davide WITH PASSWORD 'hu8jmn3'; - Change a user's valid until date: + Change the expiration date of the user's password: ALTER USER manuel VALID UNTIL 'Jan 31 2030'; @@ -220,8 +231,8 @@ ALTER USER manuel VALID UNTIL 'Jan 31 2030'; - Change a user's valid until date, specifying that his - authorization should expire at midday on 4th May 2005 using + Change a password expiration date, specifying that the password + should expire at midday on 4th May 2005 using the time zone which is one hour ahead of UTC: ALTER USER chris VALID UNTIL 'May 4 12:00:00 2005 +1'; @@ -229,7 +240,7 @@ ALTER USER chris VALID UNTIL 'May 4 12:00:00 2005 +1'; - Make a user valid forever: + Make a password valid forever: ALTER USER fred VALID UNTIL 'infinity'; diff --git a/doc/src/sgml/ref/checkpoint.sgml b/doc/src/sgml/ref/checkpoint.sgml index 8267990c2c4..0f2e20966d0 100644 --- a/doc/src/sgml/ref/checkpoint.sgml +++ b/doc/src/sgml/ref/checkpoint.sgml @@ -1,4 +1,4 @@ - + @@ -37,7 +37,7 @@ CHECKPOINT A checkpoint is a point in the transaction log sequence at which all data files have been updated to reflect the information in the - log. All data files will be flushed to disk. Refer to the + log. All data files will be flushed to disk. Refer to for more information about the WAL system. diff --git a/doc/src/sgml/ref/close.sgml b/doc/src/sgml/ref/close.sgml index a165ca3e131..3376476e26d 100644 --- a/doc/src/sgml/ref/close.sgml +++ b/doc/src/sgml/ref/close.sgml @@ -1,5 +1,5 @@ @@ -72,7 +72,8 @@ CLOSE name PostgreSQL does not have an explicit OPEN cursor statement; a cursor is considered - open when it is declared. Use the DECLARE + open when it is declared. Use the + statement to declare a cursor. @@ -95,6 +96,16 @@ CLOSE liahona; CLOSE is fully conforming with the SQL standard. + + + See Also + + + + + + + @@ -184,7 +184,7 @@ CLUSTER emp; - Cluster all the tables on the database that have previously been clustered: + Cluster all tables in the database that have previously been clustered: CLUSTER; diff --git a/doc/src/sgml/ref/comment.sgml b/doc/src/sgml/ref/comment.sgml index 1138050d2dc..4355e5fe2a1 100644 --- a/doc/src/sgml/ref/comment.sgml +++ b/doc/src/sgml/ref/comment.sgml @@ -1,5 +1,5 @@ @@ -51,12 +51,6 @@ COMMENT ON COMMENT stores a comment about a database object. - Comments can be - easily retrieved with the psql commands - \dd, \d+, and \l+. - Other user interfaces to retrieve comments can be built atop - the same built-in functions that psql uses, namely - obj_description and col_description. @@ -66,6 +60,16 @@ COMMENT ON string. Comments are automatically dropped when the object is dropped. + + + Comments can be + easily retrieved with the psql commands + \dd, \d+, and \l+. + Other user interfaces to retrieve comments can be built atop + the same built-in functions that psql uses, namely + obj_description and col_description + (see ). + @@ -141,7 +145,8 @@ COMMENT ON text - The new comment. + The new comment, written as a string literal; or NULL + to drop the comment. diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index ba82f4f1c2f..7d9b18c5740 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -1,5 +1,5 @@ @@ -131,8 +131,8 @@ COPY tablename [ ( Causes all data to be stored or read in binary format rather - than as text. You cannot specify the - or options in binary mode. + than as text. You cannot specify the , + , or @@ -186,10 +186,7 @@ COPY tablename [ ( CSV - Enables Comma Separated Variable (CSV) mode. (Also - called Comma Separated Value). It sets the default - DELIMITER to comma, and QUOTE and - ESCAPE values to double-quote. + Selects Comma Separated Value (CSV) mode. @@ -232,9 +229,9 @@ COPY tablename [ ( In CSV COPY FROM mode, process each specified column as though it were quoted and hence not a - NULL value. For the default null string in - CSV mode (''), this causes a missing - values to be input as a zero-length strings. + NULL value. For the default null string in + CSV mode (''), this causes missing + values to be input as zero-length strings. @@ -304,13 +301,11 @@ COPY tablename [ ( COPY input and output is affected by - DateStyle . For portability with other - PostgreSQL installations which might use + DateStyle. To ensure portability to other + PostgreSQL installations that might use non-default DateStyle settings, DateStyle should be set to ISO before - using COPY. In CSV mode, use ISO - or a DateStyle setting appropriate for the - external application. + using COPY TO. @@ -332,9 +327,9 @@ COPY tablename [ ( Text Format - When COPY is used without the BINARY option, - the data read or written is a text file with one line per table row, - unless CSV mode is used. + When COPY is used without the BINARY + or CSV options, + the data read or written is a text file with one line per table row. Columns in a row are separated by the delimiter character. The column values themselves are strings generated by the output function, or acceptable to the input function, of each @@ -465,7 +460,7 @@ COPY tablename [ ( This format is used for importing and exporting the Comma - Separated Variable (CSV) file format used by many other + Separated Value (CSV) file format used by many other programs, such as spreadsheets. Instead of the escaping used by PostgreSQL's standard text mode, it produces and recognises the common CSV escaping mechanism. @@ -484,7 +479,7 @@ COPY tablename [ ( - In general, the CSV format has no way to distinguish a + The CSV format has no standard way to distinguish a NULL value from an empty string. PostgreSQL's COPY handles this by quoting. A NULL is output as the NULL @@ -517,7 +512,7 @@ COPY tablename [ ( COPY might produce files that other - programs can not process. + programs cannot process. diff --git a/doc/src/sgml/ref/create_aggregate.sgml b/doc/src/sgml/ref/create_aggregate.sgml index e3098538fac..4ae1d1dd8cd 100644 --- a/doc/src/sgml/ref/create_aggregate.sgml +++ b/doc/src/sgml/ref/create_aggregate.sgml @@ -1,5 +1,5 @@ @@ -233,7 +233,7 @@ CREATE AGGREGATE name ( CREATE AGGREGATE is a PostgreSQL language extension. The SQL - standard does not provide for user-defined aggregate function. + standard does not provide for user-defined aggregate functions. diff --git a/doc/src/sgml/ref/create_domain.sgml b/doc/src/sgml/ref/create_domain.sgml index 2830d09cb5d..b07f571c793 100644 --- a/doc/src/sgml/ref/create_domain.sgml +++ b/doc/src/sgml/ref/create_domain.sgml @@ -1,5 +1,5 @@ @@ -63,7 +63,7 @@ where constraint is: name - The name (optionally schema-qualified) of a domain to be created. + The name (optionally schema-qualified) of a domain to be created. @@ -73,7 +73,7 @@ where constraint is: The underlying data type of the domain. This may include array - specifiers. + specifiers. @@ -196,6 +196,7 @@ CREATE TABLE us_snail_addy ( See Also + diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index 7d8d01f4cd3..0991e96a54a 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -1,5 +1,5 @@ @@ -62,8 +62,8 @@ CREATE [ OR REPLACE ] FUNCTION name If you drop and then recreate a function, the new function is not - the same entity as the old; you will break existing rules, views, - triggers, etc. that referred to the old function. Use + the same entity as the old; you will have to drop existing rules, views, + triggers, etc. that refer to the old function. Use CREATE OR REPLACE FUNCTION to change a function definition without breaking objects that refer to the function. @@ -106,16 +106,8 @@ CREATE [ OR REPLACE ] FUNCTION name The data type(s) of the function's arguments (optionally - schema-qualified), if any. The argument types may be base, complex, or - domains, or copy the type of an existing column. - - - The type of a column is referenced by writing - tablename.columnname%TYPE; - using this can sometimes help make a function independent from - changes to the definition of a table. + schema-qualified), if any. The argument types may be base, composite, + or domain types, or may reference the type of a table column. Depending on the implementation language it may also be allowed @@ -123,6 +115,14 @@ CREATE [ OR REPLACE ] FUNCTION name Pseudotypes indicate that the actual argument type is either incompletely specified, or outside the set of ordinary SQL data types. + + The type of a column is referenced by writing + tablename.columnname%TYPE. + Using this feature can sometimes help make a function independent of + changes to the definition of a table. + @@ -132,18 +132,22 @@ CREATE [ OR REPLACE ] FUNCTION name The return data type (optionally schema-qualified). The return type - may be a base type, complex type, or a domain, - or may be specified to copy the type of an existing column. See the description - under argtype above on how to reference the type - of an existing column. - - + may be a base, composite, or domain type, + or may reference the type of a table column. Depending on the implementation language it may also be allowed to specify pseudotypes such as cstring. + + The SETOF modifier indicates that the function will return a set of items, rather than a single item. + + The type of a column is referenced by writing + tablename.columnname%TYPE. + @@ -155,8 +159,7 @@ CREATE [ OR REPLACE ] FUNCTION name The name of the language that the function is implemented in. May be SQL, C, internal, or the name of a user-defined - procedural language. (See also .) For backward compatibility, + procedural language. For backward compatibility, the name may be enclosed by single quotes. @@ -303,7 +306,7 @@ CREATE [ OR REPLACE ] FUNCTION name isStrict - Equivalent to STRICT or RETURNS NULL ON NULL INPUT + Equivalent to STRICT or RETURNS NULL ON NULL INPUT. @@ -394,7 +397,7 @@ CREATE [ OR REPLACE ] FUNCTION name information and examples, see . CREATE FUNCTION add(integer, integer) RETURNS integer - AS $$select $1 + $2;$$ + AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT; @@ -406,10 +409,11 @@ CREATE FUNCTION add(integer, integer) RETURNS integer PL/pgSQL: -CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS ' +CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$ BEGIN RETURN i + 1; - END;' LANGUAGE plpgsql; + END; +$$ LANGUAGE plpgsql; @@ -427,17 +431,17 @@ CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS ' - + See Also - - , - , - , - , - , - - + + + + + + + + diff --git a/doc/src/sgml/ref/create_group.sgml b/doc/src/sgml/ref/create_group.sgml index 7252ae66854..94ce98a757e 100644 --- a/doc/src/sgml/ref/create_group.sgml +++ b/doc/src/sgml/ref/create_group.sgml @@ -1,5 +1,5 @@ @@ -33,9 +33,13 @@ where option can be: Description - CREATE GROUP will create a new group in the - database cluster. You must be a database - superuser to use this command. + CREATE GROUP will create a new group of users. + You must be a database superuser to use this command. + + + + Note that both users and groups are defined at the database cluster + level, and so are valid in all databases in the cluster. @@ -53,7 +57,7 @@ where option can be: name - The name of the group. + The name of the group. @@ -64,11 +68,14 @@ where option can be: The SYSID clause can be used to choose the PostgreSQL group ID of the new - group. It is not necessary to do so, however. + group. + This is normally not necessary, but may + be useful if you need to recreate a group referenced in the + permissions of some object. - If this is not specified, the highest assigned group ID plus one, - starting at 1, will be used as default. + If this is not specified, the highest assigned group ID plus one + (with a minimum of 100) will be used as default. @@ -111,6 +118,15 @@ CREATE GROUP marketing WITH USER jonathan, david; standard. Roles are similar in concept to groups. + + + See Also + + + + + + @@ -74,13 +74,7 @@ CREATE [ UNIQUE ] INDEX name ON WHERE with UNIQUE to enforce uniqueness over a subset of a - table. - - - - Indexes are not used for IS NULL clauses by default. - The best way to use indexes in such cases is to create a partial index - using an IS NULL comparison. + table. See for more discussion. @@ -211,7 +205,7 @@ CREATE [ UNIQUE ] INDEX name ON See for information about when indexes can be used, when they are not used, and in which particular situations - can be useful. + they can be useful. @@ -243,6 +237,12 @@ CREATE [ UNIQUE ] INDEX name ON to remove an index. + + + Indexes are not used for IS NULL clauses by default. + The best way to use indexes in such cases is to create a partial index + using an IS NULL predicate. + @@ -293,6 +293,15 @@ SELECT * FROM points are no provisions for indexes in the SQL standard. + + + See Also + + + + + + @@ -41,7 +41,7 @@ CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name CREATE LANGUAGE effectively associates the language name with a call handler that is responsible for executing - functions written in the language. Refer to + functions written in the language. Refer to for more information about language call handlers. @@ -186,11 +186,6 @@ CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name - - The definition of a procedural language cannot be changed once it - has been created, with the exception of the privileges. - - To be able to use a procedural language, a user must be granted the USAGE privilege. The @@ -233,8 +228,8 @@ CREATE LANGUAGE plsample - - + + diff --git a/doc/src/sgml/ref/create_operator.sgml b/doc/src/sgml/ref/create_operator.sgml index 278fe3f3a08..785f3ace46f 100644 --- a/doc/src/sgml/ref/create_operator.sgml +++ b/doc/src/sgml/ref/create_operator.sgml @@ -1,5 +1,5 @@ @@ -97,7 +97,7 @@ CREATE OPERATOR name ( The other clauses specify optional operator optimization clauses. - Their meaning is detailed in . + Their meaning is detailed in . @@ -304,6 +304,16 @@ CREATE OPERATOR === ( provisions for user-defined operators in the SQL standard. + + + See Also + + + + + + + @@ -82,10 +82,10 @@ CREATE [ OR REPLACE ] RULE name AS attempts to perform the update action, because it thinks it might end up trying to perform the action on the dummy table of the view in some cases. If you want to handle all the useful cases in - conditional rules, you can; just add an unconditional DO + conditional rules, add an unconditional DO INSTEAD NOTHING rule to ensure that the system understands it will never be called on to update the dummy table. - Then make the conditional rules not INSTEAD; in + Then make the conditional rules non-INSTEAD; in the cases where they are applied, they add to the default INSTEAD NOTHING action. @@ -213,12 +213,12 @@ CREATE [ OR REPLACE ] RULE name AS CREATE RULE "_RETURN" AS ON SELECT TO t1 DO INSTEAD - SELECT * FROM t2; + SELECT * FROM t2; CREATE RULE "_RETURN" AS ON SELECT TO t2 DO INSTEAD - SELECT * FROM t1; + SELECT * FROM t1; SELECT * FROM t1; diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml index 195ab010423..db0e436bde5 100644 --- a/doc/src/sgml/ref/create_schema.sgml +++ b/doc/src/sgml/ref/create_schema.sgml @@ -1,5 +1,5 @@ @@ -29,7 +29,7 @@ CREATE SCHEMA AUTHORIZATION usernameDescription - CREATE SCHEMA will enter a new schema + CREATE SCHEMA enters a new schema into the current database. The schema name must be distinct from the name of any existing schema in the current database. @@ -41,8 +41,9 @@ CREATE SCHEMA AUTHORIZATION usernamequalifying their names with the schema name as a prefix, or by setting a search - path that includes the desired schema(s). Unqualified objects are - created in the current schema (the one at the front of the search path, + path that includes the desired schema(s). A CREATE command + specifying an unqualified object name creates the object + in the current schema (the one at the front of the search path, which can be determined with the function current_schema). diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 344efa52e6e..6459b227207 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1,5 +1,5 @@ @@ -82,12 +82,7 @@ and table_constraint is: - A table cannot have more than 1600 columns. (In practice, the - effective limit is lower because of tuple-length constraints). - - - - The optional constraint clauses specify constraints (or tests) that + The optional constraint clauses specify constraints (tests) that new or updated rows must satisfy for an insert or update operation to succeed. A constraint is an SQL object that helps define the set of valid values in the table in various ways. @@ -99,7 +94,7 @@ and table_constraint is: column definition. A table constraint definition is not tied to a particular column, and it can encompass more than one column. Every column constraint can also be written as a table constraint; - a column constraint is only a notational convenience if the + a column constraint is only a notational convenience for use when the constraint only affects one column. @@ -156,7 +151,7 @@ and table_constraint is: The data type of the column. This may include array - specifiers. For more information on the data types included with + specifiers. For more information on the data types supported by PostgreSQL, refer to . @@ -184,29 +179,6 @@ and table_constraint is: - - LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] - - - The LIKE clause specifies a table from which - the new table automatically copies all column names, their data types, - and their not-null constraints. - - - Unlike INHERITS, the new table and original table - are completely decoupled after creation is complete. Changes to the - original table will not be applied to the new table, and it is not - possible to include data of the new table in scans of the original - table. - - - Default expressions for the copied column definitions will only be - included if INCLUDING DEFAULTS is specified. The - default is to exclude default expressions. - - - - INHERITS ( parent_table [, ... ] ) @@ -258,6 +230,30 @@ and table_constraint is: + + LIKE parent_table [ { INCLUDING | EXCLUDING } DEFAULTS ] + + + The LIKE clause specifies a table from which + the new table automatically copies all column names, their data types, + and their not-null constraints. + + + Unlike INHERITS, the new table and original table + are completely decoupled after creation is complete. Changes to the + original table will not be applied to the new table, and it is not + possible to include data of the new table in scans of the original + table. + + + Default expressions for the copied column definitions will only be + copied if INCLUDING DEFAULTS is specified. The + default behavior is to exclude default expressions, resulting in + all columns of the new table having null defaults. + + + + WITH OIDS WITHOUT OIDS @@ -274,15 +270,15 @@ and table_constraint is: - If WITHOUT OIDS is specified or implied, this - means that the generation of OIDs for this table will be - supressed. This is generally considered worthwhile, since it + If WITHOUT OIDS is specified or implied, the new + table does not store OIDs and no OID will be assigned for a row inserted + into it. This is generally considered worthwhile, since it will reduce OID consumption and thereby postpone the wraparound of the 32-bit OID counter. Once the counter wraps around, OIDs can no longer be assumed to be unique, which makes them considerably less useful. In addition, excluding OIDs from a - table reduces the space required on disk to storage the table by - 4 bytes per row, leading to increased performance. + table reduces the space required to store the table on disk by + 4 bytes per row (on most machines), slightly improving performance. @@ -319,7 +315,7 @@ and table_constraint is: - This clause is only available for compatibility with + This clause is only provided for compatibility with non-standard SQL databases. Its use is discouraged in new applications. @@ -333,7 +329,7 @@ and table_constraint is: The UNIQUE constraint specifies that a - group of one or more distinct columns of a table may contain + group of one or more columns of a table may contain only unique values. The behavior of the unique table constraint is the same as that for column constraints, with the additional capability to span multiple columns. @@ -450,9 +446,9 @@ and table_constraint is: clause specifies the action to perform when a referenced column in the referenced table is being updated to a new value. If the row is updated, but the referenced column is not actually - changed, no action is done. Referential actions apart from the - check of NO ACTION can not be deferred even if - the constraint is deferrable. There are the following possible + changed, no action is done. Referential actions other than the + NO ACTION check cannot be deferred, even if + the constraint is declared deferrable. There are the following possible actions for each clause: @@ -476,7 +472,7 @@ and table_constraint is: Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that - the check is not deferrable. + the check is not deferrable. @@ -633,13 +629,11 @@ and table_constraint is: Notes - - Using OIDs in new applications is not recommended: where possible, using a SERIAL or other sequence generator as the table's primary key is preferred. However, if - your application does make use of OIDs to identify specific rows + your application does make use of OIDs to identify specific rows of a table, it is recommended to create a unique constraint on the oid column of that table, to ensure that OIDs in the table will indeed uniquely identify rows even after @@ -656,26 +650,26 @@ and table_constraint is: unique data key, it is difficult to identify specific rows. - - PostgreSQL automatically creates an index for each unique constraint and primary key constraint to - enforce the uniqueness. Thus, it is not necessary to create an - explicit index for primary key columns. (See for more information.) - - Unique constraints and primary keys are not inherited in the current implementation. This makes the combination of inheritance and unique constraints rather dysfunctional. - - + + + A table cannot have more than 1600 columns. (In practice, the + effective limit is lower because of tuple-length constraints.) + + @@ -778,7 +772,7 @@ CREATE TABLE films ( Define a primary key constraint for table distributors. The following two examples are equivalent, the first using the table constraint syntax, the second - the column constraint notation. + the column constraint syntax. CREATE TABLE distributors ( diff --git a/doc/src/sgml/ref/create_table_as.sgml b/doc/src/sgml/ref/create_table_as.sgml index 3e5869c01f8..a232032bf14 100644 --- a/doc/src/sgml/ref/create_table_as.sgml +++ b/doc/src/sgml/ref/create_table_as.sgml @@ -1,5 +1,5 @@ @@ -20,7 +20,8 @@ PostgreSQL documentation -CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ] +CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name + [ (column_name [, ...] ) ] [ [ WITH | WITHOUT ] OIDS ] AS query @@ -91,9 +92,9 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name The name of a column in the new table. If column names are not provided, they are taken from the output column names of the - query. If the table is created out of an - EXECUTE command, a column name list can - currently not be specified. + query. If the table is created from an + EXECUTE command, a column name list cannot be + specified. @@ -105,7 +106,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name This optional clause specifies whether the table created by CREATE TABLE AS should include OIDs. If - neither form of this clause if specified, the value of the + neither form of this clause is specified, the value of the configuration parameter is used. @@ -143,12 +144,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name Prior to PostgreSQL 8.0, CREATE TABLE AS always included OIDs in the table it - produced. Furthermore, these OIDs were newly generated: they were - distinct from the OIDs of any of the rows in the source tables of - the SELECT or EXECUTE - statement. Therefore, if CREATE TABLE AS was - frequently executed, the OID counter would be rapidly - incremented. As of PostgresSQL 8.0, + produced. As of PostgresSQL 8.0, the CREATE TABLE AS command allows the user to explicitly specify whether OIDs should be included. If the presence of OIDs is not explicitly specified, diff --git a/doc/src/sgml/ref/create_type.sgml b/doc/src/sgml/ref/create_type.sgml index ef6f93b0f27..e2276ae5abe 100644 --- a/doc/src/sgml/ref/create_type.sgml +++ b/doc/src/sgml/ref/create_type.sgml @@ -1,5 +1,5 @@ @@ -45,7 +45,7 @@ CREATE TYPE name ( CREATE TYPE registers a new data type for use in - the current data base. The user who defines a type becomes its + the current database. The user who defines a type becomes its owner. @@ -68,7 +68,8 @@ CREATE TYPE name ( This is essentially the same as the row type of a table, but using CREATE TYPE avoids the need to create an actual table when all that is wanted is to define a type. - A stand-alone composite type is useful as the return type of a function. + A stand-alone composite type is useful as the argument or return type of a + function. @@ -104,7 +105,8 @@ CREATE TYPE name ( The first argument is the input text as a C string, the second argument is the element type's OID in case this is an array type (or the type's own OID for a composite type), - and the third is the typmod of the destination column, if known. + and the third is the typmod of the destination column, if known + (-1 will be passed if not). The input function should return a value of the data type itself. The output function may be declared as taking one argument of the new data type, or as taking @@ -470,6 +472,7 @@ CREATE TYPE name ( a function definition: CREATE TYPE compfoo AS (f1 int, f2 text); + CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$ SELECT fooid, fooname FROM foo $$ LANGUAGE SQL; @@ -525,7 +528,7 @@ CREATE TABLE big_objs ( More examples, including suitable input and output functions, are - in . + in . diff --git a/doc/src/sgml/ref/create_user.sgml b/doc/src/sgml/ref/create_user.sgml index 84b6b40442b..1df1acf2b66 100644 --- a/doc/src/sgml/ref/create_user.sgml +++ b/doc/src/sgml/ref/create_user.sgml @@ -1,5 +1,5 @@ @@ -25,10 +25,10 @@ CREATE USER name [ [ WITH ] option can be: SYSID uid - | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | IN GROUP groupname [, ...] + | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'abstime' @@ -54,7 +54,7 @@ where option can be: name - The name of the user. + The name of the new user. @@ -64,92 +64,92 @@ where option can be: The SYSID clause can be used to choose the - PostgreSQL user ID of the user that - is being created. This is not normally not necessary, but may + PostgreSQL user ID of the new user. + This is normally not necessary, but may be useful if you need to recreate the owner of an orphaned object. If this is not specified, the highest assigned user ID plus one - (with a minimum of 100) will be used as default. + (with a minimum of 100) will be used as default. - password + CREATEDB + NOCREATEDB - Sets the user's password. If you do not plan to use password - authentication you can omit this option, but then the user - won't be able to connect if you decide to switch to password - authentication. The password can be set or changed later, - using . + These clauses define a user's ability to create databases. If + CREATEDB is specified, the user being + defined will be allowed to create his own databases. Using + NOCREATEDB will deny a user the ability to + create databases. If not specified, + NOCREATEDB is the default. - ENCRYPTED - UNENCRYPTED + CREATEUSER + NOCREATEUSER - These key words control whether the password is stored - encrypted in the system catalogs. (If neither is specified, - the default behavior is determined by the configuration - parameter .) If the - presented password string is already in MD5-encrypted format, - then it is stored encrypted as-is, regardless of whether - ENCRYPTED or UNENCRYPTED is specified - (since the system cannot decrypt the specified encrypted - password string). This allows reloading of encrypted - passwords during dump/restore. - - - - Note that older clients may lack support for the MD5 - authentication mechanism that is needed to work with passwords - that are stored encrypted. + These clauses determine whether a user will be permitted to + create new users himself. CREATEUSER will also make + the user a superuser, who can override all access restrictions. + If not specified, + NOCREATEUSER is the default. - CREATEDB - NOCREATEDB + groupname - These clauses define a user's ability to create databases. If - CREATEDB is specified, the user being - defined will be allowed to create his own databases. Using - NOCREATEDB will deny a user the ability to - create databases. If this clause is omitted, - NOCREATEDB is used by default. + A name of an existing group into which to insert the user as a new + member. Multiple group names may be listed. - CREATEUSER - NOCREATEUSER + password - These clauses determine whether a user will be permitted to - create new users himself. This option will also make the user - a superuser who can override all access restrictions. - Omitting this clause will set the user's value of this - attribute to be NOCREATEUSER. + Sets the user's password. If you do not plan to use password + authentication you can omit this option, but then the user + won't be able to connect if you decide to switch to password + authentication. The password can be set or changed later, + using . - groupname + ENCRYPTED + UNENCRYPTED - A name of a group into which to insert the user as a new member. - Multiple group names may be listed. + These key words control whether the password is stored + encrypted in the system catalogs. (If neither is specified, + the default behavior is determined by the configuration + parameter .) If the + presented password string is already in MD5-encrypted format, + then it is stored encrypted as-is, regardless of whether + ENCRYPTED or UNENCRYPTED is specified + (since the system cannot decrypt the specified encrypted + password string). This allows reloading of encrypted + passwords during dump/restore. + + + + Note that older clients may lack support for the MD5 + authentication mechanism that is needed to work with passwords + that are stored encrypted. @@ -158,9 +158,9 @@ where option can be: abstime - The VALID UNTIL clause sets an absolute - time after which the user's password is no longer valid. If - this clause is omitted the login will be valid for all time. + The VALID UNTIL clause sets an absolute + time after which the user's password is no longer valid. If + this clause is omitted the password will be valid for all time. @@ -184,6 +184,13 @@ where option can be: the same functionality as CREATE USER (in fact, it calls this command) but can be run from the command shell. + + + The VALID UNTIL clause defines an expiration time for a + password only, not for the user account per se. In + particular, the expiration time is not enforced when logging in using + a non-password-based authentication method. + diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml index 2676005852d..29578216918 100644 --- a/doc/src/sgml/ref/create_view.sgml +++ b/doc/src/sgml/ref/create_view.sgml @@ -1,5 +1,5 @@ @@ -165,7 +165,7 @@ CREATE VIEW name [ ( CHECK OPTION - This option is to do with updatable views. All + This option has to do with updatable views. All INSERT and UPDATE commands on the view will be checked to ensure data satisfy the view-defining condition (that is, the new data would be visible through the @@ -201,6 +201,14 @@ CREATE VIEW name [ ( PostgreSQL language extension. + + + See Also + + + + + @@ -287,6 +287,16 @@ DECLARE liahona CURSOR FOR SELECT * FROM films; extension. + + + See Also + + + + + + + @@ -37,14 +37,14 @@ DELETE FROM [ ONLY ] table [ WHERE is a - PostgreSQL extension which provides a + PostgreSQL extension that provides a faster mechanism to remove all rows from a table. By default, DELETE will delete rows in the - specified table and all its subtables. If you wish to only delete + specified table and all its subtables. If you wish to delete only from the specific table mentioned, you must use the ONLY clause. diff --git a/doc/src/sgml/ref/drop_group.sgml b/doc/src/sgml/ref/drop_group.sgml index d4e421448d0..cabe908877c 100644 --- a/doc/src/sgml/ref/drop_group.sgml +++ b/doc/src/sgml/ref/drop_group.sgml @@ -1,5 +1,5 @@ @@ -48,6 +48,17 @@ DROP GROUP name + + Notes + + + It is unwise to drop a group that has any + granted permissions on objects. Currently, this is not enforced, + but it is likely that future versions of + PostgreSQL will check for the error. + + + Examples diff --git a/doc/src/sgml/ref/drop_sequence.sgml b/doc/src/sgml/ref/drop_sequence.sgml index f519ebedee7..8a7bb893eb2 100644 --- a/doc/src/sgml/ref/drop_sequence.sgml +++ b/doc/src/sgml/ref/drop_sequence.sgml @@ -1,5 +1,5 @@ @@ -82,7 +82,9 @@ DROP SEQUENCE serial; Compatibility - DROP SEQUENCE conforms with SQL:2003. + DROP SEQUENCE conforms with + SQL:2003, except that the standard only allows one + sequence to be dropped per command. diff --git a/doc/src/sgml/ref/drop_table.sgml b/doc/src/sgml/ref/drop_table.sgml index 52189f5e7d3..40cc8167ef9 100644 --- a/doc/src/sgml/ref/drop_table.sgml +++ b/doc/src/sgml/ref/drop_table.sgml @@ -1,5 +1,5 @@ @@ -36,9 +36,10 @@ DROP TABLE name [, ...] [ CASCADE | DROP TABLE always removes any indexes, rules, triggers, and constraints that exist for the target table. - However, to drop a table that is referenced by a foreign-key + However, to drop a table that is referenced by a view or a foreign-key constraint of another table, CASCADE must be - specified. (CASCADE will remove the foreign-key + specified. (CASCADE will remove a dependent view entirely, + but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely.) @@ -95,7 +96,8 @@ DROP TABLE films, distributors; Compatibility - This command conforms to the SQL standard. + This command conforms to the SQL standard, except that the standard only + allows one table to be dropped per command. diff --git a/doc/src/sgml/ref/drop_user.sgml b/doc/src/sgml/ref/drop_user.sgml index 1cdf1dfeab4..c02cf12607c 100644 --- a/doc/src/sgml/ref/drop_user.sgml +++ b/doc/src/sgml/ref/drop_user.sgml @@ -1,5 +1,5 @@ @@ -63,6 +63,13 @@ DROP USER name To drop a user who owns a database, first drop the database or change its ownership. + + + It is unwise to drop a user who either owns any database objects or has any + granted permissions on objects. Currently, this is only enforced for + the case of owners of databases, but it is likely that future versions of + PostgreSQL will check other cases. + diff --git a/doc/src/sgml/ref/drop_view.sgml b/doc/src/sgml/ref/drop_view.sgml index 3473087d58d..ee28e0ef5e7 100644 --- a/doc/src/sgml/ref/drop_view.sgml +++ b/doc/src/sgml/ref/drop_view.sgml @@ -1,5 +1,5 @@ @@ -83,7 +83,8 @@ DROP VIEW kinds; Compatibility - This command conforms to the SQL standard. + This command conforms to the SQL standard, except that the standard only + allows one view to be dropped per command. diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml index 57f280dc56d..748e7e16ae2 100644 --- a/doc/src/sgml/ref/explain.sgml +++ b/doc/src/sgml/ref/explain.sgml @@ -1,5 +1,5 @@ @@ -41,9 +41,9 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] statementPostgreSQL planner generates for the supplied statement. The execution plan shows how the table(s) - referenced by the statement will be scanned---by plain sequential scan, - index scan, etc.---and if multiple tables are referenced, what join - algorithms will be used to bring together the required row from + referenced by the statement will be scanned — by plain sequential scan, + index scan, etc. — and if multiple tables are referenced, what join + algorithms will be used to bring together the required rows from each input table. @@ -108,7 +108,7 @@ ROLLBACK; Show the full internal representation of the plan tree, rather than just a summary. Usually this option is only useful for - debugging PostgreSQL. The + specialized debugging purposes. The VERBOSE output is either pretty-printed or not, depending on the setting of the configuration parameter. @@ -248,6 +248,14 @@ EXPLAIN ANALYZE EXECUTE query(100, 200); There is no EXPLAIN statement defined in the SQL standard. + + + See Also + + + + + @@ -402,6 +402,16 @@ COMMIT WORK; name; the option to use IN is an extension. + + + See Also + + + + + + + @@ -29,20 +29,29 @@ INSERT INTO table [ ( Description - INSERT allows one to insert new rows into a - table. One can insert - a single row at a time or several rows as a result of a query. + INSERT inserts new rows into a table. + One can insert a single row specified by value expressions, + or several rows as a result of a query. - The columns in the target list may be listed in any order. - Each column not present in the target list will be inserted - using a default value, either its declared default value - or null. + The target column names may be listed in any order. If no list of + column names is given at all, the default is all the columns of the + table in their declared order; or the first N column + names, if there are only N columns supplied by the + VALUES clause or query. The values + supplied by the VALUES clause or query are + associated with the explicit or implicit column list left-to-right. - If the expression for each column is not of the correct data type, + Each column not present in the explicit or implicit column list will be + filled with a default value, either its declared default value + or null if there is none. + + + + If the expression for any column is not of the correct data type, automatic type conversion will be attempted. @@ -93,7 +102,7 @@ INSERT INTO table [ ( expression - An expression or value to assign to column. @@ -103,7 +112,8 @@ INSERT INTO table [ ( DEFAULT - This column will be filled with its default value. + The corresponding column will be filled with + its default value. @@ -152,8 +162,8 @@ INSERT INTO films VALUES - In this second example, the last column len is - omitted and therefore it will have the default value of null: + In this second example, the len column is + omitted and therefore it will have the default value: INSERT INTO films (code, title, did, date_prod, kind) @@ -174,7 +184,7 @@ INSERT INTO films (code, title, did, date_prod, kind) - This examples inserts several rows into table + This example inserts several rows into table films from table tmp: @@ -200,7 +210,13 @@ INSERT INTO tictactoe (game, board) Compatibility - INSERT conforms fully to the SQL standard. + INSERT conforms to the SQL standard. The case in + which a column name list is omitted, but not all the columns are + filled from the VALUES clause or query, + is disallowed by the standard. + + + Possible limitations of the query clause are documented under . diff --git a/doc/src/sgml/ref/load.sgml b/doc/src/sgml/ref/load.sgml index 587281e62a0..b29dfd4ed41 100644 --- a/doc/src/sgml/ref/load.sgml +++ b/doc/src/sgml/ref/load.sgml @@ -1,5 +1,5 @@ @@ -41,7 +41,7 @@ LOAD 'filename' The file name is specified in the same way as for shared library names in ; in particular, one may rely on a search path and automatic addition of the system's standard - shared library file name extension. See for + shared library file name extension. See for more information on this topic. diff --git a/doc/src/sgml/ref/lock.sgml b/doc/src/sgml/ref/lock.sgml index 72a41c3c5ef..9c2f47a8156 100644 --- a/doc/src/sgml/ref/lock.sgml +++ b/doc/src/sgml/ref/lock.sgml @@ -1,5 +1,5 @@ @@ -50,7 +50,7 @@ where lockmode is one of: restrictive lock mode possible. LOCK TABLE provides for cases when you might need more restrictive locking. For example, suppose an application runs a transaction at the - isolation level read committed and needs to ensure that data in a + Read Committed isolation level and needs to ensure that data in a table remains stable for the duration of the transaction. To achieve this you could obtain SHARE lock mode over the table before querying. This will prevent concurrent data changes @@ -66,14 +66,14 @@ where lockmode is one of: - To achieve a similar effect when running a transaction at the - isolation level serializable, you have to execute the LOCK - TABLE statement before executing any data modification - statement. A serializable transaction's view of data will be - frozen when its first data modification statement begins. A later - LOCK TABLE will still prevent concurrent writes — but it - won't ensure that what the transaction reads corresponds to the - latest committed values. + To achieve a similar effect when running a transaction at the Serializable + isolation level, you have to execute the LOCK TABLE statement + before executing any SELECT or data modification statement. + A serializable transaction's view of data will be frozen when its first + SELECT or data modification statement begins. A LOCK + TABLE later in the transaction will still prevent concurrent writes + — but it won't ensure that what the transaction reads corresponds to + the latest committed values. @@ -140,8 +140,8 @@ where lockmode is one of: Specifies that LOCK TABLE should not wait for - any conflicting locks to be released: if the specified lock - cannot be immediately acquired without waiting, the transaction + any conflicting locks to be released: if the specified lock(s) + cannot be acquired immediately without waiting, the transaction is aborted. @@ -219,7 +219,7 @@ COMMIT WORK; Compatibility - + There is no LOCK TABLE in the SQL standard, which instead uses SET TRANSACTION to specify diff --git a/doc/src/sgml/ref/move.sgml b/doc/src/sgml/ref/move.sgml index f61a504b5f3..92aed3951ee 100644 --- a/doc/src/sgml/ref/move.sgml +++ b/doc/src/sgml/ref/move.sgml @@ -1,5 +1,5 @@ @@ -86,11 +86,21 @@ COMMIT WORK; Compatibility - + There is no MOVE statement in the SQL standard. + + + See Also + + + + + + + @@ -34,6 +34,15 @@ NOTIFY name for the specified notification name in the current database. + + NOTIFY provides a simple form of signal or + interprocess communication mechanism for a collection of processes + accessing the same PostgreSQL database. + Higher-level mechanisms can be built by using tables in the database to + pass additional data (beyond a mere notification name) from notifier to + listener(s). + + The information passed to the client for a notification event includes the notification name and the notifying session's server process PID. It is up to the @@ -50,15 +59,6 @@ NOTIFY name to signal different sorts of changes to a single table. - - NOTIFY provides a simple form of signal or - interprocess communication mechanism for a collection of processes - accessing the same PostgreSQL database. - Higher-level mechanisms can be built by using tables in the database to - pass additional data (beyond a mere notification name) from notifier to - listener(s). - - When NOTIFY is used to signal the occurrence of changes to a particular table, a useful programming technique is to put the @@ -79,7 +79,8 @@ NOTIFY name the notification event will not be delivered to its connected client until just after the transaction is completed (either committed or aborted). Again, the reasoning is that if a notification were delivered within a transaction that was - later aborted, one would want the notification to be undone somehow---but + later aborted, one would want the notification to be undone somehow — + but the server cannot take back a notification once it has sent it to the client. So notification events are only delivered between transactions. The upshot of this is that applications using NOTIFY for real-time signaling -- 2.30.2