Re: BUG #7552: where clause gets ignored on one of view fields
От | Andrei Tchijov |
---|---|
Тема | Re: BUG #7552: where clause gets ignored on one of view fields |
Дата | |
Msg-id | 6D89ACD6-2F57-4F70-9DD4-8F41B1D41E32@tchijov.com обсуждение исходный текст |
Ответ на | Re: BUG #7552: where clause gets ignored on one of view fields (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: BUG #7552: where clause gets ignored on one of view fields
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | 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) ;
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Amit KapilaДата:
Сообщение: Re: BUG #7534: walreceiver takes long time to detect n/w breakdown
Следующее
От: lalbin@fhcrc.orgДата:
Сообщение: BUG #7553: Un-executable view definitions in pg_catalog.pg_views in versions 8.3.x-9.2.0