Skip site navigation (1) Skip section navigation (2)

Re: Using regoper type with OPERATOR()

From: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
To: Tony Theodore <tony(dot)theodore(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Using regoper type with OPERATOR()
Date: 2011-10-09 21:03:52
Message-ID: 4E920C38.9090306@archidevsys.co.nz (view raw or flat)
Thread:
Lists: pgsql-novice
On 08/10/11 19:21, Tony Theodore wrote:
> On 7 October 2011 06:33, Gavin Flower<GavinFlower(at)archidevsys(dot)co(dot)nz>  wrote:
>> Glad to be of help!
>>
>> There is often a tradeoff between flexibility and performance.
>>
>> What you tried to do looks pretty neat.
>>
>> Would writing something in C give you sufficient flexibility with reasonable
>> performance?
> Possibly, but I wouldn't know where to start. I just did some more
> testing, and the most performant solution is to just have both columns
> (fraction and amount) default them to 1 and 0 respectively, then just
> calculate (price * fraction + amount).
I think this is a better aproach it provides greater flexibility and 
eliminates the case statement - so I suspect it will be slightly faster.
>
>> However, in a production system, and in an environment where most people do
>> not have a range of skills in depth, it is better to keep things simple - to
>> ease ongoing maintenance.  Sometimes super smart code is a liability, as
>> mere mortals can not maintain it.  I have been guilty of this crime!
>>
>> I guess a good rule of thumb, is imagine that you are called back in 2 years
>> to fix, or modify your code - how would you feel: still proud of what you
>> did, or wonder what you were thinking at the time (or both!)?
>>
>> Somes a bit of complexity is necessar, and can save a lot of code, or imply
>> be the most practical way of doing something.
> I was trying to build some flexibility in so that I wouldn't need to
> revisit this in the future :) Down the track, I'll investigate
> operator/function lookups further, but I'll keep it simple for the
> time being.
>
> BTW, is novice the right list for questions like these?
>

I think so, but the pgsql-sql list would probably not be appropriate as 
you are not asking a trivial question - on balance, I feel this list is 
best. IMHO  How is that for a definitive answer!  :-)

I first started useing databases about 20 years ago, and came across pg 
about 10 years ago. I find reading the pg mailing lists very useful for 
learning new things.  Sometimes I solve problems better than others, but 
I also often find other people's answers provide more practically 
elegant ways of doing things than I could have come up with.  Other 
times I've found my understanding not as as good as I had thought - like 
not appreciating the need to use timestamps with timezone (timestamptz 
is a pg short form).  Having lots of experience is great, but things 
keep changing and it is important not to get complacent!

>> Note that one of the points I was trying to make is to avoid float type data
>> types for money. In COBOL we used integers to hold the number of cents, so
>> add&subtract operations were not subject to rounding, in pg you can use the
>> money type.
> Thanks for the tip, this is mostly an analysis database, so rounding
> won't be an issue.
>
> Cheers,
>
> Tony
You're okay then.  Float is probably slightly nore efficient than the 
money type.  Though the money type is probably better from the semantic 
point of view.


Cheers,
Gavin

In response to

Responses

pgsql-novice by date

Next:From: Gavin FlowerDate: 2011-10-09 21:22:54
Subject: Re: Using regoper type with OPERATOR()
Previous:From: Tony TheodoreDate: 2011-10-08 06:21:12
Subject: Re: Using regoper type with OPERATOR()

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group