From 1c63587f24304ec5dfd8b1f6aa236f19b3bdd63e Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Fri, 26 Jan 2001 23:40:39 +0000 Subject: [PATCH] Clean up markup, add description of contrib/array operators by Joel Burton . --- doc/src/sgml/array.sgml | 295 +++++++++++++++++++++++----------------- 1 file changed, 170 insertions(+), 125 deletions(-) diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgml index 0de9bd97fbe..df89e84d926 100644 --- a/doc/src/sgml/array.sgml +++ b/doc/src/sgml/array.sgml @@ -1,45 +1,37 @@ - - - -Arrays - - - - -This must become a chapter on array behavior. Volunteers? - thomas 1998-01-12 - - - - - - Postgres allows columns of a table - to be defined as variable-length multi-dimensional - arrays. Arrays of any built-in type or user-defined type - can be created. To illustrate their use, we create this table: - - + + + + Arrays + + + Postgres allows columns of a table to be + defined as variable-length multi-dimensional arrays. Arrays of any + built-in type or user-defined type can be created. To illustrate + their use, we create this table: + CREATE TABLE sal_emp ( name text, - pay_by_quarter int4[], + pay_by_quarter integer[], schedule text[][] ); - - - - - The above query will create a table named sal_emp with - a text string (name), a one-dimensional array of int4 - (pay_by_quarter), which represents the employee's - salary by quarter, and a two-dimensional array of text - (schedule), which represents the employee's weekly - schedule. Now we do some INSERTs; note that when - appending to an array, we enclose the values within - braces and separate them by commas. If you know C, - this is not unlike the syntax for initializing structures. + + The above query will create a table named + sal_emp with a text string + (name), a one-dimensional array of type + integer (pay_by_quarter), + which shall represent the employee's salary by quarter, and a + two-dimensional array of text + (schedule), which represents the + employee's weekly schedule. + + + + Now we do some INSERTs; note that when appending + to an array, we enclose the values within braces and separate them + by commas. If you know C, this is not unlike the syntax for + initializing structures. - + INSERT INTO sal_emp VALUES ('Bill', '{10000, 10000, 10000, 10000}', @@ -49,32 +41,34 @@ INSERT INTO sal_emp VALUES ('Carol', '{20000, 25000, 25000, 25000}', '{{"talk", "consult"}, {"meeting"}}'); - - - Now, we can run some queries on sal_emp. First, we - show how to access a single element of an array at a - time. This query retrieves the names of the employees - whose pay changed in the second quarter: + + + + + Now, we can run some queries on sal_emp. + First, we show how to access a single element of an array at a time. + This query retrieves the names of the employees whose pay changed in + the second quarter: - + SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2]; name ------- Carol (1 row) - + - Postgres uses the "one-based" numbering - convention for arrays --- that is, an array of n elements starts with - array[1] and ends with array[n]. - + Postgres uses the + one-based numbering convention for arrays, that is, + an array of n elements starts with array[1] and + ends with array[n]. + - - This query retrieves the third quarter pay of all - employees: + + This query retrieves the third quarter pay of all employees: - + SELECT pay_by_quarter[3] FROM sal_emp; pay_by_quarter @@ -82,110 +76,161 @@ SELECT pay_by_quarter[3] FROM sal_emp; 10000 25000 (2 rows) - - - - - We can also access arbitrary rectangular slices of an array, or - subarrays. An array slice is denoted by writing - lower subscript : - upper subscript for one or more array - dimensions. This query retrieves the first item on - Bill's schedule for the first two days of the week: + + + + + We can also access arbitrary rectangular slices of an array, or + subarrays. An array slice is denoted by writing + lower subscript : + upper subscript for one or more + array dimensions. This query retrieves the first item on Bill's + schedule for the first two days of the week: - + SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill'; schedule -------------------- {{"meeting"},{""}} (1 row) - + - We could also have written + We could also have written - + SELECT schedule[1:2][1] FROM sal_emp WHERE name = 'Bill'; - + - with the same result. An array subscripting operation is taken to - represent an array slice if any of the subscripts are written in - the form lower : - upper. A lower bound of 1 is assumed - for any subscript where only one value is specified. - + with the same result. An array subscripting operation is taken to + represent an array slice if any of the subscripts are written in the + form lower : + upper. A lower bound of 1 is assumed for + any subscript where only one value is specified. + - - An array value can be replaced completely: + + An array value can be replaced completely: - + UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}' WHERE name = 'Carol'; - + - or updated at a single element: + or updated at a single element: - + UPDATE sal_emp SET pay_by_quarter[4] = 15000 WHERE name = 'Bill'; - + - or updated in a slice: + or updated in a slice: - + UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}' WHERE name = 'Carol'; - - - - - An array can be enlarged by assigning to an element adjacent to - those already present, or by assigning to a slice that is adjacent - to or overlaps the data already present. - For example, if an array value currently has 4 elements, it will - have five elements after an update that assigns to array[5]. - Currently, enlargement in this fashion is only - allowed for one-dimensional arrays, not multidimensional arrays. - - - - The syntax for CREATE TABLE allows fixed-length arrays to be - defined: - - + + + + + An array can be enlarged by assigning to an element adjacent to + those already present, or by assigning to a slice that is adjacent + to or overlaps the data already present. For example, if an array + value currently has 4 elements, it will have five elements after an + update that assigns to array[5]. Currently, enlargement in this + fashion is only allowed for one-dimensional arrays, not + multidimensional arrays. + + + + The syntax for CREATE TABLE allows fixed-length + arrays to be defined: + + CREATE TABLE tictactoe ( - squares int4[3][3] + squares integer[3][3] ); - + - However, the current implementation does not enforce the array - size limits --- the behavior is the same as for arrays of - unspecified length. - + However, the current implementation does not enforce the array size + limits --- the behavior is the same as for arrays of unspecified + length. + - - Actually, the current implementation doesn't enforce the declared - number of dimensions either. Arrays of a particular base type - are all considered to be of the same type, regardless of size or - number of dimensions. - + + Actually, the current implementation does not enforce the declared + number of dimensions either. Arrays of a particular base type are + all considered to be of the same type, regardless of size or number + of dimensions. + - - The current dimensions of any array value can be retrieved with - the array_dims function: + + The current dimensions of any array value can be retrieved with the + array_dims function: - + SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol'; array_dims ------------ [1:2][1:1] (1 row) - - - array_dims produces a text result, - which is convenient for people to read but perhaps not so convenient - for programs. - - - + + + array_dims produces a text result, + which is convenient for people to read but perhaps not so convenient + for programs. + + + + To search for a value in an array, you must check each value of the + array. This can be done by hand (if you know the size of the array): + + +SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR + pay_by_quarter[2] = 10000 OR + pay_by_quarter[3] = 10000 OR + pay_by_quarter[4] = 10000; + + + However, this quickly becomes tedious for large arrays, and is not + helpful if the size of the array is unknown. Although it is not part + of the primary PostgreSQL distribution, + in the contributions directory, there is an extension to + PostgreSQL that defines new functions and + operators for iterating over array values. Using this, the above + query could be: + + +SELECT * FROM sal_emp WHERE pay_by_quarter[1:4] *= 10000; + + + To search the entire array (not just specified columns), you could + use: + + +SELECT * FROM sal_emp WHERE pay_by_quarter *= 10000; + + + In addition, you could find rows where the array had all values + equal to 10 000 with: + + +SELECT * FROM sal_emp WHERE pay_by_quarter **= 10000; + + + To install this optional module, look in the + contrib/array directory of the + PostgreSQL source distribution. + + + + + Arrays are not lists; using arrays in the manner described in the + previous paragraph is often a sign of database misdesign. The + array field should generally be split off into a separate table. + Tables can obviously be searched easily. + + + + -- 2.39.5