Re: Multiple psql -c / -f options

On 10/18/13 8:39 AM, Andrew Dunstan wrote:
>
> On 10/18/2013 02:19 AM, Fabien COELHO wrote:
>>
>>> IMHO the current behavior is broken:
>>>
>>> decibel(at)decina:[17:46]~/pgsql/HEAD/i$bin/psql -c 'select 1' -c 'select 2'
>>> ?column?
>>> ----------
>>> 2
>>> (1 row)
>>
>> Another try with one -c but with similar results:
>>
>> sh> psql -c "SELECT 1; SELECT 'hello';"
>> ?column?
>> ----------
>> hello
>> (1 row)
>>
>> sh> psql -V
>> psql (PostgreSQL) 9.3.1
>>
>
>
> It's not broken. All this behaviour is documented fairly explicitly. See <http://www.postgresql.org/docs/current/static/app-psql.html> For example, regarding Fabio's example, which is actually very different from Jim's, the docs say: "only the result of the last SQL command is returned."
>
> If you want to argue that it should be enhanced, then do. But it's acting as designed and as documented.

Perhaps "broken" was a bad choice of words. :)

Even if the owner's manual for your car says "You must manually lock the doors before you can start the engine" that doesn't mean it's good behavior. ;)

There's actually additional problems with compound statements. For example, EXECUTE 'CREATE TABLE foo(...); ALTER TABLE foo ...;' doesn't work (at least last I checked). I ass-u-me that there's some fundamental issue to fixing that, so I haven't even looked into it.

When it comes to multiple command-line options, ISTM that current behavior fails the "least surprise" test miserably by simply ignoring some options:

psql --cluster 9.1/us-cnuapp_b -d cnuapp_prod -c 'CREATE TEMP VIEW t AS SELECT 1' -c 'SELECT * FROM t'
ERROR: relation "t" does not exist
LINE 1: SELECT * FROM t

I've never run across any other command-line tool that does that, and I don't think we should either.

> I suspect changing this might actually have more wrinkles that you imagine, but I could be wrong.

The only one I've thought of is some users might actually be depending on existing behavior...

> Incidentally, both of you could probably achieve what you apparently want with:
>
> echo 'some sql here' | psql

True... while I personally think it'd be nice to actually support multiple -c/-f options it's not all that hard to work around that being missing.

What does concern me is that we're intentionally ignoring requests the user has made of psql. We should either fulfill the requests or throw an error.
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net