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(at)iboone(dot)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(at)postgresql(dot)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(at)esc1(dot)com