Hi, Tom,
we're using version 7.2.
I've a document table, the cdi column is indexed.
I tried to do a query by using cdi in two ways -
1. the simple query as expected is using index scan
edbs=# explain select emrxid from document where cdi='1031-15402';
NOTICE: QUERY PLAN:
Index Scan using document_cdi_key on document (cost=0.00..4.43 rows=1
width=27)
2. the one using plsql function is not using index scan!
EXPLAIN
edbs=# explain select getEmrxid('1031-15402') from document;
NOTICE: QUERY PLAN:
Seq Scan on document (cost=0.00..447377.72 rows=13018272 width=0)
here's the simple fuction -
create FUNCTION getEmrxid(text)
RETURNS text AS'
DECLARE
cdi_in ALIAS FOR $1;
docrec document%ROWTYPE;
BEGIN
select into docrec * from document where cdi = cdi_in;
RETURN docrec.emrxid;
END;
'LANGUAGE 'plpgsql';
This is not what I expected. If it's normal in pgsql, would you
providing some suggestions how to let function use index.
thanks in advance.
john