Changeset View
Changeset View
Standalone View
Standalone View
sql/swh-func.sql
Show First 20 Lines • Show All 94 Lines • ▼ Show 20 Lines | |||||
-- create a temporary table for occurrence_history | -- create a temporary table for occurrence_history | ||||
create or replace function swh_mktemp_occurrence_history() | create or replace function swh_mktemp_occurrence_history() | ||||
returns void | returns void | ||||
language sql | language sql | ||||
as $$ | as $$ | ||||
create temporary table tmp_occurrence_history( | create temporary table tmp_occurrence_history( | ||||
like occurrence_history including defaults, | like occurrence_history including defaults, | ||||
date timestamptz not null | visit bigint not null | ||||
) on commit drop; | ) on commit drop; | ||||
alter table tmp_occurrence_history | alter table tmp_occurrence_history | ||||
drop column visits, | drop column visits, | ||||
drop column object_id; | drop column object_id; | ||||
$$; | $$; | ||||
-- create a temporary table for entity_history, sans id | -- create a temporary table for entity_history, sans id | ||||
create or replace function swh_mktemp_entity_history() | create or replace function swh_mktemp_entity_history() | ||||
▲ Show 20 Lines • Show All 715 Lines • ▼ Show 20 Lines | for origin_id in | ||||
select distinct id from origin | select distinct id from origin | ||||
loop | loop | ||||
perform swh_occurrence_update_for_origin(origin_id); | perform swh_occurrence_update_for_origin(origin_id); | ||||
end loop; | end loop; | ||||
return; | return; | ||||
end; | end; | ||||
$$; | $$; | ||||
-- add a new origin_visit for origin origin_id at date. | |||||
-- | |||||
-- Returns the new visit id. | |||||
create or replace function swh_origin_visit_add(origin_id bigint, date timestamptz) | |||||
returns bigint | |||||
language sql | |||||
as $$ | |||||
with last_known_visit as ( | |||||
select coalesce(max(visit), 0) as visit | |||||
from origin_visit | |||||
where origin = origin_id | |||||
) | |||||
insert into origin_visit (origin, date, visit, status) | |||||
values (origin_id, date, (select visit from last_known_visit) + 1, 'ongoing') | |||||
returning visit; | |||||
$$; | |||||
-- add tmp_occurrence_history entries to occurrence_history | -- add tmp_occurrence_history entries to occurrence_history | ||||
-- | -- | ||||
olasd: I think this function could be done directly in language SQL : remove the declaration, and do… | |||||
Not Done Inline ActionsIndeed ^^ ardumont: Indeed ^^ | |||||
-- operates in bulk: 0. swh_mktemp(occurrence_history), 1. COPY to tmp_occurrence_history, | -- operates in bulk: 0. swh_mktemp(occurrence_history), 1. COPY to tmp_occurrence_history, | ||||
-- 2. call this function | -- 2. call this function | ||||
create or replace function swh_occurrence_history_add() | create or replace function swh_occurrence_history_add() | ||||
Done Inline ActionsThe origin_id is not used here (it's already a column in tmp_occurrence_history). olasd: The origin_id is not used here (it's already a column in tmp_occurrence_history). | |||||
returns void | returns void | ||||
language plpgsql | language plpgsql | ||||
as $$ | as $$ | ||||
declare | declare | ||||
origin_id origin.id%type; | origin_id origin.id%type; | ||||
begin | begin | ||||
-- Create new visits | |||||
with current_visits as ( | |||||
select distinct origin, date from tmp_occurrence_history | |||||
), | |||||
new_visits as ( | |||||
select origin, date, (select coalesce(max(visit), 0) | |||||
from origin_visit ov | |||||
where ov.origin = cv.origin) as max_visit | |||||
from current_visits cv | |||||
where not exists (select 1 from origin_visit ov | |||||
where ov.origin = cv.origin and | |||||
ov.date = cv.date) | |||||
) | |||||
insert into origin_visit (origin, date, visit) | |||||
select origin, date, max_visit + row_number() over | |||||
(partition by origin | |||||
order by origin, date) | |||||
from new_visits; | |||||
-- Create or update occurrence_history | -- Create or update occurrence_history | ||||
with occurrence_history_id_visit as ( | with occurrence_history_id_visit as ( | ||||
select tmp_occurrence_history.*, object_id, visits, visit from tmp_occurrence_history | select tmp_occurrence_history.*, object_id, visits from tmp_occurrence_history | ||||
left join occurrence_history using(origin, branch, target, target_type) | left join occurrence_history using(origin, branch, target, target_type) | ||||
left join origin_visit using(origin, date) | |||||
), | ), | ||||
Done Inline ActionsWe're not pulling any data from origin_visit anymore so you can ditch this join. olasd: We're not pulling any data from origin_visit anymore so you can ditch this join. | |||||
occurrences_to_update as ( | occurrences_to_update as ( | ||||
select object_id, visit from occurrence_history_id_visit where object_id is not null | select object_id, visit from occurrence_history_id_visit where object_id is not null | ||||
), | ), | ||||
update_occurrences as ( | update_occurrences as ( | ||||
update occurrence_history | update occurrence_history | ||||
set visits = array(select unnest(occurrence_history.visits) as e | set visits = array(select unnest(occurrence_history.visits) as e | ||||
union | union | ||||
select occurrences_to_update.visit as e | select occurrences_to_update.visit as e | ||||
▲ Show 20 Lines • Show All 101 Lines • ▼ Show 20 Lines | |||||
$$; | $$; | ||||
-- Find the visit of origin id closest to date visit_date | -- Find the visit of origin id closest to date visit_date | ||||
create or replace function swh_visit_get(origin bigint) | create or replace function swh_visit_get(origin bigint) | ||||
returns origin_visit | returns origin_visit | ||||
language sql | language sql | ||||
stable | stable | ||||
as $$ | as $$ | ||||
select origin, visit, date | select origin, visit, date, status | ||||
from origin_visit | from origin_visit | ||||
where origin=origin | where origin=origin | ||||
order by date desc | order by date desc | ||||
$$; | $$; | ||||
-- Retrieve occurrence by filtering on origin_id and optionally on | -- Retrieve occurrence by filtering on origin_id and optionally on | ||||
-- branch_name and/or validity range | -- branch_name and/or validity range | ||||
▲ Show 20 Lines • Show All 360 Lines • Show Last 20 Lines |
I think this function could be done directly in language SQL : remove the declaration, and do return query with [...] returning visit;