From 2f63232d30ca64a8f2684af855230f23a701d371 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 10 May 2004 22:44:49 +0000 Subject: Promote row expressions to full-fledged citizens of the expression syntax, rather than allowing them only in a few special cases as before. In particular you can now pass a ROW() construct to a function that accepts a rowtype parameter. Internal generation of RowExprs fixes a number of corner cases that used to not work very well, such as referencing the whole-row result of a JOIN or subquery. This represents a further step in the work I started a month or so back to make rowtype values into first-class citizens. --- doc/src/sgml/func.sgml | 83 +++++++++++++++++++++++++++++++++--------------- doc/src/sgml/syntax.sgml | 81 +++++++++++++++++++++++++++++++++++++++++++++- doc/src/sgml/xfunc.sgml | 35 +++++++++++++------- 3 files changed, 162 insertions(+), 37 deletions(-) (limited to 'doc/src') 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 @@ @@ -7822,13 +7822,15 @@ SELECT col1 FROM tab1 -(expression , expression ...) IN (subquery) +row_constructor IN (subquery) - The right-hand side of this form of IN is a parenthesized + The left-hand side of this form of IN is a row constructor, + as described in . + 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 IN is true if any equal subquery row is found. The result is false if no equal row is found (including the special @@ -7876,13 +7878,15 @@ SELECT col1 FROM tab1 -(expression , expression ...) NOT IN (subquery) +row_constructor NOT IN (subquery) - The right-hand side of this form of NOT IN is a parenthesized + The left-hand side of this form of NOT IN is a row constructor, + as described in . + 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 NOT IN is 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 -(expression , expression ...) operator ANY (subquery) -(expression , expression ...) operator SOME (subquery) +row_constructor operator ANY (subquery) +row_constructor operator SOME (subquery) - The right-hand side of this form of ANY is a parenthesized + The left-hand side of this form of ANY is a row constructor, + as described in . + 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 operator. Presently, only = and <> operators are allowed @@ -8003,13 +8009,15 @@ SELECT col1 FROM tab1 -(expression , expression ...) operator ALL (subquery) +row_constructor operator ALL (subquery) - The right-hand side of this form of ALL is a parenthesized + The left-hand side of this form of ALL is a row constructor, + as described in . + 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 operator. Presently, only = and <> operators are allowed @@ -8041,16 +8049,17 @@ SELECT col1 FROM tab1 -(expression , expression ...) operator (subquery) +row_constructor operator (subquery) - 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 . + 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 = and <> operators are allowed in row-wise comparisons. The result is true if the two rows are equal or unequal, respectively. @@ -8223,13 +8232,14 @@ AND Row-wise Comparison -(expression , expression ...) operator (expression , expression ...) +row_constructor operator row_constructor - 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 . + The two row values must have the same number of fields. + Each side is evaluated and they are compared row-wise. Presently, only = and <> operators are allowed in row-wise comparisons. The result is 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). + + +row_constructor IS DISTINCT FROM row_constructor + + + + This construct is similar to a <> 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. + + + +row_constructor IS NULL +row_constructor IS NOT NULL + + + + 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. + + 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 @@ @@ -920,6 +920,12 @@ SELECT 3 OPERATOR(pg_catalog.+) 4; + + + A row constructor. + + + Another value expression in parentheses, useful to group @@ -1428,6 +1434,79 @@ SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%'); + + Row Constructors + + + row + constructor + + + + 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 + ROW, a left parenthesis (, zero or more + expressions (separated by commas) for the row field values, and finally + a right parenthesis ). For example, + +SELECT myfunc(ROW(1,2.5,'this is a test')); + + The key word ROW is optional when there is more than one + expression in the list. + + + + By default, the value created by a 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 CREATE TYPE AS. An explicit cast may be needed + to avoid ambiguity. For example: + +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) + + + + + 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 + IS NULL or IS NOT NULL, for example + +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; + + For more detail see . + Row constructors can also be used in connection with subqueries, + as discussed in . + + + + Expression Evaluation Rules 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 @@ @@ -240,10 +240,11 @@ SELECT clean_emp(); <acronym>SQL</acronym> Functions on Composite Types - 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 $1 and $2) but - also the attributes of that argument. For example, suppose that + also the desired attribute (field) of that argument. For example, + suppose that emp 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 double_salary that computes what someone's @@ -252,16 +253,16 @@ SELECT clean_emp(); 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 Notice the use of the syntax $1.salary to select one field of the argument row value. Also notice - how the calling 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 SELECT command uses * + 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: -SELECT name, double_salary(emp.*) AS dream +SELECT name, double_salary(emp) AS dream FROM emp WHERE emp.cubicle ~= point '(2,1)'; - which emphasizes its row nature. + but this usage is deprecated since it's easy to get confused. + + + + Sometimes it is handy to construct a composite argument value + on-the-fly. This can be done with the ROW construct. + For example, we could adjust the data being passed to the function: + +SELECT name, double_salary(row(name, salary*1.1, age, cubicle)) AS dream + FROM emp; + -- cgit v1.2.3