Using psql's \prompt command
I want to use the \prompt command to get user input for a query. My script
fails:
-- Display person_nbr, lname, fname, direct_phone, email from people, contact history from contacts.
-- prompt for person_nbr before selecting rows:
\prompt 'Enter person_nbr: ' store
select p.person_nbr, p.lname, p.fname, p.direct_phone, p.email, c.contact_date, c.contact_time, c.contact_type, c.notes
from people as p natural inner join contacts as c
where person_nbr = store
order by c.contact_date, c.contact_time;
# \i person_view.sql
Enter person_nbr: 468
psql:person_view.sql:9: ERROR: column "store" does not exist
LINE 3: where person_nbr = store
^
What's the correct syntax for the \prompt?
TIA,
Rich
On Thursday, January 30, 2025, Rich Shepard <rshepard@appl-ecosys.com>
wrote:
What's the correct syntax for the \prompt?
Prompt isn’t your issue. Prompt stores the value into a variable. Read
how to reference variables in a psql script.
David J.
On Thu, 30 Jan 2025, David G. Johnston wrote:
Prompt isn’t your issue. Prompt stores the value into a variable. Read how
to reference variables in a psql script.
David,
Thank you. I'll look into using the \set command.
Rich
On Thu, 30 Jan 2025, Rich Shepard wrote:
Thank you. I'll look into using the \set command.
My web searches find many examples of using the \set command, but none
getting user input with \prompt.
Please point me to a reference where I can learn how to get the user input
string into the script.
Regards,
Rich
On Thu, Jan 30, 2025 at 5:28 PM Rich Shepard <rshepard@appl-ecosys.com>
wrote:
On Thu, 30 Jan 2025, David G. Johnston wrote:
Prompt isn’t your issue. Prompt stores the value into a variable. Read
how
to reference variables in a psql script.
David,
Thank you. I'll look into using the \set command.
Instead of \set (which can be tricky and unintuitive), consider the dialog
command with "psql --set=NAME=VALUE".
https://linux.die.net/man/1/dialog
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
On Thu, Jan 30, 2025 at 3:38 PM Rich Shepard <rshepard@appl-ecosys.com>
wrote:
On Thu, 30 Jan 2025, Rich Shepard wrote:
Thank you. I'll look into using the \set command.
My web searches find many examples of using the \set command, but none
getting user input with \prompt.Please point me to a reference where I can learn how to get the user input
string into the script.
That is what \prompt is for. You have the correct meta-command, you were
capturing user input just fine. Read about how to use variables in queries
for the part you are missing.
David J.
On Thu, Jan 30, 2025 at 3:44 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
On Thu, Jan 30, 2025 at 3:38 PM Rich Shepard <rshepard@appl-ecosys.com>
wrote:On Thu, 30 Jan 2025, Rich Shepard wrote:
Thank you. I'll look into using the \set command.
My web searches find many examples of using the \set command, but none
getting user input with \prompt.Please point me to a reference where I can learn how to get the user input
string into the script.That is what \prompt is for. You have the correct meta-command, you were
capturing user input just fine. Read about how to use variables in queries
for the part you are missing.
Specifically the section of the psql docs titled:
SQL Interpolation
David J.
On Thu, Jan 30, 2025 at 3:46 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
On Thu, Jan 30, 2025 at 3:44 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:On Thu, Jan 30, 2025 at 3:38 PM Rich Shepard <rshepard@appl-ecosys.com>
wrote:On Thu, 30 Jan 2025, Rich Shepard wrote:
Thank you. I'll look into using the \set command.
My web searches find many examples of using the \set command, but none
getting user input with \prompt.Please point me to a reference where I can learn how to get the user
input
string into the script.That is what \prompt is for. You have the correct meta-command, you were
capturing user input just fine. Read about how to use variables in queries
for the part you are missing.Specifically the section of the psql docs titled:
SQL Interpolation
Or:
Advanced Features
- Variables
David J.
On Thu, 30 Jan 2025, David G. Johnston wrote:
That is what \prompt is for. You have the correct meta-command, you were
capturing user input just fine. Read about how to use variables in
queries for the part you are missing.Specifically the section of the psql docs titled:
SQL Interpolation
Thanks again, David. I didn't know that's the section I need.
Much appreciated,
Rich
On Thu, 30 Jan 2025, Rich Shepard wrote:
Thanks again, David. I didn't know that's the section I need.
David/Ron, et al.:
Got it (example on page 2126 of the doc):
-- Display person_nbr, lname, fname, direct_phone, email from people, contact history from contacts.
-- prompt for person_nbr before selecting rows:
\prompt 'Enter person_nbr: ' who
select p.person_nbr, p.lname, p.fname, p.direct_phone, p.email, c.contact_date, c.contact_time, c.contact_type, c.notes
from people as p natural inner join contacts as c
where p.person_nbr = (:'who')
order by c.contact_date, c.contact_time;
This works.
Thank you,
Rich
On Thu, Jan 30, 2025 at 01:47:59PM -0800, Rich Shepard wrote:
# \i person_view.sql
Enter person_nbr: 468
psql:person_view.sql:9: ERROR: column "store" does not exist
LINE 3: where person_nbr = store
^
What's the correct syntax for the \prompt?
prompt is ok.
but you can't just throw variable name in the query and assume it will
get substituted.
the proper syntax is :store, or (better) :'store'
and again: not in the *\prompt* line - in the place where you use it.
You might want to read
https://www.depesz.com/2023/05/28/variables-in-psql-how-to-use-them/
Best regards,
depesz