postgres SELECT query returns unusable result

Question

I have a simple SELECT query that is returning an unusable result. I am using pg-promise in node.js

[
  {
    "function_name": "(f10d1988-4db5-49de-97ab-0c8b15bedfa7,image.jpg,Image)"
  },
  {
    "function_name": "(f10d1988-4db5-49de-97ab-0c8b15bedfa7,image2.jpg,Image 2)"
  }
]

but I was expecting a basic json structure like

[
  {
    id: '',
    title: '',
    image: ''
  },
  {...etc}
]

Why is it doing this? How do I get a normalized result?

My query looks like the below:

CREATE OR REPLACE FUNCTION get_photos(
  title_param     TEXT
)
RETURNS TABLE(
  id         UUID,
  image      varchar(200),
  title      varchar(200) 
) AS
$func$
BEGIN
  RETURN QUERY SELECT
    i.id,
    i.image,
    i.title
  FROM images AS i
  WHERE i.title = title_param;
END;
$func$ LANGUAGE PLPGSQL;

Here is my db conenctor setup, almost all defaults.

require('dotenv').config();
const Promise = require('bluebird');
const pg = require('pg-promise')({
  promiseLib: Promise
});

const config = {
  user: process.env.USER,
  host: process.env.HOST,
  database: process.env.DATABASE,
  password: process.env.PASSWORD
};

const db = pg(config);

export default db;

Here is the express endpoint that is calling the function:

export const getData = async (req, res) => {
  const { title } = req.query;
  let data;
  try {
    data = await db.many('SELECT function_name($1)', [title]);
  } catch (err) {
    data = err;
  }
  res.send(data);
};

EDIT

I ran the query manually instead of through a function and the data returned correctly which means that there is an issue with my TABLE() return. What could possibly cause this issue?

images = await db.many(`
  SELECT
    p.id,
    p.img,
    p.type,
    p.title
  FROM photos p
  WHERE p.type = '${type}';
`, [type]);

Kindly post your js code, where you are catching the results. — Prabhjot Singh Kainth
– Prabhjot Singh Kainth, Commented Dec 24, 2019 at 5:50
@PrabhjotSinghKainth I added the db js and endpoint, mostly defaults — Brandon
– Brandon, Commented Dec 24, 2019 at 6:14
Just try res.send(data[0]); codota.com/code/javascript/functions/pg/Client/query — Prabhjot Singh Kainth
– Prabhjot Singh Kainth, Commented Dec 24, 2019 at 7:05

user330315 · Accepted Answer · 2019-12-24 08:40:54Z

3

Because the function is defined as returning a table, you need to use it like a table:

SELECT * FROM function_name($1)

answered Dec 24, 2019 at 8:40

user330315

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

Comments

vitaly-t · Accepted Answer · 2019-12-25 15:14:27Z

1

Use func as the query method:

data = await db.func('function_name', [title]);

It assumes you return a table, and so will work for you by default.

And for stored procedures, there's proc method.

Also, your parameter formatting for the images query is wrong, see Named Parameters:

IMPORTANT: Never use the reserved ${} syntax inside ES6 template strings ...

edited Dec 25, 2019 at 15:14

answered Dec 24, 2019 at 22:21

vitaly-t

26.1k17 gold badges129 silver badges151 bronze badges

3 Comments

Brandon Over a year ago

I tried the above but received the following error error: function function_name($1)(unknown) does not exist but keeping the function as db.many(SELECT * FROM function_name(...etc)) works fine also, interesting that ${} shouldn't be used but worked in this simple case, still good to see the best practice as a reference

vitaly-t Over a year ago

@Brandon It looks like you didn't pass it the expected parameters correctly, or there is no function_name function defined. You should include the code that you actually executed here. b.t.w. I am the author of pg-promise, and the solution I gave is the right way to do it.

Brandon Over a year ago

I tried again, the issue is that I kepy function_name($1) when using db.func instead of just function_name. Thanks for your help.

Collectives™ on Stack Overflow

postgres SELECT query returns unusable result

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related