javascript - Using a cursor with pg-promise - Stack Overflow

admin管理员组

文章数量:1026989

I'm struggling to find an example of using a cursor with pg-promise. node-postgres supports its pg-cursor extension. Is there a way to use that extension with pg-promise? I'm attempting to implement an asynchronous generator (to support for-await-of). pg-query-stream doesn't seem to be appropriate for this use case (I need "pull", rather than "push").

As an example, I use SQLite for my unit tests and my (abridged) generator looks something like this...

async function* () {

    const stmt = await db.prepare(...);

    try {

        while (true) {

            const record = await stmt.get();

            if (isUndefined(record)) {

                break;
            }

            yield value;
        }
    }
    finally {

        stmt.finalize();
    }
}

Using pg-cursor, the assignment to stmt would bee something like client.query(new Cursor(...)), stmt.get would bee stmt.read(1) and stmt.finalize would bee stmt.close.

Thanks

I'm struggling to find an example of using a cursor with pg-promise. node-postgres supports its pg-cursor extension. Is there a way to use that extension with pg-promise? I'm attempting to implement an asynchronous generator (to support for-await-of). pg-query-stream doesn't seem to be appropriate for this use case (I need "pull", rather than "push").

As an example, I use SQLite for my unit tests and my (abridged) generator looks something like this...

async function* () {

    const stmt = await db.prepare(...);

    try {

        while (true) {

            const record = await stmt.get();

            if (isUndefined(record)) {

                break;
            }

            yield value;
        }
    }
    finally {

        stmt.finalize();
    }
}

Using pg-cursor, the assignment to stmt would bee something like client.query(new Cursor(...)), stmt.get would bee stmt.read(1) and stmt.finalize would bee stmt.close.

Thanks

• javascript
• pg-promise
• database-cursor

Share Improve this question Follow edited Jun 5, 2018 at 11:20 vitaly-t 26k1717 gold badges127127 silver badges150150 bronze badges asked Jun 5, 2018 at 7:52 sdc395sdc395 28511 gold badge33 silver badges1212 bronze badges 1

• Cursor examples for the base driver are here, so you just have to use manual connect to access Client and use it in the same way as in the examples. – vitaly-t Commented Jun 5, 2018 at 11:09

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 5

Following the original examples, we can modify them for use with pg-promise:

const pgp = require('pg-promise')(/* initialization options */);
const db = pgp(/* connection details */);

const Cursor = require('pg-cursor');

const c = await db.connect(); // manually managed connection

const text = 'SELECT * FROM my_large_table WHERE something > $1';
const values = [10];

const cursor = c.client.query(new Cursor(text, values));

cursor.read(100, (err, rows) => {
  cursor.close(() => {
    c.done(); // releasing connection
  });
  // or you can just do: cursor.close(c.done);
});

Since pg-promise doesn't support pg-cursor explicitly, one has to manually acquire the connection object and use it directly, as shown in the example above.

pg-query-stream doesn't seem to be appropriate for this use case (I need pull, rather than push).

Actually, in the context of these libraries, both streams and cursors are only for pulling data. So it would be ok for you to use streaming also.

I'm struggling to find an example of using a cursor with pg-promise. node-postgres supports its pg-cursor extension. Is there a way to use that extension with pg-promise? I'm attempting to implement an asynchronous generator (to support for-await-of). pg-query-stream doesn't seem to be appropriate for this use case (I need "pull", rather than "push").

As an example, I use SQLite for my unit tests and my (abridged) generator looks something like this...

async function* () {

    const stmt = await db.prepare(...);

    try {

        while (true) {

            const record = await stmt.get();

            if (isUndefined(record)) {

                break;
            }

            yield value;
        }
    }
    finally {

        stmt.finalize();
    }
}

Using pg-cursor, the assignment to stmt would bee something like client.query(new Cursor(...)), stmt.get would bee stmt.read(1) and stmt.finalize would bee stmt.close.

Thanks

I'm struggling to find an example of using a cursor with pg-promise. node-postgres supports its pg-cursor extension. Is there a way to use that extension with pg-promise? I'm attempting to implement an asynchronous generator (to support for-await-of). pg-query-stream doesn't seem to be appropriate for this use case (I need "pull", rather than "push").

As an example, I use SQLite for my unit tests and my (abridged) generator looks something like this...

async function* () {

    const stmt = await db.prepare(...);

    try {

        while (true) {

            const record = await stmt.get();

            if (isUndefined(record)) {

                break;
            }

            yield value;
        }
    }
    finally {

        stmt.finalize();
    }
}

Using pg-cursor, the assignment to stmt would bee something like client.query(new Cursor(...)), stmt.get would bee stmt.read(1) and stmt.finalize would bee stmt.close.

Thanks

• javascript
• pg-promise
• database-cursor

Share Improve this question Follow edited Jun 5, 2018 at 11:20 vitaly-t 26k1717 gold badges127127 silver badges150150 bronze badges asked Jun 5, 2018 at 7:52 sdc395sdc395 28511 gold badge33 silver badges1212 bronze badges 1

• Cursor examples for the base driver are here, so you just have to use manual connect to access Client and use it in the same way as in the examples. – vitaly-t Commented Jun 5, 2018 at 11:09

Add a ment  | 

1 Answer 1

Sorted by: Reset to default 5

Following the original examples, we can modify them for use with pg-promise:

const pgp = require('pg-promise')(/* initialization options */);
const db = pgp(/* connection details */);

const Cursor = require('pg-cursor');

const c = await db.connect(); // manually managed connection

const text = 'SELECT * FROM my_large_table WHERE something > $1';
const values = [10];

const cursor = c.client.query(new Cursor(text, values));

cursor.read(100, (err, rows) => {
  cursor.close(() => {
    c.done(); // releasing connection
  });
  // or you can just do: cursor.close(c.done);
});

Since pg-promise doesn't support pg-cursor explicitly, one has to manually acquire the connection object and use it directly, as shown in the example above.

pg-query-stream doesn't seem to be appropriate for this use case (I need pull, rather than push).

Actually, in the context of these libraries, both streams and cursors are only for pulling data. So it would be ok for you to use streaming also.

本文标签： javascriptUsing a cursor with pgpromiseStack Overflow