Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit2f63232

Browse files
committed
Promote row expressions to full-fledged citizens of the expression syntax,
rather than allowing them only in a few special cases as before. Inparticular you can now pass a ROW() construct to a function that acceptsa rowtype parameter. Internal generation of RowExprs fixes a number ofcorner cases that used to not work very well, such as referencing thewhole-row result of a JOIN or subquery. This represents a further step inthe work I started a month or so back to make rowtype values intofirst-class citizens.
1 parent9a93988 commit2f63232

File tree

34 files changed

+1278
-548
lines changed

34 files changed

+1278
-548
lines changed

‎doc/src/sgml/func.sgml

Lines changed: 58 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.200 2004/05/1021:08:28 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/func.sgml,v 1.201 2004/05/1022:44:42 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -7822,13 +7822,15 @@ SELECT col1 FROM tab1
78227822
</para>
78237823

78247824
<synopsis>
7825-
(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) IN (<replaceable>subquery</replaceable>)
7825+
<replaceable>row_constructor</replaceable> IN (<replaceable>subquery</replaceable>)
78267826
</synopsis>
78277827

78287828
<para>
7829-
The right-hand side of this form of <token>IN</token> is a parenthesized
7829+
The left-hand side of this form of <token>IN</token> is a row constructor,
7830+
as described in <xref linkend="sql-syntax-row-constructors">.
7831+
The right-hand side is a parenthesized
78307832
subquery, which must return exactly as many columns as there are
7831-
expressions in the left-handlist. The left-hand expressions are
7833+
expressions in the left-handrow. The left-hand expressions are
78327834
evaluated and compared row-wise to each row of the subquery result.
78337835
The result of <token>IN</token> is <quote>true</> if any equal subquery row is found.
78347836
The result is <quote>false</> if no equal row is found (including the special
@@ -7876,13 +7878,15 @@ SELECT col1 FROM tab1
78767878
</para>
78777879

78787880
<synopsis>
7879-
(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) NOT IN (<replaceable>subquery</replaceable>)
7881+
<replaceable>row_constructor</replaceable> NOT IN (<replaceable>subquery</replaceable>)
78807882
</synopsis>
78817883

78827884
<para>
7883-
The right-hand side of this form of <token>NOT IN</token> is a parenthesized
7885+
The left-hand side of this form of <token>NOT IN</token> is a row constructor,
7886+
as described in <xref linkend="sql-syntax-row-constructors">.
7887+
The right-hand side is a parenthesized
78847888
subquery, which must return exactly as many columns as there are
7885-
expressions in the left-handlist. The left-hand expressions are
7889+
expressions in the left-handrow. The left-hand expressions are
78867890
evaluated and compared row-wise to each row of the subquery result.
78877891
The result of <token>NOT IN</token> is <quote>true</> if only unequal subquery rows
78887892
are found (including the special case where the subquery returns no rows).
@@ -7938,14 +7942,16 @@ SELECT col1 FROM tab1
79387942
</para>
79397943

79407944
<synopsis>
7941-
(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>operator</> ANY (<replaceable>subquery</replaceable>)
7942-
(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>operator</> SOME (<replaceable>subquery</replaceable>)
7945+
<replaceable>row_constructor</replaceable> <replaceable>operator</> ANY (<replaceable>subquery</replaceable>)
7946+
<replaceable>row_constructor</replaceable> <replaceable>operator</> SOME (<replaceable>subquery</replaceable>)
79437947
</synopsis>
79447948

79457949
<para>
7946-
The right-hand side of this form of <token>ANY</token> is a parenthesized
7950+
The left-hand side of this form of <token>ANY</token> is a row constructor,
7951+
as described in <xref linkend="sql-syntax-row-constructors">.
7952+
The right-hand side is a parenthesized
79477953
subquery, which must return exactly as many columns as there are
7948-
expressions in the left-handlist. The left-hand expressions are
7954+
expressions in the left-handrow. The left-hand expressions are
79497955
evaluated and compared row-wise to each row of the subquery result,
79507956
using the given <replaceable>operator</replaceable>. Presently,
79517957
only <literal>=</literal> and <literal>&lt;&gt;</literal> operators are allowed
@@ -8003,13 +8009,15 @@ SELECT col1 FROM tab1
80038009
</para>
80048010

80058011
<synopsis>
8006-
(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>operator</replaceable> ALL (<replaceable>subquery</replaceable>)
8012+
<replaceable>row_constructor</replaceable> <replaceable>operator</replaceable> ALL (<replaceable>subquery</replaceable>)
80078013
</synopsis>
80088014

80098015
<para>
8010-
The right-hand side of this form of <token>ALL</token> is a parenthesized
8016+
The left-hand side of this form of <token>ALL</token> is a row constructor,
8017+
as described in <xref linkend="sql-syntax-row-constructors">.
8018+
The right-hand side is a parenthesized
80118019
subquery, which must return exactly as many columns as there are
8012-
expressions in the left-handlist. The left-hand expressions are
8020+
expressions in the left-handrow. The left-hand expressions are
80138021
evaluated and compared row-wise to each row of the subquery result,
80148022
using the given <replaceable>operator</replaceable>. Presently,
80158023
only <literal>=</literal> and <literal>&lt;&gt;</literal> operators are allowed
@@ -8041,16 +8049,17 @@ SELECT col1 FROM tab1
80418049
</indexterm>
80428050

80438051
<synopsis>
8044-
(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>operator</replaceable> (<replaceable>subquery</replaceable>)
8052+
<replaceable>row_constructor</replaceable> <replaceable>operator</replaceable> (<replaceable>subquery</replaceable>)
80458053
</synopsis>
80468054

80478055
<para>
8048-
The left-hand side is a list of scalar expressions. The right-hand side is
8049-
a parenthesized subquery, which must return exactly as many columns as there
8050-
are expressions on the left-hand side. Furthermore, the subquery cannot
8051-
return more than one row. (If it returns zero rows, the result is taken to
8052-
be null.) The left-hand side is evaluated and compared row-wise to the
8053-
single subquery result row.
8056+
The left-hand side is a row constructor,
8057+
as described in <xref linkend="sql-syntax-row-constructors">.
8058+
The right-hand side is a parenthesized subquery, which must return exactly
8059+
as many columns as there are expressions in the left-hand row. Furthermore,
8060+
the subquery cannot return more than one row. (If it returns zero rows,
8061+
the result is taken to be null.) The left-hand side is evaluated and
8062+
compared row-wise to the single subquery result row.
80548063
Presently, only <literal>=</literal> and <literal>&lt;&gt;</literal> operators are allowed
80558064
in row-wise comparisons.
80568065
The result is <quote>true</> if the two rows are equal or unequal, respectively.
@@ -8223,13 +8232,14 @@ AND
82238232
<title>Row-wise Comparison</title>
82248233

82258234
<synopsis>
8226-
(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>) <replaceable>operator</replaceable>(<replaceable>expression</replaceable> <optional>, <replaceable>expression</replaceable> ...</optional>)
8235+
<replaceable>row_constructor</replaceable> <replaceable>operator</replaceable> <replaceable>row_constructor</replaceable>
82278236
</synopsis>
82288237

82298238
<para>
8230-
Each side is a list of scalar expressions; the two lists must be
8231-
of the same length. Each side is evaluated and they are compared
8232-
row-wise.
8239+
Each side is a row constructor,
8240+
as described in <xref linkend="sql-syntax-row-constructors">.
8241+
The two row values must have the same number of fields.
8242+
Each side is evaluated and they are compared row-wise.
82338243
Presently, only <literal>=</literal> and <literal>&lt;&gt;</literal> operators are allowed
82348244
in row-wise comparisons.
82358245
The result is <quote>true</> if the two rows are equal or unequal, respectively.
@@ -8242,6 +8252,29 @@ AND
82428252
are unequal if any corresponding members are non-null and unequal;
82438253
otherwise the result of the row comparison is unknown (null).
82448254
</para>
8255+
8256+
<synopsis>
8257+
<replaceable>row_constructor</replaceable> IS DISTINCT FROM <replaceable>row_constructor</replaceable>
8258+
</synopsis>
8259+
8260+
<para>
8261+
This construct is similar to a <literal>&lt;&gt;</literal> row comparison,
8262+
but it does not yield null for null inputs. Instead, any null value is
8263+
considered unequal to (distinct from) any non-null value, and any two
8264+
nulls are considered equal (not distinct). Thus the result will always
8265+
be either true or false, never null.
8266+
</para>
8267+
8268+
<synopsis>
8269+
<replaceable>row_constructor</replaceable> IS NULL
8270+
<replaceable>row_constructor</replaceable> IS NOT NULL
8271+
</synopsis>
8272+
8273+
<para>
8274+
These constructs test a row value for null or not null. A row value
8275+
is considered not null if it has at least one field that is not null.
8276+
</para>
8277+
82458278
</sect2>
82468279
</sect1>
82478280

‎doc/src/sgml/syntax.sgml

Lines changed: 80 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.90 2004/03/12 00:25:40 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/syntax.sgml,v 1.91 2004/05/10 22:44:43 tgl Exp $
33
-->
44

55
<chapter id="sql-syntax">
@@ -920,6 +920,12 @@ SELECT 3 OPERATOR(pg_catalog.+) 4;
920920
</para>
921921
</listitem>
922922

923+
<listitem>
924+
<para>
925+
A row constructor.
926+
</para>
927+
</listitem>
928+
923929
<listitem>
924930
<para>
925931
Another value expression in parentheses, useful to group
@@ -1428,6 +1434,79 @@ SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%');
14281434

14291435
</sect2>
14301436

1437+
<sect2 id="sql-syntax-row-constructors">
1438+
<title>Row Constructors</title>
1439+
1440+
<indexterm>
1441+
<primary>row</primary>
1442+
<secondary>constructor</secondary>
1443+
</indexterm>
1444+
1445+
<para>
1446+
A row constructor is an expression that builds a row value from values
1447+
for its member fields. A row constructor consists of the key word
1448+
<literal>ROW</literal>, a left parenthesis <literal>(</>, zero or more
1449+
expressions (separated by commas) for the row field values, and finally
1450+
a right parenthesis <literal>)</>. For example,
1451+
<programlisting>
1452+
SELECT myfunc(ROW(1,2.5,'this is a test'));
1453+
</programlisting>
1454+
The key word <literal>ROW</> is optional when there is more than one
1455+
expression in the list.
1456+
</para>
1457+
1458+
<para>
1459+
By default, the value created by a <literal>ROW</> expression is of
1460+
an anonymous record type. If necessary, it can be cast to a named
1461+
composite type --- either the rowtype of a table, or a composite type
1462+
created with <command>CREATE TYPE AS</>. An explicit cast may be needed
1463+
to avoid ambiguity. For example:
1464+
<programlisting>
1465+
CREATE TABLE mytable(f1 int, f2 float, f3 text);
1466+
CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;
1467+
-- No cast needed since only one getf1() exists
1468+
SELECT getf1(ROW(1,2.5,'this is a test'));
1469+
getf1
1470+
-------
1471+
1
1472+
(1 row)
1473+
1474+
CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric);
1475+
CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL;
1476+
-- Now we need a cast to indicate which function to call:
1477+
SELECT getf1(ROW(1,2.5,'this is a test'));
1478+
ERROR: function getf1(record) is not unique
1479+
SELECT getf1(ROW(1,2.5,'this is a test')::mytable);
1480+
getf1
1481+
-------
1482+
1
1483+
(1 row)
1484+
1485+
SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype));
1486+
getf1
1487+
-------
1488+
11
1489+
(1 row)
1490+
</programlisting>
1491+
</para>
1492+
1493+
<para>
1494+
Row constructors have only limited uses, other than creating an argument
1495+
value for a user-defined function that accepts a rowtype parameter, as
1496+
illustrated above.
1497+
It is possible to compare two row values or test a row with
1498+
<literal>IS NULL</> or <literal>IS NOT NULL</>, for example
1499+
<programlisting>
1500+
SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same');
1501+
SELECT ROW(a, b, c) IS NOT NULL FROM table;
1502+
</programlisting>
1503+
For more detail see <xref linkend="functions-comparisons">.
1504+
Row constructors can also be used in connection with subqueries,
1505+
as discussed in <xref linkend="functions-subquery">.
1506+
</para>
1507+
1508+
</sect2>
1509+
14311510
<sect2 id="syntax-express-eval">
14321511
<title>Expression Evaluation Rules</title>
14331512

‎doc/src/sgml/xfunc.sgml

Lines changed: 24 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.81 2004/04/01 21:28:43 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/xfunc.sgml,v 1.82 2004/05/10 22:44:43 tgl Exp $
33
-->
44

55
<sect1 id="xfunc">
@@ -240,10 +240,11 @@ SELECT clean_emp();
240240
<title><acronym>SQL</acronym> Functions on Composite Types</title>
241241

242242
<para>
243-
When specifying functions with arguments of composite
243+
Whenwriting functions with arguments of composite
244244
types, we must not only specify which
245245
argument we want (as we did above with <literal>$1</> and <literal>$2</literal>) but
246-
also the attributes of that argument. For example, suppose that
246+
also the desired attribute (field) of that argument. For example,
247+
suppose that
247248
<type>emp</type> is a table containing employee data, and therefore
248249
also the name of the composite type of each row of the table. Here
249250
is a function <function>double_salary</function> that computes what someone's
@@ -252,16 +253,16 @@ SELECT clean_emp();
252253
<screen>
253254
CREATE TABLE emp (
254255
name text,
255-
salaryinteger,
256+
salarynumeric,
256257
age integer,
257258
cubicle point
258259
);
259260

260-
CREATE FUNCTION double_salary(emp) RETURNSinteger AS '
261+
CREATE FUNCTION double_salary(emp) RETURNSnumeric AS '
261262
SELECT $1.salary * 2 AS salary;
262263
' LANGUAGE SQL;
263264

264-
SELECT name, double_salary(emp) AS dream
265+
SELECT name, double_salary(emp.*) AS dream
265266
FROM emp
266267
WHERE emp.cubicle ~= point '(2,1)';
267268

@@ -274,15 +275,27 @@ SELECT name, double_salary(emp) AS dream
274275
<para>
275276
Notice the use of the syntax <literal>$1.salary</literal>
276277
to select one field of the argument row value. Also notice
277-
how the calling <command>SELECT</> command uses a table name to denote
278-
the entire current row of that table as a composite value. The table
279-
row can alternatively be referenced like this:
278+
how the calling <command>SELECT</> command uses <literal>*</>
279+
to select
280+
the entire current row of a table as a composite value. The table
281+
row can alternatively be referenced using just the table name,
282+
like this:
280283
<screen>
281-
SELECT name, double_salary(emp.*) AS dream
284+
SELECT name, double_salary(emp) AS dream
282285
FROM emp
283286
WHERE emp.cubicle ~= point '(2,1)';
284287
</screen>
285-
which emphasizes its row nature.
288+
but this usage is deprecated since it's easy to get confused.
289+
</para>
290+
291+
<para>
292+
Sometimes it is handy to construct a composite argument value
293+
on-the-fly. This can be done with the <literal>ROW</> construct.
294+
For example, we could adjust the data being passed to the function:
295+
<screen>
296+
SELECT name, double_salary(row(name, salary*1.1, age, cubicle)) AS dream
297+
FROM emp;
298+
</screen>
286299
</para>
287300

288301
<para>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp