Thread: 7.3.2 indexes in PL/PgSQL

I have select like that:

SELECT playerid FROM players WHERE nickname = 'qq' AND password = 'qqq';

query plan:

auction=# explain SELECT playerid FROM players WHERE nickname = 'qq' AND
password = 'qqq';
                                            QUERY PLAN

--------------------------------------------------------------------------------------------------
 Index Scan using nick_pass on players  (cost=0.00..6.01 rows=1 width=4)
    Index Cond: ((nickname = 'qq'::character varying) AND ("password" = 'qqq'::character varying))
(2 rows)

but in procedure on PL/PgSQL this select realy do sequense scan:

--- cut from create_session procedure ---
SELECT playerid INTO _playerid FROM players WHERE nickname = _nickname AND password = _passwd;
--- cut ---

Where i'm wrong? or that realy bug?

always thanks.

                Diabolo.

---                                                                       ---
   System Administrator of                          Phone: +7-095-939-1478
      the Philological Faculty of         E-Mail: diabolo@philol.msu.ru
         Moscow State University.      Web: http://www.philol.msu.ru

Evgeny Duzhakow <diabolo@philol.msu.ru> writes:
> SELECT playerid FROM players WHERE nickname = 'qq' AND password = 'qqq';
> [ does an index scan ]
> but in procedure on PL/PgSQL this select realy do sequense scan:

I'd bet that you were sloppy about datatypes.  Postgres will
automatically interpret those quoted literals as being the same datatype
as what they were compared to, but there's no such free lunch when
you're dealing with declared variables in a plpgsql function.  If the
datatypes don't match you probably won't get an index scan.  Change the
types to match or add explicit coercions.

            regards, tom lane