Issue #157 wontfix

Add a way to retrieve results from multi statement queries

leoasis
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 repo owner

    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 reporter

    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. Lars Kanis

    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 reporter

    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. Lars Kanis

    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