Re: Queries slow from within plpgsql

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