Обсуждение: User input to queries

While I develop the application's GUI I use the database from the command
line (psql). While some queries are self-contained others need user input.
I've not found a search term that locates this information in the
postgres-12 user manual and my web searches suggest that using '&' should
work.

I tried the latter with this query but postgres didn't like it. (There may
well be other errors in it so don't hesitate to point out my mistakes.)

------
/* This query selects all activity information for a named person */

/* Specify columns */
SELECT p.lname, p.fname, p.loc_nbr, p.job_title, p.direct_phone, p.active,
        o.org_name,
        l.loc_nbr, l.loc_name,
        a.act_date, a.act_type, a.notes, a.next_contact
/* Specify tables. */ 
FROM People AS p
      JOIN Organizations AS o ON o.org_nbr = p.org_nbr
      JOIN Locations AS l ON l.org_nbr = o.org_nbr and l.loc_nbr = p.loc_nbr
      JOIN Activities AS a ON a.person_nbr = p.person_nbr
/* Specify rows */ 
WHERE p.lname = &p.lname AND p.fname = &p.fname;
-----

Suggestions on what I should read to learn more about this subject are
appreciated.

Regards,

Rich

On Wed, 9 Dec 2020, Michael Lewis wrote:

> Are you looking for this perhaps?
> https://www.postgresql.org/docs/current/sql-prepare.html

Michael,

I don't think so. Reading the PREPARE doc page my understanding is that its
use is for statement execution optimization, not asking for user data input
for variables in the query statement. It's useful information, though not
for my immediate need.

Thanks and stay well,

Rich

On 12/9/20 10:03 AM, Rich Shepard wrote:

While I develop the application's GUI I use the database from the command
line (psql). While some queries are self-contained others need user input.
I've not found a search term that locates this information in the
postgres-12 user manual and my web searches suggest that using '&' should
work.

I tried the latter with this query but postgres didn't like it. (There may
well be other errors in it so don't hesitate to point out my mistakes.)

------
/* This query selects all activity information for a named person */

/* Specify columns */
SELECT p.lname, p.fname, p.loc_nbr, p.job_title, p.direct_phone, p.active,
       o.org_name,
       l.loc_nbr, l.loc_name,
       a.act_date, a.act_type, a.notes, a.next_contact
/* Specify tables. */ FROM People AS p
     JOIN Organizations AS o ON o.org_nbr = p.org_nbr
     JOIN Locations AS l ON l.org_nbr = o.org_nbr and l.loc_nbr = p.loc_nbr
     JOIN Activities AS a ON a.person_nbr = p.person_nbr
/* Specify rows */ WHERE p.lname = &p.lname AND p.fname = &p.fname;
-----

Suggestions on what I should read to learn more about this subject are
appreciated.

Regards,

Rich

Put the query in a file, set the desired name, then from psql

\i filename

Edit pfname, repeat

On Wed, 9 Dec 2020, Michael Lewis wrote:

> What application is taking the user input and needs to include the
> parameters in the query string?

Michael,

My business tracking tool. Yes, the GUI will have text entry widgets for
user input but I want to apply these queries using psql on the command line
until I build the GUI.

Rich

On Wed, 9 Dec 2020, Laurenz Albe wrote:

> You probably need the \prompt psql command:
> \prompt 'What is "p.lname"' p_lname
> \prompt 'What is "p.fname"' p_fname
> SELECT ... WHERE p.lname = :p_lname AND p.fname = :p_fname;

Laurenz,

Ah! I have not before encountered that command. Yes, this will do the job
and I'll make it a point to look at _all_ available psql commands.

Thanks very much.

Stay well,

Rich

On Wed, 9 Dec 2020, Rob Sargent wrote:

> Put the query in a file, set the desired name, then from psql
>   \i filename
> Edit pfname, repeat

Thanks, Rob.

Stay well,

Rich

Hi Rich,

> On 09. Dec, 2020, at 18:53, Rich Shepard <rshepard@appl-ecosys.com> wrote:
> 
> My business tracking tool. Yes, the GUI will have text entry widgets for
> user input but I want to apply these queries using psql on the command line
> until I build the GUI.

maybe you're looking for this?

https://stackoverflow.com/a/7389606

Cheers,
Paul

On Wed, 9 Dec 2020, Paul Förster wrote:

> maybe you're looking for this?
> https://stackoverflow.com/a/7389606

Paul,

That looks very useful and I'll try the provided answers.

Looking at the postgres-12 doc I cannot find a command 'PROMPT' anywhere in
it.

Thanks,

Rich

On 12/9/20 11:10 AM, Rich Shepard wrote:
> On Wed, 9 Dec 2020, Paul Förster wrote:
>
>> maybe you're looking for this?
>> https://stackoverflow.com/a/7389606
>
> Paul,
>
> That looks very useful and I'll try the provided answers.
>
> Looking at the postgres-12 doc I cannot find a command 'PROMPT' 
> anywhere in
> it.
>
> Thanks,
>
> Rich
>
>
https://www.postgresql.org/docs/12/app-psql.html

Hi Rich,

> On 09. Dec, 2020, at 19:10, Rich Shepard <rshepard@appl-ecosys.com> wrote:
>
> Looking at the postgres-12 doc I cannot find a command 'PROMPT' anywhere in
> it.

but 12 has it:

postgres=# \prompt 'input: ' input
input: this is test input
postgres=# select version(), :'input';
                                      version                                       |      ?column?
------------------------------------------------------------------------------------+--------------------
 PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (SUSE Linux) 7.5.0, 64-bit | this is test input
(1 row)

postgres=# \?
...
Variables
  \prompt [TEXT] NAME    prompt user to set internal variable
  \set [NAME [VALUE]]    set internal variable, or list all if no parameters
  \unset NAME            unset (delete) internal variable
...

Cheers,
Paul

On Wed, 9 Dec 2020, Paul Förster wrote:

> but 12 has it:
>
> postgres=# \prompt 'input: ' input
> input: this is test input
> postgres=# select version(), :'input';

Paul,

Okay. I use mupdf to view the document and my search string were 'prompt',
and 'prompt command'. I didn't use '\prompt',

Thanks again,

Rich

Hi Rich,

> On 09. Dec, 2020, at 19:22, Rich Shepard <rshepard@appl-ecosys.com> wrote:
>
> Okay. I use mupdf to view the document and my search string were 'prompt',
> and 'prompt command'. I didn't use '\prompt',

\prompt is a psql special command, hence the backslash. Only psql knows that, the database does not, as with all
backslashcommands. 

Cheers,
Paul