How to execute select which stored cell in table

Question

I have a tune table that stores SOAP requests. Some parts of the query are constant, some parts are variable. To get the values of the variables, i need to perform another select whose text is stored in a separate table column.

How to collect SOAP request text with get result of select variables?

Please, look at the picture.

I'm expect an example of pg function, which collect SOAP request text from my table.

Answer 1

You can use dynamic SQL to evaluate your queries in high_val and assemble a complete body: demo

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$;

It initially builds the request body with %s placeholders wherever a "variable" needs to go:

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>

Then runs your queries and collects their results in an array, that is then fed as a variadic argument array

select assemble_soap_request(1);

assemble_soap_request
<soapenv:Body> <read:ready_date>2023-09-27T17:51:32 </read:ready_date> <read:property_name="EXTRACT_GUID" value=">9F7F8FEC9608B02A5946DBABCE2557F2 "/></soapenv:Body>