Differential D1378 Diff 4470 swh/storage/sql/40-swh-func.sql

Changeset View

Standalone View

swh/storage/sql/40-swh-func.sql

Show First 20 Lines • Show All 617 Lines • ▼ Show 20 Lines	create or replace function swh_person_add_from_revision()
language plpgsql		language plpgsql
as $$		as $$
begin		begin
with t as (		with t as (
select author_fullname as fullname, author_name as name, author_email as email from tmp_revision		select author_fullname as fullname, author_name as name, author_email as email from tmp_revision
union		union
select committer_fullname as fullname, committer_name as name, committer_email as email from tmp_revision		select committer_fullname as fullname, committer_name as name, committer_email as email from tmp_revision
) insert into person (fullname, name, email)		) insert into person (fullname, name, email)
select distinct fullname, name, email from t		select distinct on (fullname) fullname, name, email from t
where not exists (		where not exists (
select 1		select 1
from person p		from person p
where t.fullname = p.fullname		where t.fullname = p.fullname
);		);
return;		return;
end		end
$$;		$$;
Show All 22 Lines	create or replace function swh_person_add_from_release()
returns void		returns void
language plpgsql		language plpgsql
as $$		as $$
begin		begin
with t as (		with t as (
select distinct author_fullname as fullname, author_name as name, author_email as email from tmp_release		select distinct author_fullname as fullname, author_name as name, author_email as email from tmp_release
where author_fullname is not null		where author_fullname is not null
) insert into person (fullname, name, email)		) insert into person (fullname, name, email)
select fullname, name, email from t		select distinct on (fullname) fullname, name, email from t
where not exists (		where not exists (
select 1		select 1
from person p		from person p
where t.fullname = p.fullname		where t.fullname = p.fullname
);		);
return;		return;
end		end
$$;		$$;
▲ Show 20 Lines • Show All 446 Lines • Show Last 20 Lines