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
|
CREATE PROCEDURAL LANGUAGE plpgsql;
CREATE FUNCTION mark(domain text, new_tag text) RETURNS void
LANGUAGE plpgsql STRICT
AS $$
declare
my_site varchar[];
my_site_id smallint;
my_tag text[];
my_tag_id smallint;
begin
my_site := tripdomain(domain);
select id_site from site where my_site = site into my_site_id;
if not found then
insert into site (site) values (my_site);
select id_site from site where my_site = site into my_site_id;
end if;
select tag from urls natural join tag where id_site = my_site_id into my_tag;
if not found then
my_tag := new_tag;
else
select array_agg(tag) from (select distinct unnest(my_tag || array[new_tag]) as tag order by tag asc) a into
my_tag;
delete from urls where id_site = my_site_id;
end if;
select id_tag from tag where my_tag = tag into my_tag_id;
|
|
|
|
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
|
CREATE PROCEDURAL LANGUAGE plpgsql;
CREATE FUNCTION mark(domain text, new_tag text) RETURNS void
LANGUAGE plpgsql STRICT
AS $$
declare
my_site text[];
my_site_id smallint;
my_tag text[];
my_tag_id smallint;
begin
my_site := tripdomain(domain);
select id_site from site where my_site = site into my_site_id;
if not found then
insert into site (site) values (my_site);
select id_site from site where my_site = site into my_site_id;
end if;
select tag from urls natural join tag where id_site = my_site_id into my_tag;
if not found then
my_tag := array[new_tag];
else
select array_agg(tag) from (select distinct unnest(my_tag || array[new_tag]) as tag order by tag asc) a into
my_tag;
delete from urls where id_site = my_site_id;
end if;
select id_tag from tag where my_tag = tag into my_tag_id;
|
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
|
declare
my_tag text[];
my_tag_id smallint;
begin
select tag from urls natural join tag where id_site = my_site_id into my_tag;
if not found then
my_tag := new_tag;
else
select array_agg(tag) from (select distinct unnest(my_tag || array[new_tag]) as tag order by tag asc) a into
my_tag;
delete from urls where id_site = my_site_id;
end if;
select id_tag from tag where my_tag = tag into my_tag_id;
if not found then
insert into tag (tag) values(my_tag);
select id_tag from tag where my_tag = tag into my_tag_id;
end if;
insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
end;$$;
CREATE FUNCTION tripdomain(url character varying) RETURNS character varying[]
LANGUAGE plpgsql IMMUTABLE STRICT
AS $_$
declare
result varchar[];
splitted varchar[];
reversed varchar[];
x integer;
|
|
|
|
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
|
declare
my_tag text[];
my_tag_id smallint;
begin
select tag from urls natural join tag where id_site = my_site_id into my_tag;
if not found then
my_tag := array[new_tag];
else
select array_agg(tag) from (select distinct unnest(my_tag || array[new_tag]) as tag order by tag asc) a into
my_tag;
delete from urls where id_site = my_site_id;
end if;
select id_tag from tag where my_tag = tag into my_tag_id;
if not found then
insert into tag (tag) values(my_tag);
select id_tag from tag where my_tag = tag into my_tag_id;
end if;
insert into urls (id_site, id_tag) values (my_site_id, my_tag_id);
end;$$;
CREATE FUNCTION tripdomain(url text) RETURNS text[]
LANGUAGE plpgsql IMMUTABLE STRICT
AS $_$
declare
result varchar[];
splitted varchar[];
reversed varchar[];
x integer;
|