Execute PostgreSQL Stored Procedure with Composite User-Defined OUT Types in Spring Boot

 

In one of my migration project, I had a scenario where client had made extensive use of Stored Procedure to make changes to the tables. Each procedure encapsulating business logic and database update. Our task was to migrate this to spring boot and the idea we came up was to first just migrate SP calling via JPA like use @Procedure or @Query. We had a run down on JPA and spring boot to understand how and which all scenarios can be covered. In this analysis, we found an interesting scenario where sql custom types were being returned as part of the stored procedure and with this began the struggle to understand and go through as many blogs and documents as possible and that is how we came across a solution to address this issue.

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.

Note: With introduction to JPA and its high complex entity class defining capacity, it is now not recommended to use Stored Procedure, such scenarios where Stored Procedures are used will now mostly be encountered in scenarios where migration is to be done from a very old java code to latest springboot application

---

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, null, null)", nativeQuery = true)
    void getUserAndAddressDetails(
        @Param("userId") Integer userId);
}

Important Notes:

• Here, @Query is used to call the procedure directly.
• :userId is the input parameter.
• null is a placeholder 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"
  }
}

Note: Having return type as Object instead of Map<String, Object> will also work.

---

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<>();

        return userRepository.getUserAndAddressDetails(id);
    }
}

---

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.