From 982d005d627eba9cbaeaa60941ee0d73d9c476de Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 7 Jun 2004 04:04:47 +0000 Subject: [PATCH] Some preliminary documentation for composite-type stuff. --- doc/src/sgml/array.sgml | 5 +- doc/src/sgml/catalogs.sgml | 14 +- doc/src/sgml/datatype.sgml | 4 +- doc/src/sgml/extend.sgml | 4 +- doc/src/sgml/filelist.sgml | 3 +- doc/src/sgml/rowtypes.sgml | 261 +++++++++++++++++++++++++++++++++++++ doc/src/sgml/syntax.sgml | 22 ++-- 7 files changed, 292 insertions(+), 21 deletions(-) create mode 100644 doc/src/sgml/rowtypes.sgml diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgml index 34ced3c3f4..cbd576b267 100644 --- a/doc/src/sgml/array.sgml +++ b/doc/src/sgml/array.sgml @@ -1,4 +1,4 @@ - + Arrays @@ -10,7 +10,8 @@ PostgreSQL allows columns of a table to be defined as variable-length multidimensional arrays. Arrays of any - built-in type or user-defined type can be created. + built-in or user-defined base type can be created. (Arrays of + composite types or domains are not yet supported, however.) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 0cdbdcfb7b..226eef1c8d 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1,6 +1,6 @@ @@ -957,8 +957,8 @@ The catalog pg_class catalogs tables and most everything else that has columns or is otherwise similar to a table. This includes indexes (but see also - pg_index), sequences, views, and some - kinds of special relation; see relkind. + pg_index), sequences, views, composite types, + and some kinds of special relation; see relkind. Below, when we mean all of these kinds of objects we speak of relations. Not all columns are meaningful for all relation types. @@ -999,8 +999,8 @@ oid pg_type.oid - The OID of the data type that corresponds to this table, if any - (zero for indexes, which have no pg_type entry) + The OID of the data type that corresponds to this table's rowtype, + if any (zero for indexes, which have no pg_type entry) @@ -3420,7 +3420,7 @@ typtype is b for - a base type, c for a composite type (i.e., a + a base type, c for a composite type (e.g., a table's row type), d for a domain, or p for a pseudo-type. See also typrelid and @@ -3461,7 +3461,7 @@ pg_class entry doesn't really represent a table, but it is needed anyway for the type's pg_attribute entries to link to.) - Zero for base types. + Zero for non-composite types. diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 769357ae5d..19feebe0b2 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1,5 +1,5 @@ @@ -2879,6 +2879,8 @@ SELECT * FROM test; &array; + &rowtypes; + Object Identifier Types diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml index 47b02628c5..701943448f 100644 --- a/doc/src/sgml/extend.sgml +++ b/doc/src/sgml/extend.sgml @@ -1,5 +1,5 @@ @@ -151,7 +151,7 @@ $PostgreSQL: pgsql/doc/src/sgml/extend.sgml,v 1.27 2003/11/29 19:51:37 pgsql Exp - Domains can be created using the SQL commands + Domains can be created using the SQL command CREATE DOMAIN. Their creation and use is not discussed in this chapter. diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml index ae0a44f251..d8e5b30ab2 100644 --- a/doc/src/sgml/filelist.sgml +++ b/doc/src/sgml/filelist.sgml @@ -1,4 +1,4 @@ - + @@ -17,6 +17,7 @@ + diff --git a/doc/src/sgml/rowtypes.sgml b/doc/src/sgml/rowtypes.sgml new file mode 100644 index 0000000000..4a5d013a0a --- /dev/null +++ b/doc/src/sgml/rowtypes.sgml @@ -0,0 +1,261 @@ + + + + Composite Types + + + composite type + + + + row type + + + + A composite type describes the structure of a row or record; + it is in essence just a list of field names and their datatypes. + PostgreSQL allows values of composite types to be + used in many of the same ways that simple types can be used. For example, a + column of a table can be declared to be of a composite type. + + + + Declaration of Composite Types + + + Here are two simple examples of defining composite types: + +CREATE TYPE complex AS ( + r double precision, + i double precision +); + +CREATE TYPE inventory_item AS ( + name text, + supplier_id integer, + price numeric +); + + The syntax is comparable to CREATE TABLE, except that only + field names and types can be specified; no constraints (such as NOT + NULL) can presently be included. Note that the AS keyword + is essential; without it, the system will think a quite different kind + of CREATE TYPE command is meant, and you'll get odd syntax + errors. + + + + Having defined the types, we can use them to create tables: + + +CREATE TABLE on_hand ( + item inventory_item, + count integer +); + +INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000); + + + or functions: + + +CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric +AS 'SELECT $1.price * $2' LANGUAGE SQL; + +SELECT price_extension(item, 10) FROM on_hand; + + + + + + + Composite Value Input + + + composite type + constant + + + + To write a composite value as a literal constant, enclose the field + values within parentheses and separate them by commas. You may put double + quotes around any field value, and must do so if it contains commas or + parentheses. (More details appear below.) Thus, the general format of a + composite constant is the following: + +'( val1 , val2 , ... )' + + An example is + +'("fuzzy dice",42,1.99)' + + which would be a valid value of the inventory_item type + defined above. To make a field be NULL, write no characters at all + in its position in the list. For example, this constant specifies + a NULL third field: + +'("fuzzy dice",42,)' + + If you want an empty string rather than NULL, write double quotes: + +'("",42,)' + + Here the first field is a non-NULL empty string, the third is NULL. + + + + (These constants are actually only a special case of + 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.) + + + + The ROW expression syntax may also be used to + construct composite values. In most cases this is considerably + simpler to use than the string-literal syntax, since you don't have + to worry about multiple layers of quoting. We already used this + method above: + +ROW('fuzzy dice', 42, 1.99) +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) + + The ROW expression syntax is discussed in more detail in . + + + + + Accessing Composite Types + + + To access a field of a composite column, one writes a dot and the field + name, much like selecting a field from a table name. In fact, it's so + much like selecting from a table name that you often have to use parentheses + to keep from confusing the parser. For example, you might try to select + some subfields from our on_hand example table with something + like: + + +SELECT item.name FROM on_hand WHERE item.price > 9.99; + + + This will not work since the name item is taken to be a table + name, not a field name, per SQL syntax rules. You must write it like this: + + +SELECT (item).name FROM on_hand WHERE (item).price > 9.99; + + + or if you need to use the table name as well (for instance in a multi-table + query), like this: + + +SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99; + + + Now the parenthesized object is correctly interpreted as a reference to + the item column, and then the subfield can be selected from it. + + + + Similar syntactic issues apply whenever you select a field from a composite + value. For instance, to select just one field from the result of a function + that returns a composite value, you'd need to write something like + + +SELECT (my_func(...)).field FROM ... + + + Without the extra parentheses, this will provoke a syntax error. + + + + + Composite Type Input and Output Syntax + + + The external text representation of a composite value consists of items that + are interpreted according to the I/O conversion rules for the individual + field types, plus decoration that indicates the composite structure. + The decoration consists of parentheses (( and )) + around the whole value, plus commas (,) between adjacent + items. Whitespace outside the parentheses is ignored, but within the + parentheses it is considered part of the field value, and may or may not be + significant depending on the input conversion rules for the field datatype. + For example, in + +'( 42)' + + the whitespace will be ignored if the field type is integer, but not if + it is text. + + + + As shown previously, when writing a composite value you may write double + quotes around any individual field value. + You must do so if the field value would otherwise + confuse the composite-value parser. In particular, fields containing + parentheses, commas, double quotes, or backslashes must be double-quoted. + To put a double quote or backslash in a quoted composite field value, + precede it with a backslash. (Also, a pair of double quotes within a + double-quoted field value is taken to represent a double quote character, + analogously to the rules for single quotes in SQL literal strings.) + Alternatively, you can use backslash-escaping to protect all data characters + that would otherwise be taken as composite syntax. + + + + A completely empty field value (no characters at all between the commas + or parentheses) represents a NULL. To write a value that is an empty + string rather than NULL, write "". + + + + The composite output routine will put double quotes around field values + if they are empty strings or contain parentheses, commas, + double quotes, backslashes, or white space. (Doing so for white space + is not essential, but aids legibility.) Double quotes and backslashes + embedded in field values will be doubled. + + + + + Remember that what you write in an SQL command will first be interpreted + as a string literal, and then as a composite. This doubles the number of + backslashes you need. For example, to insert a text field + containing a double quote and a backslash in a composite + value, you'd need to write + +INSERT ... VALUES ('("\\"\\\\")'); + + The string-literal processor removes one level of backslashes, so that + what arrives at the composite-value parser looks like + ("\"\\"). In turn, the string + fed to the text data type's input routine + becomes "\. (If we were working + with a data type whose input routine also treated backslashes specially, + bytea for example, we might need as many as eight backslashes + in the command to get one backslash into the stored composite field.) + + + + + + The ROW constructor syntax is usually easier to work with + than the composite-literal syntax when writing composite values in SQL + commands. + In ROW, individual field values are written the same way + they would be written when not members of a composite. + + + + + diff --git a/doc/src/sgml/syntax.sgml b/doc/src/sgml/syntax.sgml index 18eaac0aec..693f6380ef 100644 --- a/doc/src/sgml/syntax.sgml +++ b/doc/src/sgml/syntax.sgml @@ -1,5 +1,5 @@ @@ -1496,18 +1496,24 @@ SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%'); Row Constructors - row + composite type + constructor + + + + row type constructor - A row constructor is an expression that builds a row value from values + A row constructor is an expression that builds a row value (also + called a composite 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')); +SELECT ROW(1,2.5,'this is a test'); The key word ROW is optional when there is more than one expression in the list. @@ -1549,10 +1555,10 @@ SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype)); - 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 + Row constructors can be used to build composite values to be stored + in a composite-type table column, or to be passed to a function that + accepts a composite parameter. Also, + 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'); -- 2.40.0