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;