Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:

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

share|improve this question
    
Have you tried explicit cast '{''si'',''non''}'::text[] or array constructor ARRAY['si', 'non'] following the answer of question you have mentioned stackoverflow.com/questions/13328016/… – kAlmAcetA yesterday
    
How do you pass the values into the function? For the strings it must be ARRAY['si','non']. – vitaly-t yesterday
    
thanks, I added information in my question. I will try to do the cast – Pat070 13 hours ago
    
Don't put "solved" in your question. Just accept your answer, and the question will be marked as "solved" – a_horse_with_no_name 12 hours ago
    
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 6 hours ago

1 Answer 1

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.