CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-design
unseen_damage
unseen_damage

Reputation: 1376

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
)
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?

Upvotes: 0

Views: 1691

Answers (2)

unseen_damage
unseen_damage

Reputation: 1376

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

Upvotes: 0

Ezequiel Tolnay
Ezequiel Tolnay

Reputation: 4582

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?

Upvotes: 1

Related Questions