|
1 | | -Array iterator functions, by Massimo Dal Zotto <dz@cs.unitn.it> |
2 | | -Copyright (C) 1999, Massimo Dal Zotto <dz@cs.unitn.it> |
| 1 | +Array iterator functions have been removed as of PostgreSQL 7.4, because |
| 2 | +equivalent functionality is now available built in to the backend. |
3 | 3 |
|
4 | | -This software is distributed under the GNU General Public License |
5 | | -either version 2, or (at your option) any later version. |
| 4 | +For example, previously, using contrib/array, you might have used the |
| 5 | +following construct: |
6 | 6 |
|
| 7 | + create table t(id int4[], txt text[]); |
7 | 8 |
|
8 | | -This loadable module defines a new class of functions which take |
9 | | -an array and a scalar value, iterate a scalar operator over the |
10 | | -elements of the array and the value, and compute a result as |
11 | | -the logical OR or AND of the iteration results. |
12 | | -For example array_int4eq returns true if some of the elements |
13 | | -of an array of int4 is equal to the given value: |
| 9 | + -- select tuples with some id element equal to 123 |
| 10 | + select * from t where t.id *= 123; |
14 | 11 |
|
15 | | - array_int4eq({1,2,3}, 1) --> true |
16 | | - array_int4eq({1,2,3}, 4) --> false |
| 12 | +Now you would do this instead: |
17 | 13 |
|
18 | | -If we have defined T array types and O scalar operators we can |
1
8000
9 | | -define T x O x 2 array functions, each of them has a name like |
20 | | -"array_[all_]<basetype><operation>" and takes an array of type T |
21 | | -iterating the operator O over all the elements. Note however |
22 | | -that some of the possible combination are invalid, for example |
23 | | -the array_int4_like because there is no like operator for int4. |
| 14 | + -- select tuples with some id element equal to 123 |
| 15 | + select * from t where 123 = any (t.id); |
24 | 16 |
|
25 | | -We can then define new operators based on these functions and use |
26 | | -them to write queries with qualification clauses based on the |
27 | | -values of some of the elements of an array. |
28 | | -For example to select rows having some or all element of an array |
29 | | -attribute equal to a given value or matching a regular expression: |
| 17 | + -- or you could also do this |
| 18 | + select * from t where 123 = some (t.id); |
30 | 19 |
|
31 | | - create table t(id int4[], txt text[]); |
| 20 | +Similarly, if using contrib/array, you did the following: |
32 | 21 |
|
33 | | - -- select tuples with some id element equal to 123 |
34 | | - select * from t where t.id *= 123; |
| 22 | + -- select tuples with all txt elements matching '^[A-Z]' |
| 23 | + select * from t where t.txt[1:3] **~ '^[A-Z]'; |
35 | 24 |
|
36 | | - -- select tuples with some txt element matching '[a-z]' |
37 | | - select * from t where t.txt *~ '[a-z]'; |
| 25 | +Now do this instead: |
38 | 26 |
|
39 | | - -- select tuples with all txt elements matching '^[A-Z]' |
40 | | - select * from t where t.txt[1:3] **~ '^[A-Z]'; |
| 27 | + -- select tuples with all txt elements matching '^[A-Z]' |
| 28 | + select * from t where '^[A-Z]' ~ all (t.txt[1:3]); |
41 | 29 |
|
42 | | -The scheme is quite general, each operator which operates on a base type |
43 | | -can be iterated over the elements of an array. It seem to work well but |
44 | | -defining each new operator requires writing a different C function. |
45 | | -This is tedious, and error-prone since one must take care that the correct |
46 | | -datatypes are associated with the selected underlying function. |
47 | | -Can anyone suggest a better and more portable way to do it ? |
| 30 | +See the related section in the online documentation for more detail: |
| 31 | + Table of Contents => Functions and Operators => Row and Array Comparisons |
48 | 32 |
|
49 | | -See also array_iterator.sql for an example on how to use this module. |
0 commit comments