After some more digging I found there was an index: CREATE INDEX job_list_status_idx ON job_list USING btree (status(event_no));
I had previously created. I must have changed the function from IMMUTABLE to STABLE after creating the index or I assume I wouldn't have been able to create the index. When I dropped the index Like and = started working correctly. BTW should there be check so an error is thrown if I try to change a function used in an index from IMMUTABLE to STABLE? Ben "Ben Trewern" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > All, > > I've a query: > > SELECT > c.code, c.pc_no, jl.event_no, jl.order_number, jl.location, s.status, > cs.commercial_status > FROM > ((codes as c JOIN job_list as jl ON c.id = jl.code_id) JOIN status as s > ON jl.event_no = s.event_no) > JOIN commercial_status AS cs on jl.event_no = cs.event_no > WHERE > (status = 'Job Allocated') AND (code_id = 39); > > Where codes and job_list are tables and status is a view: > > CREATE VIEW status AS > SELECT job_list.event_no, status(job_list.event_no) AS status > FROM job_list > JOIN user_codes ON job_list.code_id = user_codes.code_id > WHERE user_codes.user_name::name = "current_user"(); > > CREATE FUNCTION status(int4) > RETURNS text AS > $BODY$SELECT > CASE > WHEN job_cancelled IS NOT NULL THEN 'Cancelled'::text > WHEN job_sheet_received IS NOT NULL THEN 'Job Sheet > Received'::text > WHEN works_complete IS NOT NULL THEN 'Works Complete'::text > WHEN awaiting_action_id IS NOT NULL THEN 'Awaiting > Action'::text > WHEN attend_date IS NOT NULL THEN 'Job Attended'::text > WHEN issued_to IS NOT NULL THEN 'Job Allocated'::text > ELSE 'Not Allocated'::text > END > FROM > job_list > WHERE > event_no = $1$BODY$ > LANGUAGE 'sql' STABLE; > > The above query should return one row from my current database but does > not. If I change the where clause from (status = 'Job Allocated') AND > (code_id = 39) > to > (status LIKE 'Job Allocated') AND (code_id = 39) > it does return the row. > > What am I missing? > > Regards, > > Ben > ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
