Consider the following 1-D ARRAY data type and table.
CREATE TYPE item_price AS DECIMAL(7,2) ARRAY[10]; CREATE TABLE inventory (itemkind INTEGER, regular_price item_price, sale_price item_price);
Assume the following element values for the sale_price and regular_price arrays:
sale_price[1:10] = 50, 100, 200, 90, 250, 550, 200, 200, 50, 75 regular_price[1:10] = 100, 300, 230, 110, 500, 550, 200, 400, 100, 150
The following query returns a 1-D ARRAY with scope reference [1:10] of BYTEINT element type. During evaluation, each element within the specified scope, [5:10], in the regular_price array is compared with the corresponding element of the sale_price array using the relational function ARRAY_GT. The resulting values in the output array are 0 or a non-zero number for the elements within the scope reference, and NULL for all other elements of the array.
SELECT ARRAY_GT(regular_price, sale_price, 5,10) FROM inventory;
The output from the query is a 1-D ARRAY with the following values:
output_array[1:10] = [ NULL, NULL, NULL, NULL, 1, 0, 0, 1, 1, 1]
The following query returns a 1-D ARRAY with scope reference [1:10] of type item_price. During evaluation, each element within the specified scope [5:10] in the regular_price array is compared with the corresponding element of the sale_price array using the relational function ARRAY_GT. The resulting ARRAY of element type BYTEINT value (0 or non-zero number) is multiplied by the corresponding element value of the regular_price array.
SELECT ARRAY_MUL(regular_price, ARRAY_GT(sale_price, regular_price, 5, 10)) FROM inventory;
The output of the query is a 1-D ARRAY with the following values:
output_array[1:10] = [ NULL, NULL, NULL, NULL, 500, 0, 0, 400, 100, 150]
In the following query, the relational function ARRAY_LT compares each element within the scope of the 1-D ARRAY sale_price with a literal value of 100. If the element value is less than 100, the comparison function returns a non-zero value, otherwise it returns 0if the condition is not satisfied, or NULL if the element is not initialized. A 1-D ARRAY of item_price ARRAY type is returned with the corresponding values for each element of the input 1-D ARRAY.
SELECT ARRAY_LT(sale_price,100) FROM inventory;
The output of the query is a 1-D ARRAY with the following values:
output_array[1:10] = [ 1, 0 , 0, 1, 0, 0, 0, 0, 1, 1]