CREATE PROCEDURAL LANGUAGE plpgsql;

CREATE FUNCTION mark(domain text, new_tag text) RETURNS void
    LANGUAGE plpgsql STRICT
    AS $$
declare
 my_site varchar[];
 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 := new_tag;
  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;

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;
  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 character varying) RETURNS character varying[]
CREATE FUNCTION tripdomain(url text) RETURNS text[]
    LANGUAGE plpgsql IMMUTABLE STRICT
    AS $_$
declare
  result varchar[];
  splitted varchar[];
  reversed varchar[];
  x integer;