1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
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
|
-
+
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
|
CREATE PROCEDURAL LANGUAGE plpgsql;
-- general array sorting functions
-- sorts array
CREATE FUNCTION sort(original anyarray) RETURNS anyarray
CREATE or replace FUNCTION sort(original anyarray) RETURNS anyarray
LANGUAGE sql IMMUTABLE STRICT
AS $_$
select array_agg(item) as result from (select unnest($1) as item order by item) a;
$_$;
-- sorts array and removes duplicates
CREATE FUNCTION usort(original anyarray) RETURNS anyarray
CREATE or replace FUNCTION usort(original anyarray) RETURNS anyarray
LANGUAGE sql IMMUTABLE STRICT
AS $_$
select array_agg(item) as result from (select distinct unnest($1) as item order by item) a;
$_$;
-- this functions returns id of site
create or replace function get_site(my_site text[]) returns integer
language plpgsql strict
as $$
declare
site_id integer;
begin
select id_site from site where my_site = site into site_id;
if not found then
insert into site (site) values (my_site);
select id_site from site where my_site = site into site_id;
end if;
return site_id;
end;
$$;
create or replace function get_site(domain text) returns integer
language sql immutable strict
as $$
select get_site(tripdomain($1)) as result;
$$;
-- this function adds tag to domain
CREATE or replace FUNCTION mark(domain text, new_tag text) RETURNS void
LANGUAGE sql immutable STRICT
AS $$
select mark(get_site($1), $2) as result;
$$;
|
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
|
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
|
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
-
+
|
insert into tag (tag) values (usort(my_tag));
select id_tag from tag where usort(my_tag) = tag into tag_id;
end if;
return tag_id;
end;
$$;
-- this functions returns id of site
create or replace function get_site(domain text) returns integer
language sql immutable strict
as $$
select get_site(tripdomain($1)) as result;
$$;
create or replace function get_site(my_site text[]) returns integer
language plpgsql strict
as $$
declare
site_id integer;
begin
select id_site from site where my_site = site into site_id;
if not found then
insert into site (site) values (my_site);
select id_site from site where my_site = site into site_id;
end if;
return site_id;
end;
$$;
CREATE FUNCTION tripdomain(url text) RETURNS text[]
CREATE or replace FUNCTION tripdomain(url text) RETURNS text[]
LANGUAGE plpgsql IMMUTABLE STRICT
AS $_$
declare
result text[];
splitted text[];
x integer;
length integer;
begin
splitted := string_to_array($1, '.');
length := array_length(splitted, 1);
x := 1;
loop
exit when splitted[x] is null;
result[x] := splitted[x] || ':' || length - x;
x := x + 1;
end loop;
return result;
end;$_$;
-- transforms ordered array into domain
create function untrip(site text[]) returns text
create or replace function untrip(site text[]) returns text
language plpgsql immutable strict
as $_$
declare
x integer;
splitted text[];
pair text[];
begin
|