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
|
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
|
-
+
-
+
+
|
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
CREATE or replace FUNCTION mark(domain text, new_tag text) RETURNS integer
LANGUAGE sql immutable STRICT
AS $$
select mark(get_site($1), $2) as result;
$$;
-- this function adds tag to site by site id
CREATE or replace FUNCTION mark(my_id_site integer, new_tag text) RETURNS void
CREATE or replace FUNCTION mark(my_id_site integer, new_tag text) RETURNS integer
LANGUAGE plpgsql STRICT
AS $$
declare
-- maybe check should be added to make sure supplied site id really exists
my_tag text[];
my_tag_id integer;
begin
-- selecting tags site already have and adding new tag to them
-- note that tags should be sorted to eliminate permutations
select coalesce(tag, '{}'::text[]) from urls natural left join tag where id_site = my_id_site into my_tag;
if not found then
-- no records found - creating new tag
insert into urls (id_site, id_tag) values (my_id_site, get_tag(array[new_tag]));
else
-- joining tags
select usort(my_tag || array[new_tag]) into my_tag;
-- updating existing record
update urls set id_tag = get_tag(my_tag || array[new_tag]) where id_site = my_id_site;
end if;
return my_id_site;
end;
$$;
-- this function returns id of tag array
create or replace function get_tag(my_tag text[]) returns integer
language plpgsql strict
as $$
|