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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.