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)
;
--
Sent via pgsql-bugs mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
