forked from laurenz/oracle_fdw
-
Notifications
You must be signed in to change notification settings - Fork 2
/
oracle_fdw--1.2.sql
176 lines (160 loc) · 7.22 KB
/
oracle_fdw--1.2.sql
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
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
CREATE FUNCTION oracle_fdw_handler() RETURNS fdw_handler
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;
COMMENT ON FUNCTION oracle_fdw_handler()
IS 'Oracle foreign data wrapper handler';
CREATE FUNCTION oracle_fdw_validator(text[], oid) RETURNS void
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;
COMMENT ON FUNCTION oracle_fdw_validator(text[], oid)
IS 'Oracle foreign data wrapper options validator';
CREATE FUNCTION oracle_close_connections() RETURNS void
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;
COMMENT ON FUNCTION oracle_close_connections()
IS 'closes all open Oracle connections';
CREATE FUNCTION oracle_diag(name DEFAULT NULL) RETURNS text
AS 'MODULE_PATHNAME'
LANGUAGE C STABLE CALLED ON NULL INPUT;
COMMENT ON FUNCTION oracle_diag(name)
IS 'shows the version of oracle_fdw, PostgreSQL, Oracle client and Oracle server';
CREATE FUNCTION oracle_execute(server name, statement text) RETURNS void
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT;
COMMENT ON FUNCTION oracle_execute(name, text)
IS 'executes an arbitrary SQL statement with no results on the Oracle server';
CREATE FOREIGN DATA WRAPPER oracle_fdw
HANDLER oracle_fdw_handler
VALIDATOR oracle_fdw_validator;
COMMENT ON FOREIGN DATA WRAPPER oracle_fdw
IS 'Oracle foreign data wrapper';
CREATE PROCEDURE oracle_create_or_replace_stub(func_type text, name_arg text, return_type regtype) AS $$
DECLARE
proname_raw text := split_part(name_arg, '(', 1);
proname text := ltrim(rtrim(proname_raw));
BEGIN
IF lower(func_type) = 'aggregation' OR lower(func_type) = 'aggregate' OR lower(func_type) = 'agg' OR lower(func_type) = 'a' THEN
DECLARE
proargs_raw text := right(name_arg, length(name_arg) - length(proname_raw));
proargs text := ltrim(rtrim(proargs_raw));
proargs_types text := right(left(proargs, length(proargs) - 1), length(proargs) - 2);
aggproargs text := format('(%s, %s)', return_type, proargs_types);
BEGIN
BEGIN
EXECUTE format('
CREATE FUNCTION %s_sfunc%s RETURNS %s IMMUTABLE AS $inner$
BEGIN
RAISE EXCEPTION ''stub %s_sfunc%s is called'';
RETURN NULL;
END $inner$ LANGUAGE plpgsql;',
proname, aggproargs, return_type, proname, aggproargs);
EXCEPTION
WHEN duplicate_function THEN
RAISE DEBUG 'stub function for aggregation already exists (ignored)';
END;
BEGIN
IF lower(proargs_types) = '*' THEN
name_arg := format('%s(*)', proname);
END IF;
EXECUTE format('
CREATE AGGREGATE %s
(
sfunc = %s_sfunc,
stype = %s
);', name_arg, proname, return_type);
EXCEPTION
WHEN duplicate_function THEN
RAISE DEBUG 'stub aggregation already exists (ignored)';
WHEN others THEN
RAISE EXCEPTION 'stub aggregation exception';
END;
END;
ELSIF lower(func_type) = 'function' OR lower(func_type) = 'func' OR lower(func_type) = 'f' THEN
BEGIN
EXECUTE format('
CREATE FUNCTION %s RETURNS %s IMMUTABLE AS $inner$
BEGIN
RAISE EXCEPTION ''stub %s is called'';
RETURN NULL;
END $inner$ LANGUAGE plpgsql COST 1;',
name_arg, return_type, name_arg);
EXCEPTION
WHEN duplicate_function THEN
RAISE DEBUG 'stub already exists (ignored)';
END;
ELSEIF lower(func_type) = 'stable function' OR lower(func_type) = 'sfunc' OR lower(func_type) = 'sf' THEN
BEGIN
EXECUTE format('
CREATE FUNCTION %s RETURNS %s STABLE AS $inner$
BEGIN
RAISE EXCEPTION ''stub %s is called'';
RETURN NULL;
END $inner$ LANGUAGE plpgsql COST 1;',
name_arg, return_type, name_arg);
EXCEPTION
WHEN duplicate_function THEN
RAISE DEBUG 'stub already exists (ignored)';
END;
ELSEIF lower(func_type) = 'volatile function' OR lower(func_type) = 'vfunc' OR lower(func_type) = 'vf' THEN
BEGIN
EXECUTE format('
CREATE FUNCTION %s RETURNS %s VOLATILE AS $inner$
BEGIN
RAISE EXCEPTION ''stub %s is called'';
RETURN NULL;
END $inner$ LANGUAGE plpgsql COST 1;',
name_arg, return_type, name_arg);
EXCEPTION
WHEN duplicate_function THEN
RAISE DEBUG 'stub already exists (ignored)';
END;
ELSE
RAISE EXCEPTION 'not supported function type %', func_type;
BEGIN
EXECUTE format('
CREATE FUNCTION %s_sfunc RETURNS %s AS $inner$
BEGIN
RAISE EXCEPTION ''stub %s is called'';
RETURN NULL;
END $inner$ LANGUAGE plpgsql COST 1;',
name_arg, return_type, name_arg);
EXCEPTION
WHEN duplicate_function THEN
RAISE DEBUG 'stub already exists (ignored)';
END;
END IF;
END
$$ LANGUAGE plpgsql;
-- Character Funtions
CALL oracle_create_or_replace_stub('vf', 'regexp_replace(text, text)', 'text');
CALL oracle_create_or_replace_stub('vf', 'regexp_replace(text, text, text, int)', 'text');
CALL oracle_create_or_replace_stub('vf', 'regexp_replace(text, text, text, int, int)', 'text');
CALL oracle_create_or_replace_stub('vf', 'regexp_replace(text, text, text, int, int, text)', 'text');
CALL oracle_create_or_replace_stub('vf', 'to_char(text)', 'text');
CALL oracle_create_or_replace_stub('vf', 'to_char(timestamp)', 'text');
CALL oracle_create_or_replace_stub('vf', 'to_char(interval)', 'text');
-- Date and Time Functions
CALL oracle_create_or_replace_stub('vf', 'oracle_current_date()', 'date');
CALL oracle_create_or_replace_stub('vf', 'oracle_current_timestamp()', 'timestamp');
CALL oracle_create_or_replace_stub('vf', 'oracle_localtimestamp()', 'timestamp');
CALL oracle_create_or_replace_stub('vf', 'oracle_extract(text, date)', 'integer');
-- todo: oracle fdw does not support interval year to month yet
--CALL oracle_create_or_replace_stub('vf', 'oracle_extract(text, interval year to month)', 'integer');
CALL oracle_create_or_replace_stub('vf', 'oracle_extract(text, interval day to second(6))', 'double precision');
CALL oracle_create_or_replace_stub('vf', 'oracle_extract(text, timestamp)', 'double precision');
CALL oracle_create_or_replace_stub('vf', 'oracle_extract(text, timestamp with time zone)', 'double precision');
CALL oracle_create_or_replace_stub('vf', 'add_months(date, integer)', 'date');
CALL oracle_create_or_replace_stub('vf', 'last_day(date)', 'date');
CALL oracle_create_or_replace_stub('vf', 'dbtimezone()', 'text');
CALL oracle_create_or_replace_stub('vf', 'from_tz(timestamp, text)', 'timestamp with time zone');
CALL oracle_create_or_replace_stub('vf', 'months_between(date, date)', 'double precision');
CALL oracle_create_or_replace_stub('vf', 'new_time(date, text, text)', 'date');
CALL oracle_create_or_replace_stub('vf', 'next_day(date, text)', 'date');
CALL oracle_create_or_replace_stub('vf', 'numtodsinterval(integer, text)', 'interval');
CALL oracle_create_or_replace_stub('vf', 'numtoyminterval(integer, text)', 'interval');
CALL oracle_create_or_replace_stub('vf', 'oracle_round(date)', 'date');
CALL oracle_create_or_replace_stub('vf', 'oracle_round(timestamp)', 'date');
CALL oracle_create_or_replace_stub('vf', 'oracle_round(date, text)', 'date');
CALL oracle_create_or_replace_stub('vf', 'oracle_round(timestamp, text)', 'date');
-- aggregate functions
CALL oracle_create_or_replace_stub('a', 'approx_count_distinct(anyelement)', 'integer');