Re: Indexes in PL/SQL

Tony Holmes <tony@crosswinds.net> writes:
> user_main (
>     username character varying(32),
> );

>     CREATE FUNCTION valid_user(text) RETURNS text AS '
>     DECLARE
>         _user ALIAS FOR $1;
>         _uid user_main.uid%TYPE;
>     BEGIN
>         SELECT uid INTO _uid FROM user_main WHERE username=_user;

This won't use the index because it's a cross-datatype comparison
(varchar versus text).  You could change the declared type of the
function argument to varchar, or leave the function signature alone
and cast the argument to varchar in the SELECT:

    SELECT uid INTO _uid FROM user_main WHERE username = _user::varchar;

7.4 will be more forgiving about text-versus-varchar discrepancies, but
you can still get burnt by this problem on most other cross-datatype
cases --- including char(n) versus varchar(n).  Integer versus bigint
is another common gotcha for newbies.

            regards, tom lane