Re: Command Line option misunderstanding

On 12/2/24 17:10, Laurenz Albe wrote:
> On Mon, 2024-12-02 at 16:21 -0500,punch-hassle-guise(at)duck(dot)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