Thread: Adding flexibilty to queries

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

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 <type of name column>
as '
  select name from name_table where person_id = $1;
' language sql;

The user can then call this by:
select get_name(<name id>);

Don't know if that's what you're looking for or not ...

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

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 <type of name column>
> as '
>   select name from name_table where person_id = $1;
> ' language sql;
>
> The user can then call this by:
> select get_name(<name id>);
>
> 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

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

--- Bill Moran <wmoran@potentialtech.com> 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 <type of name column>
> as '
>   select name from name_table where person_id = $1;
> ' language sql;
>
> The user can then call this by:
> select get_name(<name id>);
>
> 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

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