Dumped on 2024-12-23

Index of database - gnumed_v21

address
address_type
country
enum_comm_types
enum_ext_id_types
gender_label
identity
identity_tag
inbox_item_category
inbox_item_type
lnk_identity2comm
lnk_identity2ext_id
lnk_job2person
lnk_org_unit2comm
lnk_org_unit2ext_id
lnk_person2address
lnk_person2relative
lnk_person_org_address
marital_status
message_inbox
name_gender_map
names
occupation
org
org_category
org_unit
praxis_branch
region
relation_types
staff
street
urb
v_active_persons
v_address
v_all_persons
v_basic_address
v_deleted_persons
v_external_ids4identity
v_external_ids4org_unit
v_gender_labels
v_identity_tags
v_inbox_item_type
v_message_inbox
v_org_unit_comms
v_org_units
v_org_units_no_praxis_check
v_orgs
v_pat_addresses
v_person_comms
v_person_jobs
v_person_names
v_persons
v_praxis_branches
v_region
v_staff
v_street
v_uniq_zipped_urbs
v_urb
v_zip2data
v_zip2street
v_zip2urb
add_external_id_type(text, text)
add_name(integer, text, text, boolean)
address_exists(text, text, text, text, text, text, text)
create_address(text, text, text, text, text, text, text)
create_comm_type(text)
create_occupation(text)
create_street(text, text, text, text, text)
create_urb(text, text, text, text)
date_trunc_utc(text, timestamp with time zone)
dob_is_in_range(timestamp with time zone, interval, interval)
f_always_active_name()
f_delete_names()
f_uniq_active_name()
gm_upd_default_regions()
remove_person(integer)
set_nickname(integer, text)
trf_normalize_time_in_dob()
trf_null_empty_title()
trf_prevent_deletion_of_in_use_staff()
trf_prevent_multiple_praxi()
trf_protect_active_name_of_person()
trf_sane_identity_comment()

Table: address

an address aka a location, void of attached meaning such as type of address

address Structure
F-Key	Name	Type	Description
	pk_audit	integer	NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
	row_version	integer	NOT NULL
	modified_when	timestamp with time zone	NOT NULL DEFAULT now()
	modified_by	name	NOT NULL DEFAULT "current_user"()
	id	serial	PRIMARY KEY
street.id	id_street	integer	UNIQUE#1 NOT NULL the street this address is at from whence the urb is to be found, it thus indirectly references dem.urb(id)
	aux_street	text	UNIQUE#1 additional street-level information which formatters would usually put on lines directly below the street line of an address, such as postal box directions in CA
	number	text	UNIQUE#1 NOT NULL number of the house
	subunit	text	UNIQUE#1 directions below the unit (eg.number) level, such as appartment number, room number, level, entrance or even verbal directions
	addendum	text	UNIQUE#1 any additional information that did not fit anywhere else
	lat_lon	point	the exact location of this address in latitude-longtitude

address Constraints
Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))

Tables referencing this one via Foreign Key Constraints:

lnk_identity2comm

lnk_person_org_address

org_unit

idx_dem_address_id_street id_street

Index - Schema dem

Table: address_type

address_type Structure
F-Key	Name	Type	Description
	id	serial	PRIMARY KEY
	name	text	UNIQUE NOT NULL

Tables referencing this one via Foreign Key Constraints:

lnk_person_org_address

Index - Schema dem

Table: country

countries coded per ISO 3166-1

country Structure
Name	Type	Description
id	serial	PRIMARY KEY
code	character(2)	UNIQUE NOT NULL international two character country code as per ISO 3166-1
name	text	UNIQUE NOT NULL
deprecated	date	date when this country ceased officially to exist (if applicable)

country Constraints
Name	Constraint
no_linebreaks	CHECK ((("position"(((COALESCE(code, ''::bpchar))::text \|\| COALESCE(name, ''::text)), ''::text) = 0) AND ("position"(((COALESCE(code, ''::bpchar))::text \|\| COALESCE(name, ''::text)), ' '::text) = 0) AND ("position"(((COALESCE(code, ''::bpchar))::text \|\| COALESCE(name, ''::text)), ' '::text) = 0) AND ("position"(((COALESCE(code, ''::bpchar))::text \|\| COALESCE(name, ''::text)), ''::text) = 0)))

Tables referencing this one via Foreign Key Constraints:

region

Index - Schema dem

Table: enum_comm_types

enum_comm_types Structure
F-Key	Name	Type	Description
	pk	serial	PRIMARY KEY
	description	text	UNIQUE NOT NULL

Tables referencing this one via Foreign Key Constraints:

lnk_identity2comm

lnk_org_unit2comm

Index - Schema dem

Table: enum_ext_id_types

a list of all bureaucratic IDs/serial numbers/3rd party primary keys, etc.

enum_ext_id_types Structure
Name	Type	Description
pk	serial	PRIMARY KEY
name	text	UNIQUE#1
issuer	text	UNIQUE#1 the authority/system issuing the number

Tables referencing this one via Foreign Key Constraints:

lnk_identity2ext_id

lnk_org_unit2ext_id

Index - Schema dem

Table: gender_label

This table stores the genders known to GNUmed. FIXME: cross-check with CDA:administrative-gender-code

gender_label Structure
Name	Type	Description
pk_audit	integer	NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
row_version	integer	NOT NULL
modified_when	timestamp with time zone	NOT NULL DEFAULT now()
modified_by	name	NOT NULL DEFAULT "current_user"()
pk	serial	PRIMARY KEY
tag	text	UNIQUE NOT NULL
label	text	UNIQUE NOT NULL
sort_weight	integer	NOT NULL
comment	text	NOT NULL

gender_label Constraints
Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))
gender_label_tag_check	CHECK ((tag = ANY (ARRAY['m'::text, 'f'::text, 'h'::text, 'tm'::text, 'tf'::text])))

Tables referencing this one via Foreign Key Constraints:

identity

Index - Schema dem

Table: identity

represents the unique identity of a person

identity Structure
F-Key	Name	Type	Description
	pk_audit	integer	NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
	row_version	integer	NOT NULL
	modified_when	timestamp with time zone	NOT NULL DEFAULT now()
	modified_by	name	NOT NULL DEFAULT "current_user"()
	pk	serial	PRIMARY KEY
	deleted	boolean	NOT NULL DEFAULT false whether this identity is considered deleted
gender_label.tag	gender	text	the gender code
	dob	timestamp with time zone	date/time of birth
marital_status.pk	fk_marital_status	integer
	cob	character(2)	country of birth as per date of birth, coded as 2 character ISO code
	deceased	timestamp with time zone	date when a person has died
	title	text	Yes, a title is an attribute of an identity, not of a name ! Also, there are some incredible rants of titles.
	tob	time without time zone
	emergency_contact	text	Free text emergency contact information.
identity.pk	fk_emergency_contact	integer	Link to another dem.identity to be used as emergency contact.
	comment	text	A free-text comment on this identity.\n \n Can be used to to discriminate patients which are otherwise\n identical regarding name and date of birth.\n Should be something non-ephemereal and unique to the person\n itself across time, place and database instance.\n Good: place of birth\n Good: maiden name of mother\n Good: mother of <name>\n Good: hash of DNA\n Good (?): hair color of first pet\n Bad: current address (will change)\n Bad: primary provider in this praxis (can change, invalid in another GNUmed instance)\n Bad: nickname (will change, can dupe as well)\n Bad: favourite food\n not-quite-so-bad: occupation
staff.pk	fk_primary_provider	integer	Whether the given DOB is estimated or not. The TOB is assumed to be correct if given
	dob_is_estimated	boolean	NOT NULL DEFAULT false

identity Constraints
Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))
dem_identity_sane_dob	CHECK (((dob IS NULL) OR (dob <= now())))
dem_identity_sane_dod	CHECK (((deceased IS NULL) OR (dob IS NULL) OR (deceased >= dob)))
identity_title_check	CHECK ((btrim(COALESCE(title, 'NULL'::text)) <> ''::text))
sane_comment	CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE))
sane_emergency_contact	CHECK ((gm.is_null_or_non_empty_string(emergency_contact) IS TRUE))

Tables referencing this one via Foreign Key Constraints:

identity

identity_tag

lnk_identity2comm

lnk_identity2ext_id

lnk_job2person

lnk_person2relative

lnk_person_org_address

message_inbox

names

staff

idx_identity_dob_ymd dem.date_trunc_utc('day'::text, dob)

Index - Schema dem

Table: inbox_item_category

Holds the various categories of messages that can show up in the provider inbox.

inbox_item_category Structure
Name	Type	Description
pk_audit	integer	NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
row_version	integer	NOT NULL
modified_when	timestamp with time zone	NOT NULL DEFAULT now()
modified_by	name	NOT NULL DEFAULT "current_user"()
pk	serial	PRIMARY KEY
description	text	UNIQUE NOT NULL "clinical" "admin" "personal" ...
is_user	boolean	NOT NULL DEFAULT true whether this category was added locally, as to be left alone by database upgrades

inbox_item_category Constraints
Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))
inbox_item_category_description_check	CHECK ((btrim(COALESCE(description, 'xxxDEFAULTxxx'::text)) <> ''::text))

Tables referencing this one via Foreign Key Constraints:

inbox_item_type

Index - Schema dem

Table: inbox_item_type

Holds the various types of messages that can show up in the provider inbox.

inbox_item_type Structure
F-Key	Name	Type	Description
	pk_audit	integer	NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
	row_version	integer	NOT NULL
	modified_when	timestamp with time zone	NOT NULL DEFAULT now()
	modified_by	name	NOT NULL DEFAULT "current_user"()
	pk	serial	PRIMARY KEY
inbox_item_category.pk	fk_inbox_item_category	integer	UNIQUE#1 NOT NULL The category of this item type.
	description	text	UNIQUE#1 NOT NULL the various types of inbox items
	is_user	boolean	NOT NULL DEFAULT true whether this type was added locally, as to be left alone by database upgrades

inbox_item_type Constraints
Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))
inbox_item_type_description_check	CHECK ((btrim(COALESCE(description, 'xxxDEFAULTxxx'::text)) <> ''::text))

Tables referencing this one via Foreign Key Constraints:

message_inbox

Index - Schema dem

lnk_identity2comm Constraints
Name	Constraint
d_l_i2comm_sane_comment	CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE))
lnk_identity2comm_url_check	CHECK ((btrim(url) <> ''::text))

lnk_person2address Structure
F-Key	Name	Type	Description
	id_identity	integer
	id_address	integer
	id_type	integer

Table: lnk_person2relative

biological and social relationships between an identity and other identities

lnk_person2relative Structure
F-Key	Name	Type	Description
	pk_audit	integer	NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
	row_version	integer	NOT NULL
	modified_when	timestamp with time zone	NOT NULL DEFAULT now()
	modified_by	name	NOT NULL DEFAULT "current_user"()
	id	serial	PRIMARY KEY
identity.pk	id_identity	integer	NOT NULL primary identity to whom the relationship applies
identity.pk	id_relative	integer	NOT NULL referred-to identity of this relationship (e.g. "child" if id_identity points to the father and id_relation_type points to "parent")
relation_types.id	id_relation_type	integer	NOT NULL
	started	date	date when this relationship began
	ended	date	date when this relationship ended, biological relationships do not end !

lnk_person2relative Constraints
Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))

idx_lnk_pers2rel id_identity, id_relation_type

Index - Schema dem

Table: marital_status

marital_status Structure
F-Key	Name	Type	Description
	pk	serial	PRIMARY KEY
	name	text	UNIQUE NOT NULL

Tables referencing this one via Foreign Key Constraints:

identity

Index - Schema dem

Table: message_inbox

messages in GNUmed relating to a patient, a provider, and a context

message_inbox Structure
F-Key	Name	Type	Description
	pk_audit	integer	NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
	row_version	integer	NOT NULL
	modified_when	timestamp with time zone	NOT NULL DEFAULT now()
	modified_by	name	NOT NULL DEFAULT "current_user"()
	pk	serial	PRIMARY KEY
staff.pk	fk_staff	integer	the member of staff this message is addressed to
inbox_item_type.pk	fk_inbox_item_type	integer	NOT NULL the item (message) type
	comment	text	a free-text comment, may be NULL but not empty
	data	text	arbitrary data an application might wish to attach to the message, like a cookie, basically
	importance	smallint	NOT NULL the relative importance of this message: -1: lower than most things already in the inbox ("low") 0: same as most things ("standard") 1: higher than most things already there ("high")
identity.pk	fk_patient	integer
	ufk_context	integer[]	a nullable array of Unchecked Foreign Keys, it is up to the application to know what this points to, it will have to make sense within the context of the combination of staff ID, item type, and comment
	due_date	date	The date this message/reminder is due. If NULL then the message is not a reminder.
	expiry_date	date	The date this message/reminder "expires". Must be > .due_date it not NULL.

message_inbox Constraints
Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))
dem_inbox_sane_expiry_date	CHECK (((expiry_date IS NULL) OR (due_date IS NULL) OR (expiry_date > due_date)))
message_must_have_recipient	CHECK ((((fk_staff IS NULL) AND (fk_patient IS NULL)) IS FALSE))
provider_inbox_comment_check	CHECK ((btrim(COALESCE(comment, 'xxxDEFAULTxxx'::text)) <> ''::text))
provider_inbox_importance_check	CHECK (((importance = '-1'::integer) OR (importance = 0) OR (importance = 1)))

idx_msg_inbox_fk_patient fk_patient
idx_msg_inbox_fk_staff fk_staff

Index - Schema dem

maps (first) names to their most frequently locally assigned gender, this table is updated nightly by a cron script, names whose gender distribution is between 70/30 and 30/70 are ignored for ambiguity reasons, names with "ambigous" gender are also ignored

name_gender_map Structure
Name	Type	Description
id	serial	PRIMARY KEY
name	text	UNIQUE NOT NULL
gender	character(1)

name_gender_map Constraints
Name	Constraint
name_gender_map_gender_check	CHECK ((gender = ANY (ARRAY['m'::bpchar, 'f'::bpchar])))

Index - Schema dem

Table: names

all the names an identity is known under; As opposed to the versioning of all other tables, changed names should not be moved into the audit trail tables. Search functionality must be available at any time for all names a person ever had.

names Structure
F-Key	Name	Type	Description
	id	serial	PRIMARY KEY
identity.pk	id_identity	integer	UNIQUE#1 NOT NULL
	active	boolean	DEFAULT true true if the name is still in use
	lastnames	text	UNIQUE#1 NOT NULL all last names of an identity in legal order, IOW "major" name, "group identifier", eg. family, village, tribe, ...
	firstnames	text	UNIQUE#1 NOT NULL all first names of an identity in legal order, IOW "minor" name, identifier of this identity within the group defined by <lastnames>
	preferred	text	preferred first name, the name a person is usually called (nickname, warrior name)
	comment	text	a comment regarding this name, useful in things like "this was the name before marriage" etc

idx_names_firstnames firstnames
idx_names_last_first lastnames, firstnames

Index - Schema dem

Table: occupation

collects occupation names

occupation Structure
Name	Type	Description
pk_audit	integer	NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
row_version	integer	NOT NULL
modified_when	timestamp with time zone	NOT NULL DEFAULT now()
modified_by	name	NOT NULL DEFAULT "current_user"()
id	serial	PRIMARY KEY
name	text	NOT NULL

occupation Constraints
Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))
occupation_name_check	CHECK ((btrim(name) <> ''::text))

Tables referencing this one via Foreign Key Constraints:

lnk_job2person

Index - Schema dem

Table: org

Organizations at a conceptual level.

org Structure
F-Key	Name	Type	Description
	pk_audit	integer	NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
	row_version	integer	NOT NULL
	modified_when	timestamp with time zone	NOT NULL DEFAULT now()
	modified_by	name	NOT NULL DEFAULT "current_user"()
	pk	serial	PRIMARY KEY
	description	text	UNIQUE High-level, conceptual description (= name) of organization, such as "University of Manchester".
org_category.pk	fk_category	integer
	fk_data_source	integer	Source of the organization data.

org Constraints
Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))
org_sane_description	CHECK ((gm.is_null_or_blank_string(description) IS FALSE))

Tables referencing this one via Foreign Key Constraints:

org_unit

idx_dem_org_fk_category fk_category

Index - Schema dem

Table: org_category

org_category Structure
F-Key	Name	Type	Description
	pk	serial	PRIMARY KEY
	description	text	NOT NULL

Tables referencing this one via Foreign Key Constraints:

org_unit

Index - Schema dem

Table: org_unit

Actual branches/departments/offices/... of organizations.

org_unit Structure
F-Key	Name	Type	Description
	pk_audit	integer	NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
	row_version	integer	NOT NULL
	modified_when	timestamp with time zone	NOT NULL DEFAULT now()
	modified_by	name	NOT NULL DEFAULT "current_user"()
	pk	serial	PRIMARY KEY
	description	text	UNIQUE#1 Description (= name) of branch of organization, such as "Elms Street office of Jim Busser Praxis".
org.pk	fk_org	integer	UNIQUE#1 NOT NULL
address.id	fk_address	integer
org_category.pk	fk_category	integer

org_unit Constraints
Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))
org_unit_sane_description	CHECK ((gm.is_null_or_blank_string(description) IS FALSE))

Tables referencing this one via Foreign Key Constraints:

lnk_org_unit2comm

lnk_org_unit2ext_id

idx_dem_org_unit_fk_address fk_address
idx_dem_org_unit_fk_category fk_category

Index - Schema dem

Table: praxis_branch

Defines one branch of a praxis (which itself is a dem.org)

praxis_branch Structure
Name	Type	Description
pk_audit	integer	NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
row_version	integer	NOT NULL
modified_when	timestamp with time zone	NOT NULL DEFAULT now()
modified_by	name	NOT NULL DEFAULT "current_user"()
pk	serial	PRIMARY KEY
fk_org_unit	integer	UNIQUE NOT NULL

praxis_branch Constraints
Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))

Index - Schema dem

Table: region

region codes (country specific); Richard agreed we should require pre-existence, allow user to mail details for adding a state to developers

region Structure
F-Key	Name	Type	Description
	pk_audit	integer	NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
	row_version	integer	NOT NULL
	modified_when	timestamp with time zone	NOT NULL DEFAULT now()
	modified_by	name	NOT NULL DEFAULT "current_user"()
	pk	serial	PRIMARY KEY
	code	text	UNIQUE#1 NOT NULL region code
country.code	country	character(2)	UNIQUE#1 NOT NULL 2 character ISO 3166-1 country code
	name	text	NOT NULL

region Constraints
Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))
no_linebreaks	CHECK ((("position"(((COALESCE(code, ''::text) \|\| (COALESCE(country, ''::bpchar))::text) \|\| COALESCE(name, ''::text)), ''::text) = 0) AND ("position"(((COALESCE(code, ''::text) \|\| (COALESCE(country, ''::bpchar))::text) \|\| COALESCE(name, ''::text)), ' '::text) = 0) AND ("position"(((COALESCE(code, ''::text) \|\| (COALESCE(country, ''::bpchar))::text) \|\| COALESCE(name, ''::text)), ' '::text) = 0) AND ("position"(((COALESCE(code, ''::text) \|\| (COALESCE(country, ''::bpchar))::text) \|\| COALESCE(name, ''::text)), ''::text) = 0)))

Tables referencing this one via Foreign Key Constraints:

idx_dem_state_country_code country
idx_state_names name

Index - Schema dem

Table: relation_types

types of biological/social relationships between identities

relation_types Structure
F-Key	Name	Type	Description
	pk_audit	integer	NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
	row_version	integer	NOT NULL
	modified_when	timestamp with time zone	NOT NULL DEFAULT now()
	modified_by	name	NOT NULL DEFAULT "current_user"()
	id	serial	PRIMARY KEY
relation_types.id	inverse	integer
	biological	boolean	NOT NULL true if relationship is biological (proven or reasonable assumption), else false
	biol_verified	boolean	DEFAULT false ONLY true if there is genetic proof for this relationship
	description	text	plain text description of relationship

relation_types Constraints
Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))

Tables referencing this one via Foreign Key Constraints:

lnk_person2relative

relation_types

Index - Schema dem

Table: staff

one-to-one mapping of database user accounts (db_user) to staff identities (fk_identity)

staff Structure
F-Key	Name	Type	Description
	pk_audit	integer	NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
	row_version	integer	NOT NULL
	modified_when	timestamp with time zone	NOT NULL DEFAULT now()
	modified_by	name	NOT NULL DEFAULT "current_user"()
	pk	serial	PRIMARY KEY
identity.pk	fk_identity	integer	NOT NULL
	db_user	name	UNIQUE NOT NULL DEFAULT "current_user"()
	short_alias	text	UNIQUE NOT NULL a short signature unique to this staff member to be used in the GUI, actually this is somewhat redundant with ext_person_id...
	comment	text
	is_active	boolean	NOT NULL DEFAULT true

staff Constraints
Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))

Tables referencing this one via Foreign Key Constraints:

identity

message_inbox

Index - Schema dem

Table: street

street names, specific for distinct "urbs"

street Structure
F-Key	Name	Type	Description
	pk_audit	integer	NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
	row_version	integer	NOT NULL
	modified_when	timestamp with time zone	NOT NULL DEFAULT now()
	modified_by	name	NOT NULL DEFAULT "current_user"()
	id	serial	PRIMARY KEY
urb.id	id_urb	integer	UNIQUE#1 NOT NULL reference to information postcode, city, country and region
	name	text	UNIQUE#1 NOT NULL name of this street
	postcode	text	UNIQUE#1 postcode for systems (such as UK Royal Mail) which specify the street
	suburb	text	the suburb this street is in (if any)
	lat_lon	point	the approximate location of the street, as lat/long co-ordinates

street Constraints
Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))
no_linebreaks	CHECK ((("position"(((COALESCE(postcode, ''::text) \|\| COALESCE(suburb, ''::text)) \|\| COALESCE(name, ''::text)), ''::text) = 0) AND ("position"(((COALESCE(postcode, ''::text) \|\| COALESCE(suburb, ''::text)) \|\| COALESCE(name, ''::text)), ' '::text) = 0) AND ("position"(((COALESCE(postcode, ''::text) \|\| COALESCE(suburb, ''::text)) \|\| COALESCE(name, ''::text)), ' '::text) = 0) AND ("position"(((COALESCE(postcode, ''::text) \|\| COALESCE(suburb, ''::text)) \|\| COALESCE(name, ''::text)), ''::text) = 0)))

Tables referencing this one via Foreign Key Constraints:

address

idx_dem_street_id_urb id_urb
idx_street_names name
idx_street_zips postcode

Index - Schema dem

Table: urb

cities, towns, dwellings ..., eg. "official" places of residence

urb Structure
F-Key	Name	Type	Description
	pk_audit	integer	NOT NULL DEFAULT nextval('audit.audit_fields_pk_audit_seq'::regclass)
	row_version	integer	NOT NULL
	modified_when	timestamp with time zone	NOT NULL DEFAULT now()
	modified_by	name	NOT NULL DEFAULT "current_user"()
	id	serial	PRIMARY KEY
region.pk	fk_region	integer	UNIQUE#1 NOT NULL reference to information about country and region
	postcode	text	UNIQUE#1 NOT NULL default postcode for urb.name, useful for all the smaller urbs that only have one postcode, also useful as a default when adding new streets to an urb
	lat_lon	point	the location of the urb, as lat/long co-ordinates. Ideally this would be NOT NULL
	name	text	UNIQUE#1 NOT NULL the name of the city/town/dwelling

urb Constraints
Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))
no_linebreaks	CHECK ((("position"((COALESCE(postcode, ''::text) \|\| COALESCE(name, ''::text)), ''::text) = 0) AND ("position"((COALESCE(postcode, ''::text) \|\| COALESCE(name, ''::text)), ' '::text) = 0) AND ("position"((COALESCE(postcode, ''::text) \|\| COALESCE(name, ''::text)), ' '::text) = 0) AND ("position"((COALESCE(postcode, ''::text) \|\| COALESCE(name, ''::text)), ''::text) = 0)))

Tables referencing this one via Foreign Key Constraints:

street

idx_dem_urb_id_state fk_region
idx_urb_names name
idx_urb_zips postcode

Index - Schema dem

View: v_active_persons

This view denormalizes non-deleted persons with their active name.

v_active_persons Structure
F-Key	Name	Type	Description
	pk_identity	integer
	title	text
	firstnames	text
	preferred	text
	lastnames	text
	gender	text
	l10n_gender	text
	dob_only	timestamp with time zone
	dob	timestamp with time zone
	tob	time without time zone
	deceased	timestamp with time zone
	marital_status	text
	l10n_marital_status	text
	emergency_contact	text
	comment	text
	is_deleted	boolean
	pk_marital_status	integer
	pk_active_name	integer
	pk_emergency_contact	integer
	pk_primary_provider	integer
	xmin_identity	xid
	dob_is_estimated	boolean

SELECT d_vp.pk_identity
,
    d_vp.title
,
    d_vp.firstnames
,
    d_vp.preferred
,
    d_vp.lastnames
,
    d_vp.gender
,
    d_vp.l10n_gender
,
    d_vp.dob_only
,
    d_vp.dob
,
    d_vp.tob
,
    d_vp.deceased
,
    d_vp.marital_status
,
    d_vp.l10n_marital_status
,
    d_vp.emergency_contact
,
    d_vp.comment
,
    d_vp.is_deleted
,
    d_vp.pk_marital_status
,
    d_vp.pk_active_name
,
    d_vp.pk_emergency_contact
,
    d_vp.pk_primary_provider
,
    d_vp.xmin_identity
,
    d_vp.dob_is_estimated
   
FROM dem.v_all_persons d_vp
  
WHERE (d_vp.is_deleted IS FALSE);

Index - Schema dem

View: v_address

fully denormalizes data about addresses as entities in themselves

v_address Structure
F-Key	Name	Type	Description
	pk_address	integer
	street	text
	postcode	text
	notes_street	text
	number	text
	subunit	text
	notes_subunit	text
	lat_lon_address	point
	postcode_street	text
	lat_lon_street	point
	suburb	text
	urb	text
	postcode_urb	text
	lat_lon_urb	point
	code_region	text
	region	text
	l10n_region	text
	code_country	character(2)
	country	text
	l10n_country	text
	country_deprecated	date
	pk_street	integer
	pk_urb	integer
	pk_region	integer
	xmin_address	xid

SELECT d_adr.id AS pk_address
,
    d_str.name AS street
,
    COALESCE
(d_str.postcode
     , d_u.postcode
) AS postcode
,
    d_adr.aux_street AS notes_street
,
    d_adr.number
,
    d_adr.subunit
,
    d_adr.addendum AS notes_subunit
,
    d_adr.lat_lon AS lat_lon_address
,
    d_str.postcode AS postcode_street
,
    d_str.lat_lon AS lat_lon_street
,
    d_str.suburb
,
    d_u.name AS urb
,
    d_u.postcode AS postcode_urb
,
    d_u.lat_lon AS lat_lon_urb
,
    d_r.code AS code_region
,
    d_r.name AS region
,
    _
(d_r.name) AS l10n_region
,
    d_r.country AS code_country
,
    d_c.name AS country
,
    _
(d_c.name) AS l10n_country
,
    d_c.deprecated AS country_deprecated
,
    d_adr.id_street AS pk_street
,
    d_u.id AS pk_urb
,
    d_r.pk AS pk_region
,
    d_adr.xmin AS xmin_address
   
FROM (
     (
           (
                 (dem.address d_adr
     
               LEFT JOIN dem.street d_str 
                      ON (
                             (d_adr.id_street = d_str.id)
                       )
                 )
     
         LEFT JOIN dem.urb d_u 
                ON (
                       (d_str.id_urb = d_u.id)
                 )
           )
     
   LEFT JOIN dem.region d_r 
          ON (
                 (d_u.fk_region = d_r.pk)
           )
     )
     
LEFT JOIN dem.country d_c 
    ON (
           (d_c.code = d_r.country)
     )
);

Index - Schema dem

View: v_all_persons

This view denormalizes persons with their active name.

v_all_persons Structure
F-Key	Name	Type	Description
	pk_identity	integer
	title	text
	firstnames	text
	preferred	text
	lastnames	text
	gender	text
	l10n_gender	text
	dob_only	timestamp with time zone
	dob	timestamp with time zone
	tob	time without time zone
	deceased	timestamp with time zone
	marital_status	text
	l10n_marital_status	text
	emergency_contact	text
	comment	text
	is_deleted	boolean
	pk_marital_status	integer
	pk_active_name	integer
	pk_emergency_contact	integer
	pk_primary_provider	integer
	xmin_identity	xid
	dob_is_estimated	boolean

SELECT d_i.pk AS pk_identity
,
    d_i.title
,
    d_n.firstnames
,
    d_n.preferred
,
    d_n.lastnames
,
    d_i.gender
,
    _
(d_i.gender) AS l10n_gender
,
    d_i.dob AS dob_only
,
    
(date_trunc
     ('day'::text
           , d_i.dob
     ) + 
     (COALESCE
           (d_i.tob
                 , (d_i.dob)::time without time zone
           )
     )::interval
) AS dob
,
    d_i.tob
,
    d_i.deceased
,
    COALESCE
(d_ms.name
     ,'unknown'::text
) AS marital_status
,
    _
(COALESCE
     (d_ms.name
           ,'unknown'::text
     )
) AS l10n_marital_status
,
    d_i.emergency_contact
,
    d_i.comment
,
    d_i.deleted AS is_deleted
,
    d_i.fk_marital_status AS pk_marital_status
,
    d_n.id AS pk_active_name
,
    d_i.fk_emergency_contact AS pk_emergency_contact
,
    d_i.fk_primary_provider AS pk_primary_provider
,
    d_i.xmin AS xmin_identity
,
    d_i.dob_is_estimated
   
FROM (
     (dem.identity d_i
     
        JOIN dem.names d_n 
          ON (
                 (
                       (d_n.id_identity = d_i.pk)
                     AND (d_n.active IS TRUE)
                 )
           )
     )
     
LEFT JOIN dem.marital_status d_ms 
    ON (
           (d_i.fk_marital_status = d_ms.pk)
     )
);

Index - Schema dem

View: v_basic_address

v_basic_address Structure
F-Key	Name	Type	Description
	id	integer
	country_code	character(2)
	region_code	text
	region	text
	country	text
	postcode	text
	urb	text
	number	text
	street	text
	addendum	text
	lat_lon	point

SELECT d_adr.id
,
    d_r.country AS country_code
,
    d_r.code AS region_code
,
    d_r.name AS region
,
    d_c.name AS country
,
    COALESCE
(d_str.postcode
     , d_u.postcode
) AS postcode
,
    d_u.name AS urb
,
    d_adr.number
,
    d_str.name AS street
,
    d_adr.addendum
,
    COALESCE
(d_adr.lat_lon
     , d_str.lat_lon
     , d_u.lat_lon
) AS lat_lon
   
FROM dem.address d_adr
,
    dem.region d_r
,
    dem.country d_c
,
    dem.urb d_u
,
    dem.street d_str
  
WHERE (
     (d_r.country = d_c.code)
   AND (d_adr.id_street = d_str.id)
   AND (d_str.id_urb = d_u.id)
   AND (d_u.fk_region = d_r.pk)
);

Index - Schema dem

View: v_deleted_persons

This view denormalizes "deleted" persons with their active name.

v_deleted_persons Structure
F-Key	Name	Type	Description
	pk_identity	integer
	title	text
	firstnames	text
	preferred	text
	lastnames	text
	gender	text
	l10n_gender	text
	dob_only	timestamp with time zone
	dob	timestamp with time zone
	tob	time without time zone
	deceased	timestamp with time zone
	marital_status	text
	l10n_marital_status	text
	emergency_contact	text
	comment	text
	is_deleted	boolean
	pk_marital_status	integer
	pk_active_name	integer
	pk_emergency_contact	integer
	pk_primary_provider	integer
	xmin_identity	xid
	dob_is_estimated	boolean

SELECT d_vp.pk_identity
,
    d_vp.title
,
    d_vp.firstnames
,
    d_vp.preferred
,
    d_vp.lastnames
,
    d_vp.gender
,
    d_vp.l10n_gender
,
    d_vp.dob_only
,
    d_vp.dob
,
    d_vp.tob
,
    d_vp.deceased
,
    d_vp.marital_status
,
    d_vp.l10n_marital_status
,
    d_vp.emergency_contact
,
    d_vp.comment
,
    d_vp.is_deleted
,
    d_vp.pk_marital_status
,
    d_vp.pk_active_name
,
    d_vp.pk_emergency_contact
,
    d_vp.pk_primary_provider
,
    d_vp.xmin_identity
,
    d_vp.dob_is_estimated
   
FROM dem.v_all_persons d_vp
  
WHERE (d_vp.is_deleted IS TRUE);

Index - Schema dem

View: v_external_ids4identity

v_external_ids4identity Structure
F-Key	Name	Type	Description
	pk_identity	integer
	pk_id	integer
	name	text
	value	text
	issuer	text
	comment	text
	pk_type	integer

SELECT li2ei.id_identity AS pk_identity
,
    li2ei.id AS pk_id
,
    eit.name
,
    li2ei.external_id AS value
,
    eit.issuer
,
    li2ei.comment
,
    li2ei.fk_origin AS pk_type
   
FROM dem.lnk_identity2ext_id li2ei
,
    dem.enum_ext_id_types eit
  
WHERE (li2ei.fk_origin = eit.pk);

Index - Schema dem

View: v_external_ids4org_unit

v_external_ids4org_unit Structure
F-Key	Name	Type	Description
	pk_org_unit	integer
	pk_id	integer
	name	text
	value	text
	issuer	text
	comment	text
	pk_type	integer

SELECT d_lou2ei.fk_org_unit AS pk_org_unit
,
    d_lou2ei.pk AS pk_id
,
    d_eit.name
,
    d_lou2ei.external_id AS value
,
    d_eit.issuer
,
    d_lou2ei.comment
,
    d_lou2ei.fk_type AS pk_type
   
FROM (dem.lnk_org_unit2ext_id d_lou2ei
     
  JOIN dem.enum_ext_id_types d_eit 
    ON (
           (d_lou2ei.fk_type = d_eit.pk)
     )
);

Index - Schema dem

View: v_gender_labels

v_gender_labels Structure
F-Key	Name	Type	Description
	tag	text
	l10n_tag	text
	label	text
	l10n_label	text
	comment	text
	sort_weight	integer
	pk_gender_label	integer

SELECT gl.tag
,
    _
(gl.tag) AS l10n_tag
,
    gl.label
,
    _
(gl.label) AS l10n_label
,
    gl.comment
,
    gl.sort_weight
,
    gl.pk AS pk_gender_label
   
FROM dem.gender_label gl;

Index - Schema dem

View: v_identity_tags

v_identity_tags Structure
F-Key	Name	Type	Description
	pk_identity	integer
	description	text
	l10n_description	text
	comment	text
	filename	text
	image_size	integer
	pk_identity_tag	integer
	pk_tag_image	integer
	xmin_identity_tag	xid

SELECT dit.fk_identity AS pk_identity
,
    rti.description
,
    _
(rti.description) AS l10n_description
,
    dit.comment
,
    rti.filename
,
    octet_length
(COALESCE
     (rti.image
           ,'\x'::bytea
     )
) AS image_size
,
    dit.pk AS pk_identity_tag
,
    rti.pk AS pk_tag_image
,
    dit.xmin AS xmin_identity_tag
   
FROM (dem.identity_tag dit
     
LEFT JOIN ref.tag_image rti 
    ON (
           (dit.fk_tag = rti.pk)
     )
);

Index - Schema dem

View: v_inbox_item_type

v_inbox_item_type Structure
F-Key	Name	Type	Description
	type	text
	l10n_type	text
	category	text
	l10n_category	text
	is_user_type	boolean
	is_user_category	boolean
	pk_type	integer
	pk_category	integer

SELECT it.description AS type
,
    _
(it.description) AS l10n_type
,
    ic.description AS category
,
    _
(ic.description) AS l10n_category
,
    it.is_user AS is_user_type
,
    ic.is_user AS is_user_category
,
    it.pk AS pk_type
,
    it.fk_inbox_item_category AS pk_category
   
FROM dem.inbox_item_type it
,
    dem.inbox_item_category ic
  
WHERE (it.fk_inbox_item_category = ic.pk);

Index - Schema dem

View: v_message_inbox

Denormalized messages for the providers and/or patients. Using UNION makes sure we get the right level of uniqueness.

v_message_inbox Structure
F-Key	Name	Type	Description
	received_when	timestamp with time zone
	modified_by	text
	provider	text
	importance	integer
	category	text
	l10n_category	text
	type	text
	l10n_type	text
	comment	text
	pk_context	integer[]
	data	text
	pk_inbox_message	integer
	pk_staff	integer
	pk_category	integer
	pk_type	integer
	pk_patient	integer
	is_virtual	boolean
	due_date	timestamp with time zone
	expiry_date	timestamp with time zone
	is_overdue	boolean
	is_expired	boolean
	interval_due	interval
	xmin_message_inbox	integer

(
         
SELECT mi.modified_when AS received_when
     ,
            COALESCE
     (
           (
            SELECT staff.short_alias
                   
              FROM dem.staff
                  
             WHERE (staff.db_user = mi.modified_by)
           )
           , (
                 ('<'::text || 
                       (mi.modified_by)::text
                 ) || '>'::text
           )
     ) AS modified_by
     ,
            
     (
      SELECT staff.short_alias
                   
        FROM dem.staff
                  
       WHERE (staff.pk = mi.fk_staff)
     ) AS provider
     ,
            mi.importance
     ,
            vit.category
     ,
            vit.l10n_category
     ,
            vit.type
     ,
            vit.l10n_type
     ,
            mi.comment
     ,
            mi.ufk_context AS pk_context
     ,
            mi.data
     ,
            mi.pk AS pk_inbox_message
     ,
            mi.fk_staff AS pk_staff
     ,
            vit.pk_category
     ,
            mi.fk_inbox_item_type AS pk_type
     ,
            mi.fk_patient AS pk_patient
     ,
            false AS is_virtual
     ,
            mi.due_date
     ,
            mi.expiry_date
     ,
                CASE
                    WHEN 
     (mi.due_date IS NULL) THEN false
                    WHEN 
     (mi.due_date > now
           ()
     ) THEN false
                    WHEN 
     (mi.expiry_date IS NULL) THEN true
                    WHEN 
     (mi.expiry_date < now
           ()
     ) THEN false
                    ELSE true
                END AS is_overdue
     ,
                CASE
                    WHEN 
     (mi.expiry_date IS NULL) THEN false
                    WHEN 
     (mi.expiry_date > now
           ()
     ) THEN false
                    ELSE true
                END AS is_expired
     ,
                CASE
                    WHEN 
     (mi.due_date IS NULL) THEN NULL::interval
                    WHEN 
     (mi.due_date > now
           ()
     ) THEN 
     (
           (mi.due_date)::timestamp with time zone - now
           ()
     )
                    ELSE 
     (now
           () - 
           (mi.due_date)::timestamp with time zone
     )
                END AS interval_due
     ,
            gm.xid2int
     (mi.xmin) AS xmin_message_inbox
           
  FROM dem.message_inbox mi
     ,
            dem.v_inbox_item_type vit
          
 WHERE (mi.fk_inbox_item_type = vit.pk_type)
        
UNION ALL
         
SELECT v_unreviewed_docs_inbox.received_when
     ,
            v_unreviewed_docs_inbox.modified_by
     ,
            v_unreviewed_docs_inbox.provider
     ,
            v_unreviewed_docs_inbox.importance
     ,
            v_unreviewed_docs_inbox.category
     ,
            v_unreviewed_docs_inbox.l10n_category
     ,
            v_unreviewed_docs_inbox.type
     ,
            v_unreviewed_docs_inbox.l10n_type
     ,
            v_unreviewed_docs_inbox.comment
     ,
            v_unreviewed_docs_inbox.pk_context
     ,
            v_unreviewed_docs_inbox.data
     ,
            v_unreviewed_docs_inbox.pk_inbox_message
     ,
            v_unreviewed_docs_inbox.pk_staff
     ,
            v_unreviewed_docs_inbox.pk_category
     ,
            v_unreviewed_docs_inbox.pk_type
     ,
            v_unreviewed_docs_inbox.pk_patient
     ,
            v_unreviewed_docs_inbox.is_virtual
     ,
            v_unreviewed_docs_inbox.due_date
     ,
            v_unreviewed_docs_inbox.expiry_date
     ,
            v_unreviewed_docs_inbox.is_overdue
     ,
            v_unreviewed_docs_inbox.is_expired
     ,
            v_unreviewed_docs_inbox.interval_due
     ,
            v_unreviewed_docs_inbox.xmin_message_inbox
           
  FROM blobs.v_unreviewed_docs_inbox

)
UNION
 
SELECT now
() AS received_when
,
    vtr.modified_by
,
    
(
SELECT staff.short_alias
           
  FROM dem.staff
          
 WHERE (staff.pk = vtr.pk_intended_reviewer)
) AS provider
,
    0 AS importance
,
    'clinical'::text AS category
,
    _
('clinical'::text) AS l10n_category
,
    'review results'::text AS type
,
    _
('review results'::text) AS l10n_type
,
    
(
SELECT (
           (
                 (
                       (
                             (_
                                   ('unreviewed (normal) results for patient'::text
                             ) || ' ['::text
                       ) || dn.lastnames
                 ) || 
              ', '::text
           ) || dn.firstnames
     ) || ']'::text
)
           
FROM dem.names dn
          
WHERE (
     (dn.id_identity = vtr.pk_patient)
   AND (dn.active IS TRUE)
)
)      AS comment
,     
    NULL::integer[] AS pk_context
,     
    NULL::text AS data
,     
    NULL::integer AS pk_inbox_message
,     
    vtr.pk_intended_reviewer AS pk_staff
,     
    
(     
SELECT v_inbox_item_type.pk_category
           
FROM dem.v_inbox_item_type
          
WHERE (v_inbox_item_type.type = 'review results'::text)
)      AS pk_category
,     
    
(     
SELECT v_inbox_item_type.pk_type
           
FROM dem.v_inbox_item_type
          
WHERE (v_inbox_item_type.type = 'review results'::text)
)      AS pk_type
,     
    vtr.pk_patient
,     
    true AS is_virtual
,     
    
(     now
() - '01:00:00'::interval
)      AS due_date
,     
    NULL::timestamp with time zone AS expiry_date
,     
    true AS is_overdue
,     
    false AS is_expired
,     
    '01:00:00'::interval AS interval_due
,     
    NULL::integer AS xmin_message_inbox
   
FROM   clin.v_test_results vtr
  
WHERE  (
(vtr.reviewed IS FALSE)
AND (
     (vtr.is_technically_abnormal IS FALSE)
    OR (
           (vtr.is_technically_abnormal IS NULL)
         AND (vtr.abnormality_indicator IS NULL)
     )
)
)     
UNION 
 
SELECT now
(     ) AS received_when
,     
    vtr.modified_by
,     
    
(     
SELECT staff.short_alias
           
FROM dem.staff
          
WHERE (staff.pk = vtr.pk_intended_reviewer)
)      AS provider
,     
    1 AS importance
,     
    'clinical'::text AS category
,     
    _
(     'clinical'::text) AS l10n_category
,     
    'review results'::text AS type
,     
    _
(     'review results'::text) AS l10n_type
,     
    
(     
SELECT (
     (
           (
                 (
                       (_
                             ('unreviewed (abnormal) results for patient'::text
                       ) || ' ['::text
                 ) || dn.lastnames
           ) || 
        ', '::text
     ) || dn.firstnames
) || ']'::text
)     
           
FROM   dem.names dn
          
WHERE  (
(dn.id_identity = vtr.pk_patient)
AND (dn.active IS TRUE)
)     
)            AS comment
,           
    NULL::integer[] AS pk_context
,           
    NULL::text AS data
,           
    NULL::integer AS pk_inbox_message
,           
    vtr.pk_intended_reviewer AS pk_staff
,           
    
(           
SELECT v_inbox_item_type.pk_category
           
FROM   dem.v_inbox_item_type
          
WHERE  (v_inbox_item_type.type = 'review results'::text)
)            AS pk_category
,           
    
(           
SELECT v_inbox_item_type.pk_type
           
FROM   dem.v_inbox_item_type
          
WHERE  (v_inbox_item_type.type = 'review results'::text)
)            AS pk_type
,           
    vtr.pk_patient
,           
    true AS is_virtual
,           
    
(           now
(     ) - '01:00:00'::interval
)            AS due_date
,           
    NULL::timestamp with time zone AS expiry_date
,           
    true AS is_overdue
,           
    false AS is_expired
,           
    '01:00:00'::interval AS interval_due
,           
    NULL::integer AS xmin_message_inbox
   
FROM         clin.v_test_results vtr
  
WHERE        (
(     vtr.reviewed IS FALSE)
AND    (
(vtr.is_technically_abnormal IS TRUE)
OR (
     (vtr.is_technically_abnormal IS NULL)
   AND (vtr.abnormality_indicator IS NOT NULL)
)
)     
)           ;

Index - Schema dem

View: v_org_unit_comms

denormalizes org units to communication channels

v_org_unit_comms Structure
F-Key	Name	Type	Description
	pk_org_unit	integer
	comm_type	text
	l10n_comm_type	text
	url	text
	comment	text
	is_confidential	boolean
	pk_lnk_org_unit2comm	integer
	pk_type	integer
	xmin_lnk_org_unit2comm	xid

SELECT d_lo2c.fk_org_unit AS pk_org_unit
,
    ect.description AS comm_type
,
    _
(ect.description) AS l10n_comm_type
,
    d_lo2c.url
,
    d_lo2c.comment
,
    d_lo2c.is_confidential
,
    d_lo2c.pk AS pk_lnk_org_unit2comm
,
    d_lo2c.fk_type AS pk_type
,
    d_lo2c.xmin AS xmin_lnk_org_unit2comm
   
FROM (dem.lnk_org_unit2comm d_lo2c
     
  JOIN dem.enum_comm_types ect 
    ON (
           (d_lo2c.fk_type = ect.pk)
     )
);

Index - Schema dem

View: v_org_units

v_org_units Structure
F-Key	Name	Type	Description
	pk_org_unit	integer
	organization	text
	unit	text
	organization_category	text
	l10n_organization_category	text
	unit_category	text
	l10n_unit_category	text
	is_praxis_branch	boolean
	pk_org	integer
	pk_category_org	integer
	pk_category_unit	integer
	pk_address	integer
	xmin_org_unit	xid

SELECT d_ou.pk AS pk_org_unit
,
    d_o.description AS organization
,
    d_ou.description AS unit
,
    d_oc_o.description AS organization_category
,
    _
(d_oc_o.description) AS l10n_organization_category
,
    d_oc_u.description AS unit_category
,
    _
(d_oc_u.description) AS l10n_unit_category
,
    
(EXISTS 
     (
      SELECT 1
           
        FROM dem.praxis_branch d_pb
          
       WHERE (d_pb.fk_org_unit = d_ou.pk)
     )
) AS is_praxis_branch
,
    d_o.pk AS pk_org
,
    d_o.fk_category AS pk_category_org
,
    d_ou.fk_category AS pk_category_unit
,
    d_ou.fk_address AS pk_address
,
    d_ou.xmin AS xmin_org_unit
   
FROM (
     (
           (dem.org_unit d_ou
     
              JOIN dem.org d_o 
                ON (
                       (d_o.pk = d_ou.fk_org)
                 )
           )
     
   LEFT JOIN dem.org_category d_oc_u 
          ON (
                 (d_ou.fk_category = d_oc_u.pk)
           )
     )
     
LEFT JOIN dem.org_category d_oc_o 
    ON (
           (d_o.fk_category = d_oc_o.pk)
     )
);

Index - Schema dem

View: v_org_units_no_praxis_check

v_org_units_no_praxis_check Structure
F-Key	Name	Type	Description
	pk_org_unit	integer
	organization	text
	unit	text
	organization_category	text
	l10n_organization_category	text
	unit_category	text
	l10n_unit_category	text
	pk_org	integer
	pk_category_org	integer
	pk_category_unit	integer
	pk_address	integer
	xmin_org_unit	xid

SELECT d_ou.pk AS pk_org_unit
,
    d_o.description AS organization
,
    d_ou.description AS unit
,
    d_oc_o.description AS organization_category
,
    _
(d_oc_o.description) AS l10n_organization_category
,
    d_oc_u.description AS unit_category
,
    _
(d_oc_u.description) AS l10n_unit_category
,
    d_o.pk AS pk_org
,
    d_o.fk_category AS pk_category_org
,
    d_ou.fk_category AS pk_category_unit
,
    d_ou.fk_address AS pk_address
,
    d_ou.xmin AS xmin_org_unit
   
FROM (
     (
           (dem.org_unit d_ou
     
              JOIN dem.org d_o 
                ON (
                       (d_o.pk = d_ou.fk_org)
                 )
           )
     
   LEFT JOIN dem.org_category d_oc_u 
          ON (
                 (d_ou.fk_category = d_oc_u.pk)
           )
     )
     
LEFT JOIN dem.org_category d_oc_o 
    ON (
           (d_o.fk_category = d_oc_o.pk)
     )
);

Index - Schema dem

View: v_orgs

v_orgs Structure
F-Key	Name	Type	Description
	pk_org	integer
	organization	text
	category	text
	l10n_category	text
	is_praxis	boolean
	pk_category_org	integer
	xmin_org	xid

SELECT d_o.pk AS pk_org
,
    d_o.description AS organization
,
    d_oc.description AS category
,
    _
(d_oc.description) AS l10n_category
,
    
(EXISTS 
     (
      SELECT 1
           
        FROM dem.praxis_branch d_pb
          
       WHERE (d_pb.fk_org_unit IN 
                 (
                  SELECT d_ou.pk
                   
                    FROM dem.org_unit d_ou
                  
                   WHERE (d_ou.fk_org = d_o.pk)
                 )
           )
     )
) AS is_praxis
,
    d_o.fk_category AS pk_category_org
,
    d_o.xmin AS xmin_org
   
FROM (dem.org d_o
     
LEFT JOIN dem.org_category d_oc 
    ON (
           (d_o.fk_category = d_oc.pk)
     )
);

Index - Schema dem

View: v_pat_addresses

denormalized addresses per person

v_pat_addresses Structure
F-Key	Name	Type	Description
	pk_identity	integer
	pk_address	integer
	address_type	text
	l10n_address_type	text
	title	text
	firstnames	text
	lastnames	text
	dob	timestamp with time zone
	gender	text
	l10n_gender	text
	preferred	text
	street	text
	postcode	text
	notes_street	text
	number	text
	subunit	text
	notes_subunit	text
	lat_lon_address	point
	postcode_street	text
	lat_lon_street	point
	suburb	text
	urb	text
	postcode_urb	text
	lat_lon_urb	point
	code_region	text
	region	text
	l10n_region	text
	code_country	character(2)
	country	text
	l10n_country	text
	country_deprecated	date
	pk_street	integer
	pk_urb	integer
	pk_region	integer
	pk_lnk_person_org_address	integer
	pk_address_type	integer
	xmin_lnk_person_org_address	xid

SELECT d_vp.pk_identity
,
    d_va.pk_address
,
    d_at.name AS address_type
,
    _
(d_at.name) AS l10n_address_type
,
    d_vp.title
,
    d_vp.firstnames
,
    d_vp.lastnames
,
    d_vp.dob
,
    d_vp.gender
,
    d_vp.l10n_gender
,
    d_vp.preferred
,
    d_va.street
,
    d_va.postcode
,
    d_va.notes_street
,
    d_va.number
,
    d_va.subunit
,
    d_va.notes_subunit
,
    d_va.lat_lon_address
,
    d_va.postcode_street
,
    d_va.lat_lon_street
,
    d_va.suburb
,
    d_va.urb
,
    d_va.postcode_urb
,
    d_va.lat_lon_urb
,
    d_va.code_region
,
    d_va.region
,
    d_va.l10n_region
,
    d_va.code_country
,
    d_va.country
,
    d_va.l10n_country
,
    d_va.country_deprecated
,
    d_va.pk_street
,
    d_va.pk_urb
,
    d_va.pk_region
,
    d_lpoa.id AS pk_lnk_person_org_address
,
    d_lpoa.id_type AS pk_address_type
,
    d_lpoa.xmin AS xmin_lnk_person_org_address
   
FROM dem.v_address d_va
,
    dem.lnk_person_org_address d_lpoa
,
    dem.v_all_persons d_vp
,
    dem.address_type d_at
  
WHERE (
     (d_lpoa.id_identity = d_vp.pk_identity)
   AND (d_lpoa.id_address = d_va.pk_address)
   AND (d_lpoa.id_type = d_at.id)
);

Index - Schema dem

View: v_person_comms

denormalizes persons to communications channels

v_person_comms Structure
F-Key	Name	Type	Description
	pk_identity	integer
	comm_type	text
	l10n_comm_type	text
	url	text
	is_confidential	boolean
	comment	text
	pk_lnk_identity2comm	integer
	pk_address	integer
	pk_type	integer
	xmin_lnk_identity2comm	xid

SELECT li2c.fk_identity AS pk_identity
,
    ect.description AS comm_type
,
    _
(ect.description) AS l10n_comm_type
,
    li2c.url
,
    li2c.is_confidential
,
    li2c.comment
,
    li2c.pk AS pk_lnk_identity2comm
,
    li2c.fk_address AS pk_address
,
    li2c.fk_type AS pk_type
,
    li2c.xmin AS xmin_lnk_identity2comm
   
FROM dem.lnk_identity2comm li2c
,
    dem.enum_comm_types ect
  
WHERE (li2c.fk_type = ect.pk);

Index - Schema dem

View: v_person_jobs

denormalizes the jobs a person has

v_person_jobs Structure
F-Key	Name	Type	Description
	pk_identity	integer
	occupation	text
	l10n_occupation	text
	activities	text
	modified_when	timestamp with time zone
	pk_occupation	integer
	pk_lnk_job2person	integer
	xmin_lnk_job2person	xid

SELECT lj2p.fk_identity AS pk_identity
,
    o.name AS occupation
,
    _
(o.name) AS l10n_occupation
,
    lj2p.activities
,
    lj2p.modified_when
,
    lj2p.fk_occupation AS pk_occupation
,
    lj2p.pk AS pk_lnk_job2person
,
    lj2p.xmin AS xmin_lnk_job2person
   
FROM (dem.lnk_job2person lj2p
     
  JOIN dem.occupation o 
    ON (
           (lj2p.fk_occupation = o.id)
     )
);

Index - Schema dem

View: v_person_names

v_person_names Structure
F-Key	Name	Type	Description
	pk_identity	integer
	active_name	boolean
	title	text
	firstnames	text
	lastnames	text
	preferred	text
	comment	text
	gender	text
	dob	timestamp with time zone
	tob	time without time zone
	dod	timestamp with time zone
	identity_deleted	boolean
	deceased	boolean
	pk_name	integer
	xmin_name	xid

SELECT dn.id_identity AS pk_identity
,
    dn.active AS active_name
,
    di.title
,
    dn.firstnames
,
    dn.lastnames
,
    dn.preferred
,
    dn.comment
,
    di.gender
,
    di.dob
,
    di.tob
,
    di.deceased AS dod
,
    di.deleted AS identity_deleted
,
    
(di.deceased IS NOT NULL) AS deceased
,
    dn.id AS pk_name
,
    dn.xmin AS xmin_name
   
FROM dem.names dn
,
    dem.identity di
  
WHERE (di.pk = dn.id_identity);

Index - Schema dem

View: v_persons

This view denormalizes persons with their active name.

v_persons Structure
F-Key	Name	Type	Description
	pk_identity	integer
	title	text
	firstnames	text
	preferred	text
	lastnames	text
	gender	text
	l10n_gender	text
	dob_only	timestamp with time zone
	dob	timestamp with time zone
	tob	time without time zone
	deceased	timestamp with time zone
	marital_status	text
	l10n_marital_status	text
	emergency_contact	text
	comment	text
	is_deleted	boolean
	pk_marital_status	integer
	pk_active_name	integer
	pk_emergency_contact	integer
	pk_primary_provider	integer
	xmin_identity	xid
	dob_is_estimated	boolean

SELECT i.pk AS pk_identity
,
    i.title
,
    n.firstnames
,
    n.preferred
,
    n.lastnames
,
    i.gender
,
    _
(i.gender) AS l10n_gender
,
    i.dob AS dob_only
,
    
(date_trunc
     ('day'::text
           , i.dob
     ) + 
     (COALESCE
           (i.tob
                 , (i.dob)::time without time zone
           )
     )::interval
) AS dob
,
    i.tob
,
    i.deceased
,
        CASE
            WHEN 
(i.fk_marital_status IS NULL) THEN 'unknown'::text
            ELSE 
(
SELECT ms.name
               
  FROM dem.marital_status ms
     ,
                dem.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'::text)
            ELSE 
(
SELECT _
     (ms1.name) AS _
               
  FROM dem.marital_status ms1
     ,
                dem.identity i1
              
 WHERE (
           (ms1.pk = i.fk_marital_status)
         AND (i1.pk = i.pk)
     )
)
        END AS l10n_marital_status
,
    i.emergency_contact
,
    i.comment
,
    i.deleted AS is_deleted
,
    i.fk_marital_status AS pk_marital_status
,
    n.id AS pk_active_name
,
    i.fk_emergency_contact AS pk_emergency_contact
,
    i.fk_primary_provider AS pk_primary_provider
,
    i.xmin AS xmin_identity
,
    i.dob_is_estimated
   
FROM dem.identity i
,
    dem.names n
  
WHERE (
     (n.active IS TRUE)
   AND (n.id_identity = i.pk)
);

Index - Schema dem

View: v_praxis_branches

Denormalized praxis branches with their praxis.

v_praxis_branches Structure
F-Key	Name	Type	Description
	pk_praxis_branch	integer
	branch	text
	pk_org_unit	integer
	pk_category_unit	integer
	pk_address	integer
	pk_org	integer
	xmin_praxis_branch	xid
	xmin_org_unit	xid
	praxis	text
	pk_category_org	integer
	organization_category	text
	l10n_organization_category	text
	unit_category	text
	l10n_unit_category	text

SELECT branches_w_orgs.pk_praxis_branch
,
    branches_w_orgs.branch
,
    branches_w_orgs.pk_org_unit
,
    branches_w_orgs.pk_category_unit
,
    branches_w_orgs.pk_address
,
    branches_w_orgs.pk_org
,
    branches_w_orgs.xmin_praxis_branch
,
    branches_w_orgs.xmin_org_unit
,
    branches_w_orgs.praxis
,
    branches_w_orgs.pk_category_org
,
    d_ocat.description AS organization_category
,
    _
(d_ocat.description) AS l10n_organization_category
,
    d_ucat.description AS unit_category
,
    _
(d_ucat.description) AS l10n_unit_category
   
FROM (
     (
           (
            SELECT branches_w_units.pk_praxis_branch
                 ,
            branches_w_units.branch
                 ,
            branches_w_units.pk_org_unit
                 ,
            branches_w_units.pk_category_unit
                 ,
            branches_w_units.pk_address
                 ,
            branches_w_units.pk_org
                 ,
            branches_w_units.xmin_praxis_branch
                 ,
            branches_w_units.xmin_org_unit
                 ,
            d_o.description AS praxis
                 ,
            d_o.fk_category AS pk_category_org
           
              FROM (
                       (
                        SELECT d_pb.pk AS pk_praxis_branch
                             ,
                    d_ou.description AS branch
                             ,
                    d_pb.fk_org_unit AS pk_org_unit
                             ,
                    d_ou.fk_category AS pk_category_unit
                             ,
                    d_ou.fk_address AS pk_address
                             ,
                    d_ou.fk_org AS pk_org
                             ,
                    d_pb.xmin AS xmin_praxis_branch
                             ,
                    d_ou.xmin AS xmin_org_unit
                   
                          FROM (dem.praxis_branch d_pb
                     
                                JOIN dem.org_unit d_ou 
                                  ON (
                                         (d_pb.fk_org_unit = d_ou.pk)
                                   )
                             )
                       ) branches_w_units
             
                    JOIN dem.org d_o 
                      ON (
                             (d_o.pk = branches_w_units.pk_org)
                       )
                 )
           ) branches_w_orgs
     
   LEFT JOIN dem.org_category d_ucat 
          ON (
                 (branches_w_orgs.pk_category_unit = d_ucat.pk)
           )
     )
     
LEFT JOIN dem.org_category d_ocat 
    ON (
           (branches_w_orgs.pk_category_org = d_ocat.pk)
     )
);

Index - Schema dem

View: v_region

denormalizes region information

v_region Structure
F-Key	Name	Type	Description
	pk_region	integer
	code_region	text
	region	text
	l10n_region	text
	code_country	character(2)
	country	text
	l10n_country	text
	country_deprecated	date
	xmin_region	xid

SELECT d_r.pk AS pk_region
,
    d_r.code AS code_region
,
    d_r.name AS region
,
    _
(d_r.name) AS l10n_region
,
    d_r.country AS code_country
,
    c.name AS country
,
    _
(c.name) AS l10n_country
,
    c.deprecated AS country_deprecated
,
    d_r.xmin AS xmin_region
   
FROM (dem.region d_r
     
LEFT JOIN dem.country c 
    ON (
           (d_r.country = c.code)
     )
);

Index - Schema dem

View: v_staff

Denormalized staff data.

v_staff Structure
F-Key	Name	Type	Description
	pk_identity	integer
	pk_staff	integer
	title	text
	firstnames	text
	lastnames	text
	short_alias	text
	role	text
	dob	timestamp with time zone
	gender	text
	db_user	name
	comment	text
	is_active	boolean
	person_is_deleted	boolean
	can_login	boolean
	xmin_staff	xid

SELECT d_vp.pk_identity
,
    d_s.pk AS pk_staff
,
    d_vp.title
,
    d_vp.firstnames
,
    d_vp.lastnames
,
    d_s.short_alias
,
        CASE
            WHEN 
(
SELECT (EXISTS 
           (
            SELECT 1
                       
              FROM pg_group
                      
             WHERE (
                       (pg_group.groname = 'gm-doctors'::name)
                     AND (
                             (
                              SELECT pg_user.usesysid
                               
                                FROM pg_user
                              
                               WHERE (pg_user.usename = d_s.db_user)
                             ) = ANY 
                             (pg_group.grolist)
                       )
                 )
           )
     ) AS "exists"
) THEN 'full clinical access'::text
            WHEN 
(
SELECT (EXISTS 
           (
            SELECT 1
                       
              FROM pg_group
                      
             WHERE (
                       (pg_group.groname = 'gm-nurses'::name)
                     AND (
                             (
                              SELECT pg_user.usesysid
                               
                                FROM pg_user
                              
                               WHERE (pg_user.usename = d_s.db_user)
                             ) = ANY 
                             (pg_group.grolist)
                       )
                 )
           )
     ) AS "exists"
) THEN 'limited clinical access'::text
            WHEN 
(
SELECT (EXISTS 
           (
            SELECT 1
                       
              FROM pg_group
                      
             WHERE (
                       (pg_group.groname = 'gm-staff'::name)
                     AND (
                             (
                              SELECT pg_user.usesysid
                               
                                FROM pg_user
                              
                               WHERE (pg_user.usename = d_s.db_user)
                             ) = ANY 
                             (pg_group.grolist)
                       )
                 )
           )
     ) AS "exists"
) THEN 'non-clinical access'::text
            WHEN 
(
SELECT (EXISTS 
           (
            SELECT 1
                       
              FROM pg_group
                      
             WHERE (
                       (pg_group.groname = 'gm-public'::name)
                     AND (
                             (
                              SELECT pg_user.usesysid
                               
                                FROM pg_user
                              
                               WHERE (pg_user.usename = d_s.db_user)
                             ) = ANY 
                             (pg_group.grolist)
                       )
                 )
           )
     ) AS "exists"
) THEN 'public access'::text
            ELSE NULL::text
        END AS role
,
    d_vp.dob
,
    d_vp.gender
,
    d_s.db_user
,
    d_s.comment
,
    d_s.is_active
,
    d_vp.is_deleted AS person_is_deleted
,
    
(
SELECT (
           (
            SELECT (EXISTS 
                       (
                        SELECT 1
                           
                          FROM pg_group
                          
                         WHERE (
                                   (
                                         (
                                          SELECT pg_user.usesysid
                                   
                                            FROM pg_user
                                  
                                           WHERE (pg_user.usename = d_s.db_user)
                                         ) = ANY 
                                         (pg_group.grolist)
                                   )
                                 AND (pg_group.groname = current_database
                                         ()
                                   )
                             )
                       )
                 ) AS "exists"
           )
         AND (
            SELECT (EXISTS 
                       (
                        SELECT 1
                           
                          FROM pg_group
                          
                         WHERE (
                                   (
                                         (
                                          SELECT pg_user.usesysid
                                   
                                            FROM pg_user
                                  
                                           WHERE (pg_user.usename = d_s.db_user)
                                         ) = ANY 
                                         (pg_group.grolist)
                                   )
                                 AND (pg_group.groname = 'gm-logins'::name)
                             )
                       )
                 ) AS "exists"
           )
     )
) AS can_login
,
    d_s.xmin AS xmin_staff
   
FROM (dem.staff d_s
     
  JOIN dem.v_all_persons d_vp 
    ON (
           (d_s.fk_identity = d_vp.pk_identity)
     )
);

Index - Schema dem

View: v_street

denormalizes street data

v_street Structure
F-Key	Name	Type	Description
	pk_street	integer
	street	text
	postcode	text
	postcode_street	text
	lat_lon_street	point
	suburb	text
	urb	text
	postcode_urb	text
	lat_lon_urb	point
	code_region	text
	region	text
	l10n_region	text
	code_country	character(2)
	country	text
	l10n_country	text
	country_deprecated	date
	pk_urb	integer
	pk_region	integer
	xmin_street	xid

SELECT d_st.id AS pk_street
,
    d_st.name AS street
,
    COALESCE
(d_st.postcode
     , d_vu.postcode_urb
) AS postcode
,
    d_st.postcode AS postcode_street
,
    d_st.lat_lon AS lat_lon_street
,
    d_st.suburb
,
    d_vu.urb
,
    d_vu.postcode_urb
,
    d_vu.lat_lon_urb
,
    d_vu.code_region
,
    d_vu.region
,
    d_vu.l10n_region
,
    d_vu.code_country
,
    d_vu.country
,
    d_vu.l10n_country
,
    d_vu.country_deprecated
,
    d_st.id_urb AS pk_urb
,
    d_vu.pk_region
,
    d_st.xmin AS xmin_street
   
FROM (dem.street d_st
     
LEFT JOIN dem.v_urb d_vu 
    ON (
           (d_st.id_urb = d_vu.pk_urb)
     )
);

Index - Schema dem

View: v_uniq_zipped_urbs

convenience view that selects urbs which: - have a zip code - are not referenced in table "street" with that zip code

v_uniq_zipped_urbs Structure
F-Key	Name	Type	Description
	postcode	text
	name	text
	region	text
	code_region	text
	country	text
	l10n_country	text
	code_country	character(2)

SELECT d_u.postcode
,
    d_u.name
,
    d_r.name AS region
,
    d_r.code AS code_region
,
    d_c.name AS country
,
    _
(d_c.name) AS l10n_country
,
    d_r.country AS code_country
   
FROM dem.urb d_u
,
    dem.region d_r
,
    dem.country d_c
  
WHERE (
     (d_u.postcode IS NOT NULL)
   AND (NOT 
           (EXISTS 
                 (
                  SELECT 1
           
                    FROM dem.v_zip2street d_vz2str
          
                   WHERE (
                             (d_vz2str.postcode = d_u.postcode)
                           AND (d_vz2str.urb = d_u.name)
                       )
                 )
           )
     )
   AND (d_u.fk_region = d_r.pk)
   AND (d_r.country = d_c.code)
);

Index - Schema dem

View: v_urb

denormalizes urb data

v_urb Structure
F-Key	Name	Type	Description
	pk_urb	integer
	urb	text
	postcode_urb	text
	lat_lon_urb	point
	code_region	text
	region	text
	l10n_region	text
	code_country	character(2)
	country	text
	l10n_country	text
	country_deprecated	date
	pk_region	integer
	xmin_urb	xid

SELECT d_u.id AS pk_urb
,
    d_u.name AS urb
,
    d_u.postcode AS postcode_urb
,
    d_u.lat_lon AS lat_lon_urb
,
    d_vr.code_region
,
    d_vr.region
,
    d_vr.l10n_region
,
    d_vr.code_country
,
    d_vr.country
,
    d_vr.l10n_country
,
    d_vr.country_deprecated
,
    d_u.fk_region AS pk_region
,
    d_u.xmin AS xmin_urb
   
FROM (dem.urb d_u
     
LEFT JOIN dem.v_region d_vr 
    ON (
           (d_vr.pk_region = d_u.fk_region)
     )
);

Index - Schema dem

View: v_zip2data

aggregates nearly all known data per zip code

v_zip2data Structure
F-Key	Name	Type	Description
	zip	text
	street	text
	suburb	text
	urb	text
	region	text
	code_region	text
	country	text
	l10n_country	text
	code_country	character(2)

SELECT d_vz2s.postcode AS zip
,
    d_vz2s.street
,
    d_vz2s.suburb
,
    d_vz2s.urb
,
    d_vz2s.region
,
    d_vz2s.code_region
,
    d_vz2s.country
,
    d_vz2s.l10n_country
,
    d_vz2s.code_country
   
FROM dem.v_zip2street d_vz2s

UNION
 
SELECT d_vuzu.postcode AS zip
,
    NULL::text AS street
,
    NULL::text AS suburb
,
    d_vuzu.name AS urb
,
    d_vuzu.region
,
    d_vuzu.code_region
,
    d_vuzu.country
,
    d_vuzu.l10n_country
,
    d_vuzu.code_country
   
FROM dem.v_uniq_zipped_urbs d_vuzu;

Index - Schema dem

View: v_zip2street

list known data for streets that have a zip code

v_zip2street Structure
F-Key	Name	Type	Description
	postcode	text
	street	text
	suburb	text
	region	text
	code_region	text
	urb	text
	country	text
	l10n_country	text
	code_country	character(2)

SELECT COALESCE
(d_str.postcode
     , d_u.postcode
) AS postcode
,
    d_str.name AS street
,
    d_str.suburb
,
    d_r.name AS region
,
    d_r.code AS code_region
,
    d_u.name AS urb
,
    d_c.name AS country
,
    _
(d_c.name) AS l10n_country
,
    d_r.country AS code_country
   
FROM dem.street d_str
,
    dem.urb d_u
,
    dem.region d_r
,
    dem.country d_c
  
WHERE (
     (d_str.postcode IS NOT NULL)
   AND (d_str.id_urb = d_u.id)
   AND (d_u.fk_region = d_r.pk)
   AND (d_r.country = d_c.code)
);

Index - Schema dem

View: v_zip2urb

list known data for urbs that have a zip code

v_zip2urb Structure
F-Key	Name	Type	Description
	postcode	text
	urb	text
	region	text
	code_region	text
	country	text
	code_country	character(2)

SELECT d_u.postcode
,
    d_u.name AS urb
,
    d_r.name AS region
,
    d_r.code AS code_region
,
    _
(d_c.name) AS country
,
    d_r.country AS code_country
   
FROM dem.urb d_u
,
    dem.region d_r
,
    dem.country d_c
  
WHERE (
     (d_u.postcode IS NOT NULL)
   AND (d_u.fk_region = d_r.pk)
   AND (d_r.country = d_c.code)
);

Index - Schema dem

Function: add_external_id_type(text, text)

Returns: integer

Language: PLPGSQL

Add an external ID type if it does not exist yet. This implementation is prone to concurrency issues.

declare
	_name alias for $1;
	_issuer alias for $2;
	_pk int;
begin
	select pk into _pk from dem.enum_ext_id_types where name = _name and issuer = _issuer;
	if FOUND then
		return _pk;
	end if;
	insert into dem.enum_ext_id_types(name, issuer) values (_name, _issuer);
	select currval(pg_get_serial_sequence('dem.enum_ext_id_types', 'pk')) into _pk;
	return _pk;
end;

Function: add_name(integer, text, text, boolean)

Returns: integer

Language: PLPGSQL

DECLARE
	_id_identity alias for $1;
	_first alias for $2;
	_last alias for $3;
	_active alias for $4;
	_id integer;
BEGIN
    -- deactivate all the existing names if this name is to become active
	if _active then
		update dem.names set active = false where id_identity = _id_identity;
	end if;
	-- name already there for this identity ?
	select into _id id from dem.names where id_identity = _id_identity and firstnames = _first and lastnames = _last;
	if FOUND then
		update dem.names set active = _active where id = _id;
		return _id;
	end if;
	-- no, insert new name
	insert into dem.names (id_identity, firstnames, lastnames, active) values (_id_identity, _first, _last, _active);
	if FOUND then
		return currval('dem.names_id_seq');
	end if;
	return NULL;
END;

Function: address_exists(text, text, text, text, text, text, text)

Returns: integer

Language: PLPGSQL

This function checks whether a given address exists in the database and returns the primary key if found. It takes the following parameters: country code, region code, urb (location), postcode, street, number, subunit (can be NULL)

DECLARE
	_code_country alias for $1;
	_code_region alias for $2;
	_urb alias for $3;
	_postcode alias for $4;
	_street alias for $5;
	_number alias for $6;
	_subunit alias for $7;
	__subunit text;
	_pk_address integer;
	msg text;
BEGIN
	if (_code_country || _code_region || _urb || _postcode || _street || _number) is NULL then
		msg := '[dem.address_exists]: insufficient or invalid address definition: '
			|| 'country code <' || coalesce(_code_country, 'NULL') || '>, '
			|| 'region code <' || coalesce(_code_region, 'NULL') || '>, '
			|| 'urb <' || coalesce(_urb, 'NULL') || '>, '
			|| 'zip <' || coalesce(_postcode, 'NULL') || '>, '
			|| 'street <' || coalesce(_street, 'NULL') || '>, '
			|| 'number <' || coalesce(_number, 'NULL') || '>'
		;
		raise exception '%', msg;
	end if;
	__subunit := nullif(trim(_subunit), '');
	if __subunit is null then
		select
			pk_address into _pk_address
		from
			dem.v_address
		where
			code_country = trim(_code_country)
				and
			code_region = trim(_code_region)
				and
			urb = trim(_urb)
				and
			postcode = trim(_postcode)
				and
			street = trim(_street)
				and
			number = trim(_number)
				and
			subunit is null;
	else
		select
			pk_address into _pk_address
		from
			dem.v_address
		where
			code_country = trim(_code_country)
				and
			code_region = trim(_code_region)
				and
			urb = trim(_urb)
				and
			postcode = trim(_postcode)
				and
			street = trim(_street)
				and
			number = trim(_number)
				and
			subunit = __subunit;
	end if;
	return _pk_address;
END;

Function: create_address(text, text, text, text, text, text, text)

Returns: integer

Language: PLPGSQL

This function creates an address. It first checks whether the address already exists. It takes the following parameters: number, street, postcode, urb (location), region code, country code, subunit (can be NULL) If the country or the region do not exist in the database, the function fails.

DECLARE
	_number ALIAS FOR $1;
	_street ALIAS FOR $2;
	_postcode ALIAS FOR $3;
	_urb ALIAS FOR $4;
	_region_code ALIAS FOR $5;
	_country_code ALIAS FOR $6;
	_subunit alias for $7;
	_street_id integer;
	_pk_address integer;
	__subunit text;
	msg text;
BEGIN
	select into _pk_address dem.address_exists (
		_country_code,
		_region_code,
		_urb,
		_postcode,
		_street,
		_number,
		_subunit
	);
	if _pk_address is not null then
		return _pk_address;
	end if;
	-- this either creates dem.street and possible dem.urb rows or
	-- or else it fails (because region and/or country do not exist)
	select into _street_id dem.create_street(_street, _postcode, _urb, _region_code, _country_code);
	-- create address
	__subunit := nullif(trim(_subunit), '');
	insert into dem.address (
		number,
		id_street,
		subunit
	) values (
		_number,
		_street_id,
		__subunit
	)
	returning id
	into _pk_address;
	return _pk_address;
END;

Function: create_comm_type(text)

Returns: integer

Language: PLPGSQL

DECLARE
	_description alias for $1;
	_pk_type integer;
BEGIN
	select pk into _pk_type from dem.enum_comm_types where _(description) = _description;
	if FOUND then
		return _pk_type;
	end if;
	select pk into _pk_type from dem.enum_comm_types where description = _description;
	if FOUND then
		return _pk_type;
	end if;
	insert into dem.enum_comm_types(description) values (_description);
	select currval(pg_get_serial_sequence('dem.enum_comm_types', 'pk')) into _pk_type;
	return _pk_type;
END;

Function: create_occupation(text)

Returns: integer

Language: PLPGSQL

DECLARE
	_job alias for $1;
	_id integer;
BEGIN
	select into _id id from dem.occupation where name = _job;
	if FOUND then
		return _id;
	end if;
	insert into dem.occupation (name) values (_job);
	return currval('dem.occupation_id_seq');
END;

Function: create_street(text, text, text, text, text)

Returns: integer

Language: PLPGSQL

This function takes a parameters the name of the street, the postal code, the name of the urb, the postcode of the urb, the code of the state and the code of the country. If the country or the state does not exists in the tables, the function fails. At first, both the urb and street are tried to be retrieved according to the supplied information. If the fields do not match exactly an existing row, a new urb is created or a new street is created and returned.

DECLARE
	_street ALIAS FOR $1;
	_postcode ALIAS FOR $2;
	_urb ALIAS FOR $3;
	_state_code ALIAS FOR $4;
	_country_code ALIAS FOR $5;
	_urb_id integer;
	_street_id integer;
	msg text;
BEGIN
	-- create/get urb
	SELECT INTO _urb_id dem.create_urb(_urb, _postcode, _state_code, _country_code);
	-- create/get and return street
	SELECT INTO _street_id s.id from dem.street s WHERE s.name ILIKE _street AND s.id_urb = _urb_id AND postcode ILIKE _postcode;
	IF FOUND THEN
		RETURN _street_id;
	END IF;
	INSERT INTO dem.street (name, postcode, id_urb) VALUES (_street, _postcode, _urb_id);
	RETURN currval('dem.street_id_seq');
END;

Function: create_urb(text, text, text, text)

Returns: integer

Language: PLPGSQL

This function takes a parameters the name of the urb,\n the postcode of the urb, the name of the region and the\n name of the country.\n If the country or the region does not exists in the tables,\n the function fails.\n At first, the urb is tried to be retrieved according to the\n supplied information. If the fields do not match exactly an\n existing row, a new urb is created and returned.

DECLARE
	_urb ALIAS FOR $1;
	_urb_postcode ALIAS FOR $2;	
	_region_code ALIAS FOR $3;
	_country_code ALIAS FOR $4;
 	_region_pk integer;
	_urb_id integer;
	msg text;
BEGIN
 	-- get region
 	SELECT INTO _region_pk d_r.pk from dem.region d_r WHERE d_r.code = _region_code and d_r.country = _country_code;
 	IF NOT FOUND THEN
		msg := 'combination of region + country not registered ['
			||   'country:' || coalesce(_country_code, 'NULL')
			||  ', region:' || coalesce(_region_code, 'NULL')
			||     ', urb:' || coalesce(_urb, 'NULL')
			|| ', urb_zip:' || coalesce(_urb_postcode, 'NULL')
			|| ']';
		RAISE EXCEPTION '=> %', msg;
 	END IF;
	-- get/create and return urb
	SELECT INTO _urb_id u.id from dem.urb u WHERE u.name ILIKE _urb AND u.fk_region = _region_pk;
	IF FOUND THEN
		RETURN _urb_id;
	END IF;
	INSERT INTO dem.urb (name, postcode, fk_region) VALUES (_urb, _urb_postcode, _region_pk);
	RETURN currval('dem.urb_id_seq');
END;

Function: date_trunc_utc(text, timestamp with time zone)

Returns: timestamp without time zone

Language: SQL

date_trunc() is not immutable because it depends on the timezone setting, hence need to use this in index creation, but also need to use it in queries which want to use that index, so make it generally available as a function

select date_trunc($1, $2 at time zone 'UTC');

Function: dob_is_in_range(timestamp with time zone, interval, interval)

Returns: boolean

Language: SQL

select
	($1 - (extract(year from $1) * '1 year'::interval)) -
	(now() - (extract(year from now()) * '1 year'::interval))
	between (-1 * $2) and $3

Function: f_always_active_name()

Returns: trigger

Language: PLPGSQL

BEGIN
	if NEW.active = false then
		raise exception 'Cannot delete/disable active name. Another name must be activated first.';
		return OLD;
	end if;
	return NEW;
END;

Function: f_delete_names()

Returns: trigger

Language: PLPGSQL

DECLARE
BEGIN
	DELETE from dem.names WHERE id_identity=OLD.id;
	RETURN OLD;
END;

Function: f_uniq_active_name()

Returns: trigger

Language: PLPGSQL

DECLARE
--	tmp text;
BEGIN
--	tmp := 'identity:' || NEW.id_identity || ',id:' || NEW.id || ',name:' || NEW.firstnames || ' ' || NEW.lastnames;
--	raise notice 'uniq_active_name: [%]', tmp;
	if NEW.active = true then
		update dem.names set active = false
		where
			id_identity = NEW.id_identity
				and
			active = true;
	end if;
	return NEW;
END;

Function: gm_upd_default_regions()

Returns: boolean

Language: PLPGSQL

declare
	_region_code text;
	_region_name text;
	_country_row record;
begin
	_region_code := '??';
	_region_name := 'state/territory/province/region not available';
	-- add default region to countries needing one
	for _country_row in
		select distinct code from dem.country
		where code not in (
			select country from dem.region where code = _region_code
		)
	loop
		raise notice 'adding default region for [%]', _country_row.code;
		execute 'insert into dem.region (code, country, name) values ('
				|| quote_literal(_region_code) || ', '
				|| quote_literal(_country_row.code) || ', '
				|| quote_literal(_region_name) || ');';
	end loop;
	return true;
end;

Function: remove_person(integer)

Returns: boolean

Language: PLPGSQL

Fully remove a person from the system - except everything is still in the audit tables ;-)

DECLARE
	_pk_identity alias for $1;
BEGIN
	-- does person exist ?
	perform 1 from dem.identity where pk = _pk_identity;
	if not FOUND then
		raise notice 'dem.remove_person(): dem.identity.pk=(%) does not exist, not removing', _pk_identity;
		return false;
	end if;
	-- we cannot just get the child tables of clin.clin_root_item and
	-- delete from them since they are inter-dependent and may require
	-- a particular order of deletion, so let us do that explicitely:
	DELETE FROM clin.clin_hx_family WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.vaccination WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.allergy WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.allergy_state WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.clin_diag WHERE fk_narrative IN (
		SELECT pk FROM clin.clin_narrative WHERE fk_encounter IN (
			select pk from clin.encounter where fk_patient = _pk_identity
		)
	);
	DELETE FROM clin.test_result WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.lab_request WHERE fk_encounter in (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.substance_intake WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.procedure WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.clin_narrative WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM blobs.doc_med WHERE fk_encounter IN (
		select pk from clin.encounter where fk_patient = _pk_identity
	);
	-- now that we have deleted all the clinical data let us
	-- delete the EMR structural items as well:
	DELETE FROM clin.episode WHERE fk_encounter IN (
	    select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.health_issue WHERE fk_encounter IN (
	    select pk from clin.encounter where fk_patient = _pk_identity
	);
	DELETE FROM clin.encounter WHERE fk_patient = _pk_identity;
	-- delete demographics details:
	DELETE FROM dem.identity_tag where fk_identity = _pk_identity;
	DELETE FROM dem.names WHERE id_identity = _pk_identity;
	-- eventually delete the identity itself which does
	-- not go down without some twisting of arms:
	ALTER TABLE dem.identity disable rule r_del_identity;
	DELETE FROM dem.identity WHERE pk = _pk_identity;
	ALTER TABLE dem.identity enable rule r_del_identity;
	return true;
END;

Function: set_nickname(integer, text)

Returns: integer

Language: PLPGSQL

Setting the nickname only makes sense for the currently active name. However, we also want to keep track of previous nicknames. Hence we would set the nickname right in the active name if it is NULL. It it contains a previous nickname (eg IS NOT NULL) we will inactivate the currently active name and copy it into a new active name but with the nickname set to the new one. Unsetting works the same (IOW *setting* to NULL).

DECLARE
	_id_identity alias for $1;
	_nick alias for $2;
	_names_row record;
	msg text;
BEGIN
	-- 0.1: Just always set the nickname inside the active name
	-- post 0.1: openEHR-like (name: pk, fk_identity, name, fk_type, comment, is_legal, is_active ...)
	-- does name exist ?
	select into _names_row * from dem.names where id_identity = _id_identity and active = true;
	if not found then
		msg := 'Cannot set nickname [' || _nick || ']. No active <names> row with id_identity [' || _id_identity || '] found.';
		raise exception '%', msg;
	end if;
	update dem.names set preferred = _nick where id = _names_row.id;
	return _names_row.id;
END;

Function: trf_normalize_time_in_dob()

Returns: trigger

Language: PLPGSQL

BEGIN
	if NEW.dob is NULL then
		return NEW;
	end if;
	NEW.dob = date_trunc('day', NEW.dob) + '11 hours 11 minutes 11 seconds 111 milliseconds'::interval;
	return NEW;
END;

Function: trf_null_empty_title()

Returns: trigger

Language: PLPGSQL

begin
	if (NEW.title is null) then
		return NEW;
	end if;
	if trim(NEW.title) <> '' then
		return NEW;
	end if;
	NEW.title := NULL;
	return NEW;
end;

Function: trf_prevent_deletion_of_in_use_staff()

Returns: trigger

Language: PLPGSQL

this function is used to prevent DELETEs of staff members which had been used to store data

BEGIN
	-- does any audited table contain the dem.staff.db_user we are about to delete ?
	PERFORM 1 FROM audit.audit_fields WHERE modified_by = OLD.db_user LIMIT 1;
	IF FOUND THEN
		RAISE EXCEPTION
			'DELETE from dem.staff: Sanity check failed. User <%> is referenced from <.modified_by> of at least one audited table.', OLD.db_user
			USING ERRCODE = 'foreign_key_violation'
		;
		RETURN NULL;
	END IF;
	-- does any audit table contain the dem.staff.db_user we are about to delete ?
	PERFORM 1 FROM audit.audit_trail WHERE orig_by = OLD.db_user OR audit_by = OLD.db_user LIMIT 1;
	IF FOUND THEN
		RAISE EXCEPTION
			'DELETE from dem.staff: Sanity check failed. User <%> is referenced from <.orig_by> or <.audit_by> of at least one audit table.', OLD.db_user
			USING ERRCODE = 'foreign_key_violation'
		;
		RETURN NULL;
	END IF;
	RETURN OLD;
END;

Function: trf_prevent_multiple_praxi()

Returns: trigger

Language: PLPGSQL

Prevent praxis branches to be defined for more than one dem.org.

DECLARE
	_branch_count integer;
	_pk_org_existing integer;
	_pk_org_prospective integer;
	_msg text;
BEGIN
	select count(1) into _branch_count from dem.praxis_branch;
	if TG_OP = 'INSERT' then
		-- first branch ever
		if _branch_count = 0 then
			return NEW;
		end if;
	end if;
	if TG_OP = 'UPDATE' then
		-- only one branch (which is being updated)
		if _branch_count = 1 then
			return NEW;
		end if;
	end if;
	-- now we have either got an INSERT with at least
	-- one praxis_branch already existing or we are
	-- UPDATEing one of several existing branches
	SELECT fk_org INTO _pk_org_existing FROM dem.org_unit
	WHERE pk = (SELECT fk_org_unit FROM dem.praxis_branch LIMIT 1);
	SELECT fk_org INTO _pk_org_prospective
	FROM dem.org_unit WHERE pk = NEW.fk_org_unit;
	if _pk_org_prospective = _pk_org_existing then
		return NEW;
	end if;
	_msg := '[dem.trf_prevent_multiple_praxi] '
			|| TG_OP || ': '
			|| 'Existing praxis branches (dem.praxis_branch.fk_org->dem.org_unit.pk->dem.org_unit.fk_org) belong to an org with dem.org.pk='
			|| _pk_org_existing
			|| '. Cannot link praxis branch (dem.praxis_branch.fk_org=dem.org_unit.pk='
			|| NEW.fk_org_unit
			||') to a different org (dem.org.pk='
			|| _pk_org_prospective
			||'). There can only be one praxis (=dem.org) per database.';
	raise exception unique_violation using message = _msg;
	return NULL;
END;

Function: trf_protect_active_name_of_person()

Returns: trigger

Language: PLPGSQL

A person must have at least one, active, name record.

DECLARE
	_name_count integer;
	_msg text;
BEGIN
	-- how many names are there for the identity at the end of the Tx ?
	select count(1) into _name_count
	from dem.names
	where
		id_identity = OLD.id_identity
			and
		active is true
	;
	-- less than one name ?
	if _name_count < 1 then
		_msg := 'person ' || OLD.id_identity || ' must have at least one, active, name entry';
		raise exception check_violation using message = _msg;
		return OLD;
	end if;
	return OLD;
END;

Function: trf_sane_identity_comment()

Returns: trigger

Language: PLPGSQL

Ensures unique(identity.dob, names.firstnames, names.lastnames, identity.comment)

DECLARE
	_identity_row record;
	_names_row record;
	_other_identities integer[];
BEGIN
	-- working on dem.identity
	if TG_TABLE_NAME = 'identity' then
		-- UPDATEs ...
		if TG_OP = 'UPDATE' then
			-- ... which do NOT change .comment ...
			if NEW.comment IS NOT DISTINCT FROM OLD.comment then
				-- ... are safe because they were successfully INSERTed before
				return NEW;
			end if;
		end if;
		-- but INSERTs need checking
		_identity_row := NEW;
		select * into _names_row from dem.names where id_identity = NEW.pk;
	-- working on dem.names
	else
		select * into _identity_row from dem.identity where pk = NEW.id_identity;
		_names_row := NEW;
	end if;
	-- there cannot be any combination of identical
	-- (dob, firstname, lastname, identity.comment)
	-- so, look for clashing rows
	SELECT array_agg(pk_identity) INTO _other_identities FROM
		dem.v_person_names d_vpn
			join dem.identity d_i on (d_i.pk = d_vpn.pk_identity)
	WHERE
		-- same firstname
		d_vpn.firstnames = _names_row.firstnames
			AND
		-- same lastname
		d_vpn.lastnames = _names_row.lastnames
			AND
		-- same gender
		d_i.gender is not distinct from _identity_row.gender
			AND
		-- same dob (day)
		date_trunc('day', d_i.dob) is not distinct from date_trunc('day', _identity_row.dob)
			AND
		-- same discriminator
		d_i.comment is not distinct from _identity_row.comment
			AND
		-- but not the currently updated or inserted row
		d_i.pk != _identity_row.pk
	;
	if coalesce(array_length(_other_identities, 1), 0) > 0 then
		RAISE EXCEPTION
			'% on %.%: More than one person with (firstnames=%), (lastnames=%), (dob=%), (comment=%): % & %',
				TG_OP,
				TG_TABLE_SCHEMA,
				TG_TABLE_NAME,
				_names_row.firstnames,
				_names_row.lastnames,
				_identity_row.dob,
				_identity_row.comment,
				_identity_row.pk,
				_other_identities
			USING ERRCODE = 'unique_violation'
		;
		RETURN NULL;
	end if;
	return NEW;
END;

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict

Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))
dem_identity_tag_sane_comment	CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE))

Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))
dem_lnk_unit2comm_sane_comment	CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE))
lnk_org_unit2comm_sane_url	CHECK ((gm.is_null_or_blank_string(url) IS FALSE))

Name	Constraint
audit_audit_fields_sane_modified_when	CHECK (((modified_when <= clock_timestamp()) IS TRUE))
lnk_org_unit2ext_id_sane_comment	CHECK ((gm.is_null_or_non_empty_string(comment) IS TRUE))
lnk_org_unit2ext_id_sane_id	CHECK ((gm.is_null_or_blank_string(external_id) IS FALSE))

Index of database - gnumed_v21

Table: address

Table: address_type

Table: country

Table: enum_comm_types

Table: enum_ext_id_types

Table: gender_label

Table: identity

Table: identity_tag

Table: inbox_item_category

Table: inbox_item_type

Table: lnk_identity2comm

Table: lnk_identity2ext_id

Table: lnk_job2person

Table: lnk_org_unit2comm

Table: lnk_org_unit2ext_id

View: lnk_person2address

Table: lnk_person2relative

Table: lnk_person_org_address

Table: marital_status

Table: message_inbox

Table: name_gender_map

Table: names

Table: occupation

Table: org

Table: org_category

Table: org_unit

Table: praxis_branch

Table: region

Table: relation_types

Table: staff

Table: street

Table: urb

View: v_active_persons

View: v_address

View: v_all_persons

View: v_basic_address

View: v_deleted_persons

View: v_external_ids4identity

View: v_external_ids4org_unit

View: v_gender_labels

View: v_identity_tags

View: v_inbox_item_type

View: v_message_inbox

View: v_org_unit_comms

View: v_org_units

View: v_org_units_no_praxis_check

View: v_orgs

View: v_pat_addresses

View: v_person_comms

View: v_person_jobs

View: v_person_names

View: v_persons

View: v_praxis_branches

View: v_region

View: v_staff

View: v_street

View: v_uniq_zipped_urbs

View: v_urb

View: v_zip2data

View: v_zip2street

View: v_zip2urb

Function: add_external_id_type(text, text)

Returns: integer

Language: PLPGSQL

Function: add_name(integer, text, text, boolean)

Returns: integer

Language: PLPGSQL

Function: address_exists(text, text, text, text, text, text, text)

Returns: integer

Language: PLPGSQL

Function: create_address(text, text, text, text, text, text, text)

Returns: integer

Language: PLPGSQL

Function: create_comm_type(text)

Returns: integer

Language: PLPGSQL

Function: create_occupation(text)

Returns: integer

Language: PLPGSQL