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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] -- 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: [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
