2

I'm trying to execute this PSQL query to call a function and get a list of ids back. [This function works when run in PGAdmin as it should, verified by the ids coming back.]

SELECT get_dataids(
  'university',
  '2015-08-01',
  '2015-08-02',
  array ['air1'],
 'electricity_egauge_minutes'
);

I am using node-postgres (https://github.com/brianc/node-postgres) and have been using parameterized queries as such. [This function is successful in the Node backend.]

  var client = new pg.Client(connectionString);
  client.connect();
  return client.query('SELECT ercot.get_live_realtime_lmp($1, $2)',
    ['ercot', 'zone'])
  .then(data => {
    client.end();
    return data.rows;
  })
  .catch(err => {
    client.end();
    console.log('err', err);
  });

This is the actual query I am trying to run:

  return client.query('SELECT public.get_dataids($1, $2, $3, $4, $5)',
    [
    'university',
    '2015-08-01',
    '2015-08-02',
    ['air1'],
    'electricity_egauge_minutes',
  ])

I had thought I would be able to pass an array as a parameter to the function, but it does not seem to be working (as evidenced by 1) not returning the same ids as the raw SQL query, and 2) passing any string into the array returns the same non-list of ids).

I am guessing the issue is specifically trying to pass an array, but I am not able to figure out the accurate way to actually pass in an array. I have tried an empty array, an array with an empty string, passing a string with 'array ["air1"]', passing in just the string 'air1', etc. One of the errors come back as: error: malformed array literal, which leads me to believe it's the way I'm passing in the array. Any suggestions would be appreciated.

4
  • Change your query from SELECT func to SELECT * FROM func. It will give you back result correctly then. Commented Oct 20, 2016 at 20:33
  • Thanks for trying vitaly-t. Unfortunately, that doesn't solve the problem and would only give me the same data back in a different format (in an array rather than a string). This is confirmed with the other working functions called in exactly the same manner (without the SELECT *). Commented Oct 21, 2016 at 21:57
  • Yeah, that's only half the solution. The other half - to convert an array into array[1,2,3] format. Seems like the base driver doesn't support it. However, pg-promise does support much richer syntax, and of course arrays automatically ;) Commented Oct 21, 2016 at 23:50
  • Ah, cool, thanks. I'll keep that in mind for the future. Turns out, my issue was the database function written for me by another coworker wasn't done correctly... :P Commented Oct 24, 2016 at 18:31

0

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.