diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/config.sgml | 22 | ||||
| -rw-r--r-- | doc/src/sgml/ddl.sgml | 37 | ||||
| -rw-r--r-- | doc/src/sgml/queries.sgml | 10 | ||||
| -rw-r--r-- | doc/src/sgml/ref/alter_table.sgml | 12 | ||||
| -rw-r--r-- | doc/src/sgml/ref/delete.sgml | 27 | ||||
| -rw-r--r-- | doc/src/sgml/ref/lock.sgml | 10 | ||||
| -rw-r--r-- | doc/src/sgml/ref/select.sgml | 33 | ||||
| -rw-r--r-- | doc/src/sgml/ref/truncate.sgml | 12 | ||||
| -rw-r--r-- | doc/src/sgml/ref/update.sgml | 14 |
9 files changed, 104 insertions, 73 deletions
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index b8eaac39eaf..8ec8a6f6635 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -5236,11 +5236,23 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' <indexterm><primary>inheritance</></> <listitem> <para> - This controls the inheritance semantics. If turned <literal>off</>, - subtables are not accessed by various commands by default; basically - an implied <literal>ONLY</literal> key word. This was added for - compatibility with releases prior to 7.1. See - <xref linkend="ddl-inherit"> for more information. + This setting controls whether undecorated table references are + considered to include inheritance child tables. The default is + <literal>on</>, which means child tables are included (thus, + a <literal>*</> suffix is assumed by default). If turned + <literal>off</>, child tables are not included (thus, an + <literal>ONLY</literal> prefix is assumed). The SQL standard + requires child tables to be included, so the <literal>off</> setting + is not spec-compliant, but it is provided for compatibility with + <productname>PostgreSQL</> releases prior to 7.1. + See <xref linkend="ddl-inherit"> for more information. + </para> + + <para> + Turning <varname>sql_inheritance</> off is deprecated, because that + behavior has been found to be error-prone as well as contrary to SQL + standard. Discussions of inheritance behavior elsewhere in this + manual generally assume that it is <literal>on</>. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 038f92e1d52..d0fcdc74e0b 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -2051,6 +2051,23 @@ SELECT name, altitude </para> <para> + You can also write the table name with a trailing <literal>*</> + to explicitly specify that descendant tables are included: + +<programlisting> +SELECT name, altitude + FROM cities* + WHERE altitude > 500; +</programlisting> + + Writing <literal>*</> is not necessary, since this behavior is + the default (unless you have changed the setting of the + <xref linkend="guc-sql-inheritance"> configuration option). + However writing <literal>*</> might be useful to emphasize that + additional tables will be searched. + </para> + + <para> In some cases you might wish to know which table a particular row originated from. There is a system column called <structfield>tableoid</structfield> in each table which can tell you the @@ -2197,15 +2214,15 @@ VALUES ('New York', NULL, NULL, 'NY'); data modification, or schema modification (e.g., <literal>SELECT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>, most variants of <literal>ALTER TABLE</literal>, but - not <literal>INSERT</literal> and <literal>ALTER TABLE ... + not <literal>INSERT</literal> or <literal>ALTER TABLE ... RENAME</literal>) typically default to including child tables and support the <literal>ONLY</literal> notation to exclude them. Commands that do database maintenance and tuning (e.g., <literal>REINDEX</literal>, <literal>VACUUM</literal>) - typically only work on individual, physical tables and do no + typically only work on individual, physical tables and do not support recursing over inheritance hierarchies. The respective - behavior of each individual command is documented in the reference - part (<xref linkend="sql-commands">). + behavior of each individual command is documented in its reference + page (<xref linkend="sql-commands">). </para> <para> @@ -2255,18 +2272,6 @@ VALUES ('New York', NULL, NULL, 'NY'); inheritance is useful for your application. </para> - <note> - <title>Deprecated</title> - <para> - In releases of <productname>PostgreSQL</productname> prior to 7.1, the - default behavior was not to include child tables in queries. This was - found to be error prone and also in violation of the SQL - standard. You can get the pre-7.1 behavior by turning off the - <xref linkend="guc-sql-inheritance"> configuration - option. - </para> - </note> - </sect2> </sect1> diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index 1e29852b40e..80fbc096c1b 100644 --- a/doc/src/sgml/queries.sgml +++ b/doc/src/sgml/queries.sgml @@ -140,6 +140,16 @@ FROM <replaceable>table_reference</replaceable> <optional>, <replaceable>table_r — any columns added in subtables are ignored. </para> + <para> + Instead of writing <literal>ONLY</> before the table name, you can write + <literal>*</> after the table name to explicitly specify that descendant + tables are included. Writing <literal>*</> is not necessary since that + behavior is the default (unless you have changed the setting of the <xref + linkend="guc-sql-inheritance"> configuration option). However writing + <literal>*</> might be useful to emphasize that additional tables will be + searched. + </para> + <sect3 id="queries-join"> <title>Joined Tables</title> diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index a9fc887c778..94343f88f6e 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -490,10 +490,12 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable> <term><replaceable class="PARAMETER">name</replaceable></term> <listitem> <para> - The name (possibly schema-qualified) of an existing table to - alter. If <literal>ONLY</> is specified, only that table is - altered. If <literal>ONLY</> is not specified, the table and any - descendant tables are altered. + The name (optionally schema-qualified) of an existing table to + alter. If <literal>ONLY</> is specified before the table name, only + that table is altered. If <literal>ONLY</> is not specified, the table + and all its descendant tables (if any) are altered. Optionally, + <literal>*</> can be specified after the table name to explicitly + indicate that descendant tables are included. </para> </listitem> </varlistentry> @@ -877,7 +879,7 @@ ALTER TABLE distributors DROP CONSTRAINT zipchk; </para> <para> - To remove a check constraint from a table only: + To remove a check constraint from one table only: <programlisting> ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk; </programlisting> diff --git a/doc/src/sgml/ref/delete.sgml b/doc/src/sgml/ref/delete.sgml index 84bf6bd23f9..6c641b0b30a 100644 --- a/doc/src/sgml/ref/delete.sgml +++ b/doc/src/sgml/ref/delete.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ] +DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ] [ USING <replaceable class="PARAMETER">using_list</replaceable> ] [ WHERE <replaceable class="PARAMETER">condition</replaceable> | WHERE CURRENT OF <replaceable class="PARAMETER">cursor_name</replaceable> ] [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ] @@ -47,13 +47,6 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] </tip> <para> - By default, <command>DELETE</command> will delete rows in the - specified table and all its child tables. If you wish to delete only - from the specific table mentioned, you must use the - <literal>ONLY</literal> clause. - </para> - - <para> There are two ways to delete rows in a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the <literal>USING</literal> clause. @@ -84,20 +77,16 @@ DELETE FROM [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <variablelist> <varlistentry> - <term><literal>ONLY</></term> - <listitem> - <para> - If specified, delete rows from the named table only. When not - specified, any tables inheriting from the named table are also processed. - </para> - </listitem> - </varlistentry> - - <varlistentry> <term><replaceable class="parameter">table</replaceable></term> <listitem> <para> - The name (optionally schema-qualified) of an existing table. + The name (optionally schema-qualified) of the table to delete rows + from. If <literal>ONLY</> is specified before the table name, + matching rows are deleted from the named table only. If + <literal>ONLY</> is not specified, matching rows are also deleted + from any tables inheriting from the named table. Optionally, + <literal>*</> can be specified after the table name to explicitly + indicate that descendant tables are included. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/lock.sgml b/doc/src/sgml/ref/lock.sgml index a61cb98c7a3..a7f7551c566 100644 --- a/doc/src/sgml/ref/lock.sgml +++ b/doc/src/sgml/ref/lock.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -LOCK [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [, ...] [ IN <replaceable class="PARAMETER">lockmode</replaceable> MODE ] [ NOWAIT ] +LOCK [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ] [, ...] [ IN <replaceable class="PARAMETER">lockmode</replaceable> MODE ] [ NOWAIT ] <phrase>where <replaceable class="PARAMETER">lockmode</replaceable> is one of:</phrase> @@ -109,9 +109,11 @@ LOCK [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [, ... <listitem> <para> The name (optionally schema-qualified) of an existing table to - lock. If <literal>ONLY</> is specified, only that table is - locked. If <literal>ONLY</> is not specified, the table and all - its descendant tables (if any) are locked. + lock. If <literal>ONLY</> is specified before the table name, only that + table is locked. If <literal>ONLY</> is not specified, the table and all + its descendant tables (if any) are locked. Optionally, <literal>*</> + can be specified after the table name to explicitly indicate that + descendant tables are included. </para> <para> diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 3fbfda309e2..0297415bdc6 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -268,10 +268,12 @@ TABLE { [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] | <term><replaceable class="parameter">table_name</replaceable></term> <listitem> <para> - The name (optionally schema-qualified) of an existing table or - view. If <literal>ONLY</> is specified, only that table is - scanned. If <literal>ONLY</> is not specified, the table and - any descendant tables are scanned. + The name (optionally schema-qualified) of an existing table or view. + If <literal>ONLY</> is specified before the table name, only that + table is scanned. If <literal>ONLY</> is not specified, the table + and all its descendant tables (if any) are scanned. Optionally, + <literal>*</> can be specified after the table name to explicitly + indicate that descendant tables are included. </para> </listitem> </varlistentry> @@ -1555,15 +1557,24 @@ SELECT distributors.* WHERE distributors.name = 'Westward'; </refsect2> <refsect2> - <title><literal>ONLY</literal> and Parentheses</title> + <title><literal>ONLY</literal> and Inheritance</title> <para> - The SQL standard requires parentheses around the table name - after <literal>ONLY</literal>, as in <literal>SELECT * FROM ONLY - (tab1), ONLY (tab2) WHERE ...</literal>. PostgreSQL supports that - as well, but the parentheses are optional. (This point applies - equally to all SQL commands supporting the <literal>ONLY</literal> - option.) + The SQL standard requires parentheses around the table name when + writing <literal>ONLY</literal>, for example <literal>SELECT * FROM ONLY + (tab1), ONLY (tab2) WHERE ...</literal>. <productname>PostgreSQL</> + considers these parentheses to be optional. + </para> + + <para> + <productname>PostgreSQL</> allows a trailing <literal>*</> to be written to + explicitly specify the non-<literal>ONLY</literal> behavior of including + child tables. The standard does not allow this. + </para> + + <para> + (These points apply equally to all SQL commands supporting the + <literal>ONLY</literal> option.) </para> </refsect2> diff --git a/doc/src/sgml/ref/truncate.sgml b/doc/src/sgml/ref/truncate.sgml index 08dc670843a..99202de5b49 100644 --- a/doc/src/sgml/ref/truncate.sgml +++ b/doc/src/sgml/ref/truncate.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [, ... ] +TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ] [, ... ] [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ] </synopsis> </refsynopsisdiv> @@ -47,10 +47,12 @@ TRUNCATE [ TABLE ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [, <term><replaceable class="PARAMETER">name</replaceable></term> <listitem> <para> - The name (optionally schema-qualified) of a table to be - truncated. If <literal>ONLY</> is specified, only that table is - truncated. If <literal>ONLY</> is not specified, the table and - all its descendant tables (if any) are truncated. + The name (optionally schema-qualified) of a table to truncate. + If <literal>ONLY</> is specified before the table name, only that table + is truncated. If <literal>ONLY</> is not specified, the table and all + its descendant tables (if any) are truncated. Optionally, <literal>*</> + can be specified after the table name to explicitly indicate that + descendant tables are included. </para> </listitem> </varlistentry> diff --git a/doc/src/sgml/ref/update.sgml b/doc/src/sgml/ref/update.sgml index 8673df18ff4..10045c1683b 100644 --- a/doc/src/sgml/ref/update.sgml +++ b/doc/src/sgml/ref/update.sgml @@ -21,7 +21,7 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> -UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ] +UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> ] SET { <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } | ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) } [, ...] [ FROM <replaceable class="PARAMETER">from_list</replaceable> ] @@ -41,13 +41,6 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep </para> <para> - By default, <command>UPDATE</command> will update rows in the - specified table and all its subtables. If you wish to only update - the specific table mentioned, you must use the <literal>ONLY</> - clause. - </para> - - <para> There are two ways to modify a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the <literal>FROM</literal> clause. Which @@ -84,6 +77,11 @@ UPDATE [ ONLY ] <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <rep <listitem> <para> The name (optionally schema-qualified) of the table to update. + If <literal>ONLY</> is specified before the table name, matching rows + are updated in the named table only. If <literal>ONLY</> is not + specified, matching rows are also updated in any tables inheriting from + the named table. Optionally, <literal>*</> can be specified after the + table name to explicitly indicate that descendant tables are included. </para> </listitem> </varlistentry> |