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
|
end if;
return my_id_site;
end;
$$;
-- this function adds tag to site by site id
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[];
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 and regexp is null 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) where id_site = my_id_site and regexp is null;
end if;
return my_id_site;
end;
select mark(get_site($1), $2) as result;
$$;
-- this function sets tags for site without regexp
CREATE or replace FUNCTION set(my_id_site integer, my_id_tag integer) RETURNS integer
LANGUAGE sql STRICT
AS $$
delete from urls where $1 = id_site and regexp is NULL;
|
|
<
<
<
<
<
<
<
<
<
<
|
<
<
<
<
<
<
<
<
|
|
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
end if;
return my_id_site;
end;
$$;
-- this function adds tag to site by site id
CREATE or replace FUNCTION mark(my_id_site integer, new_tag text) RETURNS integer
LANGUAGE sql immutable STRICT
AS $$
select mark($1, get_tag(array[$2]), NULL) as result;
$$;
-- this function adds tag to domain
CREATE or replace FUNCTION mark(domain text, new_tag text) RETURNS integer
LANGUAGE sql immutable STRICT
AS $$
select mark(get_site($1), get_tag(array[$2]), NULL) as result;
$$;
-- this function sets tags for site without regexp
CREATE or replace FUNCTION set(my_id_site integer, my_id_tag integer) RETURNS integer
LANGUAGE sql STRICT
AS $$
delete from urls where $1 = id_site and regexp is NULL;
|