As I stated, I was trying to figure out if there was a problem with the documentation. Below is the documentation for -c and I cannot find anywhere it says you cannot use variables with it. The only way that that could make sense is if declaring a variable is a psql specific feature and -v is hiding behind that in the man page.
I could find no place where it is documented that declaring a variable is a psql specific feature.
From the man page for the psql application:
"psql provides variable substitution features similar to common Unix command shells."
We tend to avoid trying to define things that don't exist, and so the lack of any discussion regarding variables here:
Is the only evidence you have that SQL itself, and thus things that the server itself understands, doesn't include variables.
In addition, of what use would it be to declare a variable that could not be accessed without a command. It wouldn't make sense (at least to me) to declare something that is going immediately out of scope before the next command executes or when the psql command (and hence the session) ends.
Variables are useful for psql scripts. The -c command line option is an alternative to using a psql script, with limitations, such as this one. It either handles one meta-command or pure SQL. The later being something that excludes psql variables.
One would hope that if the variable were out of scope it would generate an error.
Is there any explanation for this?
Also in the psql application man page:
"Since colons can legally appear in SQL commands, an apparent attempt at interpolation (that is, :name, :'name', or :"name") is not replaced unless the named variable is currently set."
Though I suppose that isn't quite true - since in this case the reason for non-substitution is simply that the -c processing code doesn't even try. So :v1 is literally sent to the server, which doesn't understand the psql-specific syntax.
-c command
command must be either a command string that is completely parsable
by the server (i.e., it contains no psql-specific features),
So your fundamental misunderstanding here is that this feature described on the psql man page is a psql-specific feature.
David J.
