Pass array parameters into postgres stored procedure

[Philip K. Adetiloye]

What's the best and clean way to pass parameter into a postgres stored procedure ?

Vertx code to pass parameter to procedure.

     ...
    JsonArray params = new new JsonArray();
    params.add(????);

    connection.callWithParams(spUserInfo, params, null, response -> {


        if (response.succeeded()) {
            ResultSet result = response.result();

For example, here is the sample store procedure declared a type info and passing the array of Infos into insert_info function.

   

 CREATE TYPE info AS(
        name  varchar,
        email_add  varchar,
        contact_no  varchar
    );


    CREATE OR REPLACE FUNCTION insert_info(
        info_array  info[]
    ) RETURNS varchar AS $$
        DECLARE
            info_element  info;
        BEGIN
            FOREACH info_element IN ARRAY info_array
            LOOP
                INSERT INTO info_table(
                    name,
                    email_add,
                    contact_no
                ) VALUES(
                    info_element.name,
                    info_element.email_add,
                    info_element.contact_no
                );
            END LOOP;
            RETURN 'OK';
        END;
    $$ LANGUAGE plpgsql;

To call the procedure or function, I've to do something like this in postgres:

SELECT insert_info((ARRAY['(Arjay,myE...@email.com,1234567)'
                         ,'(Bjay,my2E...@email.com,2234567)'])::info[]);

Any idea ?

[Philip K. Adetiloye]

I figured it out...just need to escape the parameters e.g

userList.add(String.format("\"(\\\"%s\\\",%d,\\\"%s\\\",\\\"%.2f\\\",\\\"%s\\\")\"", info.getUser(), info.getAge(), info.getDescription(), info.getApt(), info.getAddress()));

SELECT insert_info((ARRAY['(Arjay,myEma...@email.com,1234567)'

                         ,'(Bjay,my2E...@email.com,2234567)'])::info[]);

Any idea ?

[Emad Alblueshi]

Would please share what exactly you did ?

[Philip K. Adetiloye]

Am not sure if you are trying to achieve the same thing here but technically, I need

to pass in my type Array into the store procedure...which is a bit tricky.

I solved it by constructing a string (Remember to triple escape the double quote) from my arrays like this:

foreach (...){

    userList.add(String.format("\"(\\\"%s\\\",%d,\\\"%s\\\",\\\"%.2f\\\",\\\"%s\\\")\"", info.getUser(), info.getAge(), info.getDescription(), info.getApt(), info.getAddress()));
}

...

//finally flatten it as one single string
return String.format("{%s}", StringUtils.join(userList, ','));

Now, you can just pass it as a parameter in the Vertx sql common query interface

JsonArray params = new JsonArray().add(usersArrayStr);
....

[Emad Alblueshi]

Yes, I'm trying to achieve the same thing and I need to see the form of the sql statement in vertx callWithParams method  

Thanks