Postgres Question

[Lennart Sorensen]

On Wed, Jun 29, 2005 at 12:07:55PM -0400, Lance F. Squire wrote:
> I need to search on the $ value of something in a table where multiple 
> currencies are allowed, and the currency conversion rates are in another 
> table.
> 
> For instance, on EBay, things can be listed in US, CDN or GBP etc...
> 
> Assuming the table has these fields:
> 
> Value | Currency
> -----------------
> 10.00 | US
> 25.00 | CDN
> 30.00 | GBP
> 
> and the conversion table had these fields:
> 
> Currency | Conversion
> ----------------------
> US       | 1
> CDN      | .75
> GBP      | .55
> 
> (Yes, values ARE guessed at here. ;) )
> 
> What would be the sainest way to search for anything less than $x CDN?
> 
> Would implimenting a View, or any other DB feature make this more efficient?
> 
> I've only done simple and joined quieries so far. How to attack this 
> escapes me so far...

You could write a postgresql PL function (or whatever they call the
function language) so that you can do soemthing like:

select * from tablename where dollarvalue(value,currency) < 10.0);

So dollarvalue would be a function that takes the value and currency,
then looks up the conversion in the currency table and multiplies it
out.  Should make it much easier then to do regular queries.

Lennart Sorensen
--
The Toronto Linux Users Group.      Meetings: http://tlug.ss.org
TLUG requests: Linux topics, No HTML, wrap text below 80 columns
How to UNSUBSCRIBE: http://tlug.ss.org/subscribe.shtml