Re: Sql Functions

Randy Neumann <Randy_Neumann(at)centralref(dot)com> writes:
> Can anyone see what the problem is with this sql function? When I run the
> query outside the function I get the expected result. It seems that SQL
> functions do not like the keyword 'OR'.

No, the problem is the casts that you've inserted, which are in the
wrong direction. You've cast char(n) columns to varchar, which produces
results like '456 '::varchar, which does not equal
'456'::varchar.

The reason the query works "by hand" without any casts is that the
untyped constants '456', 'ghi' are assigned type char(n) after seeing
what they are compared to. Comparison of char(n) values ignores
trailing blanks, so the query gives the answers you expect. In the
function as you've set it up, the comparisons are done under varchar
rules, and the presence or absence of trailing blanks makes a
difference.

A good general rule for novices is never to use type char(n), period.
You almost certainly want varchar(n), instead. There are very very
few applications where fixed-width character data is actually what is
wanted.

regards, tom lane