Re: Alternative to psql -c ?

On Thu, 26 Jun 2014 11:02:09 -0700
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> James Le Cuirot <chewi@aura-online.co.uk> writes:
> > This got me wondering what Rails uses. I dug into ActiveRecord and
> > found that apart from the odd call to PQexec with hardcoded single
> > statements, it uses PQsendQuery. The libpq docs state a few of the
> > differences but don't mention whether PQsendQuery automatically
> > creates a transaction like PQexec does. Please could you clarify
> > this?
>
> PG is not capable of executing queries that are not in transactions,
> so yes, PQsendQuery will create a single-statement transaction if you
> haven't sent BEGIN.  However, there's a huge difference for the
> purposes we're discussing here: PQsendQuery does not allow more than
> one SQL command in the string.  So most of this discussion is
> irrelevant when you're going through that API.

Heh, are you sure? From the docs...

"Using PQsendQuery and PQgetResult solves one of PQexec's problems: If a
command string contains multiple SQL commands, the results of those
commands can be obtained individually."

I also seem to be able to execute multiple statements at a time through
ActiveRecord. This method is just a thin wrapper around PQsendQuery.
Granted I only get the last result but if I change the first statement
to something erroneous, it does fail.

1.9.1 :001 > ActiveRecord::Base.connection.execute("SELECT 1; SELECT 2").first
 => {"?column?"=>"2"}

Regards,
James