Re: BUG #7552: where clause gets ignored on one of view fields

Hi,

I have a view (see SQL at the end of this e-mail). Query like following works without any problems on 9.0 and 9.1.

select * form v_jobs where rdms_job_number = 41771;

However, on 9.2 this query acts as it is

select * form v_jobs;

It seems that this problem occur only if I am trying to use this particular field (rdms_job_number), other fields works as they should.

PostgreSQL version number you are running:
PostgreSQL 9.2.0 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit

How you installed PostgreSQL: Following packages were downloaded from http://www.ubuntuupdates.org
postgresql-9.2_9.2.0-1~lucid_amd64.deb
postgresql-client-9.2_9.2.0-1~lucid_amd64.deb
postgresql-client-common_130~lucid_all.deb
postgresql-common_130~lucid_all.deb

Changes made to the settings in the postgresql.conf file: none

Operating system and version: Ubuntu 11.10

What program you're using to connect to PostgreSQL: psql

Is there anything relevant or unusual in the PostgreSQL server logs?: Nothing. No new messages logged when problem occur.

SQL (Please let me know if you need definitions for other tables/views involved):

--
--
--

CREATE OR REPLACE FUNCTION ensure_rdms_job_number( p_job_id INTEGER, p_rdms_job_number INTEGER, p_leads_job_number INTEGER)
RETURNS INTEGER
AS
$BODY$

BEGIN
IF ( p_rdms_job_number IS NULL OR p_rdms_job_number = 0 ) AND
( p_leads_job_number IS NOT NULL AND p_leads_job_number > 0 )
THEN
UPDATE jobs SET rdms_job_number = p_leads_job_number WHERE job_id_pk = p_job_id;
END IF;

RETURN p_job_id;
END;

$BODY$ language plpgsql;

DROP VIEW v_jobs CASCADE;

CREATE OR REPLACE VIEW v_jobs AS
SELECT
ensure_rdms_job_number(j.job_id_pk, j.rdms_job_number, ls.job_number) as job_id_pk,
j.job_status_id_fk,
j.rdms_link_no,
j.brand_id_fk,
j.job_desc,

jm.ca,
jm.cca,
jm.title1 AS client_title1,
jm.fname1 AS client_fname1,
jm.sname1 AS client_sname1,
jm.title2 AS client_title2,
jm.fname2 AS client_fname2,
jm.sname2 AS client_sname2,
ls.stradd AS client_stradd,
jm.csub AS client_suburb,
jm.cstate AS client_state,
jm.cpcode AS client_postcode,

jm.slot AS site_slot,
jm.sstrno AS site_strno,
jm.saddr1 AS site_addr1,
jm.saddr2 AS site_addr2,
jm.ssub AS site_suburb,
jm.sstate AS site_state,
jm.spcode AS site_postcode,

j.job_scheduled_date,
j.job_scheduler_id_fk,

COALESCE(j.rdms_job_number, ls.job_number) AS rdms_job_number,
est.suburb_shire_postcode_id_fk,
jm.email AS client_email,

jm.contract_value,

jm.phone1 AS client_phone1,
jm.phone2 AS client_phone2,
jm.fax AS client_fax,

s.supervisor_name

FROM jobs j
LEFT JOIN estimates est ON (j.job_id_pk = est.job_id_fk)
LEFT JOIN v_lead_system ls ON(ls.id = j.rdms_link_no)
LEFT JOIN v_jobmst jm ON (jm.job_number = COALESCE(j.rdms_job_number, ls.job_number))
LEFT JOIN construction c ON(c.job_number = ls.job_number)
LEFT JOIN supervisors s ON(c.supervisor_id = s.supervisor_id)
;