2

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

As well as a couple other variations.

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

I read about the Postgres parse-array issue and this stackoverflow question on parameterised arrays in node-postgres:

But the answer didn't seem to be there.

Improve this question

3 Answers 3

Reset to default
4

The parameter has to an array literal or an array constructor:

'{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 (this one defaults to int[])

For the simple task use a plain SQL function instead:

CREATE OR REPLACE FUNCTION testarray (int[])
  RETURNS int
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
'SELECT array_length($1, 1)';

Or (preferably) just use array_length($1, 1) directly.

Arrays can have arbitrary subscripts. array_length() or cardinality() are the right tools, array_upper() not so much. See:

Unlike cardinality(), array_length() and array_upper() require two parameters. The second is the array dimension - 1 in your case.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.
0

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.
Improve this answer

Comments

-2

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);
});
Improve this answer

5 Comments

It produces a new error from postgresql: syntax error at or near "{"
Right, I forgot it needs to be enclosed by quotes. Updated.
that didn't work either. what did work is: client.query("select testarray($1::int[])", [ta], ...etc
This poses an extreme security risk!
@MilesRout can you clarify? The joined params are server generated - is the problem the length of the passed params Array?