<commit_before>CREATE OR REPLACE FUNCTION update_signatures_hourly(
    fromtime timestamp with time zone,
    fortime interval DEFAULT '01:00:00'::interval,
    checkdata boolean DEFAULT true
)
    RETURNS boolean
    LANGUAGE plpgsql
    SET "TimeZone" TO 'UTC'
AS $$
DECLARE
    newfortime INTERVAL;
BEGIN

-- Function for updating signature information
-- designed to be run once per UTC day.
-- running it repeatedly won't cause issues
-- combines NULL and empty signatures

-- since we do allow dynamic timestamps, check if we split over a week
-- boundary.  if so, call self recursively for the first half of the period

IF (week_begins_utc(fromtime) <> week_begins_utc(fromtime + fortime - interval '1 second')) THEN
        PERFORM update_signatures_hourly(
            fromtime,
            (week_begins_utc(fromtime + fortime) - fromtime),
            checkdata
        );
        newfortime := (fromtime + fortime) - week_begins_utc(fromtime + fortime);
        fromtime := week_begins_utc( fromtime + fortime );
        fortime := newfortime;
END IF;

-- prevent calling for a period of more than one day

IF fortime > INTERVAL '1 day' THEN
    RAISE NOTICE 'You may not execute this function on more than one day of data.';
    RETURN FALSE;
END IF;

-- create temporary table

create temporary table new_signatures
on commit drop as
select coalesce(signature,'') as signature,
    product::citext as product,
    version::citext as version,
    build, NULL::INT as product_version_id,
    min(date_processed) as first_report
from reports
where date_processed >= fromtime
    and date_processed < (fromtime + fortime)
group by signature, product, version, build;

PERFORM 1 FROM new_signatures;
IF NOT FOUND THEN
    IF checkdata THEN
        RAISE NOTICE 'No signature data found in reports for date %',updateday;
        RETURN FALSE;
    END IF;
END IF;

ANALYZE new_signatures;

-- add product IDs for betas and matching builds
update new_signatures<commit_msg>signatures-matview: Fix error with empty timespan.<commit_after>CREATE OR REPLACE FUNCTION update_signatures_hourly(
    fromtime timestamp with time zone,
    fortime interval DEFAULT '01:00:00'::interval,
    checkdata boolean DEFAULT true
)
    RETURNS boolean
    LANGUAGE plpgsql
    SET "TimeZone" TO 'UTC'
AS $$
DECLARE
    newfortime INTERVAL;
BEGIN

-- Function for updating signature information
-- designed to be run once per UTC day.
-- running it repeatedly won't cause issues
-- combines NULL and empty signatures

-- since we do allow dynamic timestamps, check if we split over a week
-- boundary.  if so, call self recursively for the first half of the period

IF (week_begins_utc(fromtime) <> week_begins_utc(fromtime + fortime - interval '1 second')) THEN
        PERFORM update_signatures_hourly(
            fromtime,
            (week_begins_utc(fromtime + fortime) - fromtime),
            checkdata
        );
        newfortime := (fromtime + fortime) - week_begins_utc(fromtime + fortime);
        fromtime := week_begins_utc( fromtime + fortime );
        fortime := newfortime;
END IF;

-- prevent calling for a period of more than one day

IF fortime > INTERVAL '1 day' THEN
    RAISE NOTICE 'You may not execute this function on more than one day of data.';
    RETURN FALSE;
END IF;

-- create temporary table

create temporary table new_signatures
on commit drop as
select coalesce(signature,'') as signature,
    product::citext as product,
    version::citext as version,
    build, NULL::INT as product_version_id,
    min(date_processed) as first_report
from reports
where date_processed >= fromtime
    and date_processed < (fromtime + fortime)
group by signature, product, version, build;

PERFORM 1 FROM new_signatures;
IF NOT FOUND THEN
    IF checkdata THEN
        RAISE NOTICE 'No signature data found in reports for the range  % to %',fromtime,fromtime + fortime;
        RETURN FALSE;
    END IF;
END IF;

ANALYZE new_signatures;

-- add product IDs for betas and matching builds
update new_signatures
{'fraction_non_alphanumeric': 0.06759443339960239, 'fraction_numerical': 0.0044731610337972166, 'mean_word_length': 3.4426048565121414, 'pattern_counts': {'":': 0, '<': 7, '<?xml version=': 0, '>': 9, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2016434', 'n_tokens_mistral': 1212, 'n_tokens_neox': 1147, 'n_words': 461}