Postgres 9.4

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table soap_request(
  sel_num serial,
  req_id int,
  attr_id text default 'CONST',
  low_val text,
  high_val text);

insert into soap_request (req_id,attr_id,low_val,high_val) values
(1,'CONST','<soapenv:Body>',null),
(1,'VAR','<read:ready_date>',$s$SELECT current_date||'T'||to_char(current_timestamp,'HH24:MI:SS');$s$),
(1,'CONST','</read:ready_date>',null),
(1,'VAR','<read:property_name="EXTRACT_GUID" value=">',$s$SELECT upper(md5(random()::text));$s$),
(1,'CONST','"/></soapenv:Body>',null)
returning *;


sel_num
req_id
attr_id
low_val
high_val


1
1
CONST
<soapenv:Body>
null

2
1
VAR
<read:ready_date>
SELECT current_date||'T'||to_char(current_timestamp,'HH24:MI:SS');

3
1
CONST
</read:ready_date>
null

4
1
VAR
<read:property_name="EXTRACT_GUID" value=">
SELECT upper(md5(random()::text));

5
1
CONST
"/></soapenv:Body>
null



INSERT 0 5


create function assemble_soap_request(p_id int) 
returns text language plpgsql as $function_body$
DECLARE
  var_formula text;
  var_result  text;
  var_results text[];
  full_soap_request text:='';
BEGIN
  select string_agg(low_val||case when attr_id='VAR' then '%s' else '' end
                    ,E'\n' order by sel_num) 
  from soap_request where req_id=p_id
  into full_soap_request;

  for var_formula in select high_val 
                     from soap_request where attr_id='VAR' and req_id=p_id
                     order by sel_num 
  loop
      execute var_formula into var_result;
      var_results:=var_results||var_result;
  end loop;

  select format(full_soap_request,VARIADIC var_results) into full_soap_request;
  return full_soap_request;
END $function_body$;

CREATE FUNCTION


select string_agg(low_val||case when attr_id='VAR' then '%s' else '' end
                  ,E'\n' order by sel_num) 
from soap_request;


string_agg


<soapenv:Body>
<read:ready_date>%s
</read:ready_date>
<read:property_name="EXTRACT_GUID" value=">%s
"/></soapenv:Body>



SELECT 1


select assemble_soap_request(1);


assemble_soap_request


<soapenv:Body>
<read:ready_date>2023-10-02T06:29:39
</read:ready_date>
<read:property_name="EXTRACT_GUID" value=">3AEE09DE7E85E0C90DB5D3D214FDF90B
"/></soapenv:Body>



SELECT 1

sel_num	req_id	attr_id	low_val	high_val
1	1	CONST	<soapenv:Body>	null
2	1	VAR	<read:ready_date>	SELECT current_date\|\|'T'\|\|to_char(current_timestamp,'HH24:MI:SS');
3	1	CONST	</read:ready_date>	null
4	1	VAR	<read:property_name="EXTRACT_GUID" value=">	SELECT upper(md5(random()::text));
5	1	CONST	"/></soapenv:Body>	null