1

What is the correct way to call postgres function with custom types from the spring StoredProcedure?

Say I have a function and custom types as defined:

CREATE OR REPLACE FUNCTION addUser(user IN O_USER) RETURNS VOID
AS
$$
BEGIN

end;
$$ language plpgsql;

CREATE TYPE O_USER AS (
  id NUMERIC,
  name VARCHAR(50)
);

While calling it from Spring I am converting user object to String as shown below:

public class AddUserProcedurePostgres extends StoredProcedure {

    public AddUserProcedurePostgres(DataSource dataSource) {
        super(dataSource, "addUser");
        setFunction(true);

        declareParameter(new SqlParameter("i_User", Types.OTHER, "O_USER"));

        compile();
    }

    public void execute(SessionContext sessionContext, Dashboard dashboard) {

        Map<String, Object> input = new HashMap<>();
        input.put("i_User", "(1,Vishal)");

        execute(input);
    }

}

Now my questions are:

  1. Is it the right way to create a String corresponding to the object and then pass it? Or is there any better way?

  2. Suppose I need to add array of other custom type in the existing O_USER, then how the string need to be formatted?

CREATE TYPE O_USER AS (
  id NUMERIC,
  name VARCHAR(50)
  addresses O_ADDRESS[]
);
3
  • Functions can simply called using a SELECT statement. I don't see the need for mapping them to a stored procedure, simply run select adduser(...) Commented Nov 27, 2019 at 13:58
  • we also has the oracle stored procedures which are implemented in this way and we have to follow the same architecture. Even if we use select statement, we have to pass the object and that need to converted into string..right? Commented Nov 27, 2019 at 14:00
  • SOLVED: The solution is described @ stackoverflow.com/questions/59085449/… Commented Dec 3, 2019 at 12:36

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.