Re: BUG #7552: where clause gets ignored on one of view fields - Mailing list pgsql-bugs

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 =3D 41771;
   =20
However, on 9.2 this query acts as it is   =20

    select * form v_jobs;
   =20
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=20
    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
=20
Is there anything relevant or unusual in the PostgreSQL server logs?: =
Nothing. No new messages logged when problem occur.
=20
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)=20
  RETURNS INTEGER=20
AS
$BODY$

BEGIN
  IF ( p_rdms_job_number IS NULL OR p_rdms_job_number =3D 0 ) AND=20
    ( p_leads_job_number IS NOT NULL AND p_leads_job_number > 0 )
  THEN
    UPDATE jobs SET rdms_job_number =3D p_leads_job_number WHERE =
job_id_pk =3D 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=20
    ensure_rdms_job_number(j.job_id_pk, j.rdms_job_number, =
ls.job_number) as job_id_pk,=20
    j.job_status_id_fk,=20
    j.rdms_link_no,=20
    j.brand_id_fk,=20
    j.job_desc,=20

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

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

    j.job_scheduled_date,=20
    j.job_scheduler_id_fk,=20

    COALESCE(j.rdms_job_number, ls.job_number) AS rdms_job_number,=20
    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=20

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