How to configure Mulesoft (4.4) "stored procedure" component to invoke PostgreSQL Stored Procedure

I am trying to configure mule to invoke a stored procedure in PostgreSQL. Please find the configuration below,

<db:stored-procedure doc:name="Stored procedure" doc:id="ace0cc98-d170-4d0c-b7b4-905162b03797" config-ref="Database_Config1">
            <db:sql ><![CDATA[{call sp_employee_generate(:pn_name,:pn_age,:pn_add1,:pn_add2,:pn_add3,:pn_city,:pn_state,:pn_pin,:pn_Mobile_no,:pn_Email_id,:pn_Employee_no)} ]]></db:sql>
            <db:input-parameters ><![CDATA[#[{
            "pn_name":vars.requestParam.p_name,
"pn_age":vars.requestParam.p_age,
"pn_add1":vars.requestParam.p_add1,
"pn_add2":vars.requestParam.p_add2,
"pn_add3":vars.requestParam.p_add3,
"pn_city":vars.requestParam.p_city,
"pn_state":vars.requestParam.p_state,
"pn_pin":vars.requestParam.p_pin,
"pn_Mobile_no":vars.requestParam.p_Mobile_no,
"pn_Email_id":vars.requestParam.p_Email_id}]]]></db:input-parameters>
            <db:output-parameters >
                <db:output-parameter key="pn_Employee_no" type="VARCHAR" />
            </db:output-parameters>
        </db:stored-procedure>

However, while testing I am receiving the following error,

Message               : ERROR: sp_employee_generate(character varying, integer, character varying, character varying, character varying, character varying, character varying, character varying, integer, character varying) is a procedure
  Hint: To call a procedure, use CALL.
  Position: 15

Can you please help correcting the configuration to overcome the above error.

Kind Regards,

Solution

It's because of the curly braces around the statements. The Postgres driver, by default, treats those statements as function calls and translates the statement. You can remove them to use native sql:

call sp_employee_generate(:pn_name,:pn_age,:pn_add1,:pn_add2,:pn_add3,:pn_city,:pn_state,:pn_pin,:pn_Mobile_no,:pn_Email_id,:pn_Employee_no)

Or you can set a connection property escapeSyntaxCallMode to call: https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/jdbc/EscapeSyntaxCallMode.html

You can do this via a Datasource config similar to:

<bean id="dataSource" class="org.postgresql...."> 
    <property name="URL" value='"jdbc:postgresql://localhost:5432/bla />
    <property name="User" value="<USER>" />
    <property name="Password" value="<PASSWORD>" />
    <property name="ConnectionProperties">
        <value>
            escapeSyntaxCallMode:call
        </value>
    </property>
</bean>

There is a good explanation and some more alternatives here: "x is a procedure, use "call"" when I am already using call