Skip to content

Commit 373dd66

Browse files
cvvergaracvvergara
committed
(internal SQL) Throw PostgreSQL errmsg, hint and error code
- Internal functions - _pgr_checkquery - _pgr_checkcolumn - re-throw from - pgr_extractVertices - pgr_findCloseEdges - pgr_degree - Adding tests for missing column and relation not found - (pgtap) adjusting tests because of changes on internal SQL - Documentation of the changes
1 parent 49bf02b commit 373dd66

14 files changed

Lines changed: 234 additions & 152 deletions

File tree

‎NEWS.md‎

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,19 @@
88

99
### pgRouting 3.8.0 Release Notes
1010

11-
No Changes Yet
11+
**Changes on proposed functions**
12+
13+
* pgr_extractVertices
14+
15+
* Error messages adjustment.
16+
17+
* pgr_findCloseEdges
18+
19+
* Error messages adjustment.
20+
21+
* pgr_degree
22+
23+
* Error messages adjustment.
1224

1325
## pgRouting 3.7
1426

‎doc/src/release_notes.rst‎

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -39,7 +39,25 @@ pgRouting 3.8
3939
pgRouting 3.8.0 Release Notes
4040
-------------------------------------------------------------------------------
4141

42-
No Changes Yet
42+
.. rubric:: Changes on proposed functions
43+
44+
* pgr_extractVertices
45+
46+
.. include:: pgr_extractVertices.rst
47+
:start-after: Version 3.8.0
48+
:end-before: .. rubric
49+
50+
* pgr_findCloseEdges
51+
52+
.. include:: pgr_findCloseEdges.rst
53+
:start-after: Version 3.8.0
54+
:end-before: .. rubric
55+
56+
* pgr_degree
57+
58+
.. include:: pgr_degree.rst
59+
:start-after: Version 3.8.0
60+
:end-before: .. rubric
4361

4462
pgRouting 3.7
4563
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

‎doc/topology/pgr_degree.rst‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -25,9 +25,13 @@
2525

2626
.. rubric:: Availability
2727

28-
* Version 3.4.0
28+
.. rubric:: Version 3.8.0
2929

30-
* New proposed function.
30+
* Error messages adjustment.
31+
32+
.. rubric:: Version 3.4.0
33+
34+
* New proposed function.
3135

3236

3337
Description

‎doc/topology/pgr_extractVertices.rst‎

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -25,13 +25,17 @@
2525

2626
.. rubric:: Availability
2727

28-
* Version 3.3.0
28+
.. rubric:: Version 3.8.0
2929

30-
* Classified as **proposed** function
30+
* Error messages adjustment.
3131

32-
* Version 3.0.0
32+
.. rubric:: Version 3.3.0
3333

34-
* New experimental function.
34+
* Classified as **proposed** function
35+
36+
.. rubric:: Version 3.0.0
37+
38+
* New experimental function.
3539

3640

3741
Description

‎doc/utilities/pgr_findCloseEdges.rst‎

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -20,9 +20,13 @@
2020

2121
.. rubric:: Availability
2222

23-
* Version 3.4.0
23+
.. rubric:: Version 3.8.0
2424

25-
* New proposed function.
25+
* Error messages adjustment.
26+
27+
.. rubric:: Version 3.4.0
28+
29+
* New proposed function.
2630

2731
Description
2832
-------------------------------------------------------------------------------

‎pgtap/topology/degree/edge_cases.pg‎

Lines changed: 26 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -20,7 +20,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
2020
BEGIN;
2121

2222
UPDATE edges SET cost = sign(cost), reverse_cost = sign(reverse_cost);
23-
SELECT CASE WHEN min_version('3.4.0') THEN plan(12) ELSE plan(1) END;
23+
SELECT CASE WHEN min_version('3.4.0') THEN plan(16) ELSE plan(1) END;
2424

2525

2626
CREATE OR REPLACE FUNCTION edge_cases()
@@ -59,11 +59,13 @@ BEGIN
5959
SELECT * FROM pgr_degree('edges','SELECT id, out_edges FROM vertices');
6060

6161
RETURN QUERY SELECT lives_ok('query_1', 'Good execution');
62-
RETURN QUERY SELECT throws_ok('query_2', 'P0001', 'Missing column', 'Incomlete data -> throws');
63-
RETURN QUERY SELECT throws_ok('query_3', 'P0001', 'Missing column', 'Incomlete data -> throws');
64-
RETURN QUERY SELECT throws_ok('query_4', 'P0001', 'Missing column', 'Incomlete data -> throws');
62+
RETURN QUERY SELECT column_missing('query_2', 'id');
63+
RETURN QUERY SELECT column_missing('query_3', 'in_edges');
64+
RETURN QUERY SELECT column_missing('query_4', 'id');
6565
RETURN QUERY SELECT lives_ok('query_5', 'Missing column out_edges but usable');
6666
RETURN QUERY SELECT lives_ok('query_5', 'Missing column in_edges but usable');
67+
RETURN QUERY SELECT wrong_relation($$SELECT * FROM pgr_degree('SELECT id FROM foo', 'SELECT * FROM vertices')$$, 'foo');
68+
RETURN QUERY SELECT wrong_relation($$SELECT * FROM pgr_degree('SELECT id FROM edges', 'SELECT * FROM bar')$$, 'bar');
6769

6870
PREPARE subedges AS
6971
SELECT id FROM edges WHERE id < 17;
@@ -90,12 +92,29 @@ BEGIN
9092
SELECT * FROM pgr_degree('subedges','SELECT id, out_edges FROM vertices');
9193

9294
RETURN QUERY SELECT lives_ok('query_7', 'Good execution');
93-
RETURN QUERY SELECT throws_ok('query_8', 'P0001', 'Missing column', 'Incomlete data -> throws');
94-
RETURN QUERY SELECT throws_ok('query_9', 'P0001', 'Missing column', 'Incomlete data -> throws');
95-
RETURN QUERY SELECT throws_ok('query_10', 'P0001', 'Missing column', 'Incomlete data -> throws');
95+
RETURN QUERY SELECT column_missing('query_8', 'id');
96+
RETURN QUERY SELECT column_missing('query_9', 'in_edges');
97+
RETURN QUERY SELECT column_missing('query_10', 'id');
9698
RETURN QUERY SELECT lives_ok('query_11', 'Missing column out_edges but usable');
9799
RETURN QUERY SELECT lives_ok('query_12', 'Missing column in_edges but usable');
98100

101+
PREPARE empty_vertices AS
102+
SELECT * FROM pgr_degree('SELECT id FROM edges', 'SELECT * FROM vertices WHERE id > 20');
103+
RETURN QUERY SELECT is_empty('empty_vertices', 'Empty vertices give empty result');
104+
105+
PREPARE empty_graph AS
106+
SELECT * FROM pgr_degree('SELECT id FROM edges WHERE id > 20', 'SELECT * FROM vertices') ORDER BY node;
107+
RETURN QUERY
108+
SELECT CASE WHEN min_version('3.8.0') THEN
109+
collect_tap(
110+
is_empty('empty_graph', 'is empty: Empty edges give 0 count on all vertices so none is in result')
111+
)
112+
ELSE
113+
collect_tap(
114+
results_eq('empty_graph', 'SELECT generate_series (1,17)::BIGINT, 0::BIGINT', 'Empty edges give 0 count')
115+
)
116+
END;
117+
99118
END;
100119
$BODY$
101120
LANGUAGE plpgsql;

‎pgtap/topology/extractVertices/edge_cases.pg‎

Lines changed: 26 additions & 86 deletions
Original file line numberDiff line numberDiff line change
@@ -25,103 +25,43 @@ SELECT plan(30);
2525
UPDATE edges SET cost = sign(cost) + 0.001 * id * id, reverse_cost = sign(reverse_cost) + 0.001 * id * id;
2626

2727
--
28-
PREPARE query_1 AS
29-
SELECT * FROM pgr_extractVertices(
30-
'SELECT source
31-
FROM edges'
32-
);
33-
34-
PREPARE query_2 AS
35-
SELECT *
36-
FROM pgr_extractVertices(
37-
'SELECT target
38-
FROM edges'
39-
);
40-
41-
SELECT throws_ok('query_1', 'P0001', 'Missing column', 'Incomlete data -> throws');
42-
SELECT throws_ok('query_2', 'P0001', 'Missing column', 'Incomlete data -> throws');
4328

44-
--
45-
PREPARE query_3 AS
46-
SELECT *
47-
FROM pgr_extractVertices(
48-
'SELECT ST_StartPoint(geom) AS startpoint
49-
FROM edges'
50-
);
51-
52-
PREPARE query_4 AS
53-
SELECT *
54-
FROM pgr_extractVertices(
55-
'SELECT ST_EndPoint(geom) AS endpoint
56-
FROM edges'
57-
);
58-
59-
SELECT throws_ok('query_3', 'P0001', 'Missing column', 'Incomlete data -> throws');
60-
SELECT throws_ok('query_4', 'P0001', 'Missing column', 'Incomlete data -> throws');
29+
PREPARE test_1(TEXT) AS
30+
SELECT * FROM pgr_extractVertices('SELECT ' || $1 || ' FROM edges');
31+
32+
SELECT column_missing($$test_1('source')$$, 'target');
33+
SELECT column_missing($$test_1('target')$$, 'source');
6134

6235
--
6336

64-
PREPARE query_5 AS
65-
SELECT * FROM pgr_extractVertices(
66-
'SELECT source, geom
67-
FROM edges'
68-
);
37+
SELECT column_missing($$"test_1"('ST_StartPoint(geom) AS startpoint')$$, 'endpoint');
38+
SELECT column_missing($$"test_1"('ST_EndPoint(geom) AS endpoint')$$, 'startpoint');
6939

70-
PREPARE query_6 AS
71-
SELECT *
72-
FROM pgr_extractVertices(
73-
'SELECT target, geom
74-
FROM edges'
75-
);
40+
--
7641

77-
SELECT lives_ok('query_5', 'geom column makes data complete');
78-
SELECT lives_ok('query_6', 'geom column makes data complete');
42+
SELECT lives_ok($$"test_1"('source, geom')$$, 'geom column makes data complete');
43+
SELECT lives_ok($$"test_1"('target, geom')$$, 'geom column makes data complete');
7944

8045
--
81-
PREPARE query_7 AS
82-
SELECT *
83-
FROM pgr_extractVertices(
84-
'SELECT ST_StartPoint(geom) AS startpoint, geom
85-
FROM edges'
86-
);
87-
88-
PREPARE query_8 AS
89-
SELECT *
90-
FROM pgr_extractVertices(
91-
'SELECT ST_EndPoint(geom) AS endpoint, geom
92-
FROM edges'
93-
);
94-
95-
SELECT lives_ok('query_7', 'geom column makes data complete');
96-
SELECT lives_ok('query_8', 'geom column makes data complete');
9746

98-
--
47+
SELECT lives_ok($$"test_1"('ST_StartPoint(geom) AS startpoint, geom')$$, 'geom column makes data complete');
48+
SELECT lives_ok($$"test_1"('ST_EndPoint(geom) AS endpoint, geom')$$, 'geom column makes data complete');
9949

100-
SELECT set_eq(
101-
$$SELECT count(*) FROM pgr_extractVertices( 'SELECT geom FROM edges')$$,
102-
$$VALUES (17)$$,
103-
'17: Number of vertices extracted');
104-
SELECT set_eq(
105-
$$SELECT count(*) FROM pgr_extractVertices( 'SELECT ST_StartPoint(geom) AS startpoint, ST_EndPoint(geom) AS endpoint FROM edges')$$,
106-
$$VALUES (17)$$,
107-
'17: Number of vertices extracted');
108-
SELECT set_eq(
109-
$$SELECT count(*) FROM pgr_extractVertices( 'SELECT source, target FROM edges')$$,
110-
$$VALUES (17)$$,
111-
'17: Number of vertices extracted');
50+
--
11251

113-
SELECT set_eq(
114-
$$SELECT count(*) FROM pgr_extractVertices( 'SELECT id, geom FROM edges')$$,
115-
$$VALUES (17)$$,
116-
'17: Number of vertices extracted');
117-
SELECT set_eq(
118-
$$SELECT count(*) FROM pgr_extractVertices( 'SELECT id, ST_StartPoint(geom) AS startpoint, ST_EndPoint(geom) AS endpoint FROM edges')$$,
119-
$$VALUES (17)$$,
120-
'17: Number of vertices extracted');
121-
SELECT set_eq(
122-
$$SELECT count(*) FROM pgr_extractVertices( 'SELECT id, source, target FROM edges')$$,
123-
$$VALUES (17)$$,
124-
'17: Number of vertices extracted');
52+
SELECT is((SELECT count(*) FROM pgr_extractVertices('SELECT geom FROM edges')),
53+
17::BIGINT, '17 vertices with geom');
54+
SELECT is((SELECT count(*) FROM pgr_extractVertices('SELECT ST_StartPoint(geom) AS startpoint, ST_EndPoint(geom) AS endpoint FROM edges')),
55+
17::BIGINT, '17 vertices with points');
56+
SELECT is((SELECT count(*) FROM pgr_extractVertices('SELECT source, target FROM edges')),
57+
17::BIGINT, '17 vertices with source, target');
58+
59+
SELECT is((SELECT count(*) FROM pgr_extractVertices('SELECT id, geom FROM edges')),
60+
17::BIGINT, '17 vertices with id, geom');
61+
SELECT is((SELECT count(*) FROM pgr_extractVertices('SELECT id, ST_StartPoint(geom) AS startpoint, ST_EndPoint(geom) AS endpoint FROM edges')),
62+
17::BIGINT, '17 vertices with id, points');
63+
SELECT is((SELECT count(*) FROM pgr_extractVertices('SELECT id, source, target FROM edges')),
64+
17::BIGINT, '17 vertices with id, source, target');
12565

12666
--
12767
SELECT set_eq(

‎pgtap/utilities/findCloseEdges/edge_cases.pg‎

Lines changed: 15 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -20,7 +20,7 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
2020
BEGIN;
2121

2222
UPDATE edges SET cost = sign(cost), reverse_cost = sign(reverse_cost);
23-
SELECT CASE WHEN min_version('3.4.0') THEN plan(12) ELSE plan(1) END;
23+
SELECT CASE WHEN min_version('3.4.0') THEN plan(16) ELSE plan(1) END;
2424
SET client_min_messages TO 'WARNING';
2525

2626

@@ -60,9 +60,14 @@ BEGIN
6060
RETURN QUERY SELECT lives_ok('query_3', 'dryrun');
6161
RETURN QUERY SELECT is_empty('query_3', 'dryrun');
6262

63-
RETURN QUERY SELECT throws_ok('err1', 'P0001', 'Missing column', 'Incomlete data -> throws');
64-
RETURN QUERY SELECT throws_ok('err2', 'P0001', 'Missing column', 'Incomlete data -> throws');
63+
RETURN QUERY SELECT column_missing('err1', 'geom');
64+
RETURN QUERY SELECT column_missing('err2', 'id');
6565

66+
RETURN QUERY SELECT wrong_relation($$SELECT * FROM pgr_findCloseEdges(
67+
'SELECT id, geom FROM foo', (SELECT geom FROM pointsOfInterest WHERE pid = 5), 0.5)$$, 'foo');
68+
RETURN QUERY SELECT throws_ok($$SELECT * FROM pgr_findCloseEdges(
69+
'SELECT id, geom FROM edges', (SELECT geom FROM bar WHERE pid = 5), 0.5)$$,
70+
'42P01', 'relation "bar" does not exist');
6671

6772
-- Many points
6873
PREPARE query_4 AS
@@ -82,10 +87,15 @@ BEGIN
8287
RETURN QUERY SELECT lives_ok('query_6', 'dryrun');
8388
RETURN QUERY SELECT is_empty('query_6', 'dryrun');
8489

85-
RETURN QUERY SELECT throws_ok('err3', 'P0001', 'Missing column', 'Incomlete data -> throws');
86-
RETURN QUERY SELECT throws_ok('err4', 'P0001', 'Missing column', 'Incomlete data -> throws');
90+
RETURN QUERY SELECT column_missing('err3', 'geom');
91+
RETURN QUERY SELECT column_missing('err4', 'id');
8792

8893

94+
RETURN QUERY SELECT wrong_relation($$SELECT * FROM pgr_findCloseEdges(
95+
'SELECT id, geom FROM foo', (SELECT array_agg(geom) FROM pointsOfInterest), 0.5)$$, 'foo');
96+
RETURN QUERY SELECT throws_ok($$SELECT * FROM pgr_findCloseEdges(
97+
'SELECT id, geom FROM edges', (SELECT array_agg(geom) FROM bar), 0.5)$$,
98+
'42P01', 'relation "bar" does not exist');
8999

90100
END;
91101
$BODY$

‎sql/common/_checkcolumn.sql‎

Lines changed: 11 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -36,12 +36,23 @@ $BODY$
3636
DECLARE
3737
has_column BOOLEAN := TRUE;
3838
rec RECORD;
39+
sqlhint TEXT;
3940

4041
BEGIN
4142

4243
BEGIN
4344
EXECUTE format('SELECT %1$s FROM ( %2$s ) AS __a__ limit 1', $2, $1);
45+
EXCEPTION WHEN OTHERS THEN
46+
BEGIN
47+
IF NOT is_optional THEN
48+
RAISE EXCEPTION '%', SQLERRM USING HINT = $1, ERRCODE = SQLSTATE;
49+
ELSE
50+
has_column := FALSE;
51+
END IF;
52+
END;
53+
END;
4454

55+
BEGIN
4556
EXECUTE format('SELECT pg_typeof(%1$s) FROM ( %2$s ) AS __a__ limit 1', $2, $1)
4657
INTO rec;
4758

@@ -89,7 +100,5 @@ END;
89100
$BODY$
90101
LANGUAGE plpgsql VOLATILE STRICT;
91102

92-
93-
-- COMMENTS
94103
COMMENT ON FUNCTION _pgr_checkColumn(TEXT, TEXT, TEXT, BOOLEAN, BOOLEAN)
95104
IS 'pgRouting internal function';

0 commit comments

Comments
 (0)