Re: Adding flexibilty to queries

--- Bill Moran <wmoran(at)potentialtech(dot)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(at)postgresql(dot)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