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

Re: Sql Functions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Randy Neumann <Randy_Neumann(at)centralref(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Sql Functions
Date: 2002-08-09 14:15:43
Message-ID: 655.1028902543@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: James KeltyDate: 2002-08-09 15:31:44
Subject: Re: Proglems with 7.2.1
Previous:From: Randy NeumannDate: 2002-08-09 13:36:26
Subject: Sql Functions

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