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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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