plpgsql function with text array parameter coming from node postgres

Question

I have the following function in plpgsql

create or replace  function domix(mix int[],newmix text[]) RETURNS integer AS \$$
DECLARE
  I INT;
  newmix_char text;
BEGIN
  FOREACH newmix_char IN ARRAY newmix
    LOOP
     insert into mix_table (name) values (newmix_char);
    END LOOP;
  FOREACH I IN ARRAY MIX
    LOOP
      insert into domix_table (id) values (I);
   END LOOP;
  RETURN 1;
 END;
 \$$ LANGUAGE plpgsql;

The server is a nodes server. I have seen Pass array from node-postgres to plpgsql function and I have followed it. I pass the values as indicated,(domixids is an array of int, newmixes is an array of strings)

 'mix': '{'+domixids.join() +'}',
 'newmix':'{'+ newmixes.join()+'}',

printing on the server before the db call shows: mixids {14,13} newmix {si,non}

The function behaves as I expected for the mixids - loops and inserts a row for each id. But the newmix is considered as 1 string and gets inserted as such, no looping. I have tried with {'si','non'} but with same result. Where is my error ? I am obviously not sending the function what it is expecting. Or I am completely out and this is not the way to loop through and array of strings ? I am using Postgres 9.4 Thanks for any pointer

I am adding more details ( tried the suggestions - badly since it did not work). As I said all works well for the integers, and I obviously am not passing correctly the string arrays. I get the list as a set of strings comma separated from the interface (newmix).I split

   var newmixes = newmix.split(/,/);
   console.log(new mixes);  --> ["yes", "non", "trois"]
   var infoWhat= { 'mix': '{'+domixids.join() +'}',
            'newmix':'{'+ newmixes.join()+'}'
               };
   console.log(infoWhat);   ----> Object {mix: "{}",new mix: "{yes,no}"}}

In the database I get : {yes,no} in that field. I tried passing "{''yes'',''no''}", same result I get {''yes'',''no''}.

My function is called using node-postgres as a prepared statement: "select domix($1,$2);"
and $1,$2 are passed by req.body.mix, req.body.domix

If I have to explicitly cast as ::TEXT[] where should I do it ?

Thanks a lot

Have you tried explicit cast `'{''si'',''non''}'::text[]` or array constructor `ARRAY['si', 'non']` following the answer of question you have mentioned http://stackoverflow.com/questions/13328016/pass-array-from-node-postgres-to-plpgsql-function — kwarunek, Nov 27 '15 at 23:30
How do you pass the values into the function? For the strings it must be `ARRAY['si','non']`. — vitaly-t, Nov 27 '15 at 23:36
thanks, I added information in my question. I will try to do the cast — Pat070, Nov 28 '15 at 11:27
Don't put "solved" in your question. Just accept your answer, and the question will be marked as "solved" — , Nov 28 '15 at 12:10
ok - will do that tomorrow, it does not allow me to do it today. I did not know how to do it, now I learned, thanks, — Pat070, Nov 28 '15 at 18:39

score 0 · Accepted Answer · edited Nov 28 '15 at 12:10

0

I had to put the explicit cast in the function call:

select function($1,$2::TEXT[]);

and now the data is entered correctly. Thanks for the help and pointers that helped me find the solution!

edited Nov 28 '15 at 12:10

answered Nov 28 '15 at 12:06

Pat070

335
1
3
13

plpgsql function with text array parameter coming from node postgres

1 Answers1