unseen_damage unseen_damage - 1 year ago 142
SQL Question

Postgres Function to Insert Arrays

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
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);
$$ 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){
callback (err);
callback(null, results);

The output that I am expecting would be as follows:


user_id | user_info |
1 | someInfo |


user_id | some_info_id |
1 | 33 |
1 | 5 |


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?

Answer Source

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