/
phone_format_record.sql
218 lines (180 loc) · 6.98 KB
/
phone_format_record.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
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
create or replace function public.phone_format_record(
in_country_code text,
in_area_code text,
in_local_number text,
country_code_example text,
area_code_example text,
local_number_example text,
country_code out text,
area_code out text,
local_number out text
)
returns record
stable
--returns null on null input
parallel safe
language sql
set search_path = ''
as
$$;
select
case when country_code_example is null or country_code_example = '' then country_code_example
else phone_format(in_country_code, country_code_example) end as country_code,
left(case when area_code_example is null or area_code_example = '' then area_code_example
when area_code_example ~ '^\+\d|^\d+\D+\d' then phone_format(concat_ws('', in_country_code, in_area_code), area_code_example)
else phone_format(in_area_code, area_code_example) end,
32) as area_code,
left(phone_format(
concat_ws('',
case when area_code_example is null or area_code_example = '' then in_area_code else '' end,
in_local_number),
local_number_example
), 32) as local_number;
$$;
comment on function public.phone_format_record(
in_country_code text,
in_area_code text,
in_local_number text,
country_code_example text,
area_code_example text,
local_number_example text,
country_code out text,
area_code out text,
local_number out text
) is 'Форматирует номер телефона по образцу';
------------------------------------------------------------------------------------------------------------------------
create or replace function public.phone_format_record(
in_country_code int,
in_area_code text,
in_local_number text,
country_code_example int,
area_code_example text,
local_number_example text,
country_code out int,
area_code out text,
local_number out text
)
returns record
stable
--returns null on null input
parallel safe
language sql
as
$$;
select t.country_code::int, t.area_code, t.local_number
from public.phone_format_record(in_country_code::text, in_area_code, in_local_number,
country_code_example::text, area_code_example, local_number_example) as t;
$$;
comment on function public.phone_format_record(
in_country_code int,
in_area_code text,
in_local_number text,
country_code_example int,
area_code_example text,
local_number_example text,
country_code out int,
area_code out text,
local_number out text
) is 'Форматирует номер телефона по образцу';
------------------------------------------------------------------------------------------------------------------------
create or replace function public.phone_format_record(
in_country_code int,
in_area_code text,
in_local_number text,
country_code_example text,
area_code_example text,
local_number_example text,
country_code out text,
area_code out text,
local_number out text
)
returns record
stable
--returns null on null input
parallel safe
language sql
as
$$;
select t.*
from public.phone_format_record(in_country_code::text, in_area_code, in_local_number,
country_code_example, area_code_example, local_number_example) as t;
$$;
comment on function public.phone_format_record(
in_country_code int,
in_area_code text,
in_local_number text,
country_code_example text,
area_code_example text,
local_number_example text,
country_code out text,
area_code out text,
local_number out text
) is 'Форматирует номер телефона по образцу';
------------------------------------------------------------------------------------------------------------------------
create or replace function public.phone_format_record(
in_country_code text,
in_area_code text,
in_local_number text,
country_code_example int,
area_code_example text,
local_number_example text,
country_code out int,
area_code out text,
local_number out text
)
returns record
stable
--returns null on null input
parallel safe
language sql
as
$$;
select t.country_code::int, t.area_code, t.local_number
from public.phone_format_record(in_country_code, in_area_code, in_local_number,
country_code_example::text, area_code_example, local_number_example) as t;
$$;
comment on function public.phone_format_record(
in_country_code text,
in_area_code text,
in_local_number text,
country_code_example int,
area_code_example text,
local_number_example text,
country_code out int,
area_code out text,
local_number out text
) is 'Форматирует номер телефона по образцу';
------------------------------------------------------------------------------------------------------------------------
--TEST
do $$
begin
-- int - int
assert (select t is not distinct from row(null::int, null::text, '9651234567'::text)
from public.phone_format_record(7, '965', '1234567',
null::int, null::text, '000000') as t);
-- int - text
assert (select t is not distinct from row(null::text, null::text, '9651234567'::text)
from public.phone_format_record(7, '965', '1234567',
null::text, null::text, '000000') as t);
-- text - int
assert (select t is not distinct from row(null::int, null::text, '9651234567'::text)
from public.phone_format_record('7', '965', '1234567',
null::int, null::text, '000000') as t);
-- text - text
assert (select t is not distinct from row(''::text, ''::text, '965 1234567'::text)
from public.phone_format_record('7', '965', '1234567',
'', '', '000 000') as t);
assert (select t is not distinct from row('7'::text, ''::text, '965 1234567'::text)
from public.phone_format_record('7', '965', '1234567',
'0', '', '000 000') as t);
assert (select t is not distinct from row('+7'::text, '965'::text, '123-45-67 с 9 до 18'::text)
from public.phone_format_record('7', '965', '1234567',
'+0', '000', '000-00-00 с 9 до 18') as t);
assert (select t is not distinct from row(''::text, '+7965'::text, '1234567'::text)
from public.phone_format_record('7', '965', '1234567',
'', '+000', '0000000') as t);
assert (select t is not distinct from row(''::text, '7(965)'::text, '1234567'::text)
from public.phone_format_record('7', '965', '1234567',
'', '0(000)', '0000000') as t);
end;
$$;