1 2CREATE OR REPLACE FUNCTION test_anyInteger(fn TEXT, rest_sql TEXT, params TEXT[], parameter TEXT) 3RETURNS SETOF TEXT AS 4$BODY$ 5DECLARE 6start_sql TEXT; 7end_sql TEXT; 8query TEXT; 9p TEXT; 10BEGIN 11 start_sql = 'select * from ' || fn || '($$ SELECT '; 12 FOREACH p IN ARRAY params LOOP 13 IF p = parameter THEN CONTINUE; 14 END IF; 15 start_sql = start_sql || p || ', '; 16 END LOOP; 17 end_sql = ' FROM edge_table $$' || rest_sql; 18 19 query := start_sql || parameter || '::SMALLINT ' || end_sql; 20 RETURN query SELECT lives_ok(query); 21 22 query := start_sql || parameter || '::INTEGER ' || end_sql; 23 RETURN query SELECT lives_ok(query); 24 25 query := start_sql || parameter || '::BIGINT ' || end_sql; 26 RETURN query SELECT lives_ok(query); 27 28 query := start_sql || parameter || '::REAL ' || end_sql; 29 RETURN query SELECT throws_ok(query); 30 31 query := start_sql || parameter || '::FLOAT8 ' || end_sql; 32 RETURN query SELECT throws_ok(query); 33 34 query := start_sql || parameter || '::NUMERIC ' || end_sql; 35 RETURN query SELECT throws_ok(query); 36END; 37$BODY$ LANGUAGE plpgsql; 38 39CREATE OR REPLACE FUNCTION test_anyNumerical(fn TEXT, rest_sql TEXT, params TEXT[], parameter TEXT) 40RETURNS SETOF TEXT AS 41$BODY$ 42DECLARE 43start_sql TEXT; 44end_sql TEXT; 45query TEXT; 46p TEXT; 47BEGIN 48 start_sql = 'select * from ' || fn || '($$ SELECT '; 49 FOREACH p IN ARRAY params LOOP 50 IF p = parameter THEN CONTINUE; 51 END IF; 52 start_sql = start_sql || p || ', '; 53 END LOOP; 54 end_sql = ' FROM edge_table $$' || rest_sql; 55 56 query := start_sql || parameter || '::SMALLINT ' || end_sql; 57 RETURN query SELECT lives_ok(query); 58 59 query := start_sql || parameter || '::INTEGER ' || end_sql; 60 RETURN query SELECT lives_ok(query); 61 62 query := start_sql || parameter || '::BIGINT ' || end_sql; 63 RETURN query SELECT lives_ok(query); 64 65 query := start_sql || parameter || '::REAL ' || end_sql; 66 RETURN query SELECT lives_ok(query); 67 68 query := start_sql || parameter || '::FLOAT8 ' || end_sql; 69 RETURN query SELECT lives_ok(query); 70 71 query := start_sql || parameter || '::NUMERIC ' || end_sql; 72 RETURN query SELECT lives_ok(query); 73END; 74$BODY$ LANGUAGE plpgsql; 75 76 77CREATE OR REPLACE FUNCTION test_anyIntegerOrders(fn TEXT, rest_sql TEXT, params TEXT[], parameter TEXT) 78RETURNS SETOF TEXT AS 79$BODY$ 80DECLARE 81start_sql TEXT; 82end_sql TEXT; 83query TEXT; 84p TEXT; 85BEGIN 86 start_sql = 'select * from ' || fn || '($$ SELECT '; 87 FOREACH p IN ARRAY params LOOP 88 IF p = parameter THEN CONTINUE; 89 END IF; 90 start_sql = start_sql || p || ', '; 91 END LOOP; 92 end_sql = ' FROM orders LIMIT 1 $$, $$ SELECT * from Vehicles $$' || rest_sql; 93 94 query := start_sql || parameter || '::SMALLINT ' || end_sql; 95 RETURN query SELECT lives_ok(query); 96 97 query := start_sql || parameter || '::INTEGER ' || end_sql; 98 RETURN query SELECT lives_ok(query); 99 100 query := start_sql || parameter || '::BIGINT ' || end_sql; 101 RETURN query SELECT lives_ok(query); 102 103 query := start_sql || parameter || '::REAL ' || end_sql; 104 RETURN query SELECT throws_ok(query); 105 106 query := start_sql || parameter || '::FLOAT8 ' || end_sql; 107 RETURN query SELECT throws_ok(query); 108 109 query := start_sql || parameter || '::NUMERIC ' || end_sql; 110 RETURN query SELECT throws_ok(query); 111END; 112$BODY$ LANGUAGE plpgsql; 113 114CREATE OR REPLACE FUNCTION test_anyNumericalOrders(fn TEXT, rest_sql TEXT, params TEXT[], parameter TEXT) 115RETURNS SETOF TEXT AS 116$BODY$ 117DECLARE 118start_sql TEXT; 119end_sql TEXT; 120query TEXT; 121p TEXT; 122BEGIN 123 start_sql = 'select * from ' || fn || '($$ SELECT '; 124 FOREACH p IN ARRAY params LOOP 125 IF p = parameter THEN CONTINUE; 126 END IF; 127 start_sql = start_sql || p || ', '; 128 END LOOP; 129 end_sql = ' FROM orders LIMIT 1 $$, $$ SELECT * from Vehicles $$' || rest_sql; 130 131 query := start_sql || parameter || '::SMALLINT ' || end_sql; 132 RETURN query SELECT lives_ok(query); 133 134 query := start_sql || parameter || '::INTEGER ' || end_sql; 135 RETURN query SELECT lives_ok(query); 136 137 query := start_sql || parameter || '::BIGINT ' || end_sql; 138 RETURN query SELECT lives_ok(query); 139 140 query := start_sql || parameter || '::REAL ' || end_sql; 141 RETURN query SELECT lives_ok(query); 142 143 query := start_sql || parameter || '::FLOAT8 ' || end_sql; 144 RETURN query SELECT lives_ok(query); 145 146 query := start_sql || parameter || '::NUMERIC ' || end_sql; 147 RETURN query SELECT lives_ok(query); 148END; 149$BODY$ LANGUAGE plpgsql; 150 151 152