Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tIGAz-00DEvi-Lv for pgsql-novice@arkaria.postgresql.org; Mon, 02 Dec 2024 23:50:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tIGAw-004vo5-VH for pgsql-novice@arkaria.postgresql.org; Mon, 02 Dec 2024 23:50:00 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tIGAw-004vnv-0F for pgsql-novice@lists.postgresql.org; Mon, 02 Dec 2024 23:49:59 +0000 Received: from smtp-outbound2.duck.com ([20.67.223.10]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tIGAt-000jVf-JO for pgsql-novice@lists.postgresql.org; Mon, 02 Dec 2024 23:49:57 +0000 Content-Type: multipart/alternative; boundary="=_iyBUFKDQOdwMtSLGX5+AtR_=" MIME-Version: 1.0 Subject: Re: Command Line option misunderstanding References: <487DB217-EA37-4139-AB97-B61B04ECAEA7.1@smtp-inbound1.duck.com> <493C622D-D3B6-4662-A617-EBCCDE5AA4DF.1@smtp-inbound1.duck.com> <30948e6771500c0e9d8b587f4e34165aadb1cc0b.camel@cybertec.at> Content-Language: en-US X-Rspamd-Bar: - X-Rspamd-Report: MIME_GOOD(-0.1) R_SPF_ALLOW(-0.2) ARC_ALLOW(-1) X-Rspamd-Score: -1.3 X-Rspamd-Action: no action To: Laurenz Albe , punch-hassle-guise@duck.com, "pgsql-novice@lists.postgresql.org" Received: by smtp-inbound1.duck.com; Mon, 02 Dec 2024 18:49:53 -0500 Message-ID: <4FB93541-3986-4C8F-9571-1FBD8655A686.1@smtp-inbound1.duck.com> Date: Mon, 02 Dec 2024 18:49:53 -0500 From: punch-hassle-guise@duck.com DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=duck.com; h=From: Date: Message-ID: To: Content-Language: References: Subject: MIME-Version: Content-Type; q=dns/txt; s=postal-KpyQVw; t=1733183394; bh=M2t6m+lZQ3YbpQijPaRH2m1zpV7obOc2lhDKVxSM4BI=; b=JD/QUaXoD4RCVH0meGTCia2YBjm9lCHDXdtIcTsk93mElpVKEmy3khhljf6sLr1+YEI31cV9R n5NyeYdPhun9dxCH+Hv1yPeQArAs4p3cFIJYur8VLtaHev8Xr4vJOeCxyondm1v/U3uEGE86p4S 4cnv06Ea0fctZjuWe49Z+Zs= List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=_iyBUFKDQOdwMtSLGX5+AtR_= Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 12/2/24 17:10, Laurenz Albe wrote: > 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 --=_iyBUFKDQOdwMtSLGX5+AtR_= Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 12/2/24 17:10, Laurenz Albe wrote:
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





--=_iyBUFKDQOdwMtSLGX5+AtR_=--