CREATE 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 function adds tag to domain
CREATE or replace FUNCTION mark(domain text, new_tag text) RETURNS void
	LANGUAGE plpgsql STRICT
	AS $$
declare
	my_site text[];
	my_id_site smallint;
	my_tag text[];
	my_id_tag smallint;
begin
	my_site := tripdomain(domain);

	-- selecting site id from table or adding site to the table
	select id_site from site where my_site = site into my_id_site;
	if not found then
		insert into site (site) values (my_site);
		select id_site from site where my_site = site into my_id_site;
	end if;

	-- 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 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]));
		my_tag := array[new_tag];
	else
		-- joining tags
		select usort(my_tag || array[new_tag]) into my_tag;
		-- deleting old site specification


		update urls set id_tag = get_tag(my_tag || array[new_tag]) where id_site = my_id_site;





	end if;



end;
$$;

-- this function adds tag to site by site id
CREATE or replace FUNCTION mark(my_id_site integer, new_tag text) RETURNS void
	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;
end;
$$;


-- this function returns id of tag array
create or replace function get_tag(my_tag text[]) returns integer
	language plpgsql strict
	as $$
declare
	tag_id integer;
begin
	select id_tag from tag where usort(my_tag) = tag into tag_id;
	if not found then
		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;
$$;

-- transforms domain into ordered array for indexing
CREATE FUNCTION tripdomain(url text) RETURNS text[]
	LANGUAGE plpgsql IMMUTABLE STRICT
	AS $_$

-- table to hold site arrays
CREATE TABLE site (
	id_site serial,
	site text[] NOT NULL
);

ALTER TABLE ONLY site
	ADD CONSTRAINT id_site PRIMARY KEY (id_site);

CREATE UNIQUE INDEX site_s ON site (usort(site));

CREATE INDEX site_g ON site USING gin (site);

-- table to hold tag combinations
CREATE TABLE tag (