| --------------------------------------------------------------------------- |
| -- Rules: |
| -- ------ |
| -- 1) Any DB objects should be created w/o schema prefix, |
| -- since this file is executed in a separate schema context. |
| -- 2) There should be no DROP statements in this script, since |
| -- all objects created in the default schema will be cleaned-up outside. |
| --------------------------------------------------------------------------- |
| |
| m4_include(`SQLCommon.m4') |
| m4_changequote(`<!', `!>') |
| |
| --------------------------------------------------------------------------- |
| -- Setup: |
| --------------------------------------------------------------------------- |
| CREATE TABLE data AS |
| SELECT |
| '{1,2,3}'::float8[] AS an, |
| '{4,5,7}'::float8[] AS b; |
| |
| SELECT array_dot( |
| array_mult( |
| array_add(an,b), |
| array_sub(an,b)), |
| array_mult( |
| array_div(an,b), |
| normalize(an))) AS result1 |
| FROM data; |
| |
| SELECT array_max(b) FROM data; |
| SELECT array_min(b) FROM data; |
| SELECT array_sum(b) FROM data; |
| SELECT array_sum_big(b) FROM data; |
| SELECT array_mean(b) FROM data; |
| SELECT array_stddev(b) FROM data; |
| |
| SELECT array_max(b) |
| + array_min(b) |
| + array_sum(b) |
| + array_sum_big(b) |
| + array_mean(b) |
| + array_stddev(b) AS result2 |
| FROM data; |
| |
| SELECT array_sum( |
| array_scalar_mult( |
| array_fill( |
| array_of_float(20), |
| 234.343::FLOAT8), |
| 3.7::FLOAT8)) |
| FROM data; |
| |
| -- array_agg |
| CREATE TABLE test AS SELECT generate_series(1,100) x; |
| SELECT array_agg(x) FROM test; |
| |
| -- array_scalar_add |
| SELECT assert( |
| relative_error( |
| array_scalar_add(ARRAY[0.8, 9.5, 2.6]::float8[], 2.2::float8), |
| ARRAY[3.0, 11.7, 4.8]::float8[]) < 1e-6, |
| 'array_scalar_add: Wrong results'); |
| |
| -- array_square |
| SELECT assert( |
| relative_error( |
| array_square(ARRAY[3, 4, 5]::float8[]), |
| ARRAY[9.0, 16.0, 25.0]::float8[]) < 1e-6, |
| 'array_square: Wrong results'); |
| |
| SELECT array_filter(ARRAY[0.8, 9.5, 0, 2.6]::float8[]); |
| SELECT array_filter(ARRAY[0.8, 9.5, 0, 2.6]::float8[], 2.5::float8, '<'); |
| |
| ------------------------------------------------------------ |
| -- numeric[] |
| ------------------------------------------------------------ |
| -- (array) -> scalar |
| SELECT array_sum_big(ARRAY[-1, 1.3, 2.9]); |
| SELECT array_sum(ARRAY[-1, 1.3, 2.9]); |
| SELECT array_max(ARRAY[-1, 1.3, 2.9]); |
| SELECT array_min(ARRAY[-1, 1.3, 2.9]); |
| SELECT array_stddev(ARRAY[-1, 1.3, 2.9]); |
| SELECT array_mean(ARRAY[-1, 1.3, 2.9]); |
| |
| -- (array, array) -> scalar |
| SELECT array_dot(ARRAY[-1, 1.3, 2.9], ARRAY[1,2,3]::numeric[]); |
| SELECT array_contains(ARRAY[1., 2.], ARRAY[1., 0]); |
| |
| -- (array,) -> array |
| SELECT array_scalar_mult( |
| ARRAY[1,2,3,4]::numeric[], |
| (1.0/MADLIB_SCHEMA.array_sum(ARRAY[1.,2,3,4])) |
| ); |
| |
| -------------------------------------------------------------- |
| -- TESTING array_unnest_2d_to_1d FUNCTION |
| -------------------------------------------------------------- |
| -- 2-element float8 arrays |
| DROP TABLE IF EXISTS unnest_2d_tbl01; |
| CREATE TABLE unnest_2d_tbl01 (id INT, val DOUBLE PRECISION[][]); |
| INSERT INTO unnest_2d_tbl01 VALUES |
| (1, ARRAY[[1::float8,2],[3::float8,4],[5::float8,6]]), |
| (2, ARRAY[[101::float8,202],[303::float8,404],[505::float8,606]]) |
| ; |
| |
| DROP TABLE IF EXISTS unnest_2d_tbl01_groundtruth; |
| CREATE TABLE unnest_2d_tbl01_groundtruth ( |
| id INT, |
| unnest_row_id INT, |
| val DOUBLE PRECISION[] |
| ); |
| INSERT INTO unnest_2d_tbl01_groundtruth VALUES |
| (1, 1, ARRAY[1::float8,2]), |
| (1, 2, ARRAY[3::float8,4]), |
| (1, 3, ARRAY[5::float8,6]), |
| (2, 1, ARRAY[101::float8,202]), |
| (2, 2, ARRAY[303::float8,404]), |
| (2, 3, ARRAY[505::float8,606]) |
| ; |
| |
| DROP TABLE IF EXISTS unnest_2d_tbl01_out; |
| CREATE TABLE unnest_2d_tbl01_out AS |
| SELECT id, (array_unnest_2d_to_1d(val)).* FROM unnest_2d_tbl01; |
| |
| SELECT assert( |
| unnest_result = val, |
| 'array_unnest_2d_to_1d: Wrong results for test table "unnest_2d_tbl01"' |
| ) |
| FROM ( |
| SELECT * FROM |
| unnest_2d_tbl01_out t1 |
| JOIN |
| unnest_2d_tbl01_groundtruth t2 |
| USING (id,unnest_row_id) |
| ) t3; |
| |
| -- 3-element float8 arrays |
| DROP TABLE IF EXISTS unnest_2d_tbl02; |
| CREATE TABLE unnest_2d_tbl02 (id INT, val DOUBLE PRECISION[][]); |
| INSERT INTO unnest_2d_tbl02 VALUES |
| (1, ARRAY[[1.57::float8,2,3],[4::float8,5,6]]), |
| (2, ARRAY[[101::float8,202,303],[PI(),505,606]]), |
| (3, ARRAY[[1011::float8,2022,3033],[4044,5055,60.66]]) |
| ; |
| |
| DROP TABLE IF EXISTS unnest_2d_tbl02_groundtruth; |
| CREATE TABLE unnest_2d_tbl02_groundtruth ( |
| id INT, |
| unnest_row_id INT, |
| val DOUBLE PRECISION[] |
| ); |
| INSERT INTO unnest_2d_tbl02_groundtruth VALUES |
| (1, 1, array[1.57::float8,2,3]), |
| (1, 2, array[4::float8,5,6]), |
| (2, 1, array[101::float8,202,303]), |
| (2, 2, array[pi(),505,606]), |
| (3, 1, array[1011::float8,2022,3033]), |
| (3, 2, array[4044,5055,60.66]) |
| ; |
| |
| DROP TABLE IF EXISTS unnest_2d_tbl02_out; |
| CREATE TABLE unnest_2d_tbl02_out AS |
| SELECT id, (array_unnest_2d_to_1d(val)).* FROM unnest_2d_tbl02; |
| |
| SELECT assert( |
| unnest_result = val, |
| 'array_unnest_2d_to_1d: Wrong results for test table "unnest_2d_tbl02"' |
| ) |
| FROM ( |
| SELECT * FROM |
| unnest_2d_tbl02_out t1 |
| JOIN |
| unnest_2d_tbl02_groundtruth t2 |
| USING (id,unnest_row_id) |
| ) t3; |
| |
| -- 2-element text arrays |
| DROP TABLE IF EXISTS unnest_2d_tbl03; |
| CREATE TABLE unnest_2d_tbl03 (id INT, val TEXT[][]); |
| INSERT INTO unnest_2d_tbl03 VALUES |
| (1, ARRAY[['a','b'],['c','d'],['e','f']]), |
| (2, ARRAY[['apple','banana'],['cherries','kiwi'],['lemon','mango']]) |
| ; |
| |
| DROP TABLE IF EXISTS unnest_2d_tbl03_groundtruth; |
| CREATE TABLE unnest_2d_tbl03_groundtruth ( |
| id INT, |
| unnest_row_id INT, |
| val TEXT[] |
| ); |
| INSERT INTO unnest_2d_tbl03_groundtruth VALUES |
| (1, 1, ARRAY['a','b']), |
| (1, 2, ARRAY['c','d']), |
| (1, 3, ARRAY['e','f']), |
| (2, 1, ARRAY['apple','banana']), |
| (2, 2, ARRAY['cherries','kiwi']), |
| (2, 3, ARRAY['lemon','mango']) |
| ; |
| |
| DROP TABLE IF EXISTS unnest_2d_tbl03_out; |
| CREATE TABLE unnest_2d_tbl03_out AS |
| SELECT id, (array_unnest_2d_to_1d(val)).* FROM unnest_2d_tbl03; |
| |
| SELECT assert( |
| unnest_result = val, |
| 'array_unnest_2d_to_1d: Wrong results for test table "unnest_2d_tbl03"' |
| ) |
| FROM ( |
| SELECT * FROM |
| unnest_2d_tbl03_out t1 |
| JOIN |
| unnest_2d_tbl03_groundtruth t2 |
| USING (id,unnest_row_id) |
| ) t3; |
| |
| -- 3-element float8 arrays with some NULLs |
| DROP TABLE IF EXISTS unnest_2d_tbl04; |
| CREATE TABLE unnest_2d_tbl04 (id INT, val DOUBLE PRECISION[][]); |
| INSERT INTO unnest_2d_tbl04 VALUES |
| (1, ARRAY[[1::float8,NULL,3],[4.0,5,NULL]]), |
| (2, ARRAY[[101::float8,NULL,303], |
| [NULL::float8,NULL,NULL]]::double precision[][]), |
| (3, ARRAY[[NULL,2022::float8],[4044::float8,NULL]]) |
| ; |
| |
| DROP TABLE IF EXISTS unnest_2d_tbl04_groundtruth; |
| CREATE TABLE unnest_2d_tbl04_groundtruth ( |
| id INT, |
| unnest_row_id INT, |
| val DOUBLE PRECISION[] |
| ); |
| INSERT INTO unnest_2d_tbl04_groundtruth VALUES |
| (1, 1, ARRAY[1::float8,NULL,3]), |
| (1, 2, ARRAY[4.0::float8,5,NULL]), |
| (2, 1, ARRAY[101::float8,NULL,303]), |
| (2, 2, ARRAY[NULL::float8,NULL,NULL]), |
| (3, 1, ARRAY[NULL,2022::float8]), |
| (3, 2, ARRAY[4044::float8,NULL]) |
| ; |
| |
| DROP TABLE IF EXISTS unnest_2d_tbl04_out; |
| CREATE TABLE unnest_2d_tbl04_out AS |
| SELECT id, (array_unnest_2d_to_1d(val)).* FROM unnest_2d_tbl04; |
| |
| SELECT assert( |
| unnest_result = val, |
| 'array_unnest_2d_to_1d: Wrong results for test table "unnest_2d_tbl04"' |
| ) |
| FROM ( |
| SELECT * FROM |
| unnest_2d_tbl04_out t1 |
| JOIN |
| unnest_2d_tbl04_groundtruth t2 |
| USING (id,unnest_row_id) |
| ) t3; |
| |
| -- 3-element timestamp arrays with NULLs |
| DROP TABLE IF EXISTS unnest_2d_tbl05; |
| CREATE TABLE unnest_2d_tbl05 (id INT, val TIMESTAMP WITHOUT TIME ZONE[][]); |
| INSERT INTO unnest_2d_tbl05 VALUES |
| (1, array[['2017-01-01 11:00:02'::TIMESTAMP WITHOUT TIME ZONE, |
| '2017-01-01 13:00:05', |
| '2017-01-02 11:55:00'], |
| ['2016-10-12 12:00:02'::TIMESTAMP WITHOUT TIME ZONE, |
| '2016-10-12 13:15:22', |
| NULL]]), |
| (2, NULL), |
| (3, array[['2014-02-01 11:00:02'::TIMESTAMP WITHOUT TIME ZONE, |
| '2014-02-01 13:00:05', |
| '2014-02-02 11:55:00'], |
| ['2013-07-12 12:00:02'::TIMESTAMP WITHOUT TIME ZONE, |
| NULL, |
| '2013-07-12 13:15:22']]) |
| ; |
| |
| DROP TABLE IF EXISTS unnest_2d_tbl05_groundtruth; |
| CREATE TABLE unnest_2d_tbl05_groundtruth ( |
| id INT, |
| unnest_row_id INT, |
| val TIMESTAMP WITHOUT TIME ZONE[] |
| ); |
| INSERT INTO unnest_2d_tbl05_groundtruth VALUES |
| (1, 1, ARRAY['2017-01-01 11:00:02'::TIMESTAMP WITHOUT TIME ZONE, |
| '2017-01-01 13:00:05', |
| '2017-01-02 11:55:00']), |
| (1, 2, ARRAY['2016-10-12 12:00:02'::TIMESTAMP WITHOUT TIME ZONE, |
| '2016-10-12 13:15:22', |
| NULL]), |
| (2, NULL, NULL), |
| (3, 1, ARRAY['2014-02-01 11:00:02'::TIMESTAMP WITHOUT TIME ZONE, |
| '2014-02-01 13:00:05', |
| '2014-02-02 11:55:00']), |
| (3, 2, ARRAY['2013-07-12 12:00:02'::TIMESTAMP WITHOUT TIME ZONE, |
| NULL, |
| '2013-07-12 13:15:22']) |
| ; |
| |
| DROP TABLE IF EXISTS unnest_2d_tbl05_out; |
| CREATE TABLE unnest_2d_tbl05_out AS |
| SELECT id, (array_unnest_2d_to_1d(val)).* FROM unnest_2d_tbl05; |
| |
| SELECT assert( |
| unnest_result = val, |
| 'array_unnest_2d_to_1d: Wrong results for test table "unnest_2d_tbl05"' |
| ) |
| FROM ( |
| SELECT * FROM |
| unnest_2d_tbl05_out t1 |
| JOIN |
| unnest_2d_tbl05_groundtruth t2 |
| USING (id,unnest_row_id) |
| ) t3; |
| |
| -- TESTING array_to_bytea() function - skip for gpdb 4.3 |
| m4_ifelse(__PORT__ __DBMS_VERSION_MAJOR__, <!GREENPLUM 4!>,, |
| <! |
| |
| -- create input table ( n = 3 x 5 x 7 dim SMALLINT[], r = 2 x 3 x 5 dim REAL[] ) |
| DROP TABLE IF EXISTS array_input_tbl; |
| CREATE TABLE array_input_tbl (id SMALLINT, n SMALLINT[], r REAL[]); |
| INSERT INTO array_input_tbl SELECT generate_series(1, 10); |
| SELECT id, count(*), array_agg(n) from (select id, unnest(n) as n from array_input_tbl) u group by id order by id; |
| UPDATE array_input_tbl SET |
| n=array_fill(2*id, ARRAY[3, 5, 7]), |
| r=array_fill(id + 0.4, array[2, 3, 5]); |
| |
| -- create flattened input table |
| DROP TABLE IF EXISTS flat_array_input_tbl; |
| CREATE TABLE flat_array_input_tbl (id SMALLINT, n SMALLINT[], n_length SMALLINT, r REAL[], r_length SMALLINT); |
| INSERT INTO flat_array_input_tbl |
| SELECT n.id, n, n_length, r, r_length |
| FROM |
| ( |
| SELECT id, array_agg(n) AS n, 2*COUNT(*) AS n_length |
| FROM |
| ( |
| SELECT id, unnest(n) AS n FROM array_input_tbl |
| ) n_values |
| GROUP BY id |
| ) n |
| JOIN |
| ( |
| SELECT id, array_agg(r) AS r, 4*COUNT(*) AS r_length |
| FROM |
| ( |
| SELECT id, unnest(r) AS r FROM array_input_tbl |
| ) r_values |
| GROUP BY id |
| ) r |
| USING (id); |
| |
| CREATE TABLE bytea_tbl AS SELECT id, array_to_bytea(n) AS n, array_to_bytea(r) AS r FROM array_input_tbl; |
| |
| -- verify lengths of BYTEA output is correct for SMALLINT & REAL arrays |
| SELECT assert( |
| length(o.n) = i.n_length AND length(o.r) = i.r_length, |
| 'array_to_bytea() returned incorrect lengths:\n' || |
| ' Expected length(n) = ' || n_length::TEXT || ', got ' || length(o.n) || |
| ' Expected ' || r_length::TEXT || ', got ' || length(o.r) |
| ) |
| FROM flat_array_input_tbl i JOIN bytea_tbl o USING (id); |
| |
| -- convert BYTEA back to flat arrays of SMALLINT's & REAL's |
| |
| CREATE TABLE array_output_tbl AS |
| SELECT |
| id, |
| convert_bytea_to_smallint_array(n) AS n, |
| convert_bytea_to_real_array(r) AS r |
| FROM bytea_tbl; |
| |
| -- verify that data in above table matches flattened input table exactly |
| SELECT assert( |
| i.n = o.n AND i.r = o.r, |
| 'output of array_to_bytea() does not convert back to flattened input' |
| ) |
| FROM flat_array_input_tbl i JOIN array_output_tbl o USING (id); |
| !>) |
| |
| m4_changequote(,) |