Adding flexibilty to queries

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