Re: Queries slow from within plpgsql

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