Thread: psql --command option ignores --variable's

Tim Kane <tim.kane@gmail.com> writes:
> It seems the behaviour of the --command / -c option is such that it will
> ignore any variables set on the command-line.

If memory serves, the string given to -c is just fired off to the server
as-is.  It's not broken into separate commands, and I think we don't do
substitutions on it either, which would explain this result.

There have been some discussions of trying to make it work more like
processing of normal input would do, but people are too afraid of breaking
existing applications by changing the semantics.

I'd suggest something like

echo 'select :TESTVAR;' | psql --variable TESTVAR='123'

            regards, tom lane

On 09/02/2013 07:54 AM, Tim Kane wrote:
> Hi all,
>
> It seems the behaviour of the —command / -c option is such that it will
> ignore any variables set on the command-line.
>
>
> :~ psql --variable TESTVAR='123' --command 'select :TESTVAR;'
> ERROR:  syntax error at or near ":"
> LINE 1: select :TESTVAR;
>                 ^
>
> :~$ psql --variable TESTVAR='123'
> psql (9.1.9)
> Type "help" for help.
>
> timk=> select :TESTVAR;
>   ?column?
> ----------
>        123
> (1 row)
>
>
>
> Obviously, there are ways around this, but is this expected behaviour?

See  *--> section below.
http://www.postgresql.org/docs/9.1/interactive/app-psql.html

-c command
--command=command
Specifies that psql is to execute one command string, command, and then
exit. This is useful in shell scripts. Start-up files (psqlrc and
~/.psqlrc) are ignored with this option.

*-->command must be either a command string that is completely parsable
by the server (i.e., it contains no psql-specific features), or a single
backslash command. Thus you cannot mix SQL and psql meta-commands with
this option. To achieve that, you could pipe the string into psql, like
this: echo '\x \\ SELECT * FROM foo;' | psql. (\\ is the separator
meta-command.)
<--*

If the command string contains multiple SQL commands, they are processed
in a single transaction, unless there are explicit BEGIN/COMMIT commands
included in the string to divide it into multiple transactions. This is
different from the behavior when the same string is fed to psql's
standard input. Also, only the result of the last SQL command is returned.



>
>
> Tim


--
Adrian Klaver
adrian.klaver@gmail.com

Ahh, ok. It is documented. I'll get back in my box :)



On 02/09/2013 16:04, "Adrian Klaver" <adrian.klaver@gmail.com> wrote:

>On 09/02/2013 07:54 AM, Tim Kane wrote:
>> Hi all,
>>
>> It seems the behaviour of the ‹command / -c option is such that it will
>> ignore any variables set on the command-line.
>>
>>
>> :~ psql --variable TESTVAR='123' --command 'select :TESTVAR;'
>> ERROR:  syntax error at or near ":"
>> LINE 1: select :TESTVAR;
>>                 ^
>>
>> :~$ psql --variable TESTVAR='123'
>> psql (9.1.9)
>> Type "help" for help.
>>
>> timk=> select :TESTVAR;
>>   ?column?
>> ----------
>>        123
>> (1 row)
>>
>>
>>
>> Obviously, there are ways around this, but is this expected behaviour?
>
>See  *--> section below.
>http://www.postgresql.org/docs/9.1/interactive/app-psql.html
>
>-c command
>--command=command
>Specifies that psql is to execute one command string, command, and then
>exit. This is useful in shell scripts. Start-up files (psqlrc and
>~/.psqlrc) are ignored with this option.
>
>*-->command must be either a command string that is completely parsable
>by the server (i.e., it contains no psql-specific features), or a single
>backslash command. Thus you cannot mix SQL and psql meta-commands with
>this option. To achieve that, you could pipe the string into psql, like
>this: echo '\x \\ SELECT * FROM foo;' | psql. (\\ is the separator
>meta-command.)
><--*
>
>If the command string contains multiple SQL commands, they are processed
>in a single transaction, unless there are explicit BEGIN/COMMIT commands
>included in the string to divide it into multiple transactions. This is
>different from the behavior when the same string is fed to psql's
>standard input. Also, only the result of the last SQL command is returned.
>
>
>
>>
>>
>> Tim
>
>
>--
>Adrian Klaver
>adrian.klaver@gmail.com