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
|
SET client_min_messages=NOTICE;
CREATE EXTENSION multicorn;
CREATE server multicorn_srv foreign data wrapper multicorn options (
wrapper 'multicorn.testfdw.TestForeignDataWrapper'
);
CREATE user mapping FOR current_user server multicorn_srv options (usermapping 'test');
CREATE foreign table testmulticorn (
test1 character varying,
test2 character varying
) server multicorn_srv options (
option1 'option1'
);
-- Test "normal" usage
select * from testmulticorn;
-- Test quals
select * from testmulticorn where test1 like '%0';
select * from testmulticorn where test1 ilike '%0';
-- Test columns
select test2 from testmulticorn;
-- Test subquery plan
select test1, (select max(substr(test1, 9, 1))::int as max from testmulticorn t2 where substr(t2.test1, 7, 1)::int = substr(t1.test1, 7, 1)::int) as max
from testmulticorn t1 order by max desc;
select test1, (select max(substr(test1, 9, 1))::int as max from testmulticorn t2 where t2.test1 = t1.test1) as max
from testmulticorn t1 order by max desc;
select * from testmulticorn where test1 is null;
select * from testmulticorn where test1 is not null;
select * from testmulticorn where 'grou' > test1;
select * from testmulticorn where test1 < ANY(ARRAY['grou', 'MACHIN']);
CREATE foreign table testmulticorn2 (
test1 character varying,
test2 character varying
) server multicorn_srv options (
option1 'option2'
);
select * from testmulticorn union all select * from testmulticorn2;
create function test_function_immutable () returns varchar as $$
BEGIN
RETURN 'test';
END
$$ immutable language plpgsql;
create function test_function_stable () returns varchar as $$
BEGIN
RETURN 'test';
END
$$ stable language plpgsql;
create function test_function_volatile () returns varchar as $$
BEGIN
RETURN 'test';
END
$$ volatile language plpgsql;
select * from testmulticorn where test1 like test_function_immutable();
select * from testmulticorn where test1 like test_function_stable();
select * from testmulticorn where test1 like test_function_volatile();
select * from testmulticorn where test1 like length(test2)::varchar;
\set FETCH_COUNT 1000
select * from testmulticorn;
-- Test that zero values are converted to zero and not null
ALTER FOREIGN TABLE testmulticorn options (add test_type 'int');
ALTER FOREIGN TABLE testmulticorn alter test1 type integer;
select * from testmulticorn limit 1;
select * from testmulticorn where test1 = 0;
ALTER FOREIGN TABLE testmulticorn options (drop test_type);
-- Test operations with bytea
ALTER FOREIGN TABLE testmulticorn alter test2 type bytea;
ALTER FOREIGN TABLE testmulticorn alter test1 type bytea;
select encode(test1, 'escape') from testmulticorn where test2 = 'test2 1 19'::bytea;
-- Test operations with None
ALTER FOREIGN TABLE testmulticorn options (add test_type 'None');
select * from testmulticorn;
ALTER FOREIGN TABLE testmulticorn options (set test_type 'iter_none');
select * from testmulticorn;
ALTER FOREIGN TABLE testmulticorn add test3 money;
SELECT * from testmulticorn where test3 = 12::money;
SELECT * from testmulticorn where test1 = '12 €';
DROP USER MAPPING FOR current_user SERVER multicorn_srv;
DROP EXTENSION multicorn cascade;
|
