Re: Alternative to psql -c ?

On Thu, Jun 26, 2014 at 4:30 AM, James Le Cuirot
<chewi(at)aura-online(dot)co(dot)uk> wrote:
> On Wed, 25 Jun 2014 13:21:44 -0500
> Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>
>> > 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 ...
>
> This would work but given that this will be part of a public and
> widely-used cookbook, it needs to be able to deal with any scripts that
> will be thrown at it. Some of these may contain transactional
> statements and these will not work properly if wrapped in a big
> BEGIN/COMMIT. Having said that, Tom Lane has suggested that we should
> not rely on the existing transactional behaviour so maybe we'll need to
> be more explicit about whether we actually want a transaction or not.

To be clear, Tom was advising not to rely on some of the quirky
aspects of -c. psql as it stands right now has a some limitations:
single transaction mode does not work with stdin and there is no
reasonable way to pass multiple scripts through the command line.
Adding it up this means that for generic multiple .sql passing you
have to wrap psql with a script.

It'd be neat if psql had some xargs compatible facility for passing
multiple files. This is complicated by the assumption that the
unadorned argument is the database. Suppose though if the -f switch
is arguments past the first are assumed to be files. Then you could
do:

psql foodb -1f foo.sql bar.sql baz.sql

or find . | xargs psql foodb -1f

(it's arguably cleaner to allow multiple -f arguments, but that's a
lot more problematic for xargs type usage).

As things stand today though, AFAICT the best way to consolidate
scripts is to build a big script out of small ones. I realize that
stinks from your point of view since not everyone will want to use
unix/bash...

merlin