forked from orafce/orafce
-
Notifications
You must be signed in to change notification settings - Fork 0
/
orafce--3.6--3.7.sql
57 lines (45 loc) · 1.8 KB
/
orafce--3.6--3.7.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
-- bugfixes
GRANT USAGE ON SCHEMA oracle TO PUBLIC;
GRANT USAGE ON SCHEMA plunit TO PUBLIC;
CREATE OR REPLACE FUNCTION oracle.round(float4, int)
RETURNS numeric
AS $$SELECT pg_catalog.round($1::numeric, $2)$$
LANGUAGE sql IMMUTABLE STRICT;
CREATE OR REPLACE FUNCTION oracle.trunc(float4, int)
RETURNS numeric
AS $$SELECT pg_catalog.trunc($1::numeric, $2)$$
LANGUAGE sql IMMUTABLE STRICT;
COMMENT ON FUNCTION oracle.sessiontimezone() IS 'Ruturns session time zone';
COMMENT ON FUNCTION oracle.dbtimezone() IS 'Ruturns server time zone (orafce.timezone)';
CREATE OR REPLACE FUNCTION oracle.nvl(bigint, int)
RETURNS bigint AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql IMMUTABLE;
CREATE OR REPLACE FUNCTION oracle.nvl(numeric, int)
RETURNS numeric AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql IMMUTABLE;
CREATE OR REPLACE FUNCTION oracle.add_months(TIMESTAMP WITH TIME ZONE,INTEGER)
RETURNS TIMESTAMP
AS $$ SELECT (pg_catalog.add_months($1::pg_catalog.date, $2) + $1::time)::oracle.date; $$
LANGUAGE SQL IMMUTABLE STRICT;
drop view oracle.user_tables;
create view oracle.user_tables as
select table_name
from information_schema.tables
where table_type = 'BASE TABLE';
-- new functionality
CREATE FUNCTION oracle.orafce_concat2(varchar2, varchar2)
RETURNS varchar2
AS 'MODULE_PATHNAME','orafce_concat2'
LANGUAGE C IMMUTABLE;
CREATE FUNCTION oracle.orafce_concat2(nvarchar2, nvarchar2)
RETURNS nvarchar2
AS 'MODULE_PATHNAME','orafce_concat2'
LANGUAGE C STABLE;
CREATE OPERATOR || (procedure = oracle.orafce_concat2, leftarg = varchar2, rightarg = varchar2);
CREATE OPERATOR || (procedure = oracle.orafce_concat2, leftarg = nvarchar2, rightarg = nvarchar2);
CREATE OR REPLACE FUNCTION oracle.numtodsinterval(double precision, text)
RETURNS interval AS $$
SELECT $1 * ('1' || $2)::interval
$$ LANGUAGE sql IMMUTABLE STRICT;