search

will / crystal-pg

a postgres driver for crystal
BSD 3-Clause "New" or "Revised" License
463 stars 78 forks source link

Is it possible to have a multiple statement query? #139

Closed da99 closed 6 years ago

da99 commented 6 years ago

The following SQL fails using the .query methods because it has multiple statements:

 SET LOCAL my.val = 'abc';
 SELECT current_setting('my.val') as my_val, 'aa' as now_;

Is there a way to execute this query? Maybe as an unprepared statement?

will commented 6 years ago

Postgres has two protocols for executing queries. One (extended query) allows parameters and other advanced features, but only one statement at a time. The other (simple query) allows multiple statements, but is very basic.

In order to prevent some problems, we only return results using the extended query protocol. We do support the simple one also (https://github.com/will/crystal-pg/blob/master/src/pg/connection.cr#L29), but don't return results for that, it's intended for creating tables or other DDL.

I think the best way forward is to start a transaction and issue the two statements separately.

da99 commented 6 years ago

Thanks. That cleared a lot of things up for me.