Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

the plpgsql function:

CREATE OR REPLACE FUNCTION testarray (int[]) returns int as $$
  DECLARE
    len int;
  BEGIN
    len := array_upper($1);
  return len;
  END
$$ language plpgsql;

the node-postgres query + test array:

var ta = [1,2,3,4,5];
client.query('SELECT testarray($1)', [ta], function(err, result) {
  console.log('err: ' + err);
  console.log('result: ' + result);
});

output from node server:

err: error: array value must start with "{" or dimension information

result: undefined

I also tried cast the parameter in the client query like testarray($1::int[]) which returned the same error.

I changed the function argument to (anyarray int) and the output error changed:

err: error: invalid input syntax for integer: "1,2,3,4,5"

result: undefined

and a couple other variations.

I seek the variation that produces no error and returns 5.

i read the post-gres parse-array issue: https://github.com/brianc/node-postgres/issues/10 and the stackoverflow question on parameterised arrays in node-postgres : node-postgres: how to execute "WHERE col IN (<dynamic value list>)" query? but the answer didn't seem to be there.

share|improve this question

3 Answers

up vote 2 down vote

The parameter has to be in one of these forms:

'{1,2,3,4,5}'         -- array literal
'{1,2,3,4,5}'::int[]  -- array literal with explicit cast
ARRAY[1,2,3,4,5]      -- array constructor

Also, you'd better use this function:

CREATE OR REPLACE FUNCTION testarray (int[]) RETURNS int AS
$func$
BEGIN
  RETURN array_length($1, 1);
END
$func$ LANGUAGE plpgsql;

Or a simple SQL function:

CREATE OR REPLACE FUNCTION testarray2 (int[]) RETURNS int AS
$func$
  SELECT array_length($1, 1);
$func$ LANGUAGE sql;
share|improve this answer
 
thanks for the rundown on the arrays. I'd been going around in circles in the docs. I managed to get all of those variations working in node-postgres unparameterized. thanks for the detail on array_length. –  karojosh Nov 11 '12 at 6:29
up vote 1 down vote

Based on the answer you posted, this might work for you:

var ta = [1,2,3,4,5];
var params = [];
for(var i = 1, i <= ta.length; i++) {
    params.push('$'+i);
}
var ta = [1,2,3,4,5];
client.query('SELECT testarray(\'{' + params.join(', ') + '}\')', ta, function(err, result) {
  console.log('err: ' + err);
  console.log('result: ' + result);
});
share|improve this answer
 
It produces a new error from postgresql: syntax error at or near "{" –  karojosh Nov 11 '12 at 3:25
 
Right, I forgot it needs to be enclosed by quotes. Updated. –  PinnyM Nov 11 '12 at 4:25
 
that didn't work either. what did work is: client.query("select testarray($1::int[])", [ta], ...etc –  karojosh Nov 11 '12 at 6:23
up vote 0 down vote

thanks to the responses from PinnyM and Erwin. I reviewed the options and reread related answers.

the array formats described by Erwin work in node-postgres literally as follows:

'select testarray(' + "'{1,2,3,4,5}'" + ')'
'select testarray(' + "'{1,2,3,4,5}'" + '::INT[])'
'select testarray(ARRAY[1,2,3,4,5])'

the tl:dr of javascript quoting

to parameterize them in node-postgres: (based on this answer)

var ta = [1,2,3,4,5];
var tas = '{' + ta.join() + '}';

...skipped over the pg connect code

client.query("select testarray($1)", [tas] ...
client.query("select testarray($1::int[])", [tas] ...
not sure about the ARRAY constructor.
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.