CREATE OR REPLACE FUNCTION {SCHEMA_NAME}.childjobs(v_parent_id BIGINT)
RETURNS SETOF {SCHEMA_NAME}.postjobs AS $$
BEGIN
RETURN QUERY SELECT {SCHEMA_NAME}.postjobs.* FROM {SCHEMA_NAME}.postjobs WHERE parent_id=v_parent_id ORDER BY id;
END; $$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION {SCHEMA_NAME}.unresolved_childjobs(v_parent_id BIGINT)
RETURNS SETOF {SCHEMA_NAME}.postjobs AS $$
BEGIN
RETURN QUERY SELECT {SCHEMA_NAME}.postjobs.* FROM {SCHEMA_NAME}.postjobs WHERE status NOT IN ('ok', 'failed') AND parent_id=v_parent_id ORDER BY next_run_at;
END; $$ LANGUAGE plpgsql;
DROP FUNCTION IF EXISTS {SCHEMA_NAME}.find_or_create_childjob(
v_queue VARCHAR, v_workflow VARCHAR, v_workflow_method VARCHAR, v_workflow_version VARCHAR, v_args JSONB, v_parent_id BIGINT, v_tags JSONB, v_max_attempts INTEGER, v_timeout DOUBLE PRECISION); -- removed in 0.5.0
CREATE OR REPLACE FUNCTION {SCHEMA_NAME}.find_or_create_childjob(
p_worker_session_id UUID, v_queue VARCHAR, v_workflow VARCHAR, v_workflow_method VARCHAR, v_workflow_version VARCHAR, v_args JSONB, v_parent_id BIGINT, v_tags JSONB, v_max_attempts INTEGER, v_timeout DOUBLE PRECISION) RETURNS SETOF {SCHEMA_NAME}.postjobs AS $$
DECLARE
child_id BIGINT; parent {SCHEMA_NAME}.postjobs;
BEGIN
IF v_parent_id IS NULL THEN RAISE 'Invalid parent job id NULL'; END IF; -- create a child job if it doesn't exist yet. SELECT id INTO child_id FROM {SCHEMA_NAME}.postjobs WHERE parent_id=v_parent_id AND workflow=v_workflow AND workflow_method=v_workflow_method AND args=v_args ; IF child_id IS NULL THEN SELECT * INTO parent FROM {SCHEMA_NAME}.postjobs WHERE postjobs.id=v_parent_id; SELECT id INTO child_id FROM {SCHEMA_NAME}.enqueue( p_worker_session_id, COALESCE(v_queue, parent.queue), -- queue VARCHAR, v_workflow, -- workflow VARCHAR, v_workflow_method, -- workflow_method VARCHAR, NULL, -- workflow_version VARCHAR, v_args, -- args JSONB, v_parent_id, -- parent_id BIGINT, parent.tags, -- tags JSONB, COALESCE(v_max_attempts, parent.max_attempts), -- max_attempts INTEGER, v_timeout, -- timeout NULL, -- cron_interval, parent.is_sticky, -- is_sticky?, parent.is_greedy -- is_greedy?, ); UPDATE {SCHEMA_NAME}.postjobs SET sticky_host_id=parent.sticky_host_id WHERE id=child_id; END IF; RETURN QUERY SELECT * FROM {SCHEMA_NAME}.postjobs WHERE id=child_id ;
END; $$ LANGUAGE plpgsql;