How to Call a PostgreSQL Stored Procedure with Multiple OUT User-Defined Types using @Query in Spring Boot

In this article, we’ll explore how to call a PostgreSQL stored procedure that takes one input parameter and returns multiple OUT parameters of user-defined types (composite types) using Spring Data JPA's @Query — without using EntityManager.

We will also learn how to map the result directly as Map<String, Object> for flexibility.

---

1. PostgreSQL Setup: User-Defined Types and Stored Procedure

Step 1: Create User-Defined Types

sql
CREATE TYPE user_info AS (
    user_id INT,
    user_name TEXT
);

CREATE TYPE address_info AS (
    address_id INT,
    address TEXT
);

Step 2: Create Stored Procedure with Multiple OUT Parameters

sql
CREATE OR REPLACE PROCEDURE get_user_and_address_details(
    IN p_user_id INT,
    OUT user_details user_info,
    OUT address_details address_info
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Fetch user info
    SELECT id, name INTO user_details FROM users WHERE id = p_user_id;

    -- Fetch address info
    SELECT id, address INTO address_details FROM addresses WHERE user_id = p_user_id;
END;
$$;

Note: This procedure will return two OUT parameters, both of which are user-defined composite types.

---

2. Spring Boot Setup: Calling Stored Procedure using @Query

Step 1: Add PostgreSQL Dependency in pom.xml

xml
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>

Step 2: Configure application.properties

properties
spring.datasource.url=jdbc:postgresql://localhost:5432/yourdb
spring.datasource.username=yourusername
spring.datasource.password=yourpassword
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=none

---

3. Repository: Call Stored Procedure using @Query

Direct Use of @Query (Calling Procedure)

java
@Repository
public interface UserRepository extends JpaRepository<UserEntity, Integer> {

    @Query(value = "CALL get_user_and_address_details(:userId, :userDetails, :addressDetails)", nativeQuery = true)
    void getUserAndAddressDetails(
        @Param("userId") Integer userId,
        @Param("userDetails") Map<String, Object> userDetails,
        @Param("addressDetails") Map<String, Object> addressDetails
    );
}

Important Notes:

• Here, @Query is used to call the procedure directly.

• :userId is the input parameter.

• :userDetails and :addressDetails are placeholders for the OUT parameters, and we expect Spring to map them as Map<String, Object>.

Limitation Note:
Since JPA @Query with nativeQuery does not handle OUT parameters directly in all dialects, PostgreSQL's composite types as OUT parameters may not directly map this way without some wrapping. However, if your JDBC driver and Spring Boot version handle OUT params via @Query, this is the ideal approach.

---

4. Handling Return Type: Map<String, Object>

Since you are returning multiple OUT user-defined types, and want to capture them as Map<String, Object>, Spring would map each OUT parameter to its respective key in the map — if supported by the driver.

Example expected map:

json
{
  "userDetails": {
    "user_id": 1,
    "user_name": "Alice"
  },
  "addressDetails": {
    "address_id": 101,
    "address": "123 Main St"
  }
}

---

5. Example Controller to Test It

java
@RestController
@RequestMapping("/api")
public class UserController {

    private final UserRepository userRepository;

    @Autowired
    public UserController(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    @GetMapping("/user-details/{id}")
    public Map<String, Object> getUserDetails(@PathVariable Integer id) {
        Map<String, Object> userDetails = new HashMap<>();
        Map<String, Object> addressDetails = new HashMap<>();

        userRepository.getUserAndAddressDetails(id, userDetails, addressDetails);

        Map<String, Object> result = new HashMap<>();
        result.put("userDetails", userDetails);
        result.put("addressDetails", addressDetails);
        return result;
    }
}

---

6. Alternative Approach (If Direct OUT Param Mapping Fails): Use RECORD/JSON Aggregation

If you face an issue mapping OUT composite types directly, you can adjust the procedure to return a single JSON object (optional enhancement):

sql
CREATE OR REPLACE PROCEDURE get_user_and_address_details_json(
    IN p_user_id INT,
    OUT result JSON
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT json_build_object(
        'userDetails', (SELECT row_to_json(u) FROM (SELECT id, name FROM users WHERE id = p_user_id) u),
        'addressDetails', (SELECT row_to_json(a) FROM (SELECT id, address FROM addresses WHERE user_id = p_user_id) a)
    )
    INTO result;
END;
$$;

Now, your repository can be:

java
@Query(value = "CALL get_user_and_address_details_json(:userId, :result)", nativeQuery = true)
void getUserAndAddressDetailsJson(
    @Param("userId") Integer userId,
    @Param("result") Map<String, Object> result
);

And the return map will directly contain both objects as JSON.

---

7. Final Thoughts

Key Takeaways:

• PostgreSQL stored procedures can return multiple OUT parameters of user-defined composite types.

• You can call such procedures using Spring Data JPA @Query.

• Map<String, Object> provides a flexible way to handle dynamic structures without fixed DTOs.

• If mapping composite types directly becomes a challenge, returning JSON from stored procedures is a great alternative.

---

Conclusion:

While @Query with OUT composite types is a neat way when supported, PostgreSQL's complexity sometimes requires workarounds like JSON aggregation. Still, the combination of PostgreSQL procedures and Spring Boot is powerful for complex data retrieval, and using Map<String, Object> keeps your application flexible and schema-less for dynamic responses.