Adding flexibilty to queries
Adding flexibilty to queries
От:
Alan Carbutt <arcarbut@adams.edu>
Дата:
Hi all, I have a question regarding psql. As I am more familiar with oracle I can write the following select and make it rather flexible: select name from name_table where person_id = &id; The &id portion is what I am looking for in PostgreSQL. Basically when running this query, the person running the query is prompted to supply a value. What is the equivalent in postgres? TIA, -- Alan Carbutt Systems Administrator/Programmer Adams State College 719-587-7741 arcarbut@adams.edu
Re: Adding flexibilty to queries
От:
Alan Carbutt <arcarbut@adams.edu>
Дата:
Thanks, Bill. I'll give it a try. ++alan On Thu, 2004-03-25 at 06:01, Bill Moran wrote: > Alan Carbutt wrote: > > Hi all, > > > > I have a question regarding psql. As I am more familiar with oracle I > > can write the following select and make it rather flexible: > > select name > > from name_table > > where person_id = &id; > > The &id portion is what I am looking for in PostgreSQL. Basically when > > running this query, the person running the query is prompted to supply a > > value. What is the equivalent in postgres? > > Doesn't seem like anyone else has answered this ... > > I think you can accomplish what you want with stored functions. For > example: > > create function get_name(INT) > returns > as ' > select name from name_table where person_id = $1; > ' language sql; > > The user can then call this by: > select get_name(); > > Don't know if that's what you're looking for or not ... -- Alan Carbutt Systems Administrator/Programmer Adams State College 719-587-7741 arcarbut@adams.edu
Re: Adding flexibilty to queries
От:
James Thompson <jamest@math.ksu.edu>
Дата:
You can make it interactive if you are working from a unix box. jamest@hobbes:~$ more zip.sql \echo -n 'Zip to search for: ' \set value `read input; echo $input` select * from zipcode where zip = :value Not sure how/if you could do this on a windows box. Take Care, James On Thursday 25 March 2004 08:45 am, you wrote: > > > I have a question regarding psql. As I am more familiar with oracle I > > > can write the following select and make it rather flexible: > > > select name > > > from name_table > > > where person_id = &id; > > > The &id portion is what I am looking for in PostgreSQL. Basically when > > > running this query, the person running the query is prompted to supply > > > a value. What is the equivalent in postgres? > > It doesn't work interactively, but you can assign variables like this: > > select name from name_table where person_id = :id; > > I have to admit there are some sqlplus features I miss too (like the break > and compute features), and maybe one of these days I'll miss them enough > to add them to psql. :-) > -- > Mike Nolan > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-< James Thompson 138 Cardwell Hall Manhattan, Ks 66506 785-532-0561 Kansas State University Department of Mathematics ->->->->->->->->->->->->->->->->->->---<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<-<
Re: Adding flexibilty to queries
От:
Mike Nolan <nolan@gw.tssi.com>
Дата:
> > I have a question regarding psql. As I am more familiar with oracle I
> > can write the following select and make it rather flexible:
> > select name
> > from name_table
> > where person_id = &id;
> > The &id portion is what I am looking for in PostgreSQL. Basically when
> > running this query, the person running the query is prompted to supply a
> > value. What is the equivalent in postgres?
It doesn't work interactively, but you can assign variables like this:
select name from name_table where person_id = :id;
I have to admit there are some sqlplus features I miss too (like the break
and compute features), and maybe one of these days I'll miss them enough
to add them to psql. :-)
--
Mike Nolan
Re: Adding flexibilty to queries
От:
Jeff Eckermann <jeff_eckermann@yahoo.com>
Дата:
--- Bill Moran wrote: > Alan Carbutt wrote: > > Hi all, > > > > I have a question regarding psql. As I am more > familiar with oracle I > > can write the following select and make it rather > flexible: > > select name > > from name_table > > where person_id = &id; > > The &id portion is what I am looking for in > PostgreSQL. Basically when > > running this query, the person running the query > is prompted to supply a > > value. What is the equivalent in postgres? > > Doesn't seem like anyone else has answered this ... > > I think you can accomplish what you want with stored > functions. For > example: > > create function get_name(INT) > returns > as ' > select name from name_table where person_id = $1; > ' language sql; > > The user can then call this by: > select get_name(); > > Don't know if that's what you're looking for or not > ... Using psql it is possible to include variables in statements, which are identified by a colon prefix: ":variablename". The value can be set from the command line using "\set" (note that this is quite distinct from the SQL "SET" command). But in either case, AFAIK the value has to be supplied in advance, i.e. you will not be prompted for a value. > > -- > Bill Moran > Potential Technologies > http://www.potentialtech.com > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to > majordomo@postgresql.org so that your > message can get through to the mailing list cleanly __________________________________ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html
Re: Adding flexibilty to queries
От:
Bill Moran <wmoran@potentialtech.com>
Дата:
Alan Carbutt wrote: > Hi all, > > I have a question regarding psql. As I am more familiar with oracle I > can write the following select and make it rather flexible: > select name > from name_table > where person_id = &id; > The &id portion is what I am looking for in PostgreSQL. Basically when > running this query, the person running the query is prompted to supply a > value. What is the equivalent in postgres? Doesn't seem like anyone else has answered this ... I think you can accomplish what you want with stored functions. For example: create function get_name(INT) returns as ' select name from name_table where person_id = $1; ' language sql; The user can then call this by: select get_name(); Don't know if that's what you're looking for or not ... -- Bill Moran Potential Technologies http://www.potentialtech.com
