Retreive User Input

[Nlaivlys]

Is there a way to retrieve user input for postgresql? I have done a search online and found that one can use:
select * from mytable where user_id = [parameter "Enter text question here"]
--or--
select * from mytable where user_id ~* '[parameter "Enter text question here"]'

However, when I type these on a command line prompt, I get errors. The above lines are mentioned when using a gui tool (like pgaccess). I cannot seem to get these to work from the command line.

Example:
WEBWX=# select * from package where package_id=1;
This does produce an output.

If I try:
WEBWX=# select * from package where package_id=[parameter "Enter package_id"]
WEBWX-# 1;
ERROR: syntax error at or near "[" at character 40
WEBWX=# select * from package where package_id ~* '[parameter "Enter id"]'
WEBWX-# 1;
ERROR: syntax error at or near "1" at character 68
WEBWX=#

I do notice that the prompt changes from =# to -#, but cannot figure out how to get it to work. Are these commands not available from the command line (and only available in the gui tools)?

Thank you so much for everyone's help!

 

[ceco]

these queries are something from the GUI, and are not suposed to be used like that

actually where do you expect these parameters to come from to the command line??

just type the value you are trying to search like

select * from package where package_id='the value you search here';

and it will work

for more advanced/complicated and faster searches of this type try using something like tsearch2 (which is in the contrib package for the newer versions of postgresql, and tsearch for the older)

 

[Nlaivlys]

Eventually, I would create a script to read values from a user into the postgresql db. In that script, it prompts the user to enter values needed. I just wanted to test this from the command line.

 

[ceco]

well, test it as I showed

select * from package where package_id='the value you search here';

and when you do it in script you just construct the full correct query (of the type I showed) and pass it to the DB

 

[Nlaivlys]

So - there is no way to have a continuous prompt for user input and then a special terminating character within a script?

 

[ceco]

what is your script going to be written in?

let's say perl, then this is perl problem and has nothing to do with postgresql

the perl script is waiting for user actions and constructs the queries as apropriate for the postgresql (through DBI function calls), and then displays to the user in a proper maner the results from the database

the psql program is for the developer not for the end user!