Postgres Function to Insert Arrays

Question

I am trying to INSERT data via a postgres function, and I can't quite get it working. I am getting an error stating

ERROR: function unnest(integer) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You might need to add explicit type casts.

I am using Postgres 9.5, and my function is as follows:

CREATE FUNCTION insert_multiple_arrays(
some_infoid INTEGER[],
other_infoid INTEGER[],
some_user_info VARCHAR,
OUT new_user_id INTEGER
)
RETURNS INTERGER AS $$
BEGIN
INSERT INTO user_table (user_info) VALUES ($3) RETURNING user_id INTO new_user_id;
INSERT INTO some_info_mapper (user_id, some_info_id) SELECT new_user_id, unnest($1);
INSERT INTO other_info_mapper (user_id, other_info_id) SELECT  new_user_id,unnest($2);
END;
$$ LANGUAGE plpgsql;

I will be calling the stored procedure from my backend via a SELECT statement. An example is like so:

createUser(user, callback){
    let client = this.getDb();
    client.query("SELECT insert_multiple_arrays($1, $2, $3)",
        [user.some_info_ids, user.other_info_ids, user.info], function(err, results){
        if(err){
            callback (err);
        }
        callback(null, results);
    });
};

The output that I am expecting would be as follows:

user_table

   user_id            | user_info       |
----------------------+-----------------+
    1                 | someInfo        |



some_info_mapper

   user_id            | some_info_id    |
----------------------+-----------------+
    1                 | 33              |
    1                 | 5               |


other_info_mapper

   user_id            | other_info_id   |
----------------------+-----------------+
    1                 | 8               |
    1                 | 9               |
    1                 | 22              |
    1                 | 66              |   
    1                 | 99              |

How do I handle this error? Do I need to do some sort of processing to my data to put it into a format that postgres accepts?

What type are user.some_info_ids and user.other_info_ids ? Are they integers? That is what the error is saying. — cachique
– cachique, Commented Jun 27, 2016 at 19:22
Yes, the fields are integers, as are the sql query parameters. — unseen_damage
– unseen_damage, Commented Jun 28, 2016 at 11:59
1-The fields needs to be array of integers, not just integers. and 2- Your driver must support passing arrays in prepared statements. What languaje/driver version are you using in your calling code? — cachique
– cachique, Commented Jun 28, 2016 at 18:31
I thought that the "unnest" array function would "explode" the array into new rows. I am not looking to store arrays in the database, rather parse the array and make a new row for each element. I'm afraid we are not on the same page. I believe the solution has to do with typecasting but I haven't figured that out yet... — unseen_damage
– unseen_damage, Commented Jun 28, 2016 at 20:27
unnest do exactly that but what you are passing or what the backend is receiving seems not to be an array but a simple integer. That's why I suggest you to check your driver documentation on prepared statements with arrays as parameters. Otherwise you should implode your array in your calling code and use a combination of unnest and string_to_array on the postgresql side. You'll be sending strings with separators to mimic arrays and modify your function accordingly to take that kind of parameters. — cachique
– cachique, Commented Jun 28, 2016 at 20:41

Ezequiel Tolnay · Accepted Answer · 2016-06-28 03:29:12Z

1

You're calling insert_multiple_arrays with three parameters, but show the definition with four. Perhaps you have an old 3-parameter version still lurking there, buggy, and trying to find the bug in the 4-parameter version that is not actually in use?

answered Jun 28, 2016 at 3:29

Ezequiel Tolnay

4,5821 gold badge21 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

unseen_damage Over a year ago

Ah, I see what you mean. I just forgot to add the OUT parameter to the query...I will update it. Basically what I am trying to do is create a new user, return the newly created user ID, and use it as a parameter for the next two insert statement.

unseen_damage · Accepted Answer · 2016-06-30 13:44:12Z

0

After exploring @cachiques comments, it appears that the data was not being sent correctly after all. As it turns out, that the data being passed to the back end was an array objects that needed to be parsed further than I realized. Once parsed, the sql worked fine. Here is the code I used to parse from the server side, which would be sent to the sql query:

user.other_info_ids = req.body.other_info.map( function(obj) { return obj.info_id; } );

answered Jun 30, 2016 at 13:44

unseen_damage

1,3763 gold badges15 silver badges33 bronze badges

Collectives™ on Stack Overflow

Postgres Function to Insert Arrays

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related