Re: Alternative to psql -c ?

James Le Cuirot <chewi(at)aura-online(dot)co(dot)uk> writes:

> 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?

Forget about trying to use psql -c since you try doing anything
non-trivial via this method and quoting will be at least one of your
headaches.

Write a simpel $your-fav-scripting-lang client that passes stdin into a
single executor call...

#!/usr/bin/python

import psycopg2, sys

conn = psycopg2.connect(...)
cur = conn.cursor(

cur.execute(sys.stdin.read())
conn.commit()

HTH)

PS: Complex multi-statement executor calls are somewhat nuanced in their
own ways and I would be trying hard *not* to do this without very good
reason.

> Regards,
> James

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800