Re: Using regoper type with OPERATOR() - Mailing list pgsql-novice

On 08/10/11 19:21, Tony Theodore wrote:

On 7 October 2011 06:33, Gavin Flower <GavinFlower@archidevsys.co.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