Advanced Text Formatting Using a Function

There are times you need to aggregate and format text using a function for the purposes of reporting or in the Quick Info window. Having access to utility functions makes this formatting much easy and more maintainable. Below is an example of a function that does the following:

• Combines parent and child requests via the child request => r_requestrelated_id field.
• Takes a variety of delimiters so that the results can be used in text (chr(10) - new line) or in html ('<br>')
• Shows how to combine elements from both columns of the same record as well as different records into a single text string
• Shows how to use a cursor in a plpgsql function
• Uses concat_ws function to combine text with a delimiter

Example Function

CREATE OR REPLACE FUNCTION adempiere.chuboe_request_summary_f(p_delim text, p_request numeric)
  RETURNS text
  LANGUAGE plpgsql
 AS $function$
 DECLARE
 tmp RECORD; result text;
 BEGIN
 for tmp in select
 concat_ws(
     p_delim,
     'DocNo: '||r.documentno,
     case when r_requestrelated_id is null then 'Date: '||to_char(datenextaction,'MM/dd/yyyy') else null end,
     case when r_requestrelated_id is null then 'Cust: '||bp.name else null end,
     case when r_requestrelated_id is null then 'Phone: '||u.phone else null end,
     case when r_requestrelated_id is null then 'Email: '||u.email else null end,
     case when r_requestrelated_id is null then 'Time: '||sbt.description else null end,
     'Service: '||rc.name ,
     'Status: '||rs.name ,
     case when r_requestrelated_id is null then 'Tech: '||sr.name else null end,
     'Created: '||cr.name,
     case when r_requestrelated_id is null then 'Child: '||    (
             select count(1) from r_request x where x.r_requestrelated_id = r.r_request_id
         ) else null end,
     ''
 ) as summary,
 r.r_request_id,
 r.r_requestrelated_id
 from r_request r
 left join c_bpartner bp on r.c_bpartner_id = bp.c_bpartner_id
 left join ad_user u on r.ad_user_id = u.ad_user_id
 left join Chuboe_Sched_Block_Time sbt on r.Chuboe_Sched_Block_Time_ID = sbt.Chuboe_Sched_Block_Time_ID
 left join r_category rc on r.r_category_id = rc.r_category_id
 left join r_status rs on r.r_status_id = rs.r_status_id
 left join ad_user sr on r.salesrep_id = sr.ad_user_id
 left join ad_user cr on r.createdby = cr.ad_user_id
 where r.R_Request_ID = p_request or r.r_requestrelated_id = p_request
 order by r.r_request_id
 loop
     result := concat_ws(p_delim,result, tmp.summary);
 end loop;
 return result;
 END;
 $function$

Example Output

The following is what you would see if you issued the query: select chuboe_request_summary_f(chr(10),100000). The first section is from the parent or primary request. The second section is from the child request.

DocNo: 1242107
Date: 02/15/2021
Cust: JONNY PARRIS
Phone: 6902025555
Email: JONNYPARRIS@gmail.com
Time: 8 to 10 am
Service: COS Upgrade Service(s) / Truck
Status: Scheduled
Tech: james.crawford
Created: james.julias
Child: 1

DocNo: 1242108
Service: Provision Video - Update Subscription
Status: Prepared
Created: james.julias