Pass array from node-postgres to plpgsql function

问题:

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:

• node-postgres: how to execute "WHERE col IN (<dynamic value list>)" query?

But the answer didn't seem to be there.

回答1:

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 can simplify your function:

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

Or a simple SQL function:

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

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

• array_upper() is the wrong function. Arrays can have arbitrary subscripts. array_length() does what you are looking for. Related question:

  • Normalize array subscripts for 1-dimensional array so they start with 1

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

回答2:

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.

回答3:

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);
});