cannot find source of "add missing from" error

[Karsten Hilbert <Karsten.Hilbert@xxxxxxx>]

Hello,

I have a slightly involved view that draws from other views.
Upon insertion into the database (creation) it complains of

ERROR:  missing FROM-clause entry in subquery for table "v_basic_person"

and I can't for the life of it find why (I know I can work
around this with a postgresql.conf option).

The view that's erroring out and the view that's being
complained about is found in the attachments. The whole lot
of other tables and functions is found here:

http://savannah.gnu.org/cgi-bin/viewcvs/gnumed/gnumed/gnumed/server/sql/

(the function _() is found in gmI18N.sql BTW)

Can anyone help me pinpoint which relation is missing in
what FROM clause ?

(This project is a medical practice management system.)

Thanks,
Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

create view clin.v_hx_family as
-- those not linked to another patient as relative
select
	vpi.pk_patient as pk_patient,
	vpi.pk_health_issue as pk_health_issue,

	chxf.clin_when as clin_when,
	chxf.modified_when as modified_when,
	chxf.modified_by as modified_by,
	chxf.fk_encounter as pk_encounter,
	chxf.fk_episode as pk_episode,
	chxf.narrative as relationship,
	chxf.soap_cat as soap_cat,
	chxf.pk as pk_clin_hx_family,
	chxf.fk_hx_family_item as pk_hx_family_item,

	null::integer as pk_narrative_condition,
	null::integer as pk_relative_identity,
	hxfi.name_relative as name_relative,
	hxfi.dob_relative as dob_relative,
	hxfi.condition as condition,
	hxfi.age_noted as age_noted,
	hxfi.age_of_death as age_of_death,
	hxfi.is_cause_of_death as is_cause_of_death
from
	clin.v_pat_items vpi,
	clin.clin_hx_family chxf,
	clin.hx_family_item hxfi,
	v_basic_person vbp
where
	vpi.pk_item = chxf.pk_item
		and
	hxfi.pk = chxf.fk_hx_family_item
		and
	hxfi.fk_narrative_condition is null
		and
	hxfi.fk_relative is null

UNION

-- those linked to another patient as relative
select
	vpi.pk_patient as pk_patient,
	vpi.pk_health_issue as pk_health_issue,

	chxf.clin_when as clin_when,
	chxf.modified_when as modified_when,
	chxf.modified_by as modified_by,
	chxf.fk_encounter as pk_encounter,
	chxf.fk_episode as pk_episode,
	chxf.narrative as relationship,
	chxf.soap_cat as soap_cat,
	chxf.pk as pk_clin_hx_family,
	chxf.fk_hx_family_item as pk_hx_family_item,

	null::integer as pk_narrative_condition,
	hxfi.fk_relative as pk_relative_identity,
	vbp.firstnames || ' ' || vbp.lastnames as name_relative,
	vbp.dob as dob_relative,
	hxfi.condition as condition,
	hxfi.age_noted as age_noted,
	hxfi.age_of_death as age_of_death,
	hxfi.is_cause_of_death as is_cause_of_death
from
	clin.v_pat_items vpi,
	clin.clin_hx_family chxf,
	clin.hx_family_item hxfi,
	v_basic_person vbp
where
	vpi.pk_item = chxf.pk_item
		and
	hxfi.pk = chxf.fk_hx_family_item
		and
	hxfi.fk_narrative_condition is null
		and
	hxfi.fk_relative = v_basic_person.pk_identity

UNION

-- those linked to a condition of another patient being a relative
select
	vpn.pk_patient as pk_patient,
	vpn.pk_health_issue as pk_health_issue,

	chxf.clin_when as clin_when,
	chxf.modified_when as modified_when,
	chxf.modified_by as modified_by,
	chxf.fk_encounter as pk_encounter,
	chxf.fk_episode as pk_episode,
	chxf.narrative as relationship,
	chxf.soap_cat as soap_cat,
	chxf.pk as pk_clin_hx_family,
	chxf.fk_hx_family_item as pk_hx_family_item,

	hxfi.fk_narrative_condition as pk_narrative_condition,
	vpn.pk_patient as pk_relative_identity,
	vbp.firstnames || ' ' || vbp.lastnames as name_relative,
	vbp.dob as dob_relative,
	vpn.narrative as condition,
	hxfi.age_noted as age_noted,
	hxfi.age_of_death as age_of_death,
	hxfi.is_cause_of_death as is_cause_of_death
from
	clin.clin_hx_family chxf,
	clin.hx_family_item hxfi,
	v_basic_person vbp,
	clin.v_pat_narrative vpn
where
	hxfi.pk = chxf.fk_hx_family_item
		and
	hxfi.fk_narrative_condition = vpn.pk_narrative
		and
	hxfi.fk_relative is null
		and
	vbp.pk_identity = vpn.pk_patient
;

create view v_basic_person as
select
	i.pk as pk_identity,
	n.id as n_id,
	i.title as title,
	n.firstnames as firstnames,
	n.lastnames as lastnames,
	i.dob as dob,
	i.cob as cob,
	i.gender as gender,
	_(i.gender) as l10n_gender,
	i.karyotype as karyotype,
	i.pupic as pupic,
	case when i.fk_marital_status is null
		then 'unknown'
		else (select ms.name from marital_status ms, identity i1 where ms.pk=i.fk_marital_status and i1.pk=i.pk)
	end as marital_status,
	case when i.fk_marital_status is null
		then _('unknown')
		else (select _(ms1.name) from marital_status ms1, identity i1 where ms1.pk=i.fk_marital_status and i1.pk=i.pk)
	end as l10n_marital_status,
	i.fk_marital_status as pk_marital_status,
	n.preferred as preferred,
	i.xmin as xmin_identity
from
	identity i,
	names n
where
	i.deceased is NULL and
	n.active = true and
	n.id_identity = i.pk
;