Thread: 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

What's the correct syntax for the \prompt?

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