public inbox for [email protected]  

help / color / mirror / Atom feed

From: [email protected]
To: Laurenz Albe <[email protected]>
To: [email protected]
To: [email protected] <[email protected]>
Subject: Re: Command Line option misunderstanding
Date: Mon, 02 Dec 2024 18:49:53 -0500
Message-ID: <[email protected]> (raw)
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>


On 12/2/24 17:10, Laurenz Albe wrote:
> On Mon, 2024-12-02 at 16:21 -0500,[email protected]  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

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Command Line option misunderstanding
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox