babelfish_extensions/contrib/babelfishpg_common/sql/string_operators.sql at 0cd0a3d2ee455f1a188b4c98a42716fb2c4a8bfb · pranavJ23/babelfish_extensions · GitHub

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
-- Wrap built-in CONCAT function to accept two text arguments.
-- This is necessary because CONCAT accepts arguments of type VARIADIC "any".
-- CONCAT also automatically handles NULL which || does not.
CREATE OR REPLACE FUNCTION sys.babelfish_concat_wrapper(leftarg text, rightarg text) RETURNS TEXT
AS 'babelfishpg_tsql', 'babelfish_concat_wrapper'
LANGUAGE C STABLE PARALLEL SAFE;

CREATE OR REPLACE FUNCTION sys.babelfish_concat_wrapper_outer(leftarg text, rightarg text) RETURNS sys.varchar(8000) AS
$$
  SELECT sys.babelfish_concat_wrapper(cast(leftarg as text), cast(rightarg as text))
$$
LANGUAGE SQL STABLE;

-- Support strings for + operator.
CREATE OPERATOR sys.+ (
    LEFTARG = text,
    RIGHTARG = text,
    FUNCTION = sys.babelfish_concat_wrapper_outer
);

CREATE OR REPLACE FUNCTION sys.babelfish_concat_wrapper(leftarg sys.varchar, rightarg sys.varchar) RETURNS sys.varchar(8000) AS
$$
  SELECT sys.babelfish_concat_wrapper(cast(leftarg as text), cast(rightarg as text))
$$
LANGUAGE SQL STABLE;

-- Support strings for + operator.
CREATE OPERATOR sys.+ (
    LEFTARG = sys.varchar,
    RIGHTARG = sys.varchar,
    FUNCTION = sys.babelfish_concat_wrapper
);

CREATE OR REPLACE FUNCTION sys.babelfish_concat_wrapper(leftarg sys.nvarchar, rightarg sys.nvarchar) RETURNS sys.nvarchar(8000) AS
$$
  SELECT sys.babelfish_concat_wrapper(cast(leftarg as text), cast(rightarg as text))
$$
LANGUAGE SQL STABLE;

-- Support strings for + operator.
CREATE OPERATOR sys.+ (
    LEFTARG = sys.nvarchar,
    RIGHTARG = sys.nvarchar,
    FUNCTION = sys.babelfish_concat_wrapper
);

CREATE OR REPLACE FUNCTION sys.babelfish_concat_wrapper(leftarg sys.bpchar, rightarg sys.bpchar) RETURNS sys.varchar(8000) AS
$$
  SELECT sys.babelfish_concat_wrapper(cast(leftarg as text), cast(rightarg as text))
$$
LANGUAGE SQL STABLE;

-- Support strings for + operator.
CREATE OPERATOR sys.+ (
    LEFTARG = sys.bpchar,
    RIGHTARG = sys.bpchar,
    FUNCTION = sys.babelfish_concat_wrapper
);

CREATE OR REPLACE FUNCTION sys.babelfish_concat_wrapper(leftarg sys.nchar, rightarg sys.nchar) RETURNS sys.nvarchar(8000) AS
$$
  SELECT sys.babelfish_concat_wrapper(cast(leftarg as text), cast(rightarg as text))
$$
LANGUAGE SQL STABLE;

-- Support strings for + operator.
CREATE OPERATOR sys.+ (
    LEFTARG = sys.nchar,
    RIGHTARG = sys.nchar,
    FUNCTION = sys.babelfish_concat_wrapper
);

-- if one of input is nvarchar, resolve it as nvarchar. as varchar is a base type of nvarchar, we need to define this function explictly.
CREATE OR REPLACE FUNCTION sys.babelfish_concat_wrapper(leftarg sys.varchar, rightarg sys.nvarchar) RETURNS sys.nvarchar(8000) AS
$$
  SELECT sys.babelfish_concat_wrapper(cast(leftarg as text), cast(rightarg as text))
$$
LANGUAGE SQL STABLE;

-- Support strings for + operator.
CREATE OPERATOR sys.+ (
    LEFTARG = sys.varchar,
    RIGHTARG = sys.nvarchar,
    FUNCTION = sys.babelfish_concat_wrapper
);

CREATE OR REPLACE FUNCTION sys.babelfish_concat_wrapper(leftarg sys.nvarchar, rightarg sys.varchar) RETURNS sys.nvarchar(8000) AS
$$
  SELECT sys.babelfish_concat_wrapper(cast(leftarg as text), cast(rightarg as text))
$$
LANGUAGE SQL STABLE;

-- Support strings for + operator.
CREATE OPERATOR sys.+ (
    LEFTARG = sys.nvarchar,
    RIGHTARG = sys.varchar,
    FUNCTION = sys.babelfish_concat_wrapper
);

create or replace function sys.cht_(x in int) returns sys.varchar
AS
$body$
BEGIN
/***************************************************************
EXTENSION PACK function CHAR(x)
***************************************************************/
    if x = 0 then
        return ('\x00'::bytea)::sys.varbinary;
    end if;
    if x between 1 and 255 then
        return chr(x);
    else
        return null;
    end if;
END;
$body$
LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;

CREATE OR REPLACE FUNCTION sys.ncht_(x in int) RETURNS sys.nvarchar
AS
$body$
BEGIN
    if x = 0 then
        return ('\x00'::bytea)::sys.varbinary;
    end if;
    --- 65535 is 0x0000FFFF - max value permitted as specified by documentation without SC collation
    if x between 1 and 65535 then
        return(select chr(x))::sys.nvarchar;
    else
        return null;
    end if;
END;
$body$
LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE;