Composing SQL queries from table expressions and functions

query-composition.sql

-- other types, base relvars (tables), derived relvars (views), and functions elided ...

create view project_pk_in_campaign_started as
select distinct cli_id, prj_id from campaign join campaign_started using (cli_id, cmp_id);

create view active_project as
select cli_id, prj_id, prj_next_cold_call_limit
from project join project_pk_in_campaign_started using (cli_id, prj_id);

create view cold_call_queued as select * from cold_call where cc_status = 'queued';

create function call_time_okay
( dr_datetime timestamp
, timestamptz timestamp with time zone
, cc_after_timestamptz timestamp with time zone
, cli_tz client_time_zone)
returns boolean as $$
begin
  return (dial_retry.dr_datetime is null and timestamptz > cc_after_timestamptz)
         or (timestamptz > (dr_datetime at time zone cli_tz));
end;
$$ language plpgsql;

create function set_cold_call_requesting
( a_cli_id client_id
, a_cmp_id campaign_id
, a_con_id contact_id
, timestamptz timestamp with time zone)
returns void as $$
begin
  update cold_call set cc_status = 'requesting', cc_status_timestamptz = current_timestamp
  where cli_id = a_cli_id and cmp_id = a_cmp_id and con_id = a_con_id;
end;
$$ language plpgsql;

create function next_cold_call(timestamptz timestamp with time zone)
returns table
( a_cli_id client_id
, a_cmp_id campaign_id
, a_con_id contact_id
, a_con_phone_suffixed phone_number
, a_qname text) as $$
declare
  prj_record record;
begin
  for prj_record in select cli_id, prj_id, prj_next_cold_call_limit from active_project
  loop
    for a_cli_id, a_cmp_id, a_con_id, a_con_phone_suffixed, a_qname in
      select
        cli_id
      , cmp_id
      , con_id
      , con_phone_suffixed(con_phone, cli_uccx_contact_suffix)
      , qname(cli_id, prj_id)
      from cold_call_queued
      join campaign using (cli_id, cmp_id)
      join client using (cli_id)
      left join dial_retry using (cli_id, cmp_id, con_id)
      where cli_id = prj_record.cli_id
      and prj_id = prj_record.prj_id
      and call_time_okay(dial_retry.dr_datetime, timestamptz, cc_after_timestamptz, cli_tz)
      order by cc_serial
      limit prj_record.prj_next_cold_call_limit
    loop
      perform set_cold_call_requesting(a_cli_id, a_cmp_id, a_con_id, current_timestamp);
      return next;
      end loop;
  end loop;
  return;
end;
$$ language plpgsql;