8000 add pg_tle tests (#1159) · cepro/postgres@8ba33c1 · GitHub
[go: up one dir, main page]
Skip to content

Commit 8ba33c1

Browse files
oliriceolirice
authored
add pg_tle tests (supabase#1159)
1 parent b978a2e commit 8ba33c1

File tree

2 files changed

+161
-0
lines changed

2 files changed

+161
-0
lines changed

‎nix/tests/expected/pg_tle.out

Lines changed: 91 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,91 @@
1+
select
2+
pgtle.install_extension(
3+
'pg_distance',
4+
'0.1',
5+
'Distance functions for two points',
6+
$_pg_tle_$
7+
CREATE FUNCTION dist(x1 float8, y1 float8, x2 float8, y2 float8, norm int)
8+
RETURNS float8
9+
AS $$
10+
SELECT (abs(x2 - x1) ^ norm + abs(y2 - y1) ^ norm) ^ (1::float8 / norm);
11+
$$ LANGUAGE SQL;
12+
13+
CREATE FUNCTION manhattan_dist(x1 float8, y1 float8, x2 float8, y2 float8)
14+
RETURNS float8
15+
AS $$
16+
SELECT dist(x1, y1, x2, y2, 1);
17+
$$ LANGUAGE SQL;
18+
19+
CREATE FUNCTION euclidean_dist(x1 float8, y1 float8, x2 float8, y2 float8)
20+
RETURNS float8
21+
AS $$
22+
SELECT dist(x1, y1, x2, y2, 2);
23+
$$ LANGUAGE SQL;
24+
$_pg_tle_$
25+
);
26+
install_extension
27+
-------------------
28+
t
29+
(1 row)
30+
31+
create extension pg_distance;
32+
select manhattan_dist(1, 1, 5, 5);
33+
manhattan_dist
34+
----------------
35+
8
36+
(1 row)
37+
38+
select euclidean_dist(1, 1, 5, 5);
39+
euclidean_dist
40+
-------------------
41+
5.656854249492381
42+
(1 row)
43+
44+
SELECT pgtle.install_update_path(
45+
'pg_distance',
46+
'0.1',
47+
'0.2',
48+
$_pg_tle_$
49+
CREATE OR REPLACE FUNCTION dist(x1 float8, y1 float8, x2 float8, y2 float8, norm int)
50+
RETURNS float8
51+
AS $$
52+
SELECT (abs(x2 - x1) ^ norm + abs(y2 - y1) ^ norm) ^ (1::float8 / norm);
53+
$$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
54+
55+
CREATE OR REPLACE FUNCTION manhattan_dist(x1 float8, y1 float8, x2 float8, y2 float8)
56+
RETURNS float8
57+
AS $$
58+
SELECT dist(x1, y1, x2, y2, 1);
59+
$$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
60+
61+
CREATE OR REPLACE FUNCTION euclidean_dist(x1 float8, y1 float8, x2 float8, y2 float8)
62+
RETURNS float8
63+
AS $$
64+
SELECT dist(x1, y1, x2, y2, 2);
65+
$$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
66+
$_pg_tle_$
67+
);
68+
install_update_path
69+
---------------------
70+
t
71+
(1 row)
72+
73+
select
74+
pgtle.set_default_version('pg_distance', '0.2');
75+
set_default_version
76+
---------------------
77+
t
78+
(1 row)
79+
80+
alter extension pg_distance update;
81+
drop extension pg_distance;
82+
select
83+
pgtle.uninstall_extension('pg_distance');
84+
uninstall_extension
85+
---------------------
86+
t
87+
(1 row)
88+
89+
-- Restore original state if any of the above fails
90+
drop extension pg_tle cascade;
91+
create extension pg_tle;

‎nix/tests/sql/pg_tle.sql

Lines changed: 70 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,70 @@
1+
select
2+
pgtle.install_extension(
3+
'pg_distance',
4+
'0.1',
5+
'Distance functions for two points',
6+
$_pg_tle_$
7+
CREATE FUNCTION dist(x1 float8, y1 float8, x2 float8, y2 float8, norm int)
8+
RETURNS float8
9+
AS $$
10+
SELECT (abs(x2 - x1) ^ norm + abs(y2 - y1) ^ norm) ^ (1::float8 / norm);
11+
$$ LANGUAGE SQL;
12+
13+
CREATE FUNCTION manhattan_dist(x1 float8, y1 float8, x2 float8, y2 float8)
14+
RETURNS float8
15+
AS $$
16+
SELECT dist(x1, y1, x2, y2, 1);
17+
$$ LANGUAGE SQL;
18+
19+
CREATE FUNCTION euclidean_dist(x1 float8, y1 float8, x2 float8, y2 float8)
20+
RETURNS float8
21+
AS $$
22+
SELECT dist(x1, y1, x2, y2, 2);
23+
$$ LANGUAGE SQL;
24+
$_pg_tle_$
25+
);
26+
27+
create extension pg_distance;
28+
29+
select manhattan_dist(1, 1, 5, 5);
30+
select euclidean_dist(1, 1, 5, 5);
31+
32+
SELECT pgtle.install_update_path(
33+
'pg_distance',
34+
'0.1',
35+
'0.2',
36+
$_pg_tle_$
37+
CREATE OR REPLACE FUNCTION dist(x1 float8, y1 float8, x2 float8, y2 float8, norm int)
38+
RETURNS float8
39+
AS $$
40+
SELECT (abs(x2 - x1) ^ norm + abs(y2 - y1) ^ norm) ^ (1::float8 / norm);
41+
$$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
42+
43+
CREATE OR REPLACE FUNCTION manhattan_dist(x1 float8, y1 float8, x2 float8, y2 float8)
44+
RETURNS float8
45+
AS $$
46+
SELECT dist(x1, y1, x2, y2, 1);
47+
$$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
48+
49+
CREATE OR REPLACE FUNCTION euclidean_dist(x1 float8, y1 float8, x2 float8, y2 float8)
50+
RETURNS float8
51+
AS $$
52+
SELECT dist(x1, y1, x2, y2, 2);
53+
$$ LANGUAGE SQL IMMUTABLE PARALLEL SAFE;
54+
$_pg_tle_$
55+
);
56+
57+
58+
select
59+
pgtle.set_default_version('pg_distance', '0.2');
60+
61+
alter extension pg_distance update;
62+
63+
drop extension pg_distance;
64+
65+
select
66+
pgtle.uninstall_extension('pg_distance');
67+
68+
-- Restore original state if any of the above fails
69+
drop extension pg_tle cascade;
70+
create extension pg_tle;

0 commit comments

Comments
 (0)
0