This psql function returns the historical name of a course, given the term and item number.

get_historical_name

--Function parameters:  cart_item_id (ec_items)
                         product_name (ec_products)
--returns the title of the course at that point in time.

create or replace function public.get_historical_name ( integer, varchar )
     returns varchar as '
     declare
     v_cart_item_id               alias for $1;
     v_product_name               alias for $2;
begin
     db_0or1row historical_title {
          select p.product_type_id,
          i.item_id,
          mr.desired_term_id,      --need the highest dti
             dmrg.course_item_id,
             dmrg.term_id,
             cr.title,
             b.course_code,
             sec.start_date,
             sec.section_id,
             aud.publish_date,
             aud.new_revision    
    
     from ec_products p
     join ec_items i on i.product_id = p.product_id
     join dotlrn_member_rels mr on mr.cart_item_id=i.item_id
     join dotlrn_member_rels_general dmrg on dmrg.cart_item_id=i.item_id
     join cr_revisions cr on cr.item_id = dmrg.course_item_id
     join brk_courses b on b.course_id = cr.revision_id
     join cr_item_publish_audit aud on aud.item_id=cr.item_id
     join brk_course_tags bct on bct.course_item_id = aud.item_id
     join brk_sections sec on sec.tag_id = bct.tag_id

     where i.item_id=v_cart_item_id
     and aud.publish_date <= sec.start_date
     and p.product_name = v_product_name

     order by mr.desired_term_id DESC limit 1
     } {
          if { [string length $section_id] > 0 } {
          #This finds the course name given the section_id
          ns_log notice "383 section_id is $section_id"
           return (select [concat $course_code " " [db_string product_name {
            select public.find_name_given_section(:section_id)
            }]])
            ns_log notice "386 item_name is $item_name"
        } elseif { [string length $desired_term_id] > 0 } {
          #This finds the course name given the desired term
          return (select [concat $course_code " " [db_string product_name {
            select public.find_name_given_term_and_item(:desired_term_id, :course_item_id)
          } -default "" ]])
          ns_log notice "392 item_name is $item_name"
        }
        ns_log notice "item_name is $item_name"
        #At this point the default is the product name, which will be the latest
        #revision of the product.
          return v_product_name;                
      }
     }
end; ' language 'plpgsql';