如何在WSO2 REST数据服务中调用PostgreSQL存储过程？

问题背景 #

当前WSO2配置的REST数据服务中，6类查询操作依赖PostgreSQL函数实现，仅4类插入操作使用存储过程。现需将所有查询操作替换为存储过程，解决WSO2中调用PostgreSQL查询型存储过程的适配问题。

实现步骤 #

1. 调整PostgreSQL存储过程定义 #

PostgreSQL 11及以上版本支持存储过程返回结果集，需根据查询场景定义对应的存储过程：

方式一：返回表类型（多字段/多行结果场景）

通过RETURNS TABLE指定返回的字段结构，示例：

CREATE OR REPLACE PROCEDURE ObtenerDatosCliente_proc(IN p_id_cliente INTEGER)
RETURNS TABLE(
    nombre_completo VARCHAR,
    domicilio VARCHAR,
    numero_cliente INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT nombre_completo, domicilio, numero_cliente
    FROM clientes WHERE id_cliente = p_id_cliente;
END;
$$;

方式二：使用OUT参数（单值结果场景）

通过OUT参数返回单个结果，示例：

CREATE OR REPLACE PROCEDURE verificarEdad_proc(IN p_id_cliente INTEGER, OUT p_verificaredad BOOLEAN)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT edad >= 18 INTO p_verificaredad
    FROM clientes WHERE id_cliente = p_id_cliente;
END;
$$;

2. 修改WSO2数据服务的Query配置 #

将原函数调用替换为存储过程调用，根据存储过程返回方式调整<query>节点：

示例1：调用返回表类型的存储过程

替换原obtenerDatosClientes查询：

<query id="obtenerDatosClientes" useConfig="default">
  <sql>CALL ObtenerDatosCliente_proc(:id_cliente)</sql>
  <param name="id_cliente" paramType="SCALAR" sqlType="INTEGER" />
  <result outputType="json">
    {
      "cliente": {
        "nombre_completo": "$nombre_completo",
        "domicilio": "$domicilio",
        "numero_cliente": "$numero_cliente"
      }
    }
  </result>
</query>

示例2：调用带OUT参数的存储过程

替换原obtenerEdad查询：

<query id="obtenerEdad" useConfig="default">
  <sql>CALL verificarEdad_proc(:id_cliente, :verificaredad)</sql>
  <param name="id_cliente" paramType="SCALAR" sqlType="INTEGER" type="IN" />
  <param name="verificaredad" paramType="SCALAR" sqlType="BOOLEAN" type="OUT" />
  <result outputType="json">
    {
      "cliente": {
        "verificaredad": "$verificaredad"
      }
    }
  </result>
</query>

3. 关键注意事项 #

• 参数类型声明：存储过程的输出参数必须在<param>节点中指定type="OUT"，输入参数默认type="IN"可省略；
• 驱动版本：确保WSO2使用PostgreSQL JDBC驱动42.2及以上版本，避免存储过程调用兼容性问题；
• 结果映射：返回表类型的存储过程，结果映射逻辑与原函数调用完全一致，直接引用返回字段名即可。

部分修改后的核心Query示例 #

其余查询可按相同逻辑调整：

<query id="obtenerEdadCuenta" useConfig="default">
  <sql>CALL verificaredadcuenta_proc(:id_cuenta, :verificaredadcuenta)</sql>
  <param name="id_cuenta" paramType="SCALAR" sqlType="INTEGER" type="IN" />
  <param name="verificaredadcuenta" paramType="SCALAR" sqlType="BOOLEAN" type="OUT" />
  <result outputType="json">
    {
      "cliente": {
        "verificaredadcuenta": "$verificaredadcuenta"
      }
    }
  </result>
</query>

<query id="ObtenerDatosCuenta" useConfig="default">
  <sql>CALL obtenerDatosCuenta_proc(:p_cuenta_id)</sql>
  <param name="p_cuenta_id" paramType="SCALAR" sqlType="INTEGER" />
  <result outputType="json">
    {
      "cuentas": {
        "cuenta": {
          "numero_cuenta": "$numero_cuenta",
          "nombre_banco": "$nombre_banco",
          "sucursal": "$sucursal",
          "vigencia": "$vigencia"
        }
      }
    }
  </result>
</query>

内容的提问来源于stack exchange，提问作者Armando Rojas Valdez