'dates','Set of exact dates when comman will be executed',

'use_same_transaction','if set of commans should be executed within the same transaction',

'last_start_available','for how long could command execution be postponed in format of interval type' ,

'max_run_time','how longtask could be executed, NULL - infinite',

'max_run_time','how longjob could be executed, NULL - infinite',

'max_instances','the number of instances run at the same time',

'onrollback','statement to be executed after rollback if one occured',

'next_time_statement','statement to be executed last to calc next execution time'

CREATEFUNCTIONschedule._get_cron_from_attrs(params jsonb) RETURNS jsonbAS

$BODY$

DECLARE

tdatestext[];

datestext[];

cron jsonb;

datestext[];

cron jsonb;

clean_cron jsonb;

Ninteger;

nametext;

IF params?'cron' THEN

EXECUTE'SELECT schedule.cron2jsontext($1::cstring)::jsonb'

INTO cron

USING params->>'cron';

ELSIF params?'rule' THEN

cron := params->'rule';

ELSIF NOT params?'date'AND NOT params?'dates' THEN

RAISE EXCEPTION'There is no information abouttask''s schedule'

USING HINT='Use''cron'' - cron string,''rule'' - json to set schedule rules or''date'' and''dates'' to set exact date(s)';

END IF;

IF params?'cron' THEN

EXECUTE'SELECT schedule.cron2jsontext($1::cstring)::jsonb'

INTO cron

USING params->>'cron';

ELSIF params?'rule' THEN

cron := params->'rule';

ELSIF NOT params?'date'AND NOT params?'dates' THEN

RAISE EXCEPTION'There is no information aboutjob''s schedule'

USING HINT='Use''cron'' - cron string,''rule'' - json to set schedule rules or''date'' and''dates'' to set exact date(s)';

END IF;

IF cronIS NOT NULLAND cron?'dates' THEN

EXECUTE'SELECT array_agg(value)::text[] from jsonb_array_elements_text($1) as X'

INTO tdates

USING cron->'dates';

ELSE

tdates :='{}'::text[];

END IF;

IF cronIS NOT NULL THEN

IF cron?'date' THEN

dates :=schedule._get_array_from_jsonb(dates, cron->'date');

END IF;

IF cron?'dates' THEN

dates :=schedule._get_array_from_jsonb(dates, cron->'dates');

END IF;

END IF;

IF params?'date' THEN

tdates := array_append(tdates, params->>'date');

END IF;

IF params?'date' THEN

dates :=schedule._get_array_from_jsonb(dates, params->'date');

END IF;

IF params?'dates' THEN

dates :=schedule._get_array_from_jsonb(dates, params->'dates');

END IF;

N := array_length(dates,1);

IF N>0 THEN

EXECUTE'SELECT array_agg(lll) FROM (SELECT distinct(date_trunc(''min'', unnest::timestamp with time zone)) as lll FROM unnest($1) ORDER BY date_trunc(''min'', unnest::timestamp with time zone)) as Z'

INTO dates USING dates;

cron := COALESCE(cron,'{}'::jsonb)|| json_build_object('dates', array_to_json(dates))::jsonb;

END IF;

clean_cron :='{}'::jsonb;

FOR nameINSELECT*FROM unnest('{dates, crontab, onstart, days, hours, wdays, mounths, minutes}'::text[])

LOOP

IF cron?name THEN

clean_cron := jsonb_set(clean_cron, array_append('{}'::text[], name), cron->name);

END IF;

END LOOP;

RETURN clean_cron;

END

$BODY$

LANGUAGE plpgsql;

IF params?'dates' THEN

EXECUTE'SELECT array_agg(value)::text[] from jsonb_array_elements_text($1) as X'

INTO dates

USING params->'dates';

tdates := array_cat(tdates, dates);

END IF;

CREATEFUNCTIONschedule._get_array_from_jsonb(dsttext[], value jsonb) RETURNStext[]AS

$BODY$

DECLARE

vtypetext;

IF value ISNULL THEN

RETURN dst;

END IF;

IF tdatesIS NOT NULLAND array_length(tdates,1)>0 THEN

EXECUTE'SELECT array_agg(lll) FROM (SELECT distinct(date_trunc(''min'', unnest::timestamp with time zone)) as lll FROM unnest($1) ORDER BY date_trunc(''min'', unnest::timestamp with time zone)) as Z'

INTO dates

USING tdates;

cron := COALESCE(cron,'{}'::jsonb)|| json_build_object('dates', array_to_json(dates))::jsonb;

END IF;

RETURN cron;

EXECUTE'SELECT jsonb_typeof($1)'

INTO vtype

USING value;

IF vtype='string' THEN

EXECUTE'SELECT array_append($1, $2->>0)'

INTO dst

USING dst, value;

ELSIF vtype='array' THEN

EXECUTE'SELECT $1 || array_agg(value)::text[] from jsonb_array_elements_text($2)'

INTO dst

USING dst, value;

ELSE

RAISE EXCEPTION'The value could be only''string'' or''array'' type';

END IF;

RETURN dst;

END

$BODY$

LANGUAGE plpgsql;

CREATEFUNCTIONschedule._get_commands_from_attrs(params jsonb) RETURNStext[]AS

$BODY$

DECLARE

commandstext[];

commandstext[];

Ninteger;

IF params?'command' THEN

EXECUTE'SELECT array_append(''{}''::text[], $1)'

INTO commands

USING params->>'command';

ELSIF params?'commands' THEN

EXECUTE'SELECT array_agg(value)::text[] from jsonb_array_elements_text($1) as X'

INTO commands

USING params->'commands';

ELSE

RAISE EXCEPTION'There is no information about what task to execute'

USING HINT='Use''command'' or''commands'' key to transmit information';

N :=0;

IF params?'command' THEN

commands :=schedule._get_array_from_jsonb(commands, params->'command');

END IF;

IF params?'commands' THEN

commands :=schedule._get_array_from_jsonb(commands, params->'commands');

END IF;

N := array_length(commands,1);

IF N isNULLor N=0 THEN

RAISE EXCEPTION'There is no information about what job to execute'

USING HINT='Use''command'' or''commands'' key to transmit information';

END IF;

RETURN commands;