Question:
Would this problem exist if zip were defined as varchar with no specific
length defined? Is there a difference between varchar and text, at least in
the context of this discussion?
Thanks...
On Saturday 05 June 2004 12:36 am, Tom Lane saith:
> David Boone <dave@iboone.net> writes:
> > I've been trying to create functions with postgres, but it seems that
> > queries run within a function take wayyy too long to complete. The
> > increased time seems to be in the actual queries, not function call
> > overhead or something, but I can't for the life of me figure out why
> > it's slower like this.
>
> The problem here looks to be that you've declared the function parameter
> as "text" while the table column is "char(7)". When you write
> select ... where zip = 'V2P 6H3';
> the unadorned literal is taken to be char(7) to match the compared-to
> column, but in the function case the datatype of $1 is predetermined,
> and so
> select ... where zip = $1;
> involves a cross-data-type-comparison ... which is non-indexable
> in current releases. (There's a fix in place for 7.5.) Either
> change the declared type of the function parameter, or put a cast
> into the body of the function.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
Quote: 32
"The world we have created is a product of our thinking. It cannot be
changed without changing our thinking."
--Albert Einstein
Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry@esc1.com
