Re: Indexes in PL/SQL

Tony Holmes wrote:
[...snip...]
> username character varying(32),
         (1)^^^^^^^^^^^^^^^^^^
[...snip...]
> CREATE FUNCTION valid_user(text) RETURNS text AS '
                           (2)^^^^
> DECLARE
>  _user ALIAS FOR $1;
>  _uid user_main.uid%TYPE;
> BEGIN
> SELECT uid INTO _uid FROM user_main WHERE username=_user;
                                          (3)^^^^^^^^^^^^^^

I think you have a character type mismatch. When you write:

   SELECT uid FROM user_main WHERE username='bob';

the constant 'bob' is initially type "unknown". Postgres is then able to
deduce that it should be varchar given the context, and therefore finds
the index. In your function, try either:

(2) CREATE FUNCTION valid_user(varchar) RETURNS text AS '

or

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

HTH,

Joe