Results from multiple statements query returned as single dimensional array

[traderboy]

I have 3 queries I'm running as multiple statements, but the results are all returned in a single dimensional array. The MySQL library returns multiple statement queries in a multidimensional array. The problem is I can't tell which results come from which query.

SQL: select id from table1;select id from table2;select id from table3;

PG:
row: [ {aaa},{bbb},{ccc}]

MySQL:
row: [ [{aaa}], [{bbb}], [{ccc}] ]

[brianc]

1. you could alias the columns:

select id as table1_id from table1; select id as table2_id from table2; select id as table3_id from table3;

2. You could also use multiple queries & an async library to help out with this. I would recommend this in most cases because you cannot issue more than a single parameterized statement at a time to postgres, so as soon as you want to add parameters to this statement, you're gonna be hit errors coming from the backend.

eg:

select id from table1 where name = $1; select id from table2 where name = $1; select id from table3 where name = $1;

is actually an invalid sql statement in PostgreSQL

3. You could submit a pull request w/ a change supporting this. I can help you with were to look. Overall I can definitely see a case where this would come in handy, and supporting it is not hard. Note: this is a breaking change so I'll want to roll a few other breaking changes in before bumping the major version if this does end up getting rolled in as a feature.

[lalitkapoor]

Would this work with a prepared statement? I think you can't have multiple commands in a single prepared statement.

[lalitkapoor]

Ignore me - you already stated this in point number 2 above.

[clayzermk1]

👍

What is the current status of this feature? Are you still looking for pull requests?

[matt212]

reference topic
#1190 (comment)

[brianc]

Added this to the [email protected] release milestone. Long standing bug but it should be fixed.

[brianc]

This is fixed in [email protected]

[vitaly-t]

While this does improve the driver nicely, for any library that works based off the results returned from the driver, it screws up everything, and requires major architectural changes.

There is probably not a single library out there ready to handle multi-result sets. This can cause a havoc 😄

[alitaheri]

@vitaly-t It has, it broke sequelize badly, and we don't know how to handle multiple statements throughout the library 😅 😅 But it was needed. necessary evil 😈

Thanks @brianc for this nice feature. 👍 👍 Hope we can migrate soon so others can use it from sequelize too 💟

[vitaly-t]

@alitaheri same here, I'm stuck trying to figure out a good approach to supporting multiple results in pg-promise. I have already redesigned everything for that, but released nothing, as I'm not too happy about the new architecture, it seems too verbose. Here's me discussing some of it with the author of massive.js: vitaly-t/pg-promise#371