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