public inbox for [email protected]  

help / color / mirror / Atom feed

Command Line option misunderstanding
13+ messages / 5 participants
[nested] [flat]

* Command Line option misunderstanding
@ 2024-12-02 18:47  [email protected]
  0 siblings, 3 replies; 13+ messages in thread

From: [email protected] @ 2024-12-02 18:47 UTC (permalink / raw)
  To: [email protected]

It would appear that I have a fundamental misunderstanding
of either the english language or the psql man page.

A query of all the mailing lists returned no hits that I could find
relevant to this problem.  Some references to platform dependent
ticks, hence my guessing on some attempts.

Perhaps some kind soul could enlighten me.
Thanks.

psql --version
psql (PostgreSQL) 15.10 (Debian 15.10-0+deb12u1)


Simple "HERE" document that works

psql -h anna -d GT7 << HERE
\set v1 12
select :v1;
HERE

autocommit on

  ?column?
----------
        12
(1 row)
--end result--


Excerpt from man page
man psql
---cut----
-v assignment
        --set=assignment
        --variable=assignment
            Perform a variable assignment, like the \set meta-command. Note
            that you must separate name and value, if any, by an equal 
sign on
            the command line. To unset a variable, leave off the equal 
sign. To
            set a variable with an empty value, use the equal sign but leave
            off the value. These assignments are done during command line
            processing, so variables that reflect connection state will get
            overwritten later.
---end cut ---


On the surface it would appear that -v implies declaration and 
assignment of a single variable
to a value.  -v VARIABLE_NAME=VARIABLE_VALUE
which of course doesn't work for me.

the -- options are even more problematic for me
These read to me as
--set=VARIABLE_NAME=VARIABLE_VALUE
and
--variable=VARIABLE_NAME=VARIABLE_VALUE
OR
--VARIABLE_NAME=VARIABLE_VALUE
and I could find no combination of any type of any of these that would 
work for me.

My attempts at implementing my understanding of man page
and attempts at implementing what I perceive to be someones mis-writing 
of man page


----attempt 1--
psql -h anna -d GT7 -v v1=12 -c 'select :v1'

autocommit on

ERROR:  syntax error at or near ":"
LINE 1: select :v1

----end attempt---

---attempt 2 ---
psql -h anna -d GT7 -vv1=1 -c 'select ":v1" '

autocommit on

ERROR:  column ":v1" does not exist
LINE 1: select ":v1"
---end attempt ---

--- atttempt 3----
psql -h anna -d GT7 -vv1=1 -c 'select :v1'

autocommit on

ERROR:  syntax error at or near ":"
LINE 1: select :v1
---end attempt

---attempt 4----
sql -h anna -d GT7 -v "v1=1"  -c "select :v1"

autocommit on

ERROR:  syntax error at or near ":"
LINE 1: select ":v1"
--- end attempt---

---attempt 5 ---
psql -h anna -d GT7 -v 'v1=1'  -c 'select ":v1" '

autocommit on

ERROR:  column ":v1" does not exist
LINE 1: select ":v1"
----end attempt

--- attempt 6---
psql -h anna -d GT7 -v :v1=1  -c 'select ":v1" '
psql: error: invalid variable name: ":v1"
---end attempt

--- attempt 7 ---
psql -h anna -d GT7 -v "v1"=1  -c 'select ":v1" '

autocommit on

ERROR:  column ":v1" does not exist
LINE 1: select ":v1"
--- end attempt

---attempt 8 ---
psql -h anna -d GT7 -v 'v1'=1  -c 'select ":v1" '

autocommit on

ERROR:  column ":v1" does not exist
LINE 1: select ":v1"
--- end attempt ---

---attempt 9 ---
  psql -h anna -d GT7   -c '\set v1 12; select ":v1" '

autocommit on

--- end attempt ----

--- attempt 10 ---

psql -h anna -d GT7   -c '\set v1=12; select ":v1" '

autocommit on

invalid variable name: "v1=12;"
---end attempt

---attempt 11 ---

psql -h anna -d GT7   -c '\set v1 = 12; select ":v1" '

autocommit on
---end attempt ---


---attempt 12 ---
psql -h anna -d GT7   -c '\set "v1 = 12"; select ":v1" '

autocommit on

invalid variable name: ""v1 = 12";"

---end attempt ----

---attempt 13 ----
psql -h anna -d GT7   -c '\set "v1=12"; select ":v1" '

autocommit on

invalid variable name: ""v1=12";"
---end attempt---

---attempt 14 ---
psql -h anna -d GT7   -c '\set "v1=12" select ":v1" '

autocommit on

invalid variable name: ""v1=12""
---end attempt---

---attempt 15---

psql -h anna -d GT7   -c '\set v1:=12; select ":v1" '

autocommit on

invalid variable name: "v1:=12;"
---end attempt---

---attempt 16 ---
kdibble@thinkstation:~/development/gt7-scraper$ psql -h anna -d GT7   -c 
'\set v1:=12\n select ":v1" '

autocommit on

invalid variable name: "v1:=12"
---end attempt---

---attempt 17---
psql -h anna -d GT7   -c '\set v1 12\n select ":v1" '

autocommit on
---end attempt

---attempt 18---
psql -h anna -d GT7  -v  --set v1=12 -c 'select :v1 '
Password for user v1=12:
---end attempt---


---attempt 19 ---
kdibble@thinkstation:~/development/gt7-scraper$ psql -h anna -d GT7   
--set v1=12 -c 'select :v1 '

autocommit on

ERROR:  syntax error at or near ":"
LINE 1: select :v1
---end attempt

--- attempt 20---
psql -h anna -d GT7   -v --set v1=12 -c 'select :v1 '
Password for user v1=12:
--end attempt---

---attempt 21 ---
  psql -h anna -d GT7   -v v1  --set v1=12 -c 'select :v1 '

autocommit on

ERROR:  syntax error at or near ":"
LINE 1: select :v1
---end attempt

---attempt 22 ---
psql -h anna -d GT7   -v v1  --set =12 -c 'select :v1 '
psql: error: invalid variable name: ""
---end attempt---

---attempt 23---
psql -h anna -d GT7   -v v1 --set 12 -c 'select :v1 '

autocommit on

ERROR:  syntax error at or near ":"
LINE 1: select :v1

---end attempt

---attempt 24 ---
psql -h anna -d GT7   -v v1  --set v1=12 -c 'select :v1 '

autocommit on

ERROR:  syntax error at or near ":"
LINE 1: select :v1
---end attempt---

---attempt 25---
psql -h anna -d GT7    -v --set v1=12 -c 'select :v1 '
Password for user v1=12:
----

---attempt 26 ---
psql -h anna -d GT7    --set v1=12 -c 'select :v1 '

autocommit on

ERROR:  syntax error at or near ":"
LINE 1: select :v1
---end attempt

---attempt 27---
  psql -h anna -d GT7  -v v1="3" -c "select :v1"
+ psql -h anna -d GT7 -v v1=3 -c 'select :v1'

autocommit on

ERROR:  syntax error at or near ":"
LINE 1: select :v1
---end attempt

---attempt 28---
psql -h anna -d GT7  -v v1='3' -c "select :v1"
autocommit on

ERROR:  syntax error at or near ":"
LINE 1: select :v1
--- end attempt ---

---attempt 29---
psql -h anna -d GT7   -c '\set v1 12; select :v1 '
autocommit on
---end attempt ---

---attempt 30 ---
psql -h anna -d GT7 -v --set v1:=12 -c 'select :v1 '
Password for user v1:=12:
---end attempt ---

---attempt 31 ---
psql -h anna -d GT7  --variable=v1=12 -c 'select :v1 '

autocommit on

ERROR:  syntax error at or near ":"
LINE 1: select :v1
---end attempt---

---attempt 32---
psql -h anna -d GT7  --variable="v1=12" -c 'select :v1 '

autocommit on

ERROR:  syntax error at or near ":"
LINE 1: select :v1
---end attempt---






^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Command Line option misunderstanding
@ 2024-12-02 20:12  Joe Conway <[email protected]>
  parent: [email protected]
  2 siblings, 0 replies; 13+ messages in thread

From: Joe Conway @ 2024-12-02 20:12 UTC (permalink / raw)
  To: [email protected]; [email protected]

On 12/2/24 13:47, [email protected] wrote:
> It would appear that I have a fundamental misunderstanding
> of either the english language or the psql man page.

<snip>

> My attempts at implementing my understanding of man page
> and attempts at implementing what I perceive to be someones mis-writing
> of man page
> 
> 
> ----attempt 1--
> psql -h anna -d GT7 -v v1=12 -c 'select :v1'

I forget the details, but I think it has to do with the way "-c" 
commands are processed. Try this:

echo 'select :v1' | psql -d test -v v1=42
  ?column?
----------
        42
(1 row)

-- 
Joe Conway
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com





^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Command Line option misunderstanding
@ 2024-12-02 20:13  Laurenz Albe <[email protected]>
  parent: [email protected]
  2 siblings, 2 replies; 13+ messages in thread

From: Laurenz Albe @ 2024-12-02 20:13 UTC (permalink / raw)
  To: [email protected]; [email protected]

On Mon, 2024-12-02 at 13:47 -0500, [email protected] wrote:
> psql -h anna -d GT7 -v v1=12 -c 'select :v1'
> 
> autocommit on
> 
> ERROR:  syntax error at or near ":"
> LINE 1: select :v1

The documentation says:

  -c command
  --command=command

    [...]

    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.

Now variables are definitely a psql-specific feature, so variable substitution
won't work. Use a here document!

Yours,
Laurenz Albe





^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Command Line option misunderstanding
@ 2024-12-02 20:36  David G. Johnston <[email protected]>
  parent: [email protected]
  2 siblings, 1 reply; 13+ messages in thread

From: David G. Johnston @ 2024-12-02 20:36 UTC (permalink / raw)
  To: [email protected]; +Cc: [email protected]

On Mon, Dec 2, 2024 at 11:47 AM <[email protected]> wrote:

>
> ----attempt 1--
> psql -h anna -d GT7 -v v1=12 -c 'select :v1'
> ERROR:  syntax error at or near ":"
> LINE 1: select :v1
>
> The variable got assigned correctly but it isn't in scope here due to the
how -c is implemented, as the others noted.


> ----end attempt---
>
> ---attempt 2 ---
> psql -h anna -d GT7 -vv1=1 -c 'select ":v1" '
> ERROR:  column ":v1" does not exist
> LINE 1: select ":v1"
>
You double-quoted the whole thing so it's taken as a column name.  Has no
relevance to the problem at hand.

> ---end attempt ---
>
> --- atttempt 3----
> psql -h anna -d GT7 -vv1=1 -c 'select :v1'
> ERROR:  syntax error at or near ":"
> LINE 1: select :v1
>
Redundant with 1

> ---end attempt
>
> ---attempt 4----
> sql -h anna -d GT7 -v "v1=1"  -c "select :v1"
> ERROR:  syntax error at or near ":"
> LINE 1: select ":v1"
>
Redundant with 1 and 3, putting quotes around the shell option value only
makes explicit what is implicit/optional.  This is also a shell usage
education issue, not psql specific.

> --- end attempt---
>
> ---attempt 5 ---
> psql -h anna -d GT7 -v 'v1=1'  -c 'select ":v1" '
> ERROR:  column ":v1" does not exist
> LINE 1: select ":v1"
>
Redundant with 2

> ----end attempt
>
> --- attempt 6---
> psql -h anna -d GT7 -v :v1=1  -c 'select ":v1" '
> psql: error: invalid variable name: ":v1"
>
Shell command failed since you cannot name a variable with a leading colon,
otherwise the chosen syntax for referencing a variable would be challenging

> ---end attempt
>
> --- attempt 7 ---
> psql -h anna -d GT7 -v "v1"=1  -c 'select ":v1" '
> ERROR:  column ":v1" does not exist
> LINE 1: select ":v1"
>
Another redundant attempt using double-quotes

> --- end attempt
>
> ---attempt 8 ---
> psql -h anna -d GT7 -v 'v1'=1  -c 'select ":v1" '
> ERROR:  column ":v1" does not exist
> LINE 1: select ":v1"
>
And again...

> --- end attempt ---
>
> ---attempt 9 ---
>   psql -h anna -d GT7   -c '\set v1 12; select ":v1" '
>
... Not realizing how -c and meta-commands interplay (or don't in this
case) really led you to try lots of stuff that cannot work but is unrelated
to setting a variable.

> --- end attempt ----
>

Skipping 10-17 as redundant variations on not reading how -c works.


>
> ---attempt 18---
> psql -h anna -d GT7  -v  --set v1=12 -c 'select :v1 '
> Password for user v1=12:
>
You can get odd errors when you don't following the syntax diagrams and
shell command writing rules...

> ---end attempt---
>
>
> ---attempt 19 ---
> kdibble@thinkstation:~/development/gt7-scraper$ psql -h anna -d GT7
> --set v1=12 -c 'select :v1 '
>
> ERROR:  syntax error at or near ":"
> LINE 1: select :v1
>
Back to redundant with 1...

> ---end attempt
>
> --- attempt 20---
> psql -h anna -d GT7   -v --set v1=12 -c 'select :v1 '
> Password for user v1=12:
>
Again, not following syntax rules.

> --end attempt---
>
> ---attempt 21 ---
>   psql -h anna -d GT7   -v v1  --set v1=12 -c 'select :v1 '
>
> ERROR:  syntax error at or near ":"
> LINE 1: select :v1
>
And back to redundant with 1 after figuring out syntax rules for -v/--set

> ---end attempt
>
> ---attempt 22 ---
> psql -h anna -d GT7   -v v1  --set =12 -c 'select :v1 '
> psql: error: invalid variable name: ""
>
And now invalid shell syntax form for the set command.

> ---end attempt---
>
> ---attempt 23---
> psql -h anna -d GT7   -v v1 --set 12 -c 'select :v1 '
>
> ERROR:  syntax error at or near ":"
> LINE 1: select :v1
>
> Redundant with 1 again...

> ---end attempt
>
> ---attempt 24 ---
> psql -h anna -d GT7   -v v1  --set v1=12 -c 'select :v1 '
>
> ERROR:  syntax error at or near ":"
> LINE 1: select :v1
>
And again...

> ---end attempt---
>
> ---attempt 25---
> psql -h anna -d GT7    -v --set v1=12 -c 'select :v1 '
> Password for user v1=12:
>
Random stuff when inventing syntax again

> ----
>
> ---attempt 26 ---
> psql -h anna -d GT7    --set v1=12 -c 'select :v1 '
>
> ERROR:  syntax error at or near ":"
> LINE 1: select :v1
>
Redundant with 1 again...

> ---end attempt
>
> ---attempt 27---
>   psql -h anna -d GT7  -v v1="3" -c "select :v1"
> + psql -h anna -d GT7 -v v1=3 -c 'select :v1'
>
> ERROR:  syntax error at or near ":"
> LINE 1: select :v1
>
Redundant with 1 again...

> ---end attempt
>
> ---attempt 28---
> psql -h anna -d GT7  -v v1='3' -c "select :v1"
> ERROR:  syntax error at or near ":"
> LINE 1: select :v1
>
Redundant with again

> --- end attempt ---
>
> ---attempt 29---
> psql -h anna -d GT7   -c '\set v1 12; select :v1 '
>
Didn't read how -c works

> ---end attempt ---
>
> ---attempt 30 ---
> psql -h anna -d GT7 -v --set v1:=12 -c 'select :v1 '
> Password for user v1:=12:
>
Don't understand shell syntax for -v versus --set (repeat)

> ---end attempt ---
>
> ---attempt 31 ---
> psql -h anna -d GT7  --variable=v1=12 -c 'select :v1 '
> ERROR:  syntax error at or near ":"
> LINE 1: select :v1
>
Redundant with 1 again

> ---end attempt---
>
> ---attempt 32---
> psql -h anna -d GT7  --variable="v1=12" -c 'select :v1 '
> ERROR:  syntax error at or near ":"
> LINE 1: select :v1
>
Redundant with 1 again.

> ---end attempt---
>

If short, you figured out a bunch of ways to write valid and invalid
variable specifications. The invalid ones give you various different
errors.  The correct ones all give you the same error since you cannot use
a variable along with -c, which is documented in -c, not variables, since
they get set just fine.

David J.


^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Command Line option misunderstanding
@ 2024-12-02 21:21  [email protected]
  parent: Laurenz Albe <[email protected]>
  1 sibling, 0 replies; 13+ messages in thread

From: [email protected] @ 2024-12-02 21:21 UTC (permalink / raw)
  To: Laurenz Albe <[email protected]>; [email protected]; [email protected] <[email protected]>


On 12/2/24 15:13, Laurenz Albe wrote:
> On Mon, 2024-12-02 at 13:47 -0500, [email protected] wrote:
>> psql -h anna -d GT7 -v v1=12 -c 'select :v1'
>>
>> autocommit on
>>
>> ERROR:  syntax error at or near ":"
>> LINE 1: select :v1
> The documentation says:
>
>    -c command
>    --command=command
>
>      [...]
>
>      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.
>
> Now variables are definitely a psql-specific feature, so variable substitution
> won't work. Use a here document!
>
> Yours,
> Laurenz Albe


As the beginning of this thread shows, I know how to do this with a HERE 
document, I was just trying to clean it up.

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......

Regards,

Ken






^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Command Line option misunderstanding
@ 2024-12-02 21:32  Ken Dibble <[email protected]>
  parent: David G. Johnston <[email protected]>
  0 siblings, 1 reply; 13+ messages in thread

From: Ken Dibble @ 2024-12-02 21:32 UTC (permalink / raw)
  To: [email protected]


On 12/2/24 15:36, David G. Johnston wrote:
> On Mon, Dec 2, 2024 at 11:47 AM wrote: ----attempt 1-- psql -h anna -d 
> GT7 -v v1=12 -c 'select :v1'ERROR: syntax error at or near ":" LINE 1: 
> select :v1 The variable got
> *DuckDuckGo* did not detect any trackers. More 
> <https://duckduckgo.com/email/report#RFVDSzI.G_sBYJwFdqyxdoaHS2myO1fUIntbMMNtUafXrz9hUwl-PtvNQAVSvHGy...; 
>
>
> Deactivate 
> <https://duckduckgo.com/email/addresses/eyJhY3RpdmUiOmZhbHNlLCJhZGRyZXNzIjoicHVuY2gtaGFzc2xlLWd1aXNlI...; 
>
>
> On Mon, Dec 2, 2024 at 11:47 AM <[email protected]> wrote:
>
>
>     ----attempt 1--
>     psql -h anna -d GT7 -v v1=12 -c 'select :v1'
>     ERROR:  syntax error at or near ":"
>     LINE 1: select :v1
>
> The variable got assigned correctly but it isn't in scope here due to 
> the how -c is implemented, as the others noted.
>
>     ----end attempt---
>
>     ---attempt 2 ---
>     psql -h anna -d GT7 -vv1=1 -c 'select ":v1" '
>     ERROR:  column ":v1" does not exist
>     LINE 1: select ":v1"
>
> You double-quoted the whole thing so it's taken as a column name.  Has 
> no relevance to the problem at hand.
>
>     ---end attempt ---
>
>     --- atttempt 3----
>     psql -h anna -d GT7 -vv1=1 -c 'select :v1'
>     ERROR:  syntax error at or near ":"
>     LINE 1: select :v1
>
> Redundant with 1
>
>     ---end attempt
>
>     ---attempt 4----
>     sql -h anna -d GT7 -v "v1=1"  -c "select :v1"
>     ERROR:  syntax error at or near ":"
>     LINE 1: select ":v1"
>
> Redundant with 1 and 3, putting quotes around the shell option value 
> only makes explicit what is implicit/optional.  This is also a shell 
> usage education issue, not psql specific.
>
>     --- end attempt---
>
>     ---attempt 5 ---
>     psql -h anna -d GT7 -v 'v1=1'  -c 'select ":v1" '
>     ERROR:  column ":v1" does not exist
>     LINE 1: select ":v1"
>
> Redundant with 2
>
>     ----end attempt
>
>     --- attempt 6---
>     psql -h anna -d GT7 -v :v1=1  -c 'select ":v1" '
>     psql: error: invalid variable name: ":v1"
>
> Shell command failed since you cannot name a variable with a leading 
> colon, otherwise the chosen syntax for referencing a variable would be 
> challenging
>
>     ---end attempt
>
>     --- attempt 7 ---
>     psql -h anna -d GT7 -v "v1"=1  -c 'select ":v1" '
>     ERROR:  column ":v1" does not exist
>     LINE 1: select ":v1"
>
> Another redundant attempt using double-quotes
>
>     --- end attempt
>
>     ---attempt 8 ---
>     psql -h anna -d GT7 -v 'v1'=1  -c 'select ":v1" '
>     ERROR:  column ":v1" does not exist
>     LINE 1: select ":v1"
>
> And again...
>
>     --- end attempt ---
>
>     ---attempt 9 ---
>       psql -h anna -d GT7   -c '\set v1 12; select ":v1" '
>
> ... Not realizing how -c and meta-commands interplay (or don't in this 
> case) really led you to try lots of stuff that cannot work but is 
> unrelated to setting a variable.
>
>     --- end attempt ----
>
>
> Skipping 10-17 as redundant variations on not reading how -c works.
>
>
>
>     ---attempt 18---
>     psql -h anna -d GT7  -v  --set v1=12 -c 'select :v1 '
>     Password for user v1=12:
>
> You can get odd errors when you don't following the syntax diagrams 
> and shell command writing rules...
>
>     ---end attempt---
>
>
>     ---attempt 19 ---
>     kdibble@thinkstation:~/development/gt7-scraper$ psql -h anna -d GT7
>     --set v1=12 -c 'select :v1 '
>
>     ERROR:  syntax error at or near ":"
>     LINE 1: select :v1
>
> Back to redundant with 1...
>
>     ---end attempt
>
>     --- attempt 20---
>     psql -h anna -d GT7   -v --set v1=12 -c 'select :v1 '
>     Password for user v1=12:
>
> Again, not following syntax rules.
>
>     --end attempt---
>
>     ---attempt 21 ---
>       psql -h anna -d GT7   -v v1  --set v1=12 -c 'select :v1 '
>
>     ERROR:  syntax error at or near ":"
>     LINE 1: select :v1
>
> And back to redundant with 1 after figuring out syntax rules for -v/--set
>
>     ---end attempt
>
>     ---attempt 22 ---
>     psql -h anna -d GT7   -v v1  --set =12 -c 'select :v1 '
>     psql: error: invalid variable name: ""
>
> And now invalid shell syntax form for the set command.
>
>     ---end attempt---
>
>     ---attempt 23---
>     psql -h anna -d GT7   -v v1 --set 12 -c 'select :v1 '
>
>     ERROR:  syntax error at or near ":"
>     LINE 1: select :v1
>
> Redundant with 1 again...
>
>     ---end attempt
>
>     ---attempt 24 ---
>     psql -h anna -d GT7   -v v1  --set v1=12 -c 'select :v1 '
>
>     ERROR:  syntax error at or near ":"
>     LINE 1: select :v1
>
> And again...
>
>     ---end attempt---
>
>     ---attempt 25---
>     psql -h anna -d GT7    -v --set v1=12 -c 'select :v1 '
>     Password for user v1=12:
>
> Random stuff when inventing syntax again
>
>     ----
>
>     ---attempt 26 ---
>     psql -h anna -d GT7    --set v1=12 -c 'select :v1 '
>
>     ERROR:  syntax error at or near ":"
>     LINE 1: select :v1
>
> Redundant with 1 again...
>
>     ---end attempt
>
>     ---attempt 27---
>       psql -h anna -d GT7  -v v1="3" -c "select :v1"
>     + psql -h anna -d GT7 -v v1=3 -c 'select :v1'
>
>     ERROR:  syntax error at or near ":"
>     LINE 1: select :v1
>
> Redundant with 1 again...
>
>     ---end attempt
>
>     ---attempt 28---
>     psql -h anna -d GT7  -v v1='3' -c "select :v1"
>     ERROR:  syntax error at or near ":"
>     LINE 1: select :v1
>
> Redundant with again
>
>     --- end attempt ---
>
>     ---attempt 29---
>     psql -h anna -d GT7   -c '\set v1 12; select :v1 '
>
> Didn't read how -c works
>
>     ---end attempt ---
>
>     ---attempt 30 ---
>     psql -h anna -d GT7 -v --set v1:=12 -c 'select :v1 '
>     Password for user v1:=12:
>
> Don't understand shell syntax for -v versus --set (repeat)
>
>     ---end attempt ---
>
>     ---attempt 31 ---
>     psql -h anna -d GT7  --variable=v1=12 -c 'select :v1 '
>     ERROR:  syntax error at or near ":"
>     LINE 1: select :v1
>
> Redundant with 1 again
>
>     ---end attempt---
>
>     ---attempt 32---
>     psql -h anna -d GT7  --variable="v1=12" -c 'select :v1 '
>     ERROR:  syntax error at or near ":"
>     LINE 1: select :v1
>
> Redundant with 1 again.
>
>     ---end attempt---
>
>
> If short, you figured out a bunch of ways to write valid and invalid 
> variable specifications. The invalid ones give you various different 
> errors.  The correct ones all give you the same error since you cannot 
> use a variable along with -c, which is documented in -c, not 
> variables, since they get set just fine.
> David J.

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.  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.

In addition, declarations seems to work fine, it is assigning that fails.

If the variable did not exist or were out of scope it should generate an 
error, which it does not.

$ psql -h anna -d GT7 -c '\set v1  12; select :v1 '

autocommit on

$


Variable does not exist here.

$psql -h anna -d GT7 -c 'select :v1 '

autocommit on

ERROR:  syntax error at or near ":"
LINE 1: select :v1

$

One would hope that if the variable were out of scope it would generate 
an error.

Is there any explanation for this?


--man page extract --

-c command
        --command=command
            Specifies that psql is to execute the given command string,
            command. This option can be repeated and combined in any 
order with
            the -f option. When either -c or -f is specified, psql does not
            read commands from standard input; instead it terminates after
            processing all the -c and -f options in sequence.

            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. Thus you cannot mix SQL and psql
            meta-commands within a -c option. To achieve that, you could use
            repeated -c options or pipe the string into psql, for example:

                psql -c '\x' -c 'SELECT * FROM foo;'
    or

                echo '\x \\ SELECT * FROM foo;' | psql

            (\\ is the separator meta-command.)

            Each SQL command string passed to -c is sent to the server as a
            single request. Because of this, the server executes it as a 
single
            transaction even if the string contains multiple SQL commands,
            unless there are explicit BEGIN/COMMIT commands included in the
            string to divide it into multiple transactions. (See
            Section 55.2.2.1 for more details about how the server handles
            multi-query strings.)

            If having several commands executed in one transaction is not
            desired, use repeated -c commands or feed multiple commands to
            psql's standard input, either using echo as illustrated 
above, or
            via a shell here-document, for example:

                psql <<EOF
                \x
                SELECT * FROM foo;
                EOF


---end of extract---



^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Command Line option misunderstanding
@ 2024-12-02 22:10  Laurenz Albe <[email protected]>
  parent: Laurenz Albe <[email protected]>
  1 sibling, 2 replies; 13+ messages in thread

From: Laurenz Albe @ 2024-12-02 22:10 UTC (permalink / raw)
  To: [email protected]; [email protected] <[email protected]>

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





^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Command Line option misunderstanding
@ 2024-12-02 22:11  David G. Johnston <[email protected]>
  parent: Ken Dibble <[email protected]>
  0 siblings, 0 replies; 13+ messages in thread

From: David G. Johnston @ 2024-12-02 22:11 UTC (permalink / raw)
  To: Ken Dibble <[email protected]>; +Cc: [email protected]

--000000000000510929062850d8c4
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Dec 2, 2024 at 2:52=E2=80=AFPM Ken Dibble <[email protected]> wro=
te:

> 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 tha=
t
> could make sense is if declaring a variable is a psql specific feature an=
d
> -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.
>


^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Command Line option misunderstanding
@ 2024-12-02 23:49  [email protected]
  parent: Laurenz Albe <[email protected]>
  1 sibling, 1 reply; 13+ messages in thread

From: [email protected] @ 2024-12-02 23:49 UTC (permalink / raw)
  To: Laurenz Albe <[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






^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Command Line option misunderstanding
@ 2024-12-03 00:11  David G. Johnston <[email protected]>
  parent: [email protected]
  0 siblings, 1 reply; 13+ messages in thread

From: David G. Johnston @ 2024-12-03 00:11 UTC (permalink / raw)
  To: [email protected]; +Cc: Laurenz Albe <[email protected]>; [email protected] <[email protected]>

On Mon, Dec 2, 2024 at 4:50 PM <[email protected]> wrote:

> 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.
>
The word "line" here belies further misunderstanding of how shell-executed
commands work (the following "two line" command is still just one actual
multiple-option command invocation).
psql -v a=1 \
-c 'select :a'

It is best to just say "using -c and -v together".

It is correct that we haven't pointed out, probably because for experienced
people it seems obvious, that using -v and -c (or putting \set in -c) is a
pointless thing to do.  But psql doesn't go about trying to analyze intent
here so, yes, you either get useless successful output in response or a
confused server.

That said...
psql -v a=1 -c '\echo :a' postgres
1

So it truly is just this specific SQL-related usage that is pointless, not
combining -v and -c generally (I'm sure a useful backslash command can be
substituted for \echo)

>
> 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"
>
Really not caring that you are turning on autocommit...

Anyway, what I believe you managed to accomplish here is to set the named
variable "a" to the value <single-quote 11117 single-quote blah-blah-blah
equals colon a>

Then proceeded to do nothing with that variable since the -c command was
done being evaluated in the "single backslash command" mode.

David J.


^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Command Line option misunderstanding
@ 2024-12-03 06:12  Laurenz Albe <[email protected]>
  parent: Laurenz Albe <[email protected]>
  1 sibling, 1 reply; 13+ messages in thread

From: Laurenz Albe @ 2024-12-03 06:12 UTC (permalink / raw)
  To: [email protected]; [email protected] <[email protected]>

On Mon, 2024-12-02 at 18:49 -0500, [email protected] wrote:
> 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?"

"Why" questions tend to be difficult to answer.
The simple answer is: because somebody implemented it that way.

I dug into the commit history, and the current behavior seems to stem from commit
a45195a191 from 1999 that introduced psql variables and has the lapidary commit
message "Major psql overhaul by Peter Eisentraut".
I couldn't find a pertinent discussion on the list.

If you dislike the current behavior enough, you could write a patch that changes
it.  There might be some resistence, however, because a behavoir change like that
could break existing scripts and persent a backward incompatibility.

Yours,
Laurenz Albe





^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Command Line option misunderstanding
@ 2024-12-03 08:56  Ken Dibble <[email protected]>
  parent: David G. Johnston <[email protected]>
  0 siblings, 0 replies; 13+ messages in thread

From: Ken Dibble @ 2024-12-03 08:56 UTC (permalink / raw)
  To: [email protected]


On 12/2/24 19:11, David G. Johnston wrote:
> On Mon, Dec 2, 2024 at 4:50 PM wrote: 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 a
> *DuckDuckGo* did not detect any trackers. More 
> <https://duckduckgo.com/email/report#RFVDSzI.G_sBYJwFdqyxdoaHS2myO1fUIntbMMNtUafXrz9hUwl-PtvNQAVSvHGy...; 
>
>
> Deactivate 
> <https://duckduckgo.com/email/addresses/eyJhY3RpdmUiOmZhbHNlLCJhZGRyZXNzIjoicHVuY2gtaGFzc2xlLWd1aXNlI...; 
>
>
> On Mon, Dec 2, 2024 at 4:50 PM <[email protected]> wrote:
>
>     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.
>
> The word "line" here belies further misunderstanding of how 
> shell-executed commands work (the following "two line" command is 
> still just one actual multiple-option command invocation).
> psql -v a=1 \
> -c 'select :a'
>
> It is best to just say "using -c and -v together".
>
> It is correct that we haven't pointed out, probably because for 
> experienced people it seems obvious, that using -v and -c (or putting 
> \set in -c) is a pointless thing to do.  But psql doesn't go about 
> trying to analyze intent here so, yes, you either get useless 
> successful output in response or a confused server.
>
> That said...
> psql -v a=1 -c '\echo :a' postgres
> 1
>
> So it truly is just this specific SQL-related usage that is pointless, 
> not combining -v and -c generally (I'm sure a useful backslash command 
> can be substituted for \echo)
>
>
>     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"
>
> Really not caring that you are turning on autocommit...
>
> Anyway, what I believe you managed to accomplish here is to set the 
> named variable "a" to the value <single-quote 11117 single-quote 
> blah-blah-blah equals colon a>
>
> Then proceeded to do nothing with that variable since the -c command 
> was done being evaluated in the "single backslash command" mode.
>
> David J.
>
Thanks for your time and explanations.

Ken


^ permalink  raw  reply  [nested|flat] 13+ messages in thread

* Re: Command Line option misunderstanding
@ 2024-12-03 08:57  Ken Dibble <[email protected]>
  parent: Laurenz Albe <[email protected]>
  0 siblings, 0 replies; 13+ messages in thread

From: Ken Dibble @ 2024-12-03 08:57 UTC (permalink / raw)
  To: [email protected]


On 12/3/24 01:12, Laurenz Albe wrote:
> On Mon, 2024-12-02 at 18:49 -0500, [email protected] wrote:
>> 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?"
> "Why" questions tend to be difficult to answer.
> The simple answer is: because somebody implemented it that way.
>
> I dug into the commit history, and the current behavior seems to stem from commit
> a45195a191 from 1999 that introduced psql variables and has the lapidary commit
> message "Major psql overhaul by Peter Eisentraut".
> I couldn't find a pertinent discussion on the list.
>
> If you dislike the current behavior enough, you could write a patch that changes
> it.  There might be some resistence, however, because a behavoir change like that
> could break existing scripts and persent a backward incompatibility.
>
> Yours,
> Laurenz Albe
>
Thanks for your time and explanations.

Ken







^ permalink  raw  reply  [nested|flat] 13+ messages in thread

end of thread, other threads:[~2024-12-03 08:57 UTC | newest]

Thread overview: 13+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-02 18:47 Command Line option misunderstanding [email protected]
2024-12-02 20:12 ` Joe Conway <[email protected]>
2024-12-02 20:13 ` Laurenz Albe <[email protected]>
2024-12-02 21:21   ` [email protected]
2024-12-02 22:10   ` Laurenz Albe <[email protected]>
2024-12-02 23:49     ` [email protected]
2024-12-03 00:11       ` David G. Johnston <[email protected]>
2024-12-03 08:56         ` Ken Dibble <[email protected]>
2024-12-03 06:12     ` Laurenz Albe <[email protected]>
2024-12-03 08:57       ` Ken Dibble <[email protected]>
2024-12-02 20:36 ` David G. Johnston <[email protected]>
2024-12-02 21:32   ` Ken Dibble <[email protected]>
2024-12-02 22:11     ` David G. Johnston <[email protected]>

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