Re: Command Line option misunderstanding - Mailing list pgsql-novice

On Mon, 2024-12-02 at 16:21 -0500, punch-hassle-guise@duck.com wrote:

I am not trying to be contentious, but a simple search shows that every 
major implementation of SQL allows variables and there are hundreds if 
not thousands of pages dedicated to the idea of using variables in SQL 
and how they are fundamental.


There are certainly reasons this doesn't work, but saying variables are 
psql specific......

You *are* trying to be contentious.

In PostgreSQL, there are psql variables, but no SQL variables.
You may not like that, that's okay. But that's how it currently
is, and it is documented like that.

If you want that to change, your best option is to review
https://commitfest.postgresql.org/51/1608/
which tries to introduce variables into SQL.

As far as I can tell, the SQL standard knows variables only
as host variables in embedded SQL.

Yours,
Laurenz Albe

I am sorry that you think that I am being contentious.

This is a novice list.

I apologize for not knowing the difference between a psql variable and

a host variable.

At least to me it is confusing.

From: https://www.ibm.com/docs/en/i/7.1?topic=sql-using-host-variables-in-statements

"A host variable is a field in your program that is specified in an SQL statement, usually as the source or target for the value of a column."

Since you can use psql variables as a source or target for for the value of a column, you might understand my confusion. (Yes, I know that IBM doesn't speak for Postgres, but one can always hope for standards and compliance.)

And you can definitely use psql variables in a psql session that was started with the variable specified on the command line.

$ psql -h anna -d GT7   -v a='11117';

autocommit on

GT7=# select evt_id from events where sport_mode_evt_id=:a;
 evt_id
--------
    116
(1 row)

The problem seems to be, as alluded to by others attempting to help me

that the problem only exists when using -c on the same line as -v.

I am guessing here, that my original question should have been:

"Why is it okay to use a psql variable declared on a command line in a psql session that was started from said command line and not with a session that was created and destroyed with a -c command switch?"

Related Question:

Documentation says:

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.

$psql -h anna -d GT7   -c "\set  a '11117' \\ select evt_id from events where sport_mode_evt_id=:a"

autocommit on

$

So, no error generated.  One would think that the statement meets the definition of no psql specific feature. 

The select completed with no error (albeit incorrectly), so the whatever is managing the execution of the select must think the variable is in scope.

A novice would think that something is broken, no error generated, incorrect result.

If this is intended behavior, it might be nice for the documentation to say

"don't use -v with -c, there are things in play beyond your knowledge".

Thanks,

Ken