Re: Alternative to psql -c ?

On Wed, Jun 25, 2014 at 8:43 AM, James Le Cuirot
<chewi(at)aura-online(dot)co(dot)uk> wrote:
> Hello,
>
> I've been using the Chef database cookbook and found it
> frustrating because it doesn't allow you to use peer
> authentication. The client process generally runs as root and
> connects to PostgreSQL using the Ruby pg gem.
>
> I have patched it to shell out to psql instead. This has the
> added benefit of not having to install "build-essential" on boxes
> that might otherwise be very minimal. This is normally needed to
> build the pg gem. I would therefore like to push the shell out
> approach but one problem remains.
>
> The cookbook currently uses PQexec so multiple SQL commands are
> wrapped in a transaction unless an explicit transaction
> instruction appears. I don't want to change this behaviour but
> the only way to get exactly the same effect from psql is to use
> the -c option.
>
> I suspect some may shove rather large SQL scripts through this to the
> extent that it may break the command line limit, if not on Linux, then
> perhaps on Windows, where I gather it's 32,768. Passing these scripts
> on the command line doesn't seem particularly elegant in any case. I'd
> really like to use stdin but this has different transactional
> behaviour. I thought about looking for instances of transaction
> instructions in advance but I have seen that PostgreSQL does not do
> this naively; it uses the lexer.
>
> Is there another way?

If I understand you properly (not sure), I pretty commonly get around
this via 'cat'.

cat \
<(echo "BEGIN;") \
<(echo "\set ON_ERROR_STOP") \
foo.sql bar.sql etc
<(echo "COMMIT;") \
| psql ...

merlin