From 3450fd08a9a3b5f8743fd908e32e919cc999c4e5 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Tue, 22 Apr 2003 10:08:08 +0000 Subject: [PATCH] More editing of reference pages. --- doc/src/sgml/ref/create_aggregate.sgml | 364 +++++++------- doc/src/sgml/ref/create_cast.sgml | 41 +- doc/src/sgml/ref/create_constraint.sgml | 83 ++-- doc/src/sgml/ref/create_conversion.sgml | 66 ++- doc/src/sgml/ref/create_database.sgml | 318 +++++-------- doc/src/sgml/ref/create_domain.sgml | 184 +++---- doc/src/sgml/ref/create_function.sgml | 254 ++++------ doc/src/sgml/ref/create_group.sgml | 119 ++--- doc/src/sgml/ref/create_index.sgml | 442 ++++++----------- doc/src/sgml/ref/create_language.sgml | 98 +--- doc/src/sgml/ref/create_opclass.sgml | 424 ++++++++--------- doc/src/sgml/ref/create_operator.sgml | 520 +++++++------------- doc/src/sgml/ref/create_rule.sgml | 452 +++++++----------- doc/src/sgml/ref/create_schema.sgml | 244 +++++----- doc/src/sgml/ref/create_sequence.sgml | 609 +++++++++++------------- doc/src/sgml/ref/create_table.sgml | 325 +++++-------- doc/src/sgml/ref/create_table_as.sgml | 30 +- doc/src/sgml/ref/create_trigger.sgml | 441 +++++++---------- doc/src/sgml/ref/create_type.sgml | 533 ++++++++++----------- doc/src/sgml/ref/create_user.sgml | 81 ++-- doc/src/sgml/ref/create_view.sgml | 319 +++++-------- 21 files changed, 2448 insertions(+), 3499 deletions(-) diff --git a/doc/src/sgml/ref/create_aggregate.sgml b/doc/src/sgml/ref/create_aggregate.sgml index d53daa45ddd..940343efde5 100644 --- a/doc/src/sgml/ref/create_aggregate.sgml +++ b/doc/src/sgml/ref/create_aggregate.sgml @@ -1,5 +1,5 @@ @@ -10,166 +10,40 @@ PostgreSQL documentation - - CREATE AGGREGATE - - - define a new aggregate function - + CREATE AGGREGATE + define a new aggregate function + - - 2000-07-16 - - -CREATE AGGREGATE name ( BASETYPE = input_data_type, - SFUNC = sfunc, STYPE = state_type + +CREATE AGGREGATE name ( + BASETYPE = input_data_type, + SFUNC = sfunc, + STYPE = state_data_type [ , FINALFUNC = ffunc ] - [ , INITCOND = initial_condition ] ) - - - - - 2000-07-16 - - - Inputs - - - - - name - - - The name (optionally schema-qualified) of an aggregate function to - create. - - - - - - input_data_type - - - The input data type on which this aggregate function operates. - This can be specified as "ANY" for an aggregate that does - not examine its input values - (an example is count(*)). - - - - - - sfunc - - - The name of the state transition function - to be called for each input data value. - This is normally a function of two arguments, the first being of - type state_type - and the second of - type input_data_type. - Alternatively, for an aggregate that does not examine its input - values, the function takes just one argument of - type state_type. - In either case the function must return a value of - type state_type. - This function takes the current state value and the current - input data item, and returns the next state value. - - - - - - state_type - - - The data type for the aggregate's state value. - - - - - - ffunc - - - The name of the final function called to compute the aggregate's - result after all input data has been traversed. The function - must take a single argument of type - state_type. - The output data type of the aggregate is defined as the return - type of this function. - If ffunc - is not specified, then the ending state value is used as the - aggregate's result, and the output type is - state_type. - - - - - - initial_condition - - - The initial setting for the state value. This must be a literal - constant in the form accepted for the data type - state_type. - If not specified, the state value starts out NULL. - - - - - - - - - - 1998-09-09 - - - Outputs - - - - - - -CREATE AGGREGATE - - - - Message returned if the command completes successfully. - - - - - - + [ , INITCOND = initial_condition ] +) + - - - 2000-07-16 - - - Description - + + Description + - CREATE AGGREGATE - allows a user or programmer to extend PostgreSQL - functionality by defining new aggregate functions. Some aggregate functions + CREATE AGGREGATE defines a new aggregate function. Some aggregate functions for base types such as min(integer) - and avg(double precision) are already provided in the base + and avg(double precision) are already provided in the standard distribution. If one defines new types or needs an aggregate function not already provided, then CREATE AGGREGATE can be used to provide the desired features. + If a schema name is given (for example, CREATE AGGREGATE myschema.myagg ...) then the aggregate function is created in the - specified schema. Otherwise it is created in the current schema (the one - at the front of the search path; see CURRENT_SCHEMA()). + specified schema. Otherwise it is created in the current schema. + An aggregate function is identified by its name and input data type. Two aggregates in the same schema can have the same name if they operate on @@ -178,6 +52,7 @@ CREATE AGGREGATE the name and input data type(s) of every ordinary function in the same schema. + An aggregate function is made from one or two ordinary functions: @@ -186,11 +61,12 @@ CREATE AGGREGATE and an optional final calculation function ffunc. These are used as follows: - + sfunc( internal-state, next-data-item ) ---> next-internal-state ffunc( internal-state ) ---> aggregate-value - + + PostgreSQL creates a temporary variable of data type stype @@ -198,7 +74,7 @@ CREATE AGGREGATE data item, the state transition function is invoked to calculate a new internal state value. After all the data has been processed, - the final function is invoked once to calculate the aggregate's output + the final function is invoked once to calculate the aggregate's return value. If there is no final function then the ending state value is returned as-is. @@ -206,67 +82,163 @@ CREATE AGGREGATE An aggregate function may provide an initial condition, that is, an initial value for the internal state value. - This is specified and stored in the database as a field of type + This is specified and stored in the database as a column of type text, but it must be a valid external representation of a constant of the state value data type. If it is not supplied - then the state value starts out NULL. + then the state value starts out null. If the state transition function is declared strict, - then it cannot be called with NULL inputs. With such a transition - function, aggregate execution behaves as follows. NULL input values + then it cannot be called with null inputs. With such a transition + function, aggregate execution behaves as follows. Null input values are ignored (the function is not called and the previous state value - is retained). If the initial state value is NULL, then the first - non-NULL input value replaces the state value, and the transition - function is invoked beginning with the second non-NULL input value. + is retained). If the initial state value is null, then the first + nonnull input value replaces the state value, and the transition + function is invoked beginning with the second nonnull input value. This is handy for implementing aggregates like max. Note that this behavior is only available when - state_type + state_data_type is the same as input_data_type. - When these types are different, you must supply a non-NULL initial - condition or use a non-strict transition function. + When these types are different, you must supply a nonnull initial + condition or use a nonstrict transition function. If the state transition function is not strict, then it will be called - unconditionally at each input value, and must deal with NULL inputs - and NULL transition values for itself. This allows the aggregate + unconditionally at each input value, and must deal with null inputs + and null transition values for itself. This allows the aggregate author to have full control over the aggregate's handling of null values. If the final function is declared strict, then it will not - be called when the ending state value is NULL; instead a NULL result - will be output automatically. (Of course this is just the normal + be called when the ending state value is null; instead a null result + will be returned automatically. (Of course this is just the normal behavior of strict functions.) In any case the final function has - the option of returning NULL. For example, the final function for - avg returns NULL when it sees there were zero - input tuples. + the option of returning a null value. For example, the final function for + avg returns null when it sees there were zero + input rows. - - - - 2000-07-16 - - - Notes - - - Use DROP AGGREGATE - to drop aggregate functions. - + + + + Parameters - - The parameters of CREATE AGGREGATE can be written - in any order, not just the order illustrated above. - + + + name + + + The name (optionally schema-qualified) of the aggregate function + to create. + + + - + + input_data_type + + + The input data type on which this aggregate function operates. + This can be specified as "ANY" for an aggregate that + does not examine its input values (an example is + count(*)). + + + + + + sfunc + + + The name of the state transition function to be called for each + input data value. This is normally a function of two arguments, + the first being of type state_data_type and the second + of type input_data_type. Alternatively, + for an aggregate that does not examine its input values, the + function takes just one argument of type state_data_type. In either case + the function must return a value of type state_data_type. This function + takes the current state value and the current input data item, + and returns the next state value. + + + + + + state_data_type + + + The data type for the aggregate's state value. + + + + + + ffunc + + + The name of the final function called to compute the aggregate's + result after all input data has been traversed. The function + must take a single argument of type state_data_type. The return + data type of the aggregate is defined as the return type of this + function. If ffunc + is not specified, then the ending state value is used as the + aggregate's result, and the return type is state_data_type. + + + + + + initial_condition + + + The initial setting for the state value. This must be a string + constant in the form accepted for the data type state_data_type. If not + specified, the state value starts out null. + + + + + + + The parameters of CREATE AGGREGATE can be + written in any order, not just the order illustrated above. + - + + Diagnostics + + + + CREATE AGGREGATE + + + Message returned if the command completes successfully. + + + + + + + + Notes + + + Use DROP AGGREGATE to drop aggregate functions. + + + + Examples @@ -274,24 +246,14 @@ CREATE AGGREGATE - - - Compatibility - + + Compatibility - - - 1998-09-09 - - - SQL92 - - - CREATE AGGREGATE - is a PostgreSQL language extension. - There is no CREATE AGGREGATE in SQL92. - - + + CREATE AGGREGATE is a + PostgreSQL language extension. The SQL + standard does not provide for user-defined aggregate function. + diff --git a/doc/src/sgml/ref/create_cast.sgml b/doc/src/sgml/ref/create_cast.sgml index a76a417671e..b97135c9538 100644 --- a/doc/src/sgml/ref/create_cast.sgml +++ b/doc/src/sgml/ref/create_cast.sgml @@ -1,4 +1,4 @@ - + @@ -58,11 +58,11 @@ SELECT CAST(42 AS text); If the cast is marked AS ASSIGNMENT then it can be invoked - implicitly when assigning to a column of the target data type. + implicitly when assigning a value to a column of the target data type. For example, supposing that foo.f1 is a column of type text, then -INSERT INTO foo(f1) VALUES(42); +INSERT INTO foo (f1) VALUES (42); will be allowed if the cast from type integer to type text is marked AS ASSIGNMENT, otherwise @@ -75,7 +75,7 @@ INSERT INTO foo(f1) VALUES(42); If the cast is marked AS IMPLICIT then it can be invoked implicitly in any context, whether assignment or internally in an expression. For example, since || takes text - arguments, + operands, SELECT 'The time is ' || now(); @@ -106,14 +106,16 @@ SELECT 'The time is ' || CAST(now() AS text); To be able to create a cast, you must own the source or the target - data type. To create a binary-compatible cast, you must be superuser - (this restriction is made because an erroneous binary-compatible cast - conversion can easily crash the server). + data type. To create a binary-compatible cast, you must be superuser. + (This restriction is made because an erroneous binary-compatible cast + conversion can easily crash the server.) + - - Parameters + + Parameters + sourcetype @@ -183,6 +185,21 @@ SELECT 'The time is ' || CAST(now() AS text); + + Diagnostics + + + + CREATE CAST + + + Message returned if the cast was successfully created. + + + + + + Notes @@ -201,8 +218,8 @@ SELECT 'The time is ' || CAST(now() AS text); argument of a different type was automatically a cast function. This convention has been abandoned in face of the introduction of schemas and to be able to represent binary compatible casts in the - catalogs. (The built-in cast functions still follow this naming - scheme, but they have to be shown as casts in pg_cast + system catalogs. (The built-in cast functions still follow this naming + scheme, but they have to be shown as casts in the system catalog pg_cast now.) @@ -227,7 +244,7 @@ CREATE CAST (text AS int4) WITH FUNCTION int4(text); The CREATE CAST command conforms to SQL99, - except that SQL99 does not make provisions for binary compatible + except that SQL99 does not make provisions for binary-compatible types. AS IMPLICIT is a PostgreSQL extension, too. diff --git a/doc/src/sgml/ref/create_constraint.sgml b/doc/src/sgml/ref/create_constraint.sgml index 0e86c93f01e..b61cd5d911f 100644 --- a/doc/src/sgml/ref/create_constraint.sgml +++ b/doc/src/sgml/ref/create_constraint.sgml @@ -1,5 +1,5 @@ @@ -8,30 +8,35 @@ PostgreSQL documentation CREATE CONSTRAINT TRIGGER SQL - Language Statements + - - CREATE CONSTRAINT TRIGGER - - - define a new constraint trigger - + CREATE CONSTRAINT TRIGGER + define a new constraint trigger + - - 2000-04-13 - - + CREATE CONSTRAINT TRIGGER name AFTER events ON - relation constraint attributes - FOR EACH ROW EXECUTE PROCEDURE func '(' args ')' - + table constraint attributes + FOR EACH ROW EXECUTE PROCEDURE func ( args ) + + + + + Description + + + CREATE CONSTRAINT TRIGGER is used within + CREATE TABLE/ALTER TABLE and by + pg_dump to create the special triggers for + referential integrity. + It is not intended for general use. + + - - - Inputs - - + + Parameters @@ -53,10 +58,10 @@ CREATE CONSTRAINT TRIGGER name - relation + table - The name (possibly schema-qualified) of the relation in which + The name (possibly schema-qualified) of the table in which the triggering events occur. @@ -75,7 +80,7 @@ CREATE CONSTRAINT TRIGGER name attributes - Constraint attributes. + The constraint attributes. @@ -84,25 +89,19 @@ CREATE CONSTRAINT TRIGGER name func(args) - Function to call as part of the trigger processing. + The function to call as part of the trigger processing. - - + - - - Outputs - - + + Diagnostics - -CREATE TRIGGER - + CREATE TRIGGER Message returned if successful. @@ -110,24 +109,6 @@ CREATE TRIGGER - - - - - - - Description - - - CREATE CONSTRAINT TRIGGER is used within - CREATE/ALTER TABLE and by - pg_dump to create the special triggers for - referential integrity. - - - - It is not intended for general use. - diff --git a/doc/src/sgml/ref/create_conversion.sgml b/doc/src/sgml/ref/create_conversion.sgml index f0706585084..a8811968494 100644 --- a/doc/src/sgml/ref/create_conversion.sgml +++ b/doc/src/sgml/ref/create_conversion.sgml @@ -1,4 +1,4 @@ - + @@ -23,21 +23,24 @@ CREATE [DEFAULT] CONVERSION conversion_name CREATE CONVERSION defines a new encoding - conversion. Conversion names may be used in the CONVERT() function + conversion. Conversion names may be used in the convert function to specify a particular encoding conversion. Also, conversions that - are marked DEFAULT can be used for automatic encoding conversion between - frontend and backend. For this purpose, two conversions, from encoding A to - B AND from encoding B to A, must be defined. + are marked DEFAULT can be used for automatic encoding conversion between + client and server. For this purpose, two conversions, from encoding A to + B and from encoding B to A, must be defined. - To be able to create a conversion, you must have the execute right - on the function and the create right on the destination schema. + To be able to create a conversion, you must have EXECUTE privilege + on the function and CREATE privilege on the destination schema. + - - Parameters + + Parameters + + DEFAULT @@ -75,7 +78,7 @@ CREATE [DEFAULT] CONVERSION conversion_name - source_encoding + dest_encoding @@ -92,25 +95,39 @@ CREATE [DEFAULT] CONVERSION conversion_name The function used to perform the conversion. The function name may be schema-qualified. If it is not, the function will be looked up in the path. - + - + The function must have the following signature: - - conv_proc( - INTEGER, -- source encoding id - INTEGER, -- destination encoding id - CSTRING, -- source string (null terminated C string) - CSTRING, -- destination string (null terminated C string) - INTEGER -- source string length - ) returns VOID; - + +conv_proc( + integer, -- source encoding ID + integer, -- destination encoding ID + cstring, -- source string (null terminated C string) + cstring, -- destination string (null terminated C string) + integer -- source string length +) RETURNS void; + + + + Diagnostics + + + + CREATE CONVERSION + + + Message returned if the conversion was successfully created. + + + + @@ -124,15 +141,14 @@ CREATE [DEFAULT] CONVERSION conversion_name The privileges required to create a conversion may be changed in a future release. - - Examples - To create a conversion from encoding UNICODE to LATIN1 using myfunc: + To create a conversion from encoding UNICODE to + LATIN1 using myfunc: CREATE CONVERSION myconv FOR 'UNICODE' TO 'LATIN1' FROM myfunc; @@ -147,7 +163,7 @@ CREATE CONVERSION myconv FOR 'UNICODE' TO 'LATIN1' FROM myfunc; CREATE CONVERSION is a PostgreSQL extension. There is no CREATE CONVERSION - statement in SQL99. + statement in the SQL standard. diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml index 29e49d850e1..ad0008de403 100644 --- a/doc/src/sgml/ref/create_database.sgml +++ b/doc/src/sgml/ref/create_database.sgml @@ -1,5 +1,5 @@ @@ -8,34 +8,81 @@ PostgreSQL documentation CREATE DATABASE SQL - Language Statements + - - CREATE DATABASE - - - create a new database - + CREATE DATABASE + create a new database + - - 1999-12-11 - - + CREATE DATABASE name [ [ WITH ] [ OWNER [=] dbowner ] [ LOCATION [=] 'dbpath' ] [ TEMPLATE [=] template ] [ ENCODING [=] encoding ] ] - - - - - 1999-12-11 - - - Inputs - - + + + + + Description + + + CREATE DATABASE creates a new + PostgreSQL database. + + + + Normally, the creator becomes the owner of the new database. + Superusers can create databases owned by other users using the + OWNER clause. They can even create databases owned by + users with no special privileges. Non-superusers with CREATEDB + privilege can only create databases owned by themselves. + + + + An alternative location can be specified in order to, + for example, store the database on a different disk. + The path must have been prepared with the + + command. + + + + If the path name does not contain a slash, it is interpreted + as an environment variable name, which must be known to the + server process. This way the database administrator can + exercise control over locations in which databases can be created. + (A customary choice is, e.g., PGDATA2.) + If the server is compiled with ALLOW_ABSOLUTE_DBPATHS + (not so by default), absolute path names, as identified by + a leading slash + (e.g., /usr/local/pgsql/data), + are allowed as well. + + + + By default, the new database will be created by cloning the standard + system database template1. A different template can be + specified by writing TEMPLATE + name. In particular, + by writing TEMPLATE template0, you can create a virgin + database containing only the standard objects predefined by your + version of PostgreSQL. This is useful + if you wish to avoid copying + any installation-local objects that may have been added to + template1. + + + + The optional encoding parameter allows selection of the database + encoding. When not specified, it defaults to the encoding used by + the selected template database. + + + + + Parameter @@ -50,7 +97,7 @@ CREATE DATABASE name dbowner - Name of the database user who will own the new database, + The name of the database user who will own the new database, or DEFAULT to use the default (namely, the user executing the command). @@ -70,7 +117,7 @@ CREATE DATABASE name template - Name of template from which to create the new database, + The name of the template from which to create the new database, or DEFAULT to use the default template (template1). @@ -80,32 +127,30 @@ CREATE DATABASE name encoding - Multibyte encoding method to use in the new database. Specify - a string literal name (e.g., 'SQL_ASCII'), + Character set encoding to use in the new database. Specify + a string constant (e.g., 'SQL_ASCII'), or an integer encoding number, or DEFAULT to use the default encoding. - - - - - - 1999-12-11 - - - Outputs - - + + + Optional parameters can be written in any order, not only the order + illustrated above. + + + + + Diagnostics CREATE DATABASE - Message returned if the command completes successfully. + Message returned if the database was successfully created. @@ -125,8 +170,8 @@ CREATE DATABASE name ERROR: createdb: database "name" already exists - This occurs if a database with the name - specified already exists. + This occurs if a database with the specified name already + exists. @@ -166,186 +211,73 @@ CREATE DATABASE name - - - - - - - 1999-12-11 - - - Description - - - CREATE DATABASE creates a new - PostgreSQL database. - - - - Normally, the creator becomes the owner of the new database. - Superusers can create databases owned by other users using the - - - - An alternate location can be specified in order to, - for example, store the database on a different disk. - The path must have been prepared with the - - command. - - - If the path name does not contain a slash, it is interpreted - as an environment variable name, which must be known to the - server process. This way the database administrator can - exercise control over locations in which databases can be created. - (A customary choice is, e.g., PGDATA2.) - If the server is compiled with ALLOW_ABSOLUTE_DBPATHS - (not so by default), absolute path names, as identified by - a leading slash - (e.g., /usr/local/pgsql/data), - are allowed as well. - - - - By default, the new database will be created by cloning the standard - system database template1. A different template can be - specified by writing TEMPLATE = - name. In particular, - by writing TEMPLATE = template0, you can create a virgin - database containing only the standard objects predefined by your - version of PostgreSQL. This is useful - if you wish to avoid copying - any installation-local objects that may have been added to - template1. - - - - The optional encoding parameter allows selection of the database - encoding. When not specified, it defaults to the encoding used by - the selected template database. - + - - Optional parameters can be written in any order, not only the order - illustrated above. - + + Notes - - - 1999-12-11 - - - Notes - - - CREATE DATABASE is a PostgreSQL - language extension. - Use to remove a database. - - The program is a - shell script wrapper around this command, provided for convenience. - - There are security and data integrity issues - involved with using alternate database locations - specified with absolute path names, and by default - only an environment variable known to the backend may be - specified for an alternate location. - See for more information. + The program is a + wrapper program around this command, provided for convenience. - + + There are security issues involved with using alternate database + locations specified with absolute path names. See for more information. + Although it is possible to copy a database other than template1 by specifying its name as the template, this is not (yet) intended as - a general-purpose COPY DATABASE facility. + a general-purpose COPY DATABASE facility. We recommend that databases used as templates be treated as read-only. - See for more information. + See for more information. - - - - Usage - + + Examples + To create a new database: - -olly=> create database lusiadas; - + +CREATE DATABASE lusiadas; + - To create a new database in an alternate area ~/private_db: - - -$ mkdir private_db -$ initlocation ~/private_db - -The location will be initialized with username "olly". -This user will own all the files and must also own the server process. -Creating directory /home/olly/private_db -Creating directory /home/olly/private_db/base - -initlocation is complete. - - -$ psql olly - -Welcome to psql, the PostgreSQL interactive terminal. - -Type: \copyright for distribution terms - \h for help with SQL commands - \? for help on internal slash commands - \g or terminate with semicolon to execute query - \q to quit - -olly=> CREATE DATABASE elsewhere WITH LOCATION = '/home/olly/private_db'; -CREATE DATABASE - + To create a new database in an alternate area + ~/private_db, execute the following from the + shell: + + +mkdir private_db +initlocation ~/private_db + + + Then execute the following from within a + psql session: + + +CREATE DATABASE elsewhere WITH LOCATION '/home/olly/private_db'; + - - - Compatibility - - - - - 1998-04-15 - - - SQL92 - - - There is no CREATE DATABASE statement in SQL92. - Databases are equivalent to catalogs, whose creation is - implementation-defined. - - + + Compatibility + + + There is no CREATE DATABASE statement in the SQL + standard. Databases are equivalent to catalogs, whose creation is + implementation-defined. + diff --git a/doc/src/sgml/ref/create_domain.sgml b/doc/src/sgml/ref/create_domain.sgml index adcd81303f8..0d6fe6ef50a 100644 --- a/doc/src/sgml/ref/create_domain.sgml +++ b/doc/src/sgml/ref/create_domain.sgml @@ -1,28 +1,21 @@ - - CREATE DOMAIN - + CREATE DOMAIN SQL - Language Statements + - - CREATE DOMAIN - - - define a new domain - + CREATE DOMAIN + define a new domain + - - 2002-02-24 - - + CREATE DOMAIN domainname [AS] data_type [ DEFAULT default_expr ] [ constraint [ ... ] ] @@ -31,16 +24,35 @@ where constraint is: [ CONSTRAINT constraint_name ] { NOT NULL | NULL | CHECK (expression) } - + + - - - 2002-02-24 - - - Parameters - - + + Description + + + CREATE DOMAIN creates a new data domain. The + user who defines a domain becomes its owner. + + + + If a schema name is given (for example, CREATE DOMAIN + myschema.mydomain ...) then the domain is created in the + specified schema. Otherwise it is created in the current schema. + The domain name must be unique among the types and domains existing + in its schema. + + + + Domains are useful for abstracting common fields between tables into + a single location for maintenance. For example, an email address column may be used + in several tables, all with the same properties. Define a domain and + use that rather than setting up each table's constraints individually. + + + + + Parameters @@ -63,32 +75,26 @@ where constraint is: - DEFAULT - default_expr + DEFAULT default_expr + The DEFAULT clause specifies a default value for - columns of the domain data type. The value - is any variable-free expression (but subselects are not allowed). - The - data type of the default expression must match the data type of the - domain. + columns of the domain data type. The value is any + variable-free expression (but subqueries are not allowed). + The data type of the default expression must match the data + type of the domain. If no default value is specified, then + the default value is the null value. - The default expression will be used in any insert operation that - does not specify a value for the column. If there is no default - for a domain, then the default is NULL. + The default expression will be used in any insert operation + that does not specify a value for the column. If a default + value is defined for a particular column, it overrides any + default associated with the domain. In turn, the domain + default overrides any default value associated with the + underlying data type. - - - - If a default value is specified for a particular column, it - overrides any default associated with the domain. In turn, - the domain default overrides any default value associated with - the underlying data type. - - @@ -106,7 +112,7 @@ where constraint is: NOT NULL - Values of this domain are not allowed to be NULL. + Values of this domain are not allowed to be null. @@ -115,12 +121,12 @@ where constraint is: NULL - Values of this domain are allowed to be NULL. This is the default. + Values of this domain are allowed to be null. This is the default. - This clause is only available for compatibility with - non-standard SQL databases. Its use is discouraged in new + This clause is only intended for compatibility with + nonstandard SQL databases. Its use is discouraged in new applications. @@ -139,84 +145,50 @@ where constraint is: Currently, CHECK expressions cannot contain - subselects nor refer to variables other than VALUE. + subqueries nor refer to variables other than VALUE. - + + - - - - - - - 2002-02-24 - - - Outputs - - - - - - -CREATE DOMAIN - - - - Message returned if the domain is successfully created. - - - - - - - - - - - 2002-02-24 - - - Description - - - - CREATE DOMAIN allows the user to register a new - data domain with PostgreSQL for use in the - current data base. The user who defines a domain becomes its owner. - - - - If a schema name is given (for example, CREATE DOMAIN - myschema.mydomain ...) then the domain is created in the - specified schema. Otherwise it is created in the current schema (the one - at the front of the search path; see CURRENT_SCHEMA()). - The domain name must be unique among the types and domains existing - in its schema. - + + Diagnostics - - Domains are useful for abstracting common fields between tables into - a single location for maintenance. An email address column may be used - in several tables, all with the same properties. Define a domain and - use that rather than setting up each table's constraints individually. - + + + CREATE DOMAIN + + + Message returned if the domain was successfully created. + + + + Examples + This example creates the country_code data type and then uses the type in a table definition: CREATE DOMAIN country_code char(2) NOT NULL; -CREATE TABLE countrylist (id INT4, country country_code); +CREATE TABLE countrylist (id integer, country country_code); + + Compatibility + + + The command CREATE DOMAIN conforms to the SQL + standard. + + + See Also diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index 5db8f8efb1d..c4b26ac3b4e 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -1,5 +1,5 @@ @@ -37,26 +37,49 @@ CREATE [ OR REPLACE ] FUNCTION name new function, or replace an existing definition. + + If a schema name is included, then the function is created in the + specified schema. Otherwise it is created in the current schema. + The name of the new function must not match any existing function + with the same argument types in the same schema. However, + functions of different argument types may share a name (this is + called overloading). + + + + To update the definition of an existing function, use + CREATE OR REPLACE FUNCTION. It is not possible + to change the name or argument types of a function this way (if you + tried, you'd just be creating a new, distinct function). Also, + CREATE OR REPLACE FUNCTION will not let you + change the return type of an existing function. To do that, you + must drop and recreate the function. + + + + 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 + CREATE OR REPLACE FUNCTION to change a function + definition without breaking objects that refer to the function. + + The user that creates the function becomes the owner of the function. + + + + Parameters - Parameters name - The name of a function to create. If a schema name is included, - then the function is created in the - specified schema. Otherwise it is created in the current schema (the - one at the front of the search path; see CURRENT_SCHEMA()). - The name of the new function must not match any existing function - with the same argument types in the same schema. However, functions of - different argument types may share a name (this is called - overloading). + The name of a function to create. @@ -67,20 +90,21 @@ CREATE [ OR REPLACE ] FUNCTION name The data type(s) of the function's arguments (optionally - schema-qualified), if any. The input types may be base, complex, or - domain types, or the same as the type of an existing column. + schema-qualified), if any. The argument types may be base, complex, or + domain types, or copy the type of an existing column. - The type of a column is referenced by writing tablename.columnname%TYPE; + class="parameter">columnname%TYPE; using this can sometimes help make a function independent from changes to the definition of a table. Depending on the implementation language it may also be allowed - to specify pseudo-types such as cstring. - Pseudo-types indicate that the actual argument type is either + to specify pseudotypes such as cstring. + Pseudotypes indicate that the actual argument type is either incompletely specified, or outside the set of ordinary SQL data types. @@ -92,15 +116,15 @@ CREATE [ OR REPLACE ] FUNCTION name The return data type (optionally schema-qualified). The return type - may be specified as a base, complex, domain type - or the same as the type of an existing column. See the description + may be specified as a base, complex, or domain type, + or may copy the type of an existing column. See the description under argtype above on how to reference the type of an existing column. Depending on the implementation language it may also be allowed - to specify pseudo-types such as cstring. - The setof + to specify pseudotypes such as cstring. + The SETOF modifier indicates that the function will return a set of items, rather than a single item. @@ -123,9 +147,9 @@ CREATE [ OR REPLACE ] FUNCTION name - IMMUTABLE - STABLE - VOLATILE + IMMUTABLE + STABLE + VOLATILE @@ -140,7 +164,7 @@ CREATE [ OR REPLACE ] FUNCTION name IMMUTABLE indicates that the function always returns the same result when given the same argument values; that is, it does not do database lookups or otherwise use information not - directly present in its parameter list. If this option is given, + directly present in its argument list. If this option is given, any call of the function with all-constant arguments can be immediately replaced with the function value. @@ -152,7 +176,7 @@ CREATE [ OR REPLACE ] FUNCTION name result could change across SQL statements. This is the appropriate selection for functions whose results depend on database lookups, parameter variables (such as the current time zone), etc. Also note - that the CURRENT_TIMESTAMP family of functions qualify + that the current_timestamp family of functions qualify as stable, since their values do not change within a transaction. @@ -170,9 +194,9 @@ CREATE [ OR REPLACE ] FUNCTION name - CALLED ON NULL INPUT - RETURNS NULL ON NULL INPUT - STRICT + CALLED ON NULL INPUT + RETURNS NULL ON NULL INPUT + STRICT @@ -186,17 +210,17 @@ CREATE [ OR REPLACE ] FUNCTION name RETURNS NULL ON NULL INPUT or STRICT indicates that the function always - returns NULL whenever any of its arguments are NULL. If this + returns null whenever any of its arguments are null. If this parameter is specified, the function is not executed when there - are NULL arguments; instead a NULL result is assumed + are null arguments; instead a null result is assumed automatically. - EXTERNAL SECURITY INVOKER - EXTERNAL SECURITY DEFINER + EXTERNAL SECURITY INVOKER + EXTERNAL SECURITY DEFINER @@ -209,7 +233,7 @@ CREATE [ OR REPLACE ] FUNCTION name The key word EXTERNAL is present for SQL - compatibility but is optional since, unlike in SQL, this feature + conformance but is optional since, unlike in SQL, this feature does not only apply to external functions. @@ -222,25 +246,26 @@ CREATE [ OR REPLACE ] FUNCTION name A string defining the function; the meaning depends on the language. It may be an internal function name, the path to an - object file, an SQL query, or text in a procedural language. + object file, an SQL command, or text in a procedural language. - obj_file, link_symbol + obj_file, link_symbol This form of the AS clause is used for - dynamically linked C language functions when the function name + dynamically loadable C language functions when the function name in the C language source code is not the same as the name of the SQL function. The string obj_file is the name of the file containing the dynamically loadable object, and link_symbol is the - object's link symbol, that is, the name of the function in the C - language source code. + function's link symbol, that is, the name of the function in the C + language source code. If the link symbol is omitted, it is assumed + to be the same as the name of the SQL function being defined. @@ -285,48 +310,47 @@ CREATE [ OR REPLACE ] FUNCTION name + + Diagnostics + + + + CREATE FUNCTION + + + Message returned if the function was successfully created. + + + + + + Notes Refer to for further information on writing - external functions. + functions. The full SQL type syntax is allowed for input arguments and return value. However, some details of the type specification (e.g., the precision field for - numeric types) are the responsibility of the + type numeric) are the responsibility of the underlying function implementation and are silently swallowed (i.e., not recognized or enforced) by the CREATE FUNCTION command. - PostgreSQL allows function overloading; - that is, the same name can be used for several different functions - so long as they have distinct argument types. This facility must - be used with caution for internal and C-language functions, however. - - - - Two internal - functions cannot have the same C name without causing - errors at link time. To get around that, give them different C names - (for example, use the argument types as part of the C names), then - specify those names in the AS clause of CREATE FUNCTION. - If the AS clause is left empty, then CREATE FUNCTION - assumes the C name of the function is the same as the SQL name. - - - - Similarly, when overloading SQL function names with multiple C-language - functions, give - each C-language instance of the function a distinct name, then use - the alternative form of the AS clause in the - CREATE FUNCTION syntax to select the appropriate - C-language implementation of each overloaded SQL function. + PostgreSQL allows function + overloading; that is, the same name can be + used for several different functions so long as they have distinct + argument types. However, the C names of all functions must be + different, so you must give overloaded C functions different C + names (for example, use the argument types as part of the C + names). @@ -341,116 +365,26 @@ CREATE [ OR REPLACE ] FUNCTION name to remove user-defined functions. - - To update the definition of an existing function, use - CREATE OR REPLACE FUNCTION. Note that it is - not possible to change the name or argument types of a function - this way (if you tried, you'd just be creating a new, distinct - function). Also, CREATE OR REPLACE FUNCTION - will not let you change the return type of an existing function. - To do that, you must drop and re-create the function. - - - - If you drop and then re-create 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 - CREATE OR REPLACE FUNCTION to change a function - definition without breaking objects that refer to the function. - - To be able to define a function, the user must have the USAGE privilege on the language. - - - By default, only the owner (creator) of the function has the right - to execute it. Other users must be granted the - EXECUTE privilege on the function to be able to - use it. - Examples - To create a simple SQL function: - - -CREATE FUNCTION one() RETURNS integer - AS 'SELECT 1 AS RESULT;' - LANGUAGE SQL; - -SELECT one() AS answer; - - answer --------- - 1 - - - - - - The next example creates a C function by calling a routine from a - user-created shared library named funcs.so (the extension - may vary across platforms). The shared library file is sought in the - server's dynamic library search path. This particular routine calculates - a check digit and returns true if the check digit in the function - parameters is correct. It is intended for use in a CHECK - constraint. - + Here is a trivial example to help you get startet. For more + information and examples, see . -CREATE FUNCTION ean_checkdigit(char, char) RETURNS boolean - AS 'funcs' LANGUAGE C; - -CREATE TABLE product ( - id char(8) PRIMARY KEY, - eanprefix char(8) CHECK (eanprefix ~ '[0-9]{2}-[0-9]{5}') - REFERENCES brandname(ean_prefix), - eancode char(6) CHECK (eancode ~ '[0-9]{6}'), - CONSTRAINT ean CHECK (ean_checkdigit(eanprefix, eancode)) -); +CREATE FUNCTION add(integer, integer) RETURNS integer + AS 'select $1 + $2;' + LANGUAGE SQL + IMMUTABLE + RETURNS NULL ON NULL INPUT; - - - The next example creates a function that does type conversion from the - user-defined type complex to the built-in type point. The - function is implemented by a dynamically loaded object that was - compiled from C source (we illustrate the now-deprecated alternative - of specifying the absolute file name to the shared object file). - For PostgreSQL to - find a type conversion function automatically, the SQL function has - to have the same name as the return type, and so overloading is - unavoidable. The function name is overloaded by using the second - form of the AS clause in the SQL definition: - - -CREATE FUNCTION point(complex) RETURNS point - AS '/home/bernie/pgsql/lib/complex.so', 'complex_to_point' - LANGUAGE C STRICT; - - - The C declaration of the function could be: - - -Point * complex_to_point (Complex *z) -{ - Point *p; - - p = (Point *) palloc(sizeof(Point)); - p->x = z->x; - p->y = z->y; - - return p; -} - - - Note that the function is marked strict; this allows us - to skip checking for NULL input in the function body. - diff --git a/doc/src/sgml/ref/create_group.sgml b/doc/src/sgml/ref/create_group.sgml index 4be903241f0..d90dd31086a 100644 --- a/doc/src/sgml/ref/create_group.sgml +++ b/doc/src/sgml/ref/create_group.sgml @@ -1,5 +1,5 @@ @@ -8,35 +8,41 @@ PostgreSQL documentation CREATE GROUP SQL - Language Statements + - - CREATE GROUP - - - define a new user group - + CREATE GROUP + define a new user group + - - 2000-01-14 - - + CREATE GROUP name [ [ WITH ] option [ ... ] ] where option can be: SYSID gid | USER username [, ...] - + + + + + Description + + + CREATE GROUP will create a new group in the + database cluster. You must be a database + superuser to use this command. + + + + Use + to change a group's membership, and to remove a group. + + - - - 2000-01-14 - - - Inputs - - + + Parameters @@ -53,11 +59,11 @@ where option can be: The SYSID clause can be used to choose - the PostgreSQL group id of the new + the PostgreSQL group ID of the new group. It is not necessary to do so, however. - If this is not specified, the highest assigned group id plus one, + If this is not specified, the highest assigned group ID plus one, starting at 1, will be used as default. @@ -73,54 +79,26 @@ where option can be: - - + - - - 2000-01-14 - - - Outputs - - - + + Diagnostics + CREATE GROUP - Message returned if the command completes successfully. + Message returned if the group was successfully created. - - - + + + + Examples - - - 2000-01-14 - - - Description - - - CREATE GROUP will create a new group in the - database installation. You must be a database - superuser to use this command. - - - Use - to change a group's membership, and to remove a group. - - - - - Usage - Create an empty group: @@ -136,24 +114,13 @@ CREATE GROUP marketing WITH USER jonathan, david; - - - Compatibility - + + Compatibility - - - 2000-01-14 - - - SQL92 - - - - There is no CREATE GROUP statement in SQL92. - Roles are similar in concept to groups. - - + + There is no CREATE GROUP statement in the SQL + standard. Roles are similar in concept to groups. + diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index bfca7dfee82..2c6a10ce3df 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -1,5 +1,5 @@ @@ -8,39 +8,101 @@ PostgreSQL documentation CREATE INDEX SQL - Language Statements + - - CREATE INDEX - - - define a new index - + CREATE INDEX + define a new index + - - 2001-07-15 - - + CREATE [ UNIQUE ] INDEX index_name ON table - [ USING acc_method ] ( column [ ops_name ] [, ...] ) + [ USING method ] ( column [ ops_name ] [, ...] ) [ WHERE predicate ] + CREATE [ UNIQUE ] INDEX index_name ON table - [ USING acc_method ] ( func_name( column [, ... ]) [ ops_name ] ) + [ USING method ] ( func_name( column [, ... ]) [ ops_name ] ) [ WHERE predicate ] - + + + + + Description + + + CREATE INDEX constructs an index index_name on the specified table. + Indexes are primarily used to enhance database performance. But + inappropriate use will result in slower performance. + + + + In the first syntax shown above, the key field(s) for the + index are specified as column names. + Multiple fields can be specified if the index method supports + multicolumn indexes. + + + + In the second syntax shown above, an index is defined on the result + of a user-specified function func_name applied to one or more + columns of a single table. These functional + indexes can be used to obtain fast access to data based + on operators that would normally require some transformation to apply + them to the base data. For example, a functional index on + upper(col) would allow the clause + WHERE upper(col) = 'JIM' to use an index. + + + + PostgreSQL provides the index methods + B-tree, R-tree, hash, and GiST. The B-tree index method is an + implementation of Lehman-Yao high-concurrency B-trees. The R-tree + index method implements standard R-trees using Guttman's quadratic + split algorithm. The hash index method is an implementation of + Litwin's linear hashing. Users can also define their own index + methods, but that is fairly complicated. + + + + When the WHERE clause is present, a + partial index is created. + A partial index is an index that contains entries for only a portion of + a table, usually a portion that is somehow more interesting than the + rest of the table. For example, if you have a table that contains both + billed and unbilled orders where the unbilled orders take up a small + fraction of the total table and yet that is an often used section, you + can improve performance by creating an index on just that portion. + Another possible application is to use WHERE with + UNIQUE to enforce uniqueness over a subset of a + table. + - - - 1998-09-09 - - - Inputs - - + + The expression used in the WHERE clause may refer + only to columns of the underlying table (but it can use all columns, + not only the one(s) being indexed). Presently, subqueries and + aggregate expressions are also forbidden in WHERE. + + + + All functions and operators used in an index definition must be + immutable, that is, their results must depend only on + their arguments and never on any outside influence (such as + the contents of another table or the current time). This restriction + ensures that the behavior of the index is well-defined. To use a + user-defined function in an index, remember to mark the function immutable + when you create it. + + + + + Parameters - UNIQUE + UNIQUE Causes the system to check for @@ -73,52 +135,13 @@ CREATE [ UNIQUE ] INDEX index_name - acc_method + method - The name of the access method to be used for the index. The - default access method is BTREE. - PostgreSQL provides four access - methods for indexes: - - - - BTREE - - - an implementation of Lehman-Yao - high-concurrency B-trees. - - - - - - RTREE - - implements standard R-trees using Guttman's - quadratic split algorithm. - - - - - - HASH - - - an implementation of Litwin's linear hashing. - - - - - - GIST - - - Generalized Index Search Trees. - - - - + The name of the method to be used for the index. Choices are + btree, hash, + rtree, and gist. The + default method is btree. @@ -159,249 +182,73 @@ CREATE [ UNIQUE ] INDEX index_name - - - - - - 1998-09-09 - - - Outputs - - - - - - -CREATE INDEX - - - - The message returned if the index is successfully created. - - - - - - -ERROR: Cannot create index: 'index_name' already exists. - - - - This error occurs if it is impossible to create the index. - - - - - - - - - - - 1998-09-09 - - - Description - - - CREATE INDEX constructs an index - index_name - on the specified table. - - - - Indexes are primarily used to enhance database performance. - But inappropriate use will result in slower performance. - - - - - - In the first syntax shown above, the key field(s) for the - index are specified as column names. - Multiple fields can be specified if the index access method supports - multicolumn indexes. - - - - In the second syntax shown above, an index is defined on the result - of a user-specified function func_name applied to one or more - columns of a single table. These functional - indexes can be used to obtain fast access to data based - on operators that would normally require some transformation to apply - them to the base data. For example, a functional index on - upper(col) would allow the clause - WHERE upper(col) = 'JIM' to use an index. - + - - PostgreSQL provides B-tree, R-tree, hash, - and GiST access methods for indexes. The B-tree access method is an - implementation of Lehman-Yao high-concurrency B-trees. The R-tree - access method implements standard R-trees using Guttman's quadratic - split algorithm. The hash access method is an implementation of - Litwin's linear hashing. We mention the algorithms used solely to - indicate that all of these access methods are fully dynamic and do - not have to be optimized periodically (as is the case with, for - example, static hash access methods). - + + Diagnostics - - When the WHERE clause is present, a - partial index is created. - A partial index is an index that contains entries for only a portion of - a table, usually a portion that is somehow more interesting than the - rest of the table. For example, if you have a table that contains both - billed and unbilled orders where the unbilled orders take up a small - fraction of the total table and yet that is an often used section, you - can improve performance by creating an index on just that portion. - Another possible application is to use WHERE with - UNIQUE to enforce uniqueness over a subset of a - table. - + + + CREATE INDEX + + + Message returned if the index was successfully created. + + + + + - - The expression used in the WHERE clause may refer - only to columns of the underlying table (but it can use all columns, - not only the one(s) being indexed). Presently, subqueries and - aggregate expressions are also forbidden in WHERE. - + + Notes - All functions and operators used in an index definition must be - immutable, that is, their results must depend only on - their input arguments and never on any outside influence (such as - the contents of another table or the current time). This restriction - ensures that the behavior of the index is well-defined. To use a - user-defined function in an index, remember to mark the function immutable - when you create it. + See for information about when indexes can + be used, when they are not used, and in which particular situations + can be useful. - Use - to remove an index. + Currently, only the B-tree and GiST index methods support + multicolumn indexes. Up to 32 fields may be specified by default. + (This limit can be altered when building + PostgreSQL.) Only B-tree currently + supports unique indexes. - - - 1998-09-09 - - - Notes - - - - The PostgreSQL - query optimizer will consider using a B-tree index whenever - an indexed attribute is involved in a comparison using one of: - - - < - <= - = - >= - > - - - - - The PostgreSQL - query optimizer will consider using an R-tree index whenever - an indexed attribute is involved in a comparison using one of: - - - << - &< - &> - >> - @ - ~= - && - - - - - The PostgreSQL - query optimizer will consider using a hash index whenever - an indexed attribute is involved in a comparison using - the = operator. - - - Testing has shown PostgreSQL's hash indexes to be similar or slower - than B-tree indexes, and the index size and build time for hash - indexes is much worse. Hash indexes also suffer poor performance - under high concurrency. For these reasons, hash index use is - discouraged. - - - - Currently, only the B-tree and gist access methods support multicolumn - indexes. Up to 32 keys may be specified by default (this limit - can be altered when building - PostgreSQL). Only B-tree currently supports - unique indexes. - - An operator class can be specified for each column of an index. The operator class identifies the operators to be used by the index for that column. For example, a B-tree index on four-byte integers would use the int4_ops class; this operator class includes comparison functions for four-byte - integers. In practice the default operator class for the field's data + integers. In practice the default operator class for the column's data type is usually sufficient. The main point of having operator classes is that for some data types, there could be more than one meaningful ordering. For example, we might want to sort a complex-number data type either by absolute value or by real part. We could do this by defining two operator classes for the data type and then selecting - the proper class when making an index. There are also some operator - classes with special purposes: - - - - - The operator classes box_ops and - bigbox_ops both support R-tree indexes on the - box data type. - The difference between them is that bigbox_ops - scales box coordinates down, to avoid floating-point exceptions from - doing multiplication, addition, and subtraction on very large - floating-point coordinates. (Note: this was true some time ago, - but currently the two operator classes both use floating point - and are effectively identical.) - - - + the proper class when making an index. More information about + operator classes is in and in . - - The following query shows all defined operator classes: - - -SELECT am.amname AS acc_method, - opc.opcname AS ops_name - FROM pg_am am, pg_opclass opc - WHERE opc.opcamid = am.oid - ORDER BY acc_method, ops_name; - - - + + Use + to remove an index. + - - - Usage - - To create a B-tree index on the field title - in the table films: + + Examples + + + To create a B-tree index on the column title in + the table films: + +CREATE UNIQUE INDEX title_idx ON films (title); + - -CREATE UNIQUE INDEX title_idx - ON films (title); - @@ -60,7 +60,7 @@ CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE langna TRUSTED specifies that the call handler for the language is safe, that is, it does not offer an unprivileged user any functionality to bypass access - restrictions. If this keyword is omitted when registering the + restrictions. If this key word is omitted when registering the language, only users with the PostgreSQL superuser privilege can use this language to create new functions. @@ -84,8 +84,8 @@ CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE langna The name of the new procedural language. The language name is - case insensitive. A procedural language cannot override one of - the built-in languages of PostgreSQL. + case insensitive. The name must be unique among the languages + in the database. @@ -146,45 +146,16 @@ CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE langna Diagnostics - - - - - - -CREATE LANGUAGE - - - - - - + + + CREATE LANGUAGE + - This message is returned if the language is successfully - created. + Message returned if the language was successfully created. - - - - - - - - -ERROR: PL handler function funcname() doesn't exist - - - - - - - - This error is returned if the function funcname() is not found. - - - - + + + @@ -194,7 +165,7 @@ ERROR: PL handler function funcnamePostgreSQL distribution, the script should be used, which will also + linkend="app-createlang"> program should be used, which will also install the correct call handler. (createlang will call CREATE LANGUAGE internally.) @@ -205,7 +176,7 @@ ERROR: PL handler function funcnameopaque, rather than language_handler. To support loading of old dump files, CREATE LANGUAGE will accept a function - declared as returning opaque, but it will issue a NOTICE and + declared as returning opaque, but it will issue a notice and change the function's declared return type to language_handler. @@ -216,35 +187,19 @@ ERROR: PL handler function funcname Use , or better yet the script, to drop procedural languages. + linkend="app-droplang"> program, to drop procedural languages. - The system catalog pg_language records - information about the currently installed procedural languages. - - - Table "pg_language" - Attribute | Type | Modifier ----------------+-----------+---------- - lanname | name | - lanispl | boolean | - lanpltrusted | boolean | - lanplcallfoid | oid | - lanvalidator | oid | - lanacl | aclitem[] | - - lanname | lanispl | lanpltrusted | lanplcallfoid | lanvalidator | lanacl --------------+---------+--------------+---------------+--------------+-------- - internal | f | f | 0 | 2246 | - c | f | f | 0 | 2247 | - sql | f | t | 0 | 2248 | {=U} - + The system catalog pg_language (see ) records information about the + currently installed languages. Also createlang + has an option to list the installed languages. - At present, with the exception of the permissions, the definition - of a procedural language cannot be changed once it has been created. + The definition of a procedural language cannot be changed once it + has been created, with the exception of the privileges. @@ -262,7 +217,7 @@ ERROR: PL handler function funcname -CREATE FUNCTION plsample_call_handler () RETURNS language_handler +CREATE FUNCTION plsample_call_handler() RETURNS language_handler AS '$libdir/plsample' LANGUAGE C; CREATE LANGUAGE plsample @@ -280,15 +235,6 @@ CREATE LANGUAGE plsample - - History - - - The CREATE LANGUAGE command first appeared in - PostgreSQL 6.3. - - - See Also diff --git a/doc/src/sgml/ref/create_opclass.sgml b/doc/src/sgml/ref/create_opclass.sgml index dcf8259d4be..0891edc9adf 100644 --- a/doc/src/sgml/ref/create_opclass.sgml +++ b/doc/src/sgml/ref/create_opclass.sgml @@ -1,5 +1,5 @@ @@ -8,206 +8,44 @@ PostgreSQL documentation CREATE OPERATOR CLASS SQL - Language Statements + - - CREATE OPERATOR CLASS - - - define a new operator class for indexes - - + CREATE OPERATOR CLASS + define a new operator class for indexes + + - - 2002-07-28 - - -CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type USING access_method AS - { OPERATOR strategy_number operator_id [ ( type, type ) ] [ RECHECK ] - | FUNCTION support_number func_name ( parameter_types ) + +CREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type USING index_method AS + { OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] [ RECHECK ] + | FUNCTION support_number func_name ( argument_types ) | STORAGE storage_type } [, ... ] - - - - - 2002-07-28 - - - Inputs - - - - - - name - - - The name of the operator class to be created. - The name may be schema-qualified. - - - - - DEFAULT - - - If present, the operator class will become the default index - operator class for its data type. At most one operator class - can be the default for a specific data type and access method. - - - - - data_type - - - The column data type that this operator class is for. - - - - - access_method - - - The name of the index access method this operator class is for. - - - - - strategy_number - - - The index access method's strategy number for an operator associated - with the operator class. - - - - - operator_id - - - The identifier (optionally schema-qualified) of an operator associated - with the operator class. - - - - - type - - - The input data type(s) of an operator, or NONE to - signify a left-unary or right-unary operator. The input data types - may be omitted in the normal case where they are the same as the - operator class's data type. - - - - - RECHECK - - - If present, the index is lossy for this operator, - and so the tuples retrieved using the index must be rechecked - to verify that they actually satisfy the qualification clause - involving this operator. - - - - - support_number - - - The index access method's support procedure number for a function - associated with the operator class. - - - - - func_name - - - The name (optionally schema-qualified) of a function that is - an index access method support procedure for the operator class. - - - - - parameter_types - - - The parameter data type(s) of the function. - - - - - storage_type - - - The data type actually stored in the index. Normally this is the - same as the column data type, but some index access methods (only - GIST at this writing) allow it to be different. The - STORAGE clause must be omitted unless the index access - method allows a different type to be used. - - - - - - - - - - 2002-07-28 - - - Outputs - - - - - -CREATE OPERATOR CLASS - - - - Message returned if the operator class is successfully created. - - - - - - + - - - 2002-07-28 - - - Description - - - CREATE OPERATOR CLASS defines a new operator class, - name. - + + Description + + CREATE OPERATOR CLASS creates a new operator class. An operator class defines how a particular data type can be used with an index. The operator class specifies that certain operators will fill particular roles or strategies for this data type and this - access method. The operator class also specifies the support procedures to + index method. The operator class also specifies the support procedures to be used by - the index access method when the operator class is selected for an + the index method when the operator class is selected for an index column. All the operators and functions used by an operator class must be defined before the operator class is created. If a schema name is given then the operator class is created in the - specified schema. Otherwise it is created in the current schema (the one - at the front of the search path; see CURRENT_SCHEMA()). + specified schema. Otherwise it is created in the current schema. Two operator classes in the same schema can have the same name only if they - are for different index access methods. + are for different index methods. + The user who defines an operator class becomes its owner. Presently, the creating user must be a superuser. (This restriction is made because @@ -217,41 +55,187 @@ CREATE OPERATOR CLASS CREATE OPERATOR CLASS does not presently check - whether the class definition includes all the operators and functions - required by the index access method. It is the user's + whether the operator class definition includes all the operators and functions + required by the index method. It is the user's responsibility to define a valid operator class. Refer to for further information. + + + + Parameters + + + + name + + + The name of the operator class to be created. The name may be + schema-qualified. + + + + + + DEFAULT + + + If present, the operator class will become the default + operator class for its data type. At most one operator class + can be the default for a specific data type and index method. + + + + + + data_type + + + The column data type that this operator class is for. + + + + + + index_method + + + The name of the index method this operator class is for. + + + + + + strategy_number + + + The index method's strategy number for an operator + associated with the operator class. + + + + + + operator_name + + + The name (optionally schema-qualified) of an operator associated + with the operator class. + + + + + + op_type + + + The operand data type(s) of an operator, or NONE to + signify a left-unary or right-unary operator. The operand data + types may be omitted in the normal case where they are the same + as the operator class's data type. + + + + + + RECHECK + + + If present, the index is lossy for this operator, and + so the rows retrieved using the index must be rechecked to + verify that they actually satisfy the qualification clause + involving this operator. + + + + + + support_number + + + The index method's support procedure number for a + function associated with the operator class. + + + + + + func_name + + + The name (optionally schema-qualified) of a function that is an + index method support procedure for the operator class. + + + + + + argument_types + + + The parameter data type(s) of the function. + + + + + + storage_type + + + The data type actually stored in the index. Normally this is + the same as the column data type, but some index methods + (only GiST at this writing) allow it to be different. The + STORAGE clause must be omitted unless the index + method allows a different type to be used. + + + + + + + The OPERATOR, FUNCTION, and STORAGE + clauses may appear in any order. + + + + + Diagnostics + + + + CREATE OPERATOR CLASS + + + Message returned if the operator class was successfully created. + + + + + - - - 2002-07-28 - - - Notes - - - Refer to - - to delete user-defined operator classes from a database. - - + + Notes + + + Refer to + + to delete user-defined operator classes from a database. + - - - Usage - + + Examples + The following example command defines a GiST index operator class - for data type _int4 (array of int4). See + for the data type _int4 (array of int4). See contrib/intarray/ for the complete example. - + CREATE OPERATOR CLASS gist__int_ops DEFAULT FOR TYPE _int4 USING gist AS OPERATOR 3 &&, @@ -266,34 +250,18 @@ CREATE OPERATOR CLASS gist__int_ops FUNCTION 5 g_int_penalty (internal, internal, internal), FUNCTION 6 g_int_picksplit (internal, internal), FUNCTION 7 g_int_same (_int4, _int4, internal); - - - - The OPERATOR, FUNCTION, and STORAGE - clauses may appear in any order. - + - - - Compatibility - - - - - 2002-07-28 - - - SQL92 - + + Compatibility - - CREATE OPERATOR CLASS - is a PostgreSQL extension. - There is no CREATE OPERATOR CLASS - statement in SQL92. - - + + CREATE OPERATOR CLASS is a + PostgreSQL extension. There is no + CREATE OPERATOR CLASS statement in the SQL + standard. + diff --git a/doc/src/sgml/ref/create_operator.sgml b/doc/src/sgml/ref/create_operator.sgml index b3c4d00405c..2ac9d3f2edd 100644 --- a/doc/src/sgml/ref/create_operator.sgml +++ b/doc/src/sgml/ref/create_operator.sgml @@ -1,5 +1,5 @@ @@ -8,49 +8,120 @@ PostgreSQL documentation CREATE OPERATOR SQL - Language Statements + - - CREATE OPERATOR - - - define a new operator - - + CREATE OPERATOR + define a new operator + + - - 2000-03-25 - - -CREATE OPERATOR name ( PROCEDURE = func_name - [, LEFTARG = lefttype - ] [, RIGHTARG = righttype ] - [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ] - [, RESTRICT = res_proc ] [, JOIN = join_proc ] - [, HASHES ] [, MERGES ] - [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ] - [, LTCMP = less_than_op ] [, GTCMP = greater_than_op ] ) - - - - - 2000-03-25 - - - Inputs - - + +CREATE OPERATOR name ( + PROCEDURE = func_name + [, LEFTARG = lefttype ] [, RIGHTARG = righttype ] + [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ] + [, RESTRICT = res_proc ] [, JOIN = join_proc ] + [, HASHES ] [, MERGES ] + [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ] + [, LTCMP = less_than_op ] [, GTCMP = greater_than_op ] +) + + + + + Description + + + CREATE OPERATOR defines a new operator, + name. The user who + defines an operator becomes its owner. If a schema name is given + then the operator is created in the specified schema. Otherwise it + is created in the current schema. + + + + The operator name is a sequence of up to NAMEDATALEN-1 + (63 by default) characters from the following list: + ++ - * / < > = ~ ! @ # % ^ & | ` ? $ + + + There are a few restrictions on your choice of name: + + + + $ cannot be defined as a single-character operator, + although it can be part of a multicharacter operator name. + + + + + -- and /* cannot appear anywhere in an operator name, + since they will be taken as the start of a comment. + + + + + A multicharacter operator name cannot end in + or + -, + unless the name also contains at least one of these characters: + +~ ! @ # % ^ & | ` ? $ + + For example, @- is an allowed operator name, + but *- is not. + This restriction allows PostgreSQL to + parse SQL-compliant commands without requiring spaces between tokens. + + + + + + + The operator != is mapped to + <> on input, so these two names are always + equivalent. + + + + At least one of LEFTARG and RIGHTARG must be defined. For + binary operators, both must be defined. For right unary + operators, only LEFTARG should be defined, while for left + unary operators only RIGHTARG should be defined. + + + + The func_name + procedure must have been previously defined using CREATE + FUNCTION and must be defined to accept the correct number + of arguments (either one or two) of the indicated types. + + + + The other clauses specify optional operator optimization clauses. + Their meaning is detailed in . + + + + + Parameters name - The operator to be defined. See below for allowable characters. - The name may be schema-qualified, for example - CREATE OPERATOR myschema.+ (...). + The name of the operator to be defined. See above for allowable + characters. The name may be schema-qualified, for example + CREATE OPERATOR myschema.+ (...). If not, then + the operator is created in the current schema. Two operators + in the same schema can have the same name if they operate on + different data types. This is called + overloading. + func_name @@ -59,6 +130,7 @@ CREATE OPERATOR name ( PROCEDURE = + lefttype @@ -68,6 +140,7 @@ CREATE OPERATOR name ( PROCEDURE = + righttype @@ -77,6 +150,7 @@ CREATE OPERATOR name ( PROCEDURE = + com_op @@ -85,6 +159,7 @@ CREATE OPERATOR name ( PROCEDURE = + neg_op @@ -93,6 +168,7 @@ CREATE OPERATOR name ( PROCEDURE = + res_proc @@ -101,6 +177,7 @@ CREATE OPERATOR name ( PROCEDURE = + join_proc @@ -109,22 +186,25 @@ CREATE OPERATOR name ( PROCEDURE = + - HASHES + HASHES Indicates this operator can support a hash join. + - MERGES + MERGES Indicates this operator can support a merge join. + left_sort_op @@ -134,6 +214,7 @@ CREATE OPERATOR name ( PROCEDURE = + right_sort_op @@ -143,6 +224,7 @@ CREATE OPERATOR name ( PROCEDURE = + less_than_op @@ -152,6 +234,7 @@ CREATE OPERATOR name ( PROCEDURE = + greater_than_op @@ -162,341 +245,78 @@ CREATE OPERATOR name ( PROCEDURE = - - - - - - 2000-03-25 - - - Outputs - - - - - -CREATE OPERATOR - - - - Message returned if the operator is successfully created. - - - - - - - - - - 2000-03-25 - - - Description - - - CREATE OPERATOR defines a new operator, - name. - The user who defines an operator becomes its owner. - - - If a schema name is given then the operator is created in the - specified schema. Otherwise it is created in the current schema (the one - at the front of the search path; see CURRENT_SCHEMA()). - - Two operators in the same schema can have the same name if they operate on - different data types. This is called overloading. The - system will attempt to pick the intended operator based on the actual - input data types when there is ambiguity. + To give a schema-qualified operator name in com_op or the other optional + arguments, use the OPERATOR() syntax, for example + +COMMUTATOR = OPERATOR(myschema.===) , + + - - The operator name - is a sequence of up to NAMEDATALEN-1 (63 by default) characters - from the following list: - -+ - * / < > = ~ ! @ # % ^ & | ` ? $ - + + Diagnostics - There are a few restrictions on your choice of name: - - - - $ cannot be defined as a single-character operator, - although it can be part of a multicharacter operator name. - - - - - -- and /* cannot appear anywhere in an operator name, - since they will be taken as the start of a comment. - - + + + CREATE OPERATOR - A multicharacter operator name cannot end in + or - -, - unless the name also contains at least one of these characters: - -~ ! @ # % ^ & | ` ? $ - - For example, @- is an allowed operator name, - but *- is not. - This restriction allows PostgreSQL to - parse SQL-compliant queries without requiring spaces between tokens. + Message returned if the operator was successfully created. - + + + + + + Notes - - - When working with non-SQL-standard operator names, you will usually - need to separate adjacent operators with spaces to avoid ambiguity. - For example, if you have defined a left-unary operator named @, - you cannot write X*@Y; you must write - X* @Y to ensure that - PostgreSQL reads it as two operator names - not one. - - - - The operator != is mapped to <> on input, so these two names - are always equivalent. - - - At least one of LEFTARG and RIGHTARG must be defined. For - binary operators, both should be defined. For right unary - operators, only LEFTARG should be defined, while for left - unary operators only RIGHTARG should be defined. - - - The - func_name procedure must have - been previously defined using CREATE FUNCTION and must - be defined to accept the correct number of arguments - (either one or two) of the indicated types. - - - The commutator operator should be identified if one exists, - so that PostgreSQL can - reverse the order of the operands if it wishes. - For example, the operator area-less-than, <<<, - would probably have a commutator - operator, area-greater-than, >>>. - Hence, the query optimizer could freely convert: - - -box '((0,0), (1,1))' >>> MYBOXES.description - - - to - - -MYBOXES.description <<< box '((0,0), (1,1))' - - - - This allows the execution code to always use the latter - representation and simplifies the query optimizer somewhat. - - - Similarly, if there is a negator operator then it should be - identified. - Suppose that an - operator, area-equal, ===, exists, as well as an area not - equal, !==. - The negator link allows the query optimizer to simplify - -NOT MYBOXES.description === box '((0,0), (1,1))' - - to - -MYBOXES.description !== box '((0,0), (1,1))' - - - - If a commutator operator name is supplied, - PostgreSQL - searches for it in the catalog. If it is found and it - does not yet have a commutator itself, then the commutator's - entry is updated to have the newly created operator as its - commutator. This applies to the negator, as well. - This is to allow the definition of two operators that are - the commutators or the negators of each other. The first - operator should be defined without a commutator or negator - (as appropriate). When the second operator is defined, - name the first as the commutator or negator. The first - will be updated as a side effect. (As of - PostgreSQL 6.5, - it also works to just have both operators refer to each other.) - - - The HASHES, MERGES, SORT1, - SORT2, LTCMP, and GTCMP options - are present to support the query optimizer in performing joins. - PostgreSQL can always evaluate a join - (i.e., processing a clause with two tuple variables separated by an - operator that returns a boolean) by iterative - substitution . In addition, - PostgreSQL can use a hash-join algorithm - ; however, it must know whether this - strategy is applicable. The current hash-join algorithm is only - correct for operators that represent equality tests; furthermore, - equality of the data type must mean bitwise equality of the - representation of the type. (For example, a data type that - contains unused bits that don't matter for equality tests could not - be hash-joined.) The HASHES flag indicates to the query optimizer - that a hash join may safely be used with this operator. - - - Similarly, the MERGES flag indicates whether merge-sort - is a usable join strategy for this operator. A merge join requires - that the two input data types have consistent orderings, and that - the merge-join operator behave like equality with respect to that - ordering. For example, it is possible to merge-join equality - between an integer and a float variable by sorting both inputs in - ordinary numeric order. Execution of a merge join requires that - the system be able to identify four operators related to the - merge-join equality operator: less-than comparison for the left - input data type, less-than comparison for the right input data - type, less-than comparison between the two data types, and - greater-than comparison between the two data types. It is possible - to specify these by name, as the SORT1, - SORT2, LTCMP, and GTCMP options - respectively. The system will fill in the default names - <, <, <, - > respectively if any of these are omitted when - MERGES is specified. Also, MERGES will be - assumed to be implied if any of these four operator options appear. - - - If other join strategies are found to be practical, - PostgreSQL - will change the optimizer and run-time system to use - them and will require additional specification when an - operator is defined. Fortunately, the research community - invents new join strategies infrequently, and the added - generality of user-defined join strategies was not felt to - be worth the complexity involved. - - - The RESTRICT and JOIN options assist the - query optimizer in estimating result sizes. If a clause of the - form: - -myboxes.description <<< box '((0,0), (1,1))' - - is present in the qualification, - then PostgreSQL may have to - estimate the fraction of the instances in myboxes that - satisfy the clause. The function - res_proc - must be a registered function (meaning it is already defined using - CREATE FUNCTION) which accepts arguments of the correct - data types and returns a floating-point number. The - query optimizer simply calls this function, passing the - parameter ((0,0), (1,1)) and multiplies the result by the relation - size to get the expected number of instances. - - - Similarly, when the operands of the operator both contain - instance variables, the query optimizer must estimate the - size of the resulting join. The function join_proc will - return another floating-point number which will be multiplied - by the cardinalities of the two tables involved to - compute the expected result size. + Refer to for further information. + - The difference between the function - -my_procedure_1 (MYBOXES.description, box '((0,0), (1,1))') - - and the operator - -MYBOXES.description === box '((0,0), (1,1))' - - is that PostgreSQL - attempts to optimize operators and can - decide to use an index to restrict the search space when - operators are involved. However, there is no attempt to - optimize functions, and they are performed by brute force. - Moreover, functions can have any number of arguments while - operators are restricted to one or two. + Use DROP OPERATOR to delete user-defined + operators from a database. - - - - 2000-03-25 - - - Notes - - - Refer to for further information. - Use DROP OPERATOR to delete - user-defined operators from a database. - - - - To give a schema-qualified operator name in com_op or the other optional - arguments, use the OPERATOR() syntax, for example - - COMMUTATOR = OPERATOR(myschema.===) , - - - - - - Usage - - The following command defines a new operator, - area-equality, for the BOX data type: - - + + Examples + + + The following command defines a new operator, area-equality, for + the data type box: + CREATE OPERATOR === ( - LEFTARG = box, - RIGHTARG = box, - PROCEDURE = area_equal_procedure, - COMMUTATOR = ===, - NEGATOR = !==, - RESTRICT = area_restriction_procedure, - JOIN = area_join_procedure, - HASHES, - SORT1 = <<<, - SORT2 = <<< - -- Since sort operators were given, MERGES is implied. - -- LTCMP and GTCMP are assumed to be < and > respectively + LEFTARG = box, + RIGHTARG = box, + PROCEDURE = area_equal_procedure, + COMMUTATOR = ===, + NEGATOR = !==, + RESTRICT = area_restriction_procedure, + JOIN = area_join_procedure, + HASHES, + SORT1 = <<<, + SORT2 = <<< + -- Since sort operators were given, MERGES is implied. + -- LTCMP and GTCMP are assumed to be < and > respectively ); - + + - - - Compatibility - - - - - 2000-03-25 - - - SQL92 - - - - CREATE OPERATOR - is a PostgreSQL extension. - There is no CREATE OPERATOR - statement in SQL92. - - + + Compatibility + + + CREATE OPERATOR is a + PostgreSQL extension. There are no + provisions for user-defined operators in the SQL standard. + diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml index 6185f644825..ff7f2bfb134 100644 --- a/doc/src/sgml/ref/create_rule.sgml +++ b/doc/src/sgml/ref/create_rule.sgml @@ -1,5 +1,5 @@ @@ -8,138 +8,22 @@ PostgreSQL documentation CREATE RULE SQL - Language Statements + - - CREATE RULE - - - define a new rewrite rule - + CREATE RULE + define a new rewrite rule + - - 2001-01-05 - - + CREATE [ OR REPLACE ] RULE name AS ON event TO table [ WHERE condition ] - DO [ INSTEAD ] action - -where action can be: - -NOTHING -| query -| ( query ; query ... ) - - - - - 2001-01-05 - - - Inputs - - - - - - name - - - The name of a rule to create. This must be distinct from the name - of any other rule for the same table. - - - - - event - - - Event is one of SELECT, - UPDATE, DELETE - or INSERT. - - - - - table - - - The name (optionally schema-qualified) of the table or view the rule - applies to. - - - - - condition - - - Any SQL conditional expression (returning boolean). - The condition expression may not - refer to any tables except new and - old, and may not contain aggregate functions. - - - - - query - - - The query or queries making up the - action - can be any SQL SELECT, INSERT, - UPDATE, DELETE, or - NOTIFY statement. - - - - - - - - Within the condition - and action, the special - table names new and old may be - used to refer to values in the referenced table. - new is valid in ON INSERT and ON UPDATE rules - to refer to the new row being inserted or updated. - old is valid in ON UPDATE and ON DELETE - rules to refer to the existing row being updated or deleted. - - - - - - 1998-09-11 - - - Outputs - - - - - - -CREATE RULE - - - - Message returned if the rule is successfully created. - - - - - - + DO [ INSTEAD ] { NOTHING | command | ( command ; command ... ) } + - - - 1998-09-11 - - - Description - + + Description CREATE RULE defines a new rule applying to a specified @@ -150,186 +34,206 @@ CREATE RULE - The PostgreSQL - rule system allows one to define an - alternate action to be performed on inserts, updates, or deletions - from database tables. Rules are used to - implement table views as well. + The PostgreSQL rule system allows one to + define an alternate action to be performed on insertions, updates, + or deletions in database tables. Roughly speaking, a rule causes + additional commands to be executed when a given command on a given + table is executed. Alternatively, a rule can replace a given + command by another, or cause a command not to be executed at all. + Rules are used to implement table views as well. It is important + to realize that a rule is really a command transformation + mechanism, or command macro. The transformation happens before the + execution of the commands starts. If you actually want an + operation that fires independently for each physical row, you + probably want to use a trigger, not a rule. More information about + the rules system is in . - + - The semantics of a rule is that at the time an individual instance (row) - is - accessed, inserted, updated, or deleted, there is an old instance (for - selects, updates and deletes) and a new instance (for inserts and - updates). All the rules for the given event type and the given target - table are examined successively (in order by name). If the - condition specified in the - WHERE clause (if any) is true, the - action part of the rule is - executed. The action is - done instead of the original query if INSTEAD is specified; otherwise - it is done after the original query in the case of ON INSERT, or before - the original query in the case of ON UPDATE or ON DELETE. - Within both the condition - and action, values from - fields in the old instance and/or the new instance are substituted for - old.attribute-name - and new.attribute-name. + Presently, ON SELECT rules must be unconditional + INSTEAD rules and must have actions that consist + of a single SELECT command. Thus, an + ON SELECT rule effectively turns the table into + a view, whose visible contents are the rows returned by the rule's + SELECT command rather than whatever had been + stored in the table (if anything). It is considered better style + to write a CREATE VIEW command than to create a + real table and define an ON SELECT rule for it. - The action part of the - rule can consist of one or more queries. To write multiple queries, - surround them with parentheses. Such queries will be performed in the - specified order. The action can also be NOTHING indicating - no action. Thus, a DO INSTEAD NOTHING rule suppresses the original - query from executing (when its condition is true); a DO NOTHING rule - is useless. + You can create the illusion of an updatable view by defining + ON INSERT, ON UPDATE, and + ON DELETE rules (or any subset of those that's + sufficient for your purposes) to replace update actions on the view + with appropriate updates on other tables. - The action part of the rule - executes with the same command and transaction identifier as the user - command that caused activation. + There is a catch if you try to use conditional rules for view + updates: there must be an unconditional + INSTEAD rule for each action you wish to allow + on the view. If the rule is conditional, or is not + INSTEAD, then the system will still reject + 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 + 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 + the cases where they are applied, they add to the default + INSTEAD NOTHING action. + + + + Parameters + + + + name + + + The name of a rule to create. This must be distinct from the + name of any other rule for the same table. Multiple rules on + the same table and same event type are applied in alphabetical + name order. + + + + + + event + + + The even is one of SELECT, + INSERT, UPDATE, or + DELETE. + + + + + + table + + + The name (optionally schema-qualified) of the table or view the + rule applies to. + + + + + + condition + + + Any SQL conditional expression (returning boolean). + The condition expression may not refer to any tables except + NEW and OLD, and may not + contain aggregate functions. + + + + + + command + + + The command or commands that make up the rule action. Valid + commands are SELECT, + INSERT, UPDATE, + DELETE, or NOTIFY. + + + + - It is important to realize that a rule is really a query transformation - mechanism, or query macro. The entire query is processed to convert it - into a series of queries that include the rule actions. This occurs - before evaluation of the query starts. So, conditional rules are - handled by adding the rule condition to the WHERE clause of the action(s) - derived from the rule. The above description of a rule as an operation - that executes for each row is thus somewhat misleading. If you actually - want an operation that fires independently for each physical row, you - probably want to use a trigger not a rule. Rules are most useful for - situations that call for transforming entire queries independently of - the specific data being handled. + Within condition and + command, the special + table names NEW and OLD may + be used to refer to values in the referenced table. + NEW is valid in ON INSERT and + ON UPDATE rules to refer to the new row being + inserted or updated. OLD is valid in + ON UPDATE and ON DELETE rules + to refer to the existing row being updated or deleted. - - - - 2001-11-06 - - - Rules and Views - - - Presently, ON SELECT rules must be unconditional INSTEAD rules and must - have actions that consist of a single SELECT query. Thus, an ON SELECT - rule effectively turns the table into a view, whose visible - contents are the rows returned by the rule's SELECT query rather than - whatever had been stored in the table (if anything). It is considered - better style to write a CREATE VIEW command than to create a real table - and define an ON SELECT rule for it. - + - - creates a dummy table (with no underlying - storage) and associates an ON SELECT rule with it. The system will not - allow updates to the view, since it knows there is no real table there. - You can create the - illusion of an updatable view by defining ON INSERT, ON UPDATE, and - ON DELETE rules (or any subset of those that's sufficient - for your purposes) to replace update actions on the view with - appropriate updates on other tables. - + + Diagnostics + + + + CREATE RULE + + + Message returned if the rule was successfully created. + + + + + - - There is a catch if you try to use conditional - rules for view updates: there must be an unconditional - INSTEAD rule for each action you wish to allow on the view. If the - rule is conditional, or is not INSTEAD, then the system will still reject - attempts to perform the update action, because it thinks it might end up - trying to perform the action on the dummy table in some cases. - If you want to - handle all the useful cases in conditional rules, you can; just 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 non-INSTEAD; in the cases where they fire, - they add to the default INSTEAD NOTHING action. - - + + Notes - - - 2001-01-05 - - - Notes - - - You must have rule definition access to a table in order - to define a rule on it. Use GRANT - and REVOKE to change permissions. - + + You must have the privilege RULE on a table to + be allowed to define a rule on it. + - - It is very important to take care to avoid circular rules. - For example, though each - of the following two rule definitions are accepted by - PostgreSQL, the - select command will cause PostgreSQL to - report an error because the query cycled too many times: + + It is very important to take care to avoid circular rules. For + example, though each of the following two rule definitions are + accepted by PostgreSQL, the + SELECT command would cause + PostgreSQL to report an error because + the query cycled too many times: CREATE RULE "_RETURN" AS - ON SELECT TO emp + ON SELECT TO t1 DO INSTEAD - SELECT * FROM toyemp; + SELECT * FROM t2; CREATE RULE "_RETURN" AS - ON SELECT TO toyemp + ON SELECT TO t2 DO INSTEAD - SELECT * FROM emp; - - - This attempt to select from EMP will cause - PostgreSQL to issue an error - because the queries cycled too many times: + SELECT * FROM t1; - -SELECT * FROM emp; +SELECT * FROM t1; - + - - Presently, if a rule contains a NOTIFY query, the NOTIFY will be executed - unconditionally --- that is, the NOTIFY will be issued even if there are - not any rows that the rule should apply to. For example, in - + + Presently, if a rule action contains a NOTIFY + command, the NOTIFY command will be executed + unconditionally, that is, the NOTIFY will be + issued even if there are not any rows that the rule should apply + to. For example, in + CREATE RULE notify_me AS ON UPDATE TO mytable DO NOTIFY mytable; UPDATE mytable SET name = 'foo' WHERE id = 42; - - one NOTIFY event will be sent during the UPDATE, whether or not there - are any rows with id = 42. This is an implementation restriction that - may be fixed in future releases. - - + + one NOTIFY event will be sent during the + UPDATE, whether or not there are any rows with + id = 42. This is an implementation restriction + that may be fixed in future releases. + - - - Compatibility - + + Compatibility - - - 1998-09-11 - - - SQL92 - - - - CREATE RULE is a PostgreSQL - language extension. - There is no CREATE RULE statement in SQL92. - - + + CREATE RULE is a + PostgreSQL language extension, as is the + entire rules system. + diff --git a/doc/src/sgml/ref/create_schema.sgml b/doc/src/sgml/ref/create_schema.sgml index 7fd2aa3880c..9f32a8f342e 100644 --- a/doc/src/sgml/ref/create_schema.sgml +++ b/doc/src/sgml/ref/create_schema.sgml @@ -1,5 +1,5 @@ @@ -8,25 +8,51 @@ PostgreSQL documentation CREATE SCHEMA SQL - Language Statements + - - CREATE SCHEMA - - - define a new schema - + CREATE SCHEMA + define a new schema + - + CREATE SCHEMA schemaname [ AUTHORIZATION username ] [ schema_element [ ... ] ] CREATE SCHEMA AUTHORIZATION username [ schema_element [ ... ] ] - + + + + + Description + + + CREATE SCHEMA will enter a new schema + into the current database. + The schema name must be distinct from the name of any existing schema + in the current database. + + + + A schema is essentially a namespace: + it contains named objects (tables, data types, functions, and operators) + whose names may duplicate those of other objects existing in other + schemas. Named objects are accessed either by qualifying + 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, + which can be determined with the function current_schema). + + + + Optionally, CREATE SCHEMA can include subcommands + to create objects within the new schema. The subcommands are treated + essentially the same as separate commands issued after creating the + schema, except that if the AUTHORIZATION clause is used, + all the created objects will be owned by that user. + + - - - Inputs - - + + Parameters @@ -63,164 +89,112 @@ CREATE SCHEMA AUTHORIZATION username - - - - - - Outputs - - + - - - -CREATE SCHEMA - - - - Message returned if the command is successful. - - - - - -ERROR: namespace "schemaname" already exists - - - - If the schema specified already exists. - - - - - - - + + Diagnostics + + + + CREATE SCHEMA + + + Message returned if the schema was successfully created. + + + + + + ERROR: namespace "schemaname" already exists + + + Message returned if the schema specified already exists. + + + + + - - - Description - - - CREATE SCHEMA will enter a new schema - into the current database. - The schema name must be distinct from the name of any existing schema - in the current database. - + + Notes - A schema is essentially a namespace: - it contains named objects (tables, data types, functions, and operators) - whose names may duplicate those of other objects existing in other - schemas. Named objects are accessed either by qualifying - 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; - see CURRENT_SCHEMA()). + To create a schema, the invoking user must have CREATE + privilege for the current database. (Of course, superusers bypass + this check.) - Optionally, CREATE SCHEMA can include subcommands - to create objects within the new schema. The subcommands are treated - essentially the same as separate commands issued after creating the - schema, except that if the AUTHORIZATION clause is used, - all the created objects will be owned by that user. + Use DROP SCHEMA to remove a schema. - - - - Notes - - - - To create a schema, the invoking user must have CREATE - privilege for the current database. (Of course, superusers bypass - this check.) - - - - Use DROP SCHEMA to remove a schema. - - - - - Examples - + + Examples + Create a schema: - - + CREATE SCHEMA myschema; - + - Create a schema for user joe --- the schema will also - be named joe: - - + Create a schema for user joe; the schema will also be + named joe: + CREATE SCHEMA AUTHORIZATION joe; - + Create a schema and create a table and view within it: - - + CREATE SCHEMA hollywood CREATE TABLE films (title text, release date, awards text[]) CREATE VIEW winners AS SELECT title, release FROM films WHERE awards IS NOT NULL; - + Notice that the individual subcommands do not end with semicolons. The following is an equivalent way of accomplishing the same result: - + CREATE SCHEMA hollywood; CREATE TABLE hollywood.films (title text, release date, awards text[]); CREATE VIEW hollywood.winners AS SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL; - + - - - Compatibility - - - - - SQL92 - - - - SQL92 allows a DEFAULT CHARACTER SET clause in - CREATE SCHEMA, as well as more subcommand types - than are presently accepted by PostgreSQL. - - - - SQL92 specifies that the subcommands in CREATE SCHEMA - may appear in any order. The present - PostgreSQL implementation does not handle all - cases of forward references in subcommands; it may sometimes be necessary - to reorder the subcommands to avoid forward references. - - - - In SQL92, the owner of a schema always owns all objects within it. - PostgreSQL allows schemas to contain objects - owned by users other than the schema owner. This can happen only if the - schema owner grants CREATE rights on his schema to someone - else. - - + + Compatibility + + + The SQL standard allows a DEFAULT CHARACTER SET clause + in CREATE SCHEMA, as well as more subcommand + types than are presently accepted by + PostgreSQL. + + + + The SQL standard specifies that the subcommands in CREATE + SCHEMA may appear in any order. The present + PostgreSQL implementation does not + handle all cases of forward references in subcommands; it may + sometimes be necessary to reorder the subcommands to avoid forward + references. + + + + According to the SQL standard, the owner of a schema always owns + all objects within it. PostgreSQL + allows schemas to contain objects owned by users other than the + schema owner. This can happen only if the schema owner grants the + CREATE privilege on his schema to someone else. + diff --git a/doc/src/sgml/ref/create_sequence.sgml b/doc/src/sgml/ref/create_sequence.sgml index 8e5ca32962c..374f34cb0a8 100644 --- a/doc/src/sgml/ref/create_sequence.sgml +++ b/doc/src/sgml/ref/create_sequence.sgml @@ -1,5 +1,5 @@ @@ -8,248 +8,36 @@ PostgreSQL documentation CREATE SEQUENCE SQL - Language Statements + - - CREATE SEQUENCE - - - define a new sequence generator - + CREATE SEQUENCE + define a new sequence generator + - - 1999-07-20 - - + CREATE [ TEMPORARY | TEMP ] SEQUENCE seqname [ INCREMENT [ BY ] increment ] [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ] [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ] - - - - - 1998-09-11 - - - Inputs - - - - - - TEMPORARY or TEMP - - - If specified, the sequence object is created only for this session, - and is automatically dropped on session exit. - Existing permanent sequences with the same name are not visible - (in this session) while the temporary sequence exists, unless - they are referenced with schema-qualified names. - - - - - - seqname - - - The name (optionally schema-qualified) of a sequence to be created. - - - - - - increment - - - The - - clause is optional. A positive value will make an - ascending sequence, a negative one a descending sequence. - The default value is one (1). - - - - - - minvalue - NO MINVALUE - - - The optional clause - determines the minimum value - a sequence can generate. If this clause is not supplied or - is specified, then defaults will be used. The defaults are 1 and -2^63-1 for - ascending and descending sequences, respectively. - - - - - - maxvalue - NO MAXVALUE - - - The optional clause - determines the maximum - value for the sequence. If this clause is not supplied or - is specified, then default values will be used. - The defaults are 2^63-1 and -1 for ascending and descending sequences, respectively. - - - - - - start - - - The optional enables the sequence to begin anywhere. - The default starting value is - minvalue - for ascending sequences and - maxvalue - for descending ones. - - - - - - cache - - - The option - enables sequence numbers to be preallocated - and stored in memory for faster access. The minimum - value is 1 (only one value can be generated at a time, i.e., no cache) - and this is also the default. - - - - - - CYCLE - - - The optional keyword may be used to enable - the sequence to wrap around when the - maxvalue or - minvalue has been - reached by - an ascending or descending sequence respectively. If the limit is - reached, the next number generated will be the - minvalue or - maxvalue, - respectively. - - - - - - NO CYCLE - - - If the optional keyword is specified, any - calls to nextval after the sequence has reached - its maximum value will return an error. If neither - or are specified, - is the default. - - - - - - - - - - 1998-09-11 - - - Outputs - - - - - - -CREATE SEQUENCE - - - - Message returned if the command is successful. - - - - - -ERROR: Relation 'seqname' already exists - - - - If the sequence specified already exists. - - - - - -ERROR: DefineSequence: MINVALUE (start) can't be >= MAXVALUE (max) - - - - If the specified starting value is out of range. - - - - - -ERROR: DefineSequence: START value (start) can't be < MINVALUE (min) - - - - If the specified starting value is out of range. - - - - - -ERROR: DefineSequence: MINVALUE (min) can't be >= MAXVALUE (max) - - - - If the minimum and maximum values are inconsistent. - - - - - - + - - - 1998-09-11 - - - Description - + + Description + - CREATE SEQUENCE will enter a new sequence number generator - into the current database. This involves creating and initializing a - new single-row - table with the name seqname. - The generator will be owned by the user issuing the command. + CREATE SEQUENCE creates a new sequence number + generator. This involves creating and initializing a new special + single-row table with the name seqname. The generator will be + owned by the user issuing the command. If a schema name is given then the sequence is created in the - specified schema. Otherwise it is created in the current schema (the one - at the front of the search path; see CURRENT_SCHEMA()). - TEMP sequences exist in a special schema, so a schema name may not be - given when creating a TEMP sequence. + specified schema. Otherwise it is created in the current schema. + Temporary sequences exist in a special schema, so a schema name may not be + given when creating a temporary sequence. The sequence name must be distinct from the name of any other sequence, table, index, or view in the same schema. @@ -257,7 +45,7 @@ ERROR: DefineSequence: MINVALUE (min After a sequence is created, you use the functions nextval, - currval and + currval, and setval to operate on the sequence. These functions are documented in . @@ -266,132 +54,293 @@ ERROR: DefineSequence: MINVALUE (min Although you cannot update a sequence directly, you can use a query like - + SELECT * FROM seqname; - + to examine the parameters and current state of a sequence. In particular, the last_value field of the sequence shows the last value - allocated by any backend process. (Of course, this value may be obsolete - by the time it's printed, if other processes are actively doing + allocated by any session. (Of course, this value may be obsolete + by the time it's printed, if other sessions are actively doing nextval calls.) + + + + Parameters + + + + TEMPORARY or TEMP + + + If specified, the sequence object is created only for this + session, and is automatically dropped on session exit. Existing + permanent sequences with the same name are not visible (in this + session) while the temporary sequence exists, unless they are + referenced with schema-qualified names. + + + + + + seqname + + + The name (optionally schema-qualified) of the sequence to be created. + + + + + + increment + + + The optional clause INCREMENT BY increment specified, + which value is added to the current sequence value to create a + new value. A positive value will make an ascending sequence, a + negative one a descending sequence. The default value is 1. + + + + + + minvalue + NO MINVALUE + + + The optional clause MINVALUE minvalue determines + the minimum value a sequence can generate. If this clause is not + supplied or is specified, then + defaults will be used. The defaults are 1 and + -263-1 for ascending and descending sequences, + respectively. + + + + + + maxvalue + NO MAXVALUE + + + The optional clause MAXVALUE maxvalue determines + the maximum value for the sequence. If this clause is not + supplied or is specified, then + default values will be used. The defaults are + 263-1 and -1 for ascending and descending + sequences, respectively. + + + + + + start + + + The optional clause START WITH start allows the + sequence to begin anywhere. The default starting value is + minvalue for + ascending sequences and maxvalue for descending ones. + + + - - - Unexpected results may be obtained if a cache setting greater than one - is used for a sequence object that will be used concurrently by multiple - backends. Each backend will allocate and cache successive sequence values - during one access to the sequence object and increase the sequence - object's last_value accordingly. Then, the next cache-1 uses of nextval - within that backend simply return the preallocated values without touching - the shared object. So, any numbers allocated but not used within a session - will be lost when that session ends. Furthermore, although multiple backends are guaranteed to - allocate distinct sequence values, the values may be generated out of - sequence when all the backends are considered. (For example, with a cache - setting of 10, backend A might reserve values 1..10 and return nextval=1, - then - backend B might reserve values 11..20 and return nextval=11 before backend - A has generated nextval=2.) Thus, with a cache setting of one it is safe - to assume that nextval values are generated sequentially; with a cache - setting greater than one you should only assume that the nextval values - are all distinct, not that they are generated purely sequentially. - Also, last_value will reflect the latest value reserved by any backend, - whether or not it has yet been returned by nextval. - Another consideration is that a setval executed on such a sequence - will not be noticed by other backends until they have used up any - preallocated values they have cached. - - - - - - 1998-09-11 - - - Notes - - - - Use DROP SEQUENCE to remove a sequence. - - - - Sequences are based on bigint arithmetic, so the range cannot - exceed the range of an eight-byte integer - (-9223372036854775808 to 9223372036854775807). On some older platforms, - there may be no compiler support for eight-byte integers, in which case - sequences use regular integer arithmetic (range - -2147483648 to +2147483647). - - - - When cache is greater than - one, each backend uses its own cache to store preallocated numbers. - Numbers that are cached but not used in the current session will be - lost, resulting in holes in the sequence. - - + + cache + + + The optional clause CACHE cache specifies how + many sequence numbers are to be preallocated and stored in + memory for faster access. The minimum value is 1 (only one value + can be generated at a time, i.e., no cache), and this is also the + default. + + + + + + CYCLE + NO CYCLE + + + The CYCLE option allows the sequence to wrap + around when the maxvalue or minvalue has been reached by an + ascending or descending sequence respectively. If the limit is + reached, the next number generated will be the minvalue or maxvalue, respectively. + + + + If NO CYCLE is specified, any calls to + nextval after the sequence has reached its + maximum value will return an error. If neither + CYCLE or NO CYCLE are + specified, NO CYCLE is the default. + + + + - - - Usage - + + Diagnostics + + + + CREATE SEQUENCE + + + Message returned if the sequence was successfully created. + + + + + + ERROR: Relation 'seqname' already exists + + + A sequence, table, view, or index of the specified name already exists. + + + + + + ERROR: DefineSequence: MINVALUE (start) can't be >= MAXVALUE (max) + ERROR: DefineSequence: START value (start) can't be < MINVALUE (min) + + + The specified starting value is out of range. + + + + + + ERROR: DefineSequence: MINVALUE (min) can't be >= MAXVALUE (max) + + + The minimum and maximum values are inconsistent. + + + + + + + + Notes + - Create an ascending sequence called serial, starting at 101: + Use DROP SEQUENCE to remove a sequence. + + + + Sequences are based on bigint arithmetic, so the range + cannot exceed the range of an eight-byte integer + (-9223372036854775808 to 9223372036854775807). On some older + platforms, there may be no compiler support for eight-byte + integers, in which case sequences use regular integer + arithmetic (range -2147483648 to +2147483647). + + + + Unexpected results may be obtained if a cache setting greater than one is + used for a sequence object that will be used concurrently by + multiple sessions. Each session will allocate and cache successive + sequence values during one access to the sequence object and + increase the sequence object's last_value accordingly. + Then, the next cache-1 + uses of nextval within that session simply return the + preallocated values without touching the sequence object. So, any + numbers allocated but not used within a session will be lost when + that session ends, resulting in holes in the + sequence. + + + + Furthermore, although multiple sessions are guaranteed to allocate + distinct sequence values, the values may be generated out of + sequence when all the sessions are considered. FFor example, with + a cache setting of 10, + session A might reserve values 1..10 and return + nextval=1, then session B might reserve values + 11..20 and return nextval=11 before session A + has generated nextval=2. Thus, with a + cache setting of one + it is safe to assume that nextval values are generated + sequentially; with a cache setting greater than one you + should only assume that the nextval values are all + distinct, not that they are generated purely sequentially. Also, + last_value will reflect the latest value reserved by + any session, whether or not it has yet been returned by + nextval. + + + + Another consideration is that a setval executed on + such a sequence will not be noticed by other sessions until they + have used up any preallocated values they have cached. - + + + + Examples + + + Create an ascending sequence called serial, starting at 101: + CREATE SEQUENCE serial START 101; - + + + Select the next number from this sequence: - + SELECT nextval('serial'); -nextval -------- - 114 - + nextval +--------- + 114 + + - Use this sequence in an INSERT: - + Use this sequence in an INSERT command: + INSERT INTO distributors VALUES (nextval('serial'), 'nothing'); - + - Update the sequence value after a COPY FROM: - + Update the sequence value after a COPY FROM: + BEGIN; - COPY distributors FROM 'input_file'; - SELECT setval('serial', max(id)) FROM distributors; +COPY distributors FROM 'input_file'; +SELECT setval('serial', max(id)) FROM distributors; END; - + - - - Compatibility - - - - - 1998-09-11 - - - SQL92 - - - - CREATE SEQUENCE is a PostgreSQL - language extension. - There is no CREATE SEQUENCE statement - in SQL92. - - + + Compatibility + + + CREATE SEQUENCE is a + PostgreSQL language extension. There is + no CREATE SEQUENCE statement in the SQL + standard. + diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 9ee71705f96..c95baad4175 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1,5 +1,5 @@ @@ -29,7 +29,7 @@ where column_constraint is: [ CONSTRAINT constraint_name ] { NOT NULL | NULL | UNIQUE | PRIMARY KEY | CHECK (expression) | - REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL ] + REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] @@ -40,7 +40,7 @@ and table_constraint is: PRIMARY KEY ( column_name [, ... ] ) | CHECK ( expression ) | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] - [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE action ] [ ON UPDATE action ] } + [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] @@ -58,17 +58,16 @@ and table_constraint is: If a schema name is given (for example, CREATE TABLE myschema.mytable ...) then the table is created in the - specified schema. Otherwise it is created in the current schema (the one - at the front of the search path; see CURRENT_SCHEMA()). - TEMP tables exist in a special schema, so a schema name may not be - given when creating a TEMP table. + specified schema. Otherwise it is created in the current schema. + Temporary tables exist in a special schema, so a schema name may not be + given when creating a temporary table. The table name must be distinct from the name of any other table, sequence, index, or view in the same schema. CREATE TABLE also automatically creates a data - type that represents the tuple type (structure type) corresponding + type that represents the composite type corresponding to one row of the table. Therefore, tables cannot have the same name as any existing data type in the same schema. @@ -81,9 +80,8 @@ and table_constraint is: The optional constraint clauses specify constraints (or tests) that new or updated rows must satisfy for an insert or update operation - to succeed. A constraint is a named rule: an SQL object which - helps define valid sets of values by putting limits on the results - of insert, update, or delete operations performed on a table. + to succeed. A constraint is an SQL object that helps define the + set of valid values in the table in various ways. @@ -158,7 +156,7 @@ and table_constraint is: The DEFAULT clause assigns a default data value for the column whose column definition it appears within. The value - is any variable-free expression (subselects and cross-references + is any variable-free expression (subqueries and cross-references to other columns in the current table are not allowed). The data type of the default expression must match the data type of the column. @@ -167,7 +165,7 @@ and table_constraint is: The default expression will be used in any insert operation that does not specify a value for the column. If there is no default - for a column, then the default is NULL. + for a column, then the default is null. @@ -212,7 +210,8 @@ and table_constraint is: - WITH OIDS or WITHOUT OIDS + WITH OIDS + WITHOUT OIDS This optional clause specifies whether rows of the new table @@ -250,7 +249,7 @@ and table_constraint is: NOT NULL - The column is not allowed to contain NULL values. + The column is not allowed to contain null values. @@ -259,7 +258,7 @@ and table_constraint is: NULL - The column is allowed to contain NULL values. This is the default. + The column is allowed to contain null values. This is the default. @@ -276,7 +275,7 @@ and table_constraint is: - The UNIQUE constraint specifies a rule that a + The UNIQUE constraint specifies that a group of one or more distinct 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 @@ -284,7 +283,7 @@ and table_constraint is: - For the purpose of a unique constraint, NULL values are not + For the purpose of a unique constraint, null values are not considered equal. @@ -303,11 +302,11 @@ and table_constraint is: The primary key constraint specifies that a column or columns of a table - may contain only unique (non-duplicate), non-NULL values. + may contain only unique (non-duplicate), nonnull values. Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of columns as primary key also provides - meta-data about the design of the schema, as a primary key + metadata about the design of the schema, as a primary key implies that other tables may rely on this set of columns as a unique identifier for rows. @@ -329,21 +328,19 @@ and table_constraint is: CHECK (expression) - CHECK clauses specify integrity constraints or tests - which new or updated rows must satisfy for an insert or update - operation to succeed. Each constraint must be an expression - producing a Boolean result. A condition appearing within a - column definition should reference that column's value only, - while a condition appearing as a table constraint may reference - multiple columns. + The CHECK clause specifies an expression producing a + Boolean result which new or updated rows must satisfy for an + insert or update operation to succeed. A check constraint + specified as a column constraint should reference that column's + value only, while an expression appearing in a table constraint + may reference multiple columns. Currently, CHECK expressions cannot contain - subselects nor refer to variables other than columns of the + subqueries nor refer to variables other than columns of the current row. - @@ -360,7 +357,7 @@ and table_constraint is: - The REFERENCES column constraint specifies + Theses clauses specify a foreign key constraint, which specifies that a group of one or more columns of the new table must only contain values which match against values in the referenced column(s) refcolumn @@ -374,23 +371,23 @@ and table_constraint is: - A value added to these columns is matched against the values of - the referenced table and referenced columns using the given - match type. There are three match types: MATCH - FULL, MATCH PARTIAL, and a default match type if - none is specified. MATCH FULL will not allow one - column of a multicolumn foreign key to be NULL unless all - foreign key columns are NULL. The default match type allows some - foreign key columns to be NULL while other parts of the foreign - key are not NULL. MATCH PARTIAL is not yet - implemented. + A value inserted into these columns is matched against the + values of the referenced table and referenced columns using the + given match type. There are three match types: MATCH + FULL, MATCH PARTIAL, and MATCH + SIMPLE, which is also the default. MATCH + FULL will not allow one column of a multicolumn foreign key + to be null unless all foreign key columns are null. + MATCH SIMPLE allows some foreign key columns + to be null while other parts of the foreign key are not + null. MATCH PARTIAL is not yet implemented. In addition, when the data in the referenced columns is changed, certain actions are performed on the data in this table's columns. The ON DELETE clause specifies the - action to do when a referenced row in the referenced table is + action to perform when a referenced row in the referenced table is being deleted. Likewise, the ON UPDATE clause specifies the action to perform when a referenced column in the referenced table is being updated to a new value. If the @@ -434,7 +431,7 @@ and table_constraint is: SET NULL - Set the referencing column values to NULL. + Set the referencing column values to null. @@ -449,19 +446,20 @@ and table_constraint is: + If primary key column is updated frequently, it may be wise to - add an index to the REFERENCES column so that - NO ACTION and CASCADE - actions associated with the REFERENCES - column can be more efficiently performed. + add an index to the foreign key column so that NO + ACTION and CASCADE actions + associated with the foreign key column can be more efficiently + performed. - - DEFERRABLE or NOT DEFERRABLE + DEFERRABLE + NOT DEFERRABLE This controls whether the constraint can be deferred. A @@ -477,7 +475,8 @@ and table_constraint is: - INITIALLY IMMEDIATE or INITIALLY DEFERRED + INITIALLY IMMEDIATE + INITIALLY DEFERRED If a constraint is deferrable, this clause specifies the default @@ -541,45 +540,16 @@ and table_constraint is: Diagnostics - - - - - - CREATE TABLE - - - - - - - Message returned if table is successfully created. - - - - - - - - - - ERROR - - - - - - - Message returned if table creation failed. This is usually - accompanied by some descriptive text, such as: - ERROR: Relation 'table' already - exists, which occurs at run time if the table - specified already exists in the database. - - - - + + + CREATE TABLE + + + Message returned if the table was successfully created. + + + + @@ -622,17 +592,6 @@ and table_constraint is: - - - The SQL92 standard says that CHECK column constraints - may only refer to the column they apply to; only - CHECK table constraints may refer to multiple - columns. PostgreSQL does not enforce - this restriction; it treats column and table check constraints - alike. - - - Unique constraints and primary keys are not inherited in the @@ -653,19 +612,19 @@ and table_constraint is: CREATE TABLE films ( - code CHARACTER(5) CONSTRAINT firstkey PRIMARY KEY, - title CHARACTER VARYING(40) NOT NULL, - did DECIMAL(3) NOT NULL, - date_prod DATE, - kind CHAR(10), - len INTERVAL HOUR TO MINUTE + code char(5) CONSTRAINT firstkey PRIMARY KEY, + title varchar(40) NOT NULL, + did integer NOT NULL, + date_prod date, + kind varchar(10), + len interval hour to minute ); CREATE TABLE distributors ( - did DECIMAL(3) PRIMARY KEY DEFAULT NEXTVAL('serial'), - name VARCHAR(40) NOT NULL CHECK (name <> '') + did integer PRIMARY KEY DEFAULT nextval('serial'), + name varchar(40) NOT NULL CHECK (name <> '') ); @@ -675,23 +634,24 @@ CREATE TABLE distributors ( CREATE TABLE array ( - vector INT[][] + vector int[][] ); - Define a unique table constraint for the table films. Unique table - constraints can be defined on one or more columns of the table: + Define a unique table constraint for the table + films. Unique table constraints can be defined + on one or more columns of the table. CREATE TABLE films ( - code CHAR(5), - title VARCHAR(40), - did DECIMAL(3), - date_prod DATE, - kind VARCHAR(10), - len INTERVAL HOUR TO MINUTE, + code char(5), + title varchar(40), + did integer, + date_prod date, + kind varchar(10), + len interval hour to minute, CONSTRAINT production UNIQUE(date_prod) ); @@ -702,8 +662,8 @@ CREATE TABLE films ( CREATE TABLE distributors ( - did DECIMAL(3) CHECK (did > 100), - name VARCHAR(40) + did integer CHECK (did > 100), + name varchar(40) ); @@ -713,8 +673,8 @@ CREATE TABLE distributors ( CREATE TABLE distributors ( - did DECIMAL(3), - name VARCHAR(40) + did integer, + name varchar(40) CONSTRAINT con1 CHECK (did > 100 AND name <> '') ); @@ -727,12 +687,12 @@ CREATE TABLE distributors ( CREATE TABLE films ( - code CHAR(5), - title VARCHAR(40), - did DECIMAL(3), - date_prod DATE, - kind VARCHAR(10), - len INTERVAL HOUR TO MINUTE, + code char(5), + title varchar(40), + did integer, + date_prod date, + kind varchar(10), + len interval hour to minute, CONSTRAINT code_title PRIMARY KEY(code,title) ); @@ -746,33 +706,33 @@ CREATE TABLE films ( CREATE TABLE distributors ( - did DECIMAL(3), - name CHAR VARYING(40), + did integer, + name varchar(40), PRIMARY KEY(did) ); CREATE TABLE distributors ( - did DECIMAL(3) PRIMARY KEY, - name VARCHAR(40) + did integer PRIMARY KEY, + name varchar(40) ); This assigns a literal constant default value for the column - name, and arranges for the default value of - column did to be generated by selecting the next - value of a sequence object. The default value of - modtime will be the time at which the row is + name, arranges for the default value of column + did to be generated by selecting the next value + of a sequence object, and makes the default value of + modtime be the time at which the row is inserted. CREATE TABLE distributors ( - name VARCHAR(40) DEFAULT 'luso films', - did INTEGER DEFAULT NEXTVAL('distributors_serial'), - modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP + name varchar(40) DEFAULT 'Luso Films', + did integer DEFAULT nextval('distributors_serial'), + modtime timestamp DEFAULT current_timestamp ); @@ -784,8 +744,8 @@ CREATE TABLE distributors ( CREATE TABLE distributors ( - did DECIMAL(3) CONSTRAINT no_null NOT NULL, - name VARCHAR(40) NOT NULL + did integer CONSTRAINT no_null NOT NULL, + name varchar(40) NOT NULL ); @@ -795,8 +755,8 @@ CREATE TABLE distributors ( CREATE TABLE distributors ( - did DECIMAL(3), - name VARCHAR(40) UNIQUE + did integer, + name varchar(40) UNIQUE ); @@ -804,8 +764,8 @@ CREATE TABLE distributors ( CREATE TABLE distributors ( - did DECIMAL(3), - name VARCHAR(40), + did integer, + name varchar(40), UNIQUE(name) ); @@ -818,8 +778,7 @@ CREATE TABLE distributors ( The CREATE TABLE command conforms to SQL92 - and to a subset of SQL99, with exceptions listed below and in the - descriptions above. + and to a subset of SQL99, with exceptions listed below. @@ -827,27 +786,25 @@ CREATE TABLE distributors ( Although the syntax of CREATE TEMPORARY TABLE - resembles that of SQL92, the effect is not the same. In the standard, + resembles that of SQL standard, the effect is not the same. In the standard, temporary tables are defined just once and automatically exist (starting with empty contents) in every session that needs them. PostgreSQL instead requires each session to issue its own CREATE TEMPORARY TABLE command for each temporary table to be used. This allows different sessions to use the same temporary table name for different - purposes, whereas the spec's approach constrains all instances of a + purposes, whereas the standard's approach constrains all instances of a given temporary table name to have the same table structure. - - - The spec-mandated behavior of temporary tables is widely ignored. - PostgreSQL's behavior on this point is similar - to that of several other RDBMSs. - - + + The behavior of temporary tables mandated by the standard is + widely ignored. PostgreSQL's behavior + on this point is similar to that of several other SQL databases. + - SQL92's distinction between global and local temporary tables + The standard's distinction between global and local temporary tables is not in PostgreSQL, since that distinction depends on the concept of modules, which PostgreSQL does not have. @@ -855,63 +812,39 @@ CREATE TABLE distributors ( The ON COMMIT clause for temporary tables - also resembles SQL92, but has some differences. - If the ON COMMIT clause is omitted, SQL92 specifies that the + also resembles the SQL standard, but has some differences. + If the ON COMMIT clause is omitted, SQL specifies that the default behavior is ON COMMIT DELETE ROWS. However, the default behavior in PostgreSQL is ON COMMIT PRESERVE ROWS. The ON COMMIT - DROP option does not exist in SQL92. + DROP option does not exist in SQL. - <literal>NULL</literal> <quote>Constraint</quote> + Column Check Constraints - The NULL constraint (actually a - non-constraint) is a PostgreSQL - extension to SQL92 that is included for compatibility with some - other RDBMSs (and for symmetry with the NOT - NULL constraint). Since it is the default for any - column, its presence is simply noise. + The SQL standard says that CHECK column constraints + may only refer to the column they apply to; only CHECK + table constraints may refer to multiple columns. + PostgreSQL does not enforce this + restriction; it treats column and table check constraints alike. - - - Assertions - - - An assertion is a special type of integrity constraint and shares - the same namespace as other constraints. However, an assertion is - not necessarily dependent on one particular table as constraints - are, so SQL92 provides the CREATE ASSERTION - statement as an alternate method for defining a constraint: - -CREATE ASSERTION name CHECK ( condition ) - - + + <literal>NULL</literal> <quote>Constraint</quote> - PostgreSQL does not implement assertions at present. + The NULL constraint (actually a + non-constraint) is a PostgreSQL + extension to the SQL standard that is included for compatibility with some + other database systems (and for symmetry with the NOT + NULL constraint). Since it is the default for any + column, its presence is simply noise. - - Inheritance diff --git a/doc/src/sgml/ref/create_table_as.sgml b/doc/src/sgml/ref/create_table_as.sgml index 17b19468b01..cc30ce0e3f7 100644 --- a/doc/src/sgml/ref/create_table_as.sgml +++ b/doc/src/sgml/ref/create_table_as.sgml @@ -1,5 +1,5 @@ @@ -18,16 +18,12 @@ PostgreSQL documentation CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name [ (column_name [, ...] ) ] AS query - + - - 2001-03-20 - - - Description - + Description + CREATE TABLE AS creates a table and fills it with data computed by a SELECT command. The @@ -75,10 +71,9 @@ CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_namecolumn_name - The name of a column in the new table. Multiple column names can - be specified using a comma-delimited list of column names. If - column names are not provided, they are taken from the output - column names of the query. + 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. @@ -124,21 +119,12 @@ CREATE [ [ LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name This command is modeled after an Oracle feature. There is no command with equivalent functionality in - SQL92 or SQL99. However, a combination of CREATE + the SQL standard. However, a combination of CREATE TABLE and INSERT ... SELECT can accomplish the same thing with little more effort. - - History - - - The CREATE TABLE AS command has been available - since PostgreSQL 6.3. - - - See Also diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index a671e6c430e..1856264954c 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -1,5 +1,5 @@ @@ -8,186 +8,57 @@ PostgreSQL documentation CREATE TRIGGER SQL - Language Statements + - - CREATE TRIGGER - - - define a new trigger - + CREATE TRIGGER + define a new trigger + - - 2000-03-25 - - + CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] } ON table [ FOR [ EACH ] { ROW | STATEMENT } ] EXECUTE PROCEDURE func ( arguments ) - - - - - 1998-09-21 - - - Inputs - - - - - - name - - - The name to give the new trigger. This must be distinct from the name - of any other trigger for the same table. - - - - - - BEFORE - AFTER - - - Determines whether the function is called before or after the - event. - - - - - - event - - - One of INSERT, DELETE or - UPDATE; this specifies the event that will - fire the trigger. Multiple events can be specified using - OR. - - - - - table - - - The name (optionally schema-qualified) of the table the - trigger is for. - - - - - - FOR EACH ROW - FOR EACH STATEMENT - - - - This specifies whether the trigger procedure should be fired - once for every row affected by the trigger event, or just once - per SQL statement. If neither is specified, FOR EACH - STATEMENT is the default. - - - - - - func - - - A user-supplied function that is declared as taking no arguments - and returning type trigger. - - - - - arguments - - - An optional comma-separated list of arguments to be provided to - the function when the trigger is executed, along with the standard - trigger data such as old and new tuple contents. The arguments - are literal string constants. Simple names and numeric constants - may be written here too, but they will all be converted to - strings. Note that these arguments are not provided as normal - function parameters (since a trigger procedure must be declared to - take zero parameters), but are instead accessed through the - TG_ARGV array. - - - - - - - - - - 1998-09-21 - - - Outputs - - - - - - -CREATE TRIGGER - - - - This message is returned if the trigger is successfully created. - - - - - - + - - - 1998-09-21 - - - Description - + + Description - CREATE TRIGGER will enter a new trigger into the current - database. The trigger will be associated with the relation - table and will execute - the specified function func. + CREATE TRIGGER creates a new trigger. The + trigger will be associated with the specified table and will + execute the specified function func when certain events occur. - The trigger can be specified to fire either before BEFORE the - operation is attempted on a tuple (before constraints are checked and - the INSERT, UPDATE or - DELETE is attempted) or AFTER the operation has - been attempted (e.g., after constraints are checked and the - INSERT, UPDATE or - DELETE has completed). If the trigger fires before - the event, the trigger may skip the operation for the current tuple, - or change the tuple being inserted (for INSERT and - UPDATE operations only). If the trigger fires - after the event, all changes, including the last insertion, update, - or deletion, are visible to the trigger. + The trigger can be specified to fire either before before the + operation is attempted on a row (before constraints are checked and + the INSERT, UPDATE, or + DELETE is attempted) or after the operation has + completed (after constraints are checked and the + INSERT, UPDATE, or + DELETE has completed). If the trigger fires + before the event, the trigger may skip the operation for the + current row, or change the row being inserted (for + INSERT and UPDATE operations + only). If the trigger fires after the event, all changes, including + the last insertion, update, or deletion, are visible + to the trigger. - A trigger that executes FOR EACH ROW of the - specified operation is called once for every row that the operation - modifies. For example, a DELETE that affects 10 - rows will cause any ON DELETE triggers on the - target relation to be called 10 separate times, once for each - deleted tuple. In contrast, a trigger that executes FOR - EACH STATEMENT of the specified operation only executes - once for any given operation, regardless of how many rows it - modifies (in particular, an operation that modifies zero rows will - still result in the execution of any applicable FOR EACH - STATEMENT triggers). + A trigger that is marked FOR EACH ROW is called + once for every row that the operation modifies. For example, a + DELETE that affects 10 rows will cause any + ON DELETE triggers on the target relation to be + called 10 separate times, once for each deleted row. In contrast, a + trigger that is marked FOR EACH STATEMENT only + executes once for any given operation, regardless of how many rows + it modifies (in particular, an operation that modifies zero rows + will still result in the execution of any applicable FOR + EACH STATEMENT triggers). @@ -202,9 +73,114 @@ CREATE TRIGGER - Refer to for more information. + Refer to for more information about triggers. + + + Parameters + + + + name + + + The name to give the new trigger. This must be distinct from + the name of any other trigger for the same table. + + + + + + BEFORE + AFTER + + + Determines whether the function is called before or after the + event. + + + + + + event + + + One of INSERT, UPDATE, or + DELETE; this specifies the event that will + fire the trigger. Multiple events can be specified using + OR. + + + + + + table + + + The name (optionally schema-qualified) of the table the trigger + is for. + + + + + + FOR EACH ROW + FOR EACH STATEMENT + + + + This specifies whether the trigger procedure should be fired + once for every row affected by the trigger event, or just once + per SQL statement. If neither is specified, FOR EACH + STATEMENT is the default. + + + + + + func + + + A user-supplied function that is declared as taking no arguments + and returning type trigger, which is executed when + the trigger fires. + + + + + + arguments + + + An optional comma-separated list of arguments to be provided to + the function when the trigger is executed. The arguments are + literal string constants. Simple names and numeric constants + may be written here, too, but they will all be converted to + strings. Please check the description of the implementation + language of the trigger function about how the trigger arguments + are accessible within the function; it may be different from + normal function arguments. + + + + + + + + Diagnostics + + + + CREATE TRIGGER + + + Message returned if the trigger was successfully created. + + + + + Notes @@ -219,13 +195,13 @@ CREATE TRIGGER necessary to declare trigger functions as returning the placeholder type opaque, rather than trigger. To support loading of old dump files, CREATE TRIGGER will accept a function - declared as returning opaque, but it will issue a NOTICE and + declared as returning opaque, but it will issue a notice and change the function's declared return type to trigger. - Refer to the command for - information on how to remove triggers. + Use to remove a trigger. @@ -233,111 +209,64 @@ CREATE TRIGGER Examples - Check if the specified distributor code exists in the distributors - table before appending or updating a row in the table films: - - -CREATE TRIGGER if_dist_exists - BEFORE INSERT OR UPDATE ON films FOR EACH ROW - EXECUTE PROCEDURE check_primary_key ('did', 'distributors', 'did'); - - - - - Before cancelling a distributor or updating its code, remove every - reference to the table films: - -CREATE TRIGGER if_film_exists - BEFORE DELETE OR UPDATE ON distributors FOR EACH ROW - EXECUTE PROCEDURE check_foreign_key (1, 'CASCADE', 'did', 'films', 'did'); - - - - - The second example can also be done by using a foreign key, - constraint as in: - - -CREATE TABLE distributors ( - did DECIMAL(3), - name VARCHAR(40), - CONSTRAINT if_film_exists - FOREIGN KEY(did) REFERENCES films - ON UPDATE CASCADE ON DELETE CASCADE -); - + contains a complete example. Compatibility - - - SQL92 + + The CREATE TRIGGER statement in + PostgreSQL implements a subset of the + SQL99 standard. (There are no provisions for triggers in SQL92.) + The following functionality is missing: + + - There is no CREATE TRIGGER statement in SQL92. + SQL99 allows triggers to fire on updates to specific columns + (e.g., AFTER UPDATE OF col1, col2). - - - SQL99 - The CREATE TRIGGER statement in - PostgreSQL implements a subset of the - SQL99 standard. The following functionality is missing: - - - - SQL99 allows triggers to fire on updates to specific columns - (e.g., AFTER UPDATE OF col1, col2). - - - - - - SQL99 allows you to define aliases for the old - and new rows or tables for use in the definition - of the triggered action (e.g., CREATE TRIGGER ... ON - tablename REFERENCING OLD ROW AS somename NEW ROW AS - othername ...). Since - PostgreSQL allows trigger - procedures to be written in any number of user-defined - languages, access to the data is handled in a - language-specific way. - - - - - - PostgreSQL only allows the - execution of a stored procedure for the triggered action. - SQL99 allows the execution of a number of other SQL commands, - such as CREATE TABLE as triggered action. - This limitation is not hard to work around by creating a - stored procedure that executes these commands. - - - - - - - SQL99 specifies that multiple triggers should be fired in - time-of-creation order. PostgreSQL - uses name order, which was judged more convenient to work with. + SQL99 allows you to define aliases for the old + and new rows or tables for use in the definition + of the triggered action (e.g., CREATE TRIGGER ... ON + tablename REFERENCING OLD ROW AS somename NEW ROW AS othername + ...). Since PostgreSQL + allows trigger procedures to be written in any number of + user-defined languages, access to the data is handled in a + language-specific way. + + - The ability to specify multiple actions for a single trigger - using OR is a PostgreSQL - extension of the SQL standard. + PostgreSQL only allows the execution + of a user-defined function for the triggered action. SQL99 + allows the execution of a number of other SQL commands, such as + CREATE TABLE as triggered action. This + limitation is not hard to work around by creating a user-defined + function that executes the desired commands. - - + + + + + SQL99 specifies that multiple triggers should be fired in + time-of-creation order. PostgreSQL uses + name order, which was judged more convenient to work with. + + + + The ability to specify multiple actions for a single trigger using + OR is a PostgreSQL extension of + the SQL standard. + diff --git a/doc/src/sgml/ref/create_type.sgml b/doc/src/sgml/ref/create_type.sgml index 6d6d8e30bcd..70a4a6cb808 100644 --- a/doc/src/sgml/ref/create_type.sgml +++ b/doc/src/sgml/ref/create_type.sgml @@ -1,5 +1,5 @@ @@ -8,22 +8,17 @@ PostgreSQL documentation CREATE TYPE SQL - Language Statements + - - CREATE TYPE - - - define a new data type - + CREATE TYPE + define a new data type + - - 1999-07-20 - - -CREATE TYPE typename ( INPUT = input_function, OUTPUT = output_function - , INTERNALLENGTH = { internallength | VARIABLE } + +CREATE TYPE typename ( + INPUT = input_function, OUTPUT = output_function + , INTERNALLENGTH = { internallength | VARIABLE } [ , DEFAULT = default ] [ , ELEMENT = element ] [ , DELIMITER = delimiter ] [ , PASSEDBYVALUE ] @@ -32,184 +27,26 @@ CREATE TYPE typename ( INPUT = typename AS - ( column_name data_type [, ... ] ) - - - - - 1998-09-21 - - - Inputs - - - - - - typename - - - The name (optionally schema-qualified) of a type to be created. - - - - - - internallength - - - A literal value, which specifies the internal length of - the new type. - - - - - - input_function - - - The name of a function, created by - CREATE FUNCTION, which - converts data from its external form to the type's - internal form. - - - - - - output_function - - - The name of a function, created by - CREATE FUNCTION, which - converts data from its internal form to a form suitable - for display. - - - - - - element - - - The type being created is an array; this specifies - the type of the array elements. - - - - - - delimiter - - - The delimiter character to be used between values in arrays made - of this type. - - - - - - default - - - The default value for the data type. Usually this is omitted, - so that the default is NULL. - - - - - - alignment - - - Storage alignment requirement of the data type. If specified, must - be char, int2, - int4, or double; - the default is int4. - - - - - - storage - - - Storage technique for the data type. If specified, must - be plain, external, - extended, or main; - the default is plain. - - - - - - column_name - - - The name of a column of the composite type. - - - - - - data_type - - - The name of an existing data type. - - - - - - - - - - - 1998-09-21 - - - Outputs - - - - - - -CREATE TYPE - - - - Message returned if the type is successfully created. - - - - - - + ( attribute_name data_type [, ... ] ) + - - - 1998-09-21 - - - Description - + + Description - CREATE TYPE allows the user to register a new data - type with PostgreSQL for use in the current data base. - The user who defines a type becomes its owner. + CREATE TYPE registers a new data type for use in + the current data base. The user who defines a type becomes its + owner. - If a schema name is given then the type is created in the - specified schema. Otherwise it is created in the current schema (the one - at the front of the search path; see CURRENT_SCHEMA()). - The type name must be distinct from the name of any existing type or - domain in the same schema. (Because tables have associated data types, - type names also must not conflict with table names in the same schema.) + If a schema name is given then the type is created in the specified + schema. Otherwise it is created in the current schema. The type + name must be distinct from the name of any existing type or domain + in the same schema. (Because tables have associated data types, + the type name must also be distinct from the name of any existing + table in the same schema.) @@ -220,74 +57,59 @@ CREATE TYPE (scalar type). It requires the registration of two functions (using CREATE FUNCTION) before defining the - type. The representation of a new base type is determined by + type. The internal representation of the new base type is determined by input_function, which - converts the type's external representation to an internal + converts the type's external representation to an internal representation usable by the - operators and functions defined for the type. Naturally, + operators and functions defined for the type. output_function performs the reverse transformation. The input function may be declared as taking one argument of type cstring, or as taking three arguments of types - cstring, OID, int4. - (The first argument is the input text as a C string, the second + cstring, oid, integer. + The first argument is the input text as a C string, the second argument is the element type in case this is an array 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. It 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 - two arguments of which the second is type OID. - (The second argument is again the array element type for array types.) + two arguments of which the second is type oid. + The second argument is again the array element type for array types. The output function should return type cstring. You should at this point be wondering how the input and output functions - can be declared to have results or inputs of the new type, when they have + can be declared to have results or arguments of the new type, when they have to be created before the new type can be created. The answer is that the input function must be created first, then the output function, then the data type. PostgreSQL will first see the name of the new data type as the return type of the input function. It will create a shell type, which is simply a placeholder entry in - pg_type, and link the input function definition to the shell + the system catalog, and link the input function definition to the shell type. Similarly the output function will be linked to the (now already existing) shell type. Finally, CREATE TYPE replaces the shell entry with a complete type definition, and the new type can be used. - - In PostgreSQL versions before 7.3, it was - customary to avoid creating a shell type by replacing the functions' - forward references to the type name with the placeholder pseudo-type - OPAQUE. The cstring inputs and - results also had to be declared as OPAQUE before 7.3. - To support loading - of old dump files, CREATE TYPE will accept functions - declared using opaque, but it will issue a NOTICE and - change the function's declaration to use the correct types. - - - - - New base data types can be fixed length, in which case + Base data types can be fixed-length, in which case internallength is a positive integer, or variable length, indicated by setting internallength - to . (Internally, this is represented + to VARIABLE. (Internally, this is represented by setting typlen to -1.) The internal representation of all - variable-length types must start with an integer giving the total + variable-length types must start with a 4-byte integer giving the total length of this value of the type. - To indicate that a type is an array, - specify the type of the array - elements using the @@ -295,29 +117,28 @@ CREATE TYPE representation of arrays of this type, delimiter can be set to a specific character. The default delimiter is the comma - (','). Note that the delimiter is associated + (,). Note that the delimiter is associated with the array element type, not the array type itself. A default value may be specified, in case a user wants columns of the - data type to default to something other than NULL. - Specify the default with the keyword. - (Such a default may be overridden by an explicit + data type to default to something other than the null value. + Specify the default with the DEFAULT key word. + (Such a default may be overridden by an explicit DEFAULT clause attached to a particular column.) - The optional flag, , indicates that - values of this data type are passed - by value rather than by reference. Note that you - may not pass by value types whose internal representation is - longer than the width of the Datum type (four bytes on - most machines, eight bytes on a few). + The optional flag PASSEDBYVALUE indicates that + values of this data type are passed by value rather than by + reference. You may not pass by value types whose internal + representation is larger than the size of the Datum type + (4 bytes on most machines, 8 bytes on a few). - The alignment keyword + The alignment parameter specifies the storage alignment required for the data type. The allowed values equate to alignment on 1, 2, 4, or 8 byte boundaries. Note that variable-length types must have an alignment of at least @@ -325,21 +146,22 @@ CREATE TYPE - The storage keyword - allows selection of storage strategies for variable-length data types - (only plain is allowed for fixed-length types). - plain disables TOAST for the data type: it will always - be stored in-line and not compressed. - extended gives full TOAST capability: the system will - first try to compress a long data value, and will move the value out of + The storage parameter + allows selection of storage strategies for variable-length data + types. (Only plain is allowed for fixed-length + types.) plain specifies that data of the type + will always be stored in-line and not compressed. + extended specifies that the system will first + try to compress a long data value, and will move the value out of the main table row if it's still too long. - external allows the value to be moved out of the main - table, but the system will not try to compress it. - main allows compression, but discourages moving the - value out of the main table. (Data items with this storage method may - still be moved out of the main table if there is no other way to make - a row fit, but they will be kept in the main table preferentially over - extended and external items.) + external allows the value to be moved out of the + main table, but the system will not try to compress it. + main allows compression, but discourages moving + the value out of the main table. (Data items with this storage + strategy may still be moved out of the main table if there is no + other way to make a row fit, but they will be kept in the main + table preferentially over extended and + external items.) @@ -349,7 +171,7 @@ CREATE TYPE The second form of CREATE TYPE creates a composite type. - The composite type is specified by a list of column names and data types. + The composite type is specified by a list of attribute names and data types. 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. @@ -373,19 +195,19 @@ CREATE TYPE - You might reasonably ask why is there an + You might reasonably ask why there is an + + + Parameter + + + + typename + + + The name (optionally schema-qualified) of a type to be created. + + + + + + internallength + + + A numeric constant that specifies the internal length of the new + type. + + + + + + input_function + + + The name of a function that converts data from the type's + external form to the its internal form. + + + + + + output_function + + + The name of a function that converts data from the type's + internal form to a form suitable for display. + + + + + + element + + + The type being created is an array; this specifies the type of + the array elements. + + + + + + delimiter + + + The delimiter character to be used between values in arrays made + of this type. + + + + + + default + + + The default value for the data type. If this is omitted, the + default is null. + + + + + + alignment + + + The storage alignment requirement of the data type. If specified, + it must be char, int2, + int4, or double; the + default is int4. + + + + + + storage + + + The storage strateg for the data type. If specified, must be + plain, external, + extended, or main; the + default is plain. + + + + + + attribute_name + + + The name of an attribute of the composite type. + + + + + + data_type + + + The name of an existing data type. + + + + + + + + Diagnostics + + + + CREATE TYPE + + + Message returned if the type was successfully created. + + + + + Notes - - User-defined type names cannot begin with the underscore character - (_) and can only be 62 - characters long (or in general NAMEDATALEN - 2, rather than - the NAMEDATALEN - 1 characters allowed for other names). - Type names beginning with underscore are - reserved for internally-created array type names. - + + User-defined type names cannot begin with the underscore character + (_) and can only be 62 characters + long (or in general NAMEDATALEN - 2, rather than + the NAMEDATALEN - 1 characters allowed for other + names). Type names beginning with underscore are reserved for + internally-created array type names. + + + + In PostgreSQL versions before 7.3, it + was customary to avoid creating a shell type by replacing the + functions' forward references to the type name with the placeholder + pseudotype opaque. The cstring arguments and + results also had to be declared as opaque before 7.3. To + support loading of old dump files, CREATE TYPE will + accept functions declared using opaque, but it will issue + a notice and change the function's declaration to use the correct + types. + Examples + - This example creates the box data type and then uses the + This example creates the data type box and then uses the type in a table definition: -CREATE TYPE box (INTERNALLENGTH = 16, - INPUT = my_procedure_1, OUTPUT = my_procedure_2); -CREATE TABLE myboxes (id INT4, description box); +CREATE TYPE box ( + INTERNALLENGTH = 16, + INPUT = my_box_in_function, + OUTPUT = my_box_out_function +); + +CREATE TABLE myboxes ( + id integer, + description box +); - If box's internal structure were an array of four - float4s, we might instead say + If the internal structure of box were an array of four + float4 elements, we might instead use -CREATE TYPE box (INTERNALLENGTH = 16, - INPUT = my_procedure_1, OUTPUT = my_procedure_2, - ELEMENT = float4); +CREATE TYPE box ( + INTERNALLENGTH = 16, + INPUT = my_box_in_function, + OUTPUT = my_box_out_function, + ELEMENT = float4 +); - which would allow a box value's component floats to be accessed + which would allow a box value's component numbers to be accessed by subscripting. Otherwise the type behaves the same as before. @@ -436,20 +413,30 @@ CREATE TYPE box (INTERNALLENGTH = 16, This example creates a large object type and uses it in a table definition: -CREATE TYPE bigobj (INPUT = lo_filein, OUTPUT = lo_fileout, - INTERNALLENGTH = VARIABLE); -CREATE TABLE big_objs (id int4, obj bigobj); +CREATE TYPE bigobj ( + INPUT = lo_filein, OUTPUT = lo_fileout, + INTERNALLENGTH = VARIABLE +); +CREATE TABLE big_objs ( + id integer, + obj bigobj +); This example creates a composite type and uses it in - a table function definition: + 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; + + + More examples, including suitable input and output functions, are + in . + diff --git a/doc/src/sgml/ref/create_user.sgml b/doc/src/sgml/ref/create_user.sgml index 522ea5acfbc..23d64bd2c54 100644 --- a/doc/src/sgml/ref/create_user.sgml +++ b/doc/src/sgml/ref/create_user.sgml @@ -1,5 +1,5 @@ @@ -33,16 +33,17 @@ where option can be: Description - CREATE USER will add a new user to an instance - of PostgreSQL. Refer to - for information about managing users and authentication. You must - be a database superuser to use this command. + CREATE USER adds a new user to a + PostgreSQL database cluster. Refer to + and for information about managing + users and authentication. You must be a database superuser to use + this command. + - - Parameters - - + + Parameters @@ -60,9 +61,9 @@ where option can be: The SYSID clause can be used to choose the PostgreSQL user ID of the user that - is being created. It is not at all necessary that those match - the Unix user IDs, but some people choose to keep the numbers - the same. + is being created. This is not 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 @@ -76,10 +77,11 @@ where option can be: Sets the user's password. If you do not plan to use password - authentication you can omit this option, but the user - won't be able to connect to a password-authenticated server. - The password can be set or changed later, using - . + 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 . @@ -89,23 +91,22 @@ where option can be: UNENCRYPTED - These keywords control whether the password is stored - encrypted in pg_shadow. (If neither is specified, - the default behavior is determined by the - PASSWORD_ENCRYPTION server parameter.) If - the presented string is already in MD5-encrypted format, then - it is stored as-is, regardless of whether - ENCRYPTED or UNENCRYPTED is specified. - This allows reloading of encrypted passwords during - dump/restore. + 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 password_encryption.) 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. - See - for details on how to set up authentication mechanisms. Note - that older clients may lack support for the MD5 authentication - mechanism that is needed to work with passwords that are - stored encrypted. + Note that older clients may lack support for the MD5 + authentication mechanism that is needed to work with passwords + that are stored encrypted. @@ -160,26 +161,21 @@ where option can be: - - - Diagnostics - CREATE USER - Message returned if the command completes successfully. + Message returned if the user account was successfully created. - @@ -191,9 +187,12 @@ where option can be: endterm="SQL-DROPUSER-title"> to remove a user. Use to add the user to groups or remove the user from groups. + + + PostgreSQL includes a program that has - the same functionality as this command (in fact, it calls this + the same functionality as CREATE USER (in fact, it calls this command) but can be run from the command shell. @@ -216,12 +215,12 @@ CREATE USER davide WITH PASSWORD 'jw8s0F4'; - Create a user with a password, whose account is valid until the end of 2001. - Note that after one second has ticked in 2002, the account is not - valid: + Create a user with a password that is valid until the end of 2004. + After one second has ticked in 2005, the password is no longer + valid. -CREATE USER miriam WITH PASSWORD 'jw8s0F4' VALID UNTIL 'Jan 1 2002'; +CREATE USER miriam WITH PASSWORD 'jw8s0F4' VALID UNTIL '2005-01-01'; diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml index 387dd07f9e6..8bfb4f16636 100644 --- a/doc/src/sgml/ref/create_view.sgml +++ b/doc/src/sgml/ref/create_view.sgml @@ -1,5 +1,5 @@ @@ -8,136 +8,26 @@ PostgreSQL documentation CREATE VIEW SQL - Language Statements + - - CREATE VIEW - - - define a new view - + CREATE VIEW + define a new view - - - 2000-03-25 - - -CREATE [ OR REPLACE ] VIEW view [ ( column name list ) ] AS SELECT query - - - - - 2000-03-25 - - - Inputs - - - - - - view - - - The name (optionally schema-qualified) of a view to be created. - - - - - column name list - - - An optional list of names to be used for columns of the view. - If given, these names override the column names that would be - deduced from the SQL query. - - - - - query - - - An SQL query (that is, a SELECT statement) - which will provide the columns and rows of the view. - - - Refer to for more information - about valid arguments. - - - - - - - - - - 2000-03-25 - - - Outputs - - - - - -CREATE VIEW - - - - The message returned if the view is successfully created. - - - - - -ERROR: Relation 'view' already exists - - - - This error occurs if the view specified already exists in the database. - - - - - -WARNING: Attribute 'column' has an unknown type - - - - The view will be created having a column with an unknown type - if you do not specify it. For example, the following command gives - a warning: - -CREATE VIEW vista AS SELECT 'Hello World' - - - whereas this command does not: - -CREATE VIEW vista AS SELECT text 'Hello World' - - - - - - - + + +CREATE [ OR REPLACE ] VIEW name [ ( column_name [, ...] ) ] AS query + - - - 2000-03-25 - - - Description - + + Description - CREATE VIEW defines a view of a query. - The view is not physically materialized. Instead, a query - rewrite rule (an ON SELECT rule) is automatically generated to - support SELECT operations on views. + CREATE VIEW defines a view of a query. The view + is not physically materialized. Instead, the query is run everytime + the view is referenced in a query. @@ -150,19 +40,87 @@ CREATE VIEW vista AS SELECT text 'Hello World' If a schema name is given (for example, CREATE VIEW myschema.myview ...) then the view is created in the - specified schema. Otherwise it is created in the current schema (the one - at the front of the search path; see CURRENT_SCHEMA()). + specified schema. Otherwise it is created in the current schema. The view name must be distinct from the name of any other view, table, sequence, or index in the same schema. + - - - 2000-03-25 - - - Notes - + + Parameters + + + + name + + + The name (optionally schema-qualified) of a view to be created. + + + + + + column_name + + + An optional list of names to be used for columns of the view. + If not given, the column names are deduced from the query. + + + + + + query + + + A query (that is, a SELECT statement) which will + provide the columns and rows of the view. + + + + Refer to + for more information about valid queries. + + + + + + + + Diagnostics + + + + CREATE VIEW + + + Message returned if the view was successfully created. + + + + + + WARNING: Attribute 'column' has an unknown type + + + The view will be created having a column with an unknown type if + you do not specify it. For example, the following command gives + this warning: + +CREATE VIEW vista AS SELECT 'Hello World' + + whereas this command does not: + +CREATE VIEW vista AS SELECT text 'Hello World' + + + + + + + + + Notes Currently, views are read only: the system will not allow an insert, @@ -175,100 +133,79 @@ CREATE VIEW vista AS SELECT text 'Hello World' Use the DROP VIEW statement to drop views. - - - - Usage - + + Examples + - Create a view consisting of all Comedy films: + Create a view consisting of all comedy films: - -CREATE VIEW kinds AS + +CREATE VIEW comedies AS SELECT * FROM films WHERE kind = 'Comedy'; - -SELECT * FROM kinds; - - code | title | did | date_prod | kind | len --------+---------------------------+-----+------------+--------+------- - UA502 | Bananas | 105 | 1971-07-13 | Comedy | 01:22 - C_701 | There's a Girl in my Soup | 107 | 1970-06-11 | Comedy | 01:36 -(2 rows) - + - - - Compatibility - - - - - 2000-03-25 - - - SQL92 - + + Compatibility - - SQL92 specifies some additional capabilities for the - CREATE VIEW statement: - - -CREATE VIEW view [ column [, ...] ] - AS SELECT expression [ AS colname ] [, ...] - FROM table [ WHERE condition ] + + The SQL standard specifies some additional capabilities for the + CREATE VIEW statement: + +CREATE VIEW name [ ( column [, ...] ) ] + AS query [ WITH [ CASCADE | LOCAL ] CHECK OPTION ] - + + - - The optional clauses for the full SQL92 command are: + + The optional clauses for the full SQL command are: - CHECK OPTION + CHECK OPTION - This option is to do with updatable views. - All INSERT and UPDATE commands on the view will be - checked to ensure data satisfy the view-defining - condition. If they do not, the update will be rejected. + This option is 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 + view). If they do not, the update will be rejected. - LOCAL + LOCAL - Check for integrity on this view. + Check for integrity on this view. - CASCADE + CASCADE - Check for integrity on this view and on any dependent - view. CASCADE is assumed if neither CASCADE nor LOCAL is specified. + Check for integrity on this view and on any dependent + view. CASCADE is assumed if neither + CASCADE nor LOCAL is specified. - - - - - CREATE OR REPLACE VIEW is a - PostgreSQL language extension. - + + - + + CREATE OR REPLACE VIEW is a + PostgreSQL language extension. + -- 2.39.5