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)
;
