Subject: Re: Using psql's \prompt command

From: "David G. Johnston" <[email-masked]>
Date: Thu, 30 Jan 2025 14:50:06 -0700

On Thursday, January 30, 2025, Rich Shepard <[email-masked]>
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.

---

From: Rich Shepard <[email-masked]>
Date: Thu, 30 Jan 2025 14:28:11 -0800 (PST)

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

---

From: Rich Shepard <[email-masked]>
Date: Thu, 30 Jan 2025 14:38:34 -0800 (PST)

> 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

---

From: Ron Johnson <[email-masked]>
Date: Thu, 30 Jan 2025 17:40:56 -0500

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

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 30 Jan 2025 15:44:20 -0700

> Please point me to a reference where I can learn how to get the user input

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.

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 30 Jan 2025 15:46:26 -0700

> for the part you are missing.

Specifically the section of the psql docs titled:

SQL Interpolation

David J.

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 30 Jan 2025 15:47:45 -0700

> Specifically the section of the psql docs titled:
Or:

Advanced Features
- Variables

David J.

---

From: Rich Shepard <[email-masked]>
Date: Thu, 30 Jan 2025 15:00:44 -0800 (PST)

On Thu, 30 Jan 2025, David G. Johnston wrote:

>> capturing user input just fine. Read about how to use variables in
>> queries for the part you are missing.
>>
>

Thanks again, David. I didn't know that's the section I need.

Much appreciated,

Rich

---

From: Rich Shepard <[email-masked]>
Date: Thu, 30 Jan 2025 15:35:04 -0800 (PST)

> 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