syntax error at or near "$" in parameter aggregation in named queries

Question

syntax error at or near "$" in parameter aggregation in named queries

dtluna opened this issue a year ago · 2 comments

Description

I am trying to create named queries with parameter aggregation, but I get syntax error at or near "$" errors. I tested with the queries from the manual, but they also failed:

user@localhost:db> \ns users_by_age select * from users where id in ($*)
Saved.
Time: 0.001s
user@localhost:db> \n users_by_age 42
syntax error at or near "$"
LINE 1: select * from users where id in ($*)

Your environment

OS: Ubuntu 22.04
CLI version: 3.3.1
What is the output of pip freeze

appdirs==1.4.4
attrs==21.2.0
Automat==20.2.0
Babel==2.8.0
backcall==0.2.0
bcrypt==3.2.0
beautifulsoup4==4.10.0
beniget==0.4.1
black==23.7.0
blinker==1.4
Brotli==1.0.9
build==0.10.0
CacheControl==0.13.1
certifi==2020.6.20
cfgv==3.3.1
chardet==4.0.0
charset-normalizer==3.2.0
cleo==2.0.1
cli-helpers==2.2.1
click==8.0.3
cloud-init==23.2.2
colorama==0.4.4
command-not-found==0.3
configobj==5.0.6
constantly==15.1.0
crashtest==0.4.1
cryptography==3.4.8
cycler==0.11.0
dbus-python==1.2.18
decorator==4.4.2
distlib==0.3.7
distro==1.7.0
distro-info==1.1+ubuntu0.1
dulwich==0.21.6
filelock==3.12.3
fonttools==4.29.1
fs==2.4.12
gast==0.5.2
greenlet==1.1.2
html5lib==1.1
httplib2==0.20.2
hyperlink==21.0.0
identify==2.4.10
idna==3.3
importlib-metadata==6.8.0
incremental==21.3.0
installer==0.7.0
ipython==7.31.1
jaraco.classes==3.3.0
jedi==0.18.0
jeepney==0.7.1
Jinja2==3.0.3
jsonpatch==1.32
jsonpointer==2.0
jsonschema==4.17.3
keyring==24.2.0
kiwisolver==1.3.2
launchpadlib==1.10.16
lazr.restfulclient==0.14.4
lazr.uri==1.0.6
lxml==4.8.0
lz4==3.1.3+dfsg
MarkupSafe==2.0.1
matplotlib==3.5.1
matplotlib-inline==0.1.3
more-itertools==8.10.0
mpmath==0.0.0
msgpack==1.0.3
mypy-extensions==1.0.0
netifaces==0.11.0
nodeenv==0.13.4
numpy==1.21.5
oauthlib==3.2.0
olefile==0.46
packaging==23.1
parso==0.8.1
pathspec==0.11.2
pendulum==2.1.2
pexpect==4.8.0
pgcli==3.3.1
pgspecial==1.11.10
pickleshare==0.7.5
Pillow==9.0.1
pkginfo==1.9.6
platformdirs==3.10.0
ply==3.11
poetry==1.6.1
poetry-core==1.7.0
poetry-plugin-export==1.5.0
pre-commit==2.17.0
prompt-toolkit==3.0.28
psycopg2==2.9.6
ptyprocess==0.7.0
pyasn1==0.4.8
pyasn1-modules==0.2.1
Pygments==2.11.2
PyGObject==3.42.1
PyHamcrest==2.0.2
PyJWT==2.3.0
pymacaroons==0.13.0
PyNaCl==1.5.0
pynvim==0.4.2
pyOpenSSL==21.0.0
pyparsing==2.4.7
pyproject_hooks==1.0.0
pyrsistent==0.18.1
pyserial==3.5
python-apt==2.4.0+ubuntu2
python-dateutil==2.8.1
python-debian==0.1.43+ubuntu1.1
python-magic==0.4.24
pythran==0.10.0
pytz==2022.1
pytzdata==2020.1
PyYAML==5.4.1
rapidfuzz==2.15.1
requests==2.31.0
scipy==1.8.0
SecretStorage==3.3.1
service-identity==18.1.0
setproctitle==1.2.2
shellingham==1.5.3
six==1.16.0
sos==4.5.6
soupsieve==2.3.1
sqlparse==0.4.2
ssh-import-id==5.11
sympy==1.9
systemd-python==234
tabulate==0.8.9
terminaltables==3.1.0
toml==0.10.2
tomli==2.0.1
tomlkit==0.12.1
traitlets==5.1.1
trove-classifiers==2023.8.7
Twisted==22.1.0
typing_extensions==4.7.1
ubuntu-advantage-tools==8001
ufoLib2==0.13.1
ufw==0.36.1
unattended-upgrades==0.1
unicodedata2==14.0.0
urllib3==1.26.5
virtualenv==20.24.4
wadllib==1.3.6
wcwidth==0.2.5
webencodings==0.5.1
zipp==1.0.0
zope.interface==5.4.0

Answer 1 · 2023-09-27T07:06:42.000Z

You seem to use version 3.3.1. Could you try updating to the latest version (3.5.0)? I cannot reproduce this issue with that latest version:

pgcli -h localhost -U pgcli-dev -W pgcli-dev
Password for pgcli-dev: 
Server: PostgreSQL 15.4
Version: 3.5.0
Home: http://pgcli.com

pgcli-dev> create table users (id int)
CREATE TABLE
pgcli-dev> insert into users (id) values (1337);
INSERT 0 1
pgcli-dev> insert into users (id) values (42);
INSERT 0 1
pgcli-dev> insert into users (id) values (1);
INSERT 0 1
pgcli-dev> \ns users_by_age select * from users where id in ($*)
Saved.
pgcli-dev> \n users_by_age 42
> select * from users where id in ($*)
+----+
| id |
|----|
| 42 |
+----+
SELECT 1

(By the way, I opened dbcli/pgcli.com#58 to fix the typo in the users_by_age named query. The name subtly suggests that it should filter on anage column, not the id. ;) That documentation fix is irrelevant to this issue, though.)