Skip to content

Problems of <unnamed portal> using stored procedure returning REFCURSOR #2466

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
floSeur opened this issue Feb 11, 2021 · 3 comments
Closed

Comments

@floSeur
Copy link

floSeur commented Feb 11, 2021

Context

Hi, I'm currently trying to execute the code below using node-postgres

const config: QueryConfig = {
   text: 'select * from theriaque.get_drugs($1)',
   values: [`%${name}%`]
};
this._client.query(config)
   .then((res) => {
        console.log(res);
        console.log('QUERY >>>>>', `fetch all from "${res.rows[0]['get_drugs']}"`);
        this._client.query(`fetch all from "${res.rows[0]['get_drugs']}"`)
            .then((fetchedData) => {
                 console.log(fetchedData);
             })
             .catch((err) => {
                 console.log(err);
              });
     })
     .catch((err) => {
        console.log(err);
});
  • theriaque.get_drugs() is a stored procedure return a REFCURSOR
  • res.rows[0]['get_drugs'] is returning <unnamed portal 1>

Problem

When the second query is called to fecth data from the REFCURSOR unnamed portal 1, it goes to the catch and tells me that cursor "<unnamed portal 1>" does not exist.
I tried using pg-cursor but there is no result from the first query.

Any suggestions ?
Thanks

@SunilManthenaG01
Copy link

@floSeur where you able to find a solution for this one ?

@gustavofiaschi
Copy link

gustavofiaschi commented Aug 30, 2022

Hi. I had the same issue.
The solution is to run queries 1# and 2# over the same transaction. That way it'll find the cursor.

@brianc
Copy link
Owner

brianc commented Aug 30, 2022

Sounds like a postgres issue & not a driver issue. Thanks for the info! (note: using a named portal would probably also help)

@brianc brianc closed this as completed Aug 30, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

4 participants