diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/func.sgml | 83 | ||||
| -rw-r--r-- | doc/src/sgml/syntax.sgml | 81 | ||||
| -rw-r--r-- | doc/src/sgml/xfunc.sgml | 35 |
3 files changed, 162 insertions, 37 deletions
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 9e306a99282..1dde8b59a3c 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.200 2004/05/10 21:08:28 neilc Exp $ +$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.201 2004/05/10 22:44:42 tgl Exp $ PostgreSQL documentation --> @@ -7822,13 +7822,15 @@ SELECT col1 FROM tab1 </para> <synopsis> -(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) IN (<replaceable>subquery</replaceable>) +<replaceable>row_constructor</replaceable> IN (<replaceable>subquery</replaceable>) </synopsis> <para> - The right-hand side of this form of <token>IN</token> is a parenthesized + The left-hand side of this form of <token>IN</token> is a row constructor, + as described in <xref linkend="sql-syntax-row-constructors">. + The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are - expressions in the left-hand list. The left-hand expressions are + expressions in the left-hand row. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result. The result of <token>IN</token> is <quote>true</> if any equal subquery row is found. The result is <quote>false</> if no equal row is found (including the special @@ -7876,13 +7878,15 @@ SELECT col1 FROM tab1 </para> <synopsis> -(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) NOT IN (<replaceable>subquery</replaceable>) +<replaceable>row_constructor</replaceable> NOT IN (<replaceable>subquery</replaceable>) </synopsis> <para> - The right-hand side of this form of <token>NOT IN</token> is a parenthesized + The left-hand side of this form of <token>NOT IN</token> is a row constructor, + as described in <xref linkend="sql-syntax-row-constructors">. + The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are - expressions in the left-hand list. The left-hand expressions are + expressions in the left-hand row. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result. The result of <token>NOT IN</token> is <quote>true</> if only unequal subquery rows are found (including the special case where the subquery returns no rows). @@ -7938,14 +7942,16 @@ SELECT col1 FROM tab1 </para> <synopsis> -(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>operator</> ANY (<replaceable>subquery</replaceable>) -(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>operator</> SOME (<replaceable>subquery</replaceable>) +<replaceable>row_constructor</replaceable> <replaceable>operator</> ANY (<replaceable>subquery</replaceable>) +<replaceable>row_constructor</replaceable> <replaceable>operator</> SOME (<replaceable>subquery</replaceable>) </synopsis> <para> - The right-hand side of this form of <token>ANY</token> is a parenthesized + The left-hand side of this form of <token>ANY</token> is a row constructor, + as described in <xref linkend="sql-syntax-row-constructors">. + The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are - expressions in the left-hand list. The left-hand expressions are + expressions in the left-hand row. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result, using the given <replaceable>operator</replaceable>. Presently, only <literal>=</literal> and <literal><></literal> operators are allowed @@ -8003,13 +8009,15 @@ SELECT col1 FROM tab1 </para> <synopsis> -(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>operator</replaceable> ALL (<replaceable>subquery</replaceable>) +<replaceable>row_constructor</replaceable> <replaceable>operator</replaceable> ALL (<replaceable>subquery</replaceable>) </synopsis> <para> - The right-hand side of this form of <token>ALL</token> is a parenthesized + The left-hand side of this form of <token>ALL</token> is a row constructor, + as described in <xref linkend="sql-syntax-row-constructors">. + The right-hand side is a parenthesized subquery, which must return exactly as many columns as there are - expressions in the left-hand list. The left-hand expressions are + expressions in the left-hand row. The left-hand expressions are evaluated and compared row-wise to each row of the subquery result, using the given <replaceable>operator</replaceable>. Presently, only <literal>=</literal> and <literal><></literal> operators are allowed @@ -8041,16 +8049,17 @@ SELECT col1 FROM tab1 </indexterm> <synopsis> -(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>operator</replaceable> (<replaceable>subquery</replaceable>) +<replaceable>row_constructor</replaceable> <replaceable>operator</replaceable> (<replaceable>subquery</replaceable>) </synopsis> <para> - The left-hand side is a list of scalar expressions. The right-hand side is - a parenthesized subquery, which must return exactly as many columns as there - are expressions on the left-hand side. Furthermore, the subquery cannot - return more than one row. (If it returns zero rows, the result is taken to - be null.) The left-hand side is evaluated and compared row-wise to the - single subquery result row. + The left-hand side is a row constructor, + as described in <xref linkend="sql-syntax-row-constructors">. + The right-hand side is a parenthesized subquery, which must return exactly + as many columns as there are expressions in the left-hand row. Furthermore, + the subquery cannot return more than one row. (If it returns zero rows, + the result is taken to be null.) The left-hand side is evaluated and + compared row-wise to the single subquery result row. Presently, only <literal>=</literal> and <literal><></literal> operators are allowed in row-wise comparisons. The result is <quote>true</> if the two rows are equal or unequal, respectively. @@ -8223,13 +8232,14 @@ AND <title>Row-wise Comparison</title> <synopsis> -(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>operator</replaceable> (<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) +<replaceable>row_constructor</replaceable> <replaceable>operator</replaceable> <replaceable>row_constructor</replaceable> </synopsis> <para> - Each side is a list of scalar expressions; the two lists must be - of the same length. Each side is evaluated and they are compared - row-wise. + Each side is a row constructor, + as described in <xref linkend="sql-syntax-row-constructors">. + The two row values must have the same number of fields. + Each side is evaluated and they are compared row-wise. Presently, only <literal>=</literal> and <literal><></literal> operators are allowed in row-wise comparisons. The result is <quote>true</> if the two rows are equal or unequal, respectively. @@ -8242,6 +8252,29 @@ AND are unequal if any corresponding members are non-null and unequal; otherwise the result of the row comparison is unknown (null). </para> + +<synopsis> +<replaceable>row_constructor</replaceable> IS DISTINCT FROM <replaceable>row_constructor</replaceable> +</synopsis> + + <para> + This construct is similar to a <literal><></literal> row comparison, + but it does not yield null for null inputs. Instead, any null value is + considered unequal to (distinct from) any non-null value, and any two + nulls are considered equal (not distinct). Thus the result will always + be either true or false, never null. + </para> + +<synopsis> +<replaceable>row_constructor</replaceable> IS NULL +<replaceable>row_constructor</replaceable> IS NOT NULL +</synopsis> + + <para> + These constructs test a row value for null or not null. A row value + is considered not null if it has at least one field that is not null. + </para> + </sect2> </sect1> diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index c6093b84637..24a01891d31 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.90 2004/03/12 00:25:40 neilc Exp $ +$PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.91 2004/05/10 22:44:43 tgl Exp $ --> <chapter id="sql-syntax"> @@ -922,6 +922,12 @@ SELECT 3 OPERATOR(pg_catalog.+) 4; <listitem> <para> + A row constructor. + </para> + </listitem> + + <listitem> + <para> Another value expression in parentheses, useful to group subexpressions and override precedence.<indexterm><primary>parenthesis</></> @@ -1428,6 +1434,79 @@ SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%'); </sect2> + <sect2 id="sql-syntax-row-constructors"> + <title>Row Constructors</title> + + <indexterm> + <primary>row</primary> + <secondary>constructor</secondary> + </indexterm> + + <para> + A row constructor is an expression that builds a row value from values + for its member fields. A row constructor consists of the key word + <literal>ROW</literal>, a left parenthesis <literal>(</>, zero or more + expressions (separated by commas) for the row field values, and finally + a right parenthesis <literal>)</>. For example, +<programlisting> +SELECT myfunc(ROW(1,2.5,'this is a test')); +</programlisting> + The key word <literal>ROW</> is optional when there is more than one + expression in the list. + </para> + + <para> + By default, the value created by a <literal>ROW</> expression is of + an anonymous record type. If necessary, it can be cast to a named + composite type --- either the rowtype of a table, or a composite type + created with <command>CREATE TYPE AS</>. An explicit cast may be needed + to avoid ambiguity. For example: +<programlisting> +CREATE TABLE mytable(f1 int, f2 float, f3 text); +CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; +-- No cast needed since only one getf1() exists +SELECT getf1(ROW(1,2.5,'this is a test')); + getf1 +------- + 1 +(1 row) + +CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric); +CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; +-- Now we need a cast to indicate which function to call: +SELECT getf1(ROW(1,2.5,'this is a test')); +ERROR: function getf1(record) is not unique +SELECT getf1(ROW(1,2.5,'this is a test')::mytable); + getf1 +------- + 1 +(1 row) + +SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype)); + getf1 +------- + 11 +(1 row) +</programlisting> + </para> + + <para> + Row constructors have only limited uses, other than creating an argument + value for a user-defined function that accepts a rowtype parameter, as + illustrated above. + It is possible to compare two row values or test a row with + <literal>IS NULL</> or <literal>IS NOT NULL</>, for example +<programlisting> +SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same'); +SELECT ROW(a, b, c) IS NOT NULL FROM table; +</programlisting> + For more detail see <xref linkend="functions-comparisons">. + Row constructors can also be used in connection with subqueries, + as discussed in <xref linkend="functions-subquery">. + </para> + + </sect2> + <sect2 id="syntax-express-eval"> <title>Expression Evaluation Rules</title> diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 4b06aefd362..a3c24a1c4c1 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -1,5 +1,5 @@ <!-- -$PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.81 2004/04/01 21:28:43 tgl Exp $ +$PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.82 2004/05/10 22:44:43 tgl Exp $ --> <sect1 id="xfunc"> @@ -240,10 +240,11 @@ SELECT clean_emp(); <title><acronym>SQL</acronym> Functions on Composite Types</title> <para> - When specifying functions with arguments of composite + When writing functions with arguments of composite types, we must not only specify which argument we want (as we did above with <literal>$1</> and <literal>$2</literal>) but - also the attributes of that argument. For example, suppose that + also the desired attribute (field) of that argument. For example, + suppose that <type>emp</type> is a table containing employee data, and therefore also the name of the composite type of each row of the table. Here is a function <function>double_salary</function> that computes what someone's @@ -252,16 +253,16 @@ SELECT clean_emp(); <screen> CREATE TABLE emp ( name text, - salary integer, + salary numeric, age integer, cubicle point ); -CREATE FUNCTION double_salary(emp) RETURNS integer AS ' +CREATE FUNCTION double_salary(emp) RETURNS numeric AS ' SELECT $1.salary * 2 AS salary; ' LANGUAGE SQL; -SELECT name, double_salary(emp) AS dream +SELECT name, double_salary(emp.*) AS dream FROM emp WHERE emp.cubicle ~= point '(2,1)'; @@ -274,15 +275,27 @@ SELECT name, double_salary(emp) AS dream <para> Notice the use of the syntax <literal>$1.salary</literal> to select one field of the argument row value. Also notice - how the calling <command>SELECT</> command uses a table name to denote - the entire current row of that table as a composite value. The table - row can alternatively be referenced like this: + how the calling <command>SELECT</> command uses <literal>*</> + to select + the entire current row of a table as a composite value. The table + row can alternatively be referenced using just the table name, + like this: <screen> -SELECT name, double_salary(emp.*) AS dream +SELECT name, double_salary(emp) AS dream FROM emp WHERE emp.cubicle ~= point '(2,1)'; </screen> - which emphasizes its row nature. + but this usage is deprecated since it's easy to get confused. + </para> + + <para> + Sometimes it is handy to construct a composite argument value + on-the-fly. This can be done with the <literal>ROW</> construct. + For example, we could adjust the data being passed to the function: +<screen> +SELECT name, double_salary(row(name, salary*1.1, age, cubicle)) AS dream + FROM emp; +</screen> </para> <para> |