As I cannot return a complete row of a record from a function (written
in PL), I return an oid. So the function looks like this
CREATE FUNCTION task_next (int4) RETURNS oid AS
'
DECLARE
...
END;
' LANGUAGE 'plpgsql';
This function can return null. I then select the next row with
tt> select * from tasksids where oid=task_next(0);
and this is very slow, especially if task_next(0) returns null. I thus
defined an index on the oids:
tt> create unique index tasksids_oid_idx on tasksids(oid);
But get the following
tt=> explain select * from tasksids where oid=''::oid;
NOTICE: QUERY PLAN:
Index Scan using tasksids_oid_idx on tasksids (cost=2.05 size=1
width=33)
EXPLAIN
tt=> explain select * from tasksids where oid=task_next(0)::oid;
NOTICE: QUERY PLAN:
Seq Scan on tasksids (cost=2.22 size=4 width=33)
EXPLAIN
So why doesn't the query use the index when the oid is returned from a
function? And is there a better way of getting that row (or a null
record) returned from the function? This seems kind-of clumsy.
Cheers,
Adriaan