Issue #157 wontfix

Add a way to retrieve results from multi statement queries

leoasis avatarleoasis created an issue

Currently, the gem only allow to fetch the last query's results if executing a multi statement query. It would be great to provide a way to get all the results, for example the way that the mysql2 gem is doing (https://github.com/brianmario/mysql2#connection-options)

Comments (6)

  1. Michael Granger

    There's no real way to support this without the upstream client library also providing support. As it is, the docs for PQexec say:

    The command string can include multiple SQL commands (separated by semicolons). Multiple queries sent in a single PQexec call are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the query string to divide it into multiple transactions. Note however that the returned PGresult structure describes only the result of the last command executed from the string.

    If you can get it supported upstream, I'd be happy to make sure pg supports it as well.

  2. leoasis

    Thanks for pointing that out, didn't know that the upstream library wasn't supporting it. Will check there to see if they are willing to add this.

  3. larskanis

    You can retrieve all results by using send_query and get_result:

    conn.send_query("SELECT 1; SELECT 2")
    conn.get_result.to_a   # => [{"?column?"=>"1"}]
    conn.get_result.to_a   # => [{"?column?"=>"2"}]
    
  4. leoasis

    That is nice! Will check that a little further. Anyway, I sent an email to one of the members of the PostgreSQL core team, and he told me that this feature (for the sync version) is in the TODO list but noone is there to implement it.

    Since I somewhat suck at C and I haven't worked directly with the client lib I don't know how useful I will be, but if someone else is interested in this I could help in any way I can.

  5. larskanis

    It's just for the sake of simplicity. Use exec for the most common use case and use prepared statements, async API, COPY, notifications etc. for all the glory features of libpq/postgres.

  6. Log in to comment
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.