Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1c8IML-0002rs-7Y for pgsql-docs@arkaria.postgresql.org; Sun, 20 Nov 2016 03:00:01 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1c8IMK-0001wR-B1 for pgsql-docs@arkaria.postgresql.org; Sun, 20 Nov 2016 03:00:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1c8ILw-0001Xz-V7 for pgsql-docs@postgreSQL.org; Sun, 20 Nov 2016 02:59:37 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1c8ILs-0001Uy-TW for pgsql-docs@postgreSQL.org; Sun, 20 Nov 2016 02:59:36 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id uAK2xUWo016289 for ; Sat, 19 Nov 2016 21:59:30 -0500 From: Tom Lane To: pgsql-docs@postgreSQL.org Subject: WIP docs patch about composite-type behavior MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <16247.1479610704.0@sss.pgh.pa.us> Date: Sat, 19 Nov 2016 21:59:30 -0500 Message-ID: <16288.1479610770@sss.pgh.pa.us> X-Pg-Spam-Score: -4.8 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-docs Precedence: bulk Sender: pgsql-docs-owner@postgresql.org ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <16247.1479610704.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable There have been a couple of recent threads bemoaning our lack of clarity about how references to composite values work, eg https://www.postgresql.org/message-id/flat/20161029121906.15993.79158%40wri= gleys.postgresql.org https://www.postgresql.org/message-id/flat/CAGwx4BnDQZyJJpzom-UU15wh7s3DQE%= 3DChEdEDQ2%2Brd5S7S2UNA%40mail.gmail.com (same instigator but distinct threads) I suggested that we ought to try to consolidate information about this in one place, and attached is a WIP patch to that end. Some notes: * I'm not completely sold on either the new section's title or its location; but I cannot find a better location. * Some of the text is borrowed from section 36.4.3 "SQL Functions on Composite Types", which should probably now be shortened in favor of linking to this section, but I didn't do that yet. There may be more places that should get merged in. * I noticed that there is noplace in Part II that describes RETURNING clauses as such; there's some material in 7.8.2 "Data-Modifying Statements in WITH", but that really is assuming that you know what RETURNING does already, and in any case it fails to explain the clause's use in standalone statements. This seems like an, um, oversight. Comments, objections, better ideas? Should this get back-patched? regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="better-composite-types-docs.patch"; charset="us-ascii" Content-ID: <16247.1479610704.2@sss.pgh.pa.us> Content-Description: better-composite-types-docs.patch Content-Transfer-Encoding: quoted-printable diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml index 718262f..30d487f 100644 *** a/doc/src/sgml/queries.sgml --- b/doc/src/sgml/queries.sgml *************** SELECT tbl1.a, tbl2.a, tbl1.b FROM ... *** 1457,1463 **** SELECT tbl1.*, tbl2.a FROM ... ! (See also .) =20=20 --- 1457,1464 ---- SELECT tbl1.*, tbl2.a FROM ... ! See for more about the * ! notation. =20=20 diff --git a/doc/src/sgml/rowtypes.sgml b/doc/src/sgml/rowtypes.sgml index 605dc71..23600a3 100644 *** a/doc/src/sgml/rowtypes.sgml --- b/doc/src/sgml/rowtypes.sgml *************** *** 19,25 **** column of a table can be declared to be of a composite type. =20=20 ! Declaration of Composite Types =20=20 --- 19,25 ---- column of a table can be declared to be of a composite type. =20=20 ! Declaration of Composite Types =20=20 *************** CREATE TABLE inventory_item ( *** 90,96 **** =20=20 ! Composite Value Input =20=20 composite type --- 90,96 ---- =20=20 ! Constructing Composite Values =20=20 composite type *************** CREATE TABLE inventory_item ( *** 101,108 **** To write a composite value as a literal constant, enclose the field values within parentheses and separate them by commas. You can put do= uble quotes around any field value, and must do so if it contains commas or ! parentheses. (More details appear below.) Thus, the general format o= f a ! composite constant is the following: '( val1 , val2 , ..= . )' --- 101,109 ---- To write a composite value as a literal constant, enclose the field values within parentheses and separate them by commas. You can put do= uble quotes around any field value, and must do so if it contains commas or ! parentheses. (More details appear below.) Thus, the general forma= t of ! a composite constant is the following: '( val1 , val2 , ..= . )' *************** CREATE TABLE inventory_item ( *** 129,135 **** the generic type constants discussed in . The constant is initially treated as a string and passed to the composite-type input conversion ! routine. An explicit type specification might be necessary.) =20=20 --- 130,137 ---- the generic type constants discussed in . The constant is initially treated as a string and passed to the composite-type input conversion ! routine. An explicit type specification might be necessary to tell ! which type to convert the constant to.) =20=20 *************** ROW('fuzzy dice', 42, 1.99) *** 143,149 **** ROW('', 42, NULL) The ROW keyword is actually optional as long as you have more than one ! field in the expression, so these can simplify to: ('fuzzy dice', 42, 1.99) ('', 42, NULL) --- 145,151 ---- ROW('', 42, NULL) The ROW keyword is actually optional as long as you have more than one ! field in the expression, so these can be simplified to: ('fuzzy dice', 42, 1.99) ('', 42, NULL) *************** ROW('', 42, NULL) *** 153,159 **** =20=20 ! Accessing Composite Types =20=20 --- 155,161 ---- =20=20 ! Accessing Composite Types =20=20 *************** SELECT (my_func(...)).field FROM ... *** 198,203 **** --- 200,210 ---- =20=20 Without the extra parentheses, this will generate a syntax error. +=20 + + The special field name * means all + fields, as further explained in . + =20=20 *************** INSERT INTO mytab (complex_col.r, comple *** 243,248 **** --- 250,447 ---- =20=20 + + Using Composite Types in Queries +=20 + + There are various special syntax rules and behaviors associated with + composite types in queries. These rules provide useful shortcuts, + but can be confusing if you don't understand the logic behind them. + +=20 + + In PostgreSQL, a reference to a table name (or alias) + in a query is effectively a reference to the composite value of the + table's current row. For example, if we had a table + inventory_item as shown + above, we could write: + + SELECT c FROM inventory_item c; + + This query produces a single composite-valued column, so we might get + output like: + + c + ------------------------ + ("fuzzy dice",42,1.99) + (1 row) + + Note however that simple names are matched to column names before table + names, so this example works only because there is no column + named c in the query's tables. + +=20 + + The ordinary qualified-column-name + syntax tablename.columnname + can be understood as applying field + selection to the composite value of the table's current row. + (For efficiency reasons, it's not actually implemented that way.) + +=20 + + When we write + + SELECT c.* FROM inventory_item c; + + then, according to the SQL standard, we should get the contents of the + table expanded into separate columns: + + name | supplier_id | price + ------------+-------------+------- + fuzzy dice | 42 | 1.99 + (1 row) + + as if the query were + + SELECT c.name, c.supplier_id, c.price FROM inventory_item c; + + PostgreSQL will apply this expansion behavior to + any composite-valued expression, although as shown above, you need to write parenth= eses + around the value that .* is applied to whenever it's not a + simple table name. For example, if myfunc() is a function + returning a composite type with columns a, + b, and c, then these two queries have = the + same result: + + SELECT (myfunc(x)).* FROM some_table; + SELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table; + + +=20 + + + PostgreSQL handles column expansion by + actually transforming the first form into the second. So, in this + example, myfunc() would get invoked three times per row + with either syntax. If it's an expensive function you may wish to + avoid that, which you can do with a query like: + + SELECT (m).* FROM (SELECT myfunc(x) AS m FROM some_table OFFSET 0) ss; + + The OFFSET 0 clause keeps the optimizer + from flattening the sub-select to arrive at the form with + multiple calls of myfunc(). + + +=20 + + The compositevalue.* syntax results in + column expansion of this kind when it appears at the top level of + a SELECT output + list, a RETURNING list + in INSERT/UPDATE/DELETE, + a VALUES clause, or + a ROW + constructor. + In all other contexts (including when nested inside one of those + constructs), attaching .* to a composite value does not + change the value, since it means all columns and so the + same composite value is produced again. For example, + if somefunc() accepts a composite-valued argument, + these queries are the same: +=20 + + SELECT somefunc(c.*) FROM inventory_item c; + SELECT somefunc(c) FROM inventory_item c; + +=20 + In both cases, the current row of inventory_item is + passed to the function as a single composite-valued argument. + Even though .* does nothing in such cases, using it is good + style, since it makes clear that a composite value is intended. In + particular, the parser will consider c in c.* = to + refer to a table name or alias, not to a column name, so that there is + no ambiguity; whereas without .*, it is not clear + whether c means a table name or a column name, and in fact + the column-name interpretation will be preferred if there is a column + named c. + +=20 + + Another example demonstrating these concepts is that all these queries + mean the same thing: + + SELECT * FROM inventory_item c ORDER BY c; + SELECT * FROM inventory_item c ORDER BY c.*; + SELECT * FROM inventory_item c ORDER BY ROW(c.*); + + All of these ORDER BY clauses specify the row's composite + value, resulting in sorting the rows according to the rules described + in . However, + if inventory_item contained a column + named c, the first case would be different from the + others, as it would mean to sort by that column only. Given the column + names previously shown, these queries are also equivalent to those abo= ve: + + SELECT * FROM inventory_item c ORDER BY ROW(c.name, c.supplier_id, c.pric= e); + SELECT * FROM inventory_item c ORDER BY (c.name, c.supplier_id, c.price); + + (The last case uses a row constructor with the key word ROW + omitted.) + +=20 + + Another special syntactical behavior associated with composite values = is + that we can use functional notation for extracting a fie= ld + of a composite value. The simple way to explain this is that + the notations field(table) + and table.field + are interchangeable. For example, these queries are equivalent: +=20 + + SELECT c.name FROM inventory_item c WHERE c.price > 1000; + SELECT name(c) FROM inventory_item c WHERE price(c) > 1000; + +=20 + Moreover, if we have a function that accepts a single argument of a + composite type, we can call it with either notation. These queries are + all equivalent: +=20 + + SELECT somefunc(c) FROM inventory_item c; + SELECT somefunc(c.*) FROM inventory_item c; + SELECT c.somefunc FROM inventory_item c; + + +=20 + + This equivalence between functional notation and field notation + makes it possible to use functions on composite types to implement + computed fields. + + computed field + + + field + computed + + An application using the last query above wouldn't need to be directly + aware that somefunc isn't a real column of the table. + +=20 + + + Because of this behavior, it's unwise to give a function that takes a + single composite-type argument the same name as any of the fields of + that composite type. If there is ambiguity, the field-name + interpretation will be preferred, so that such a function could not be + called without tricks. + + + +=20 Composite Type Input and Output Syntax =20=20 diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 36df6c6..a0b4795 100644 *** a/doc/src/sgml/syntax.sgml --- b/doc/src/sgml/syntax.sgml *************** $1.somecolumn *** 1449,1460 **** =20=20 ! In a select list (see ), you ! can ask for all fields of a composite value by writing .*: (compositecol).* =20=20 --- 1449,1461 ---- =20=20 ! You can ask for all fields of a composite value by writing .*: (compositecol).* + This notation behaves differently depending on context; + see for details. =20=20 *************** SELECT ROW(1,2.5,'this is a test'); *** 2291,2297 **** rowvalue.*, which will be expanded to a list of the elements of the row value, just as occurs when the .* syntax is used at the top level ! of a SELECT list. For example, if table t has columns f1 and f2, these are the same: SELECT ROW(t.*, 42) FROM t; --- 2292,2299 ---- rowvalue.*, which will be expanded to a list of the elements of the row value, just as occurs when the .* syntax is used at the top level ! of a SELECT list (see ). ! For example, if table t has columns f1 and f2, these are the same: SELECT ROW(t.*, 42) FROM t; *************** SELECT ROW(t.f1, t.f2, 42) FROM t; *** 2302,2310 **** Before PostgreSQL 8.2, the ! .* syntax was not expanded, so that writing ! ROW(t.*, 42) created a two-field row whose first field ! was another row value. The new behavior is usually more useful. If you need the old behavior of nested row values, write the inner row value without .*, for instance ROW(t, 42). --- 2304,2312 ---- Before PostgreSQL 8.2, the ! .* syntax was not expanded in row constructors, so ! that writing ROW(t.*, 42) created a two-field row whose = first ! field was another row value. The new behavior is usually more usefu= l. If you need the old behavior of nested row values, write the inner row value without .*, for instance ROW(t, 42). ------- =_aaaaaaaaaa0 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs ------- =_aaaaaaaaaa0--