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.

Kimberly
  • 31
  • 1
  • 4
  • Change your query from `SELECT func` to `SELECT * FROM func`. It will give you back result correctly then. – vitaly-t Oct 20 '16 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 *`). – Kimberly Oct 21 '16 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](https://github.com/vitaly-t/pg-promise) does support much richer syntax, and of course arrays automatically ;) – vitaly-t Oct 21 '16 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 – Kimberly Oct 24 '16 at 18:31

0 Answers0