Postgres
Along with the tables and views described below, there are custom datatypes and rules in use.
The Schema is INCOMPLETE, EVOLVING AND SUBJECT TO CHANGE
Note
to generate this output, use the following command
postgresql_autodoc -h 127.0.0.1 -u openmolar –password=PASSWORD -d openmolar_demo -t html
replacing user and password fields as appropriate
Index for openmolar_demo
Dumped on 2012-08-10
address_link Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
address_cat
address_type
NOT NULL
DEFAULT 'home'::address_type
patients.ix
patient_id
integer
NOT NULL
addresses.ix
address_id
integer
NOT NULL
from_date
date
NOT NULL
DEFAULT ('now'::text)::date
to_date
date
mailing_pref
mailing_pref_type
comments
character varying(255)
Index -
Schema public
addresses Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
addr1
character varying(60)
NOT NULL
addr2
character varying(60)
DEFAULT NULL::character varying
addr3
character varying(60)
DEFAULT NULL::character varying
city
character varying(60)
NOT NULL
county
character varying(30)
DEFAULT NULL::character varying
country
character varying(30)
DEFAULT NULL::character varying
postal_cd
character varying(30)
NOT NULL
modified_by
character varying(20)
NOT NULL
DEFAULT "current_user"()
time_stamp
timestamp without time zone
NOT NULL
DEFAULT now()
addresses Constraints
Name
Constraint
ck_addr1
CHECK (((addr1)::text = upper((addr1)::text)))
ck_addr2
CHECK (((addr2)::text = upper((addr2)::text)))
ck_addr3
CHECK (((addr3)::text = upper((addr3)::text)))
ck_city
CHECK (((city)::text = upper((city)::text)))
ck_country
CHECK (((country)::text = upper((country)::text)))
ck_county
CHECK (((county)::text = upper((county)::text)))
ck_postal_cd
CHECK (((postal_cd)::text = upper((postal_cd)::text)))
Tables referencing this one via Foreign Key Constraints:
Index -
Schema public
after_sessions Structure
F-Key
Name
Type
Description
diary_id
integer
start
timestamp with time zone
finish
timestamp with time zone
SELECT DISTINCT
ON (diary_in_office.diary_id) diary_in_office.diary_id
, diary_in_office.finish AS start
, to_timestamp
('30000101'::text
,'YYYYMMDD'::text
) AS finish
FROM diary_in_office
ORDER BY diary_in_office.diary_id
, diary_in_office.finish DESC;
Index -
Schema public
avatars Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
description
character varying(50)
NOT NULL
svg_data
text
Index -
Schema public
before_sessions Structure
F-Key
Name
Type
Description
diary_id
integer
start
timestamp with time zone
finish
timestamp with time zone
SELECT DISTINCT
ON (diary_in_office.diary_id) diary_in_office.diary_id
, to_timestamp
(
(0)::double precision
) AS start
, diary_in_office.start AS finish
FROM diary_in_office
ORDER BY diary_in_office.diary_id
, to_timestamp
(
(0)::double precision
);
Index -
Schema public
calendar Structure
F-Key
Name
Type
Description
date_id
date
PRIMARY KEY
event
character varying(255)
NOT NULL
DEFAULT ''::character varying
Index -
Schema public
clerical_memos Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
UNIQUE
NOT NULL
memo
character varying(255)
NOT NULL
DEFAULT ''::character varying
checked_date
date
NOT NULL
DEFAULT ('now'::text)::date
checked_by
character varying(20)
NOT NULL
DEFAULT "current_user"()
Index -
Schema public
clinical_memos Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
UNIQUE
NOT NULL
memo
character varying(255)
NOT NULL
DEFAULT ''::character varying
checked_date
date
NOT NULL
DEFAULT ('now'::text)::date
checked_by
character varying(20)
NOT NULL
DEFAULT "current_user"()
Index -
Schema public
contracted_practitioners Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
NOT NULL
practitioners.ix
practitioner_id
integer
NOT NULL
contract_type
character varying(20)
start_date
date
NOT NULL
DEFAULT ('now'::text)::date
end_date
date
comments
character varying(255)
Index -
Schema public
diaries Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
book_start
date
NOT NULL
book_end
date
NOT NULL
comment
text
active
boolean
NOT NULL
DEFAULT true
diaries Constraints
Name
Constraint
ck_diary_limits
CHECK ((book_start < book_end))
Tables referencing this one via Foreign Key Constraints:
Index -
Schema public
diary_adjacent_entries Structure
F-Key
Name
Type
Description
diary_id
integer
start
timestamp with time zone
finish
timestamp with time zone
gap
interval
next_appt
timestamp with time zone
SELECT appts.diary_id
, appts.start
, appts.finish
, (next.start - appts.finish) AS gap
, next.start AS next_appt
FROM (
SELECT diary_entries.diary_id
, row_number
() OVER
(
ORDER BY diary_entries.diary_id
, diary_entries.start
) AS row_
, diary_entries.start
, diary_entries.finish
FROM diary_entries
) appts
, (
SELECT diary_entries.diary_id
, row_number
() OVER
(
ORDER BY diary_entries.diary_id
, diary_entries.start
) AS row_
, diary_entries.start
FROM diary_entries
) next
WHERE (
(next.row_ =
(appts.row_ + 1)
)
AND (next.diary_id = appts.diary_id)
);
Index -
Schema public
diary_adjacent_in_office_entries Structure
F-Key
Name
Type
Description
diary_id
integer
start
timestamp with time zone
finish
timestamp with time zone
gap
interval
next_appt
timestamp with time zone
SELECT appts.diary_id
, appts.start
, appts.finish
, (next.start - appts.finish) AS gap
, next.start AS next_appt
FROM (
SELECT diary_work.diary_id
, row_number
() OVER
(
ORDER BY diary_work.diary_id
, diary_work.start
) AS row_
, diary_work.start
, diary_work.finish
FROM diary_work
) appts
, (
SELECT diary_work.diary_id
, row_number
() OVER
(
ORDER BY diary_work.diary_id
, diary_work.start
) AS row_
, diary_work.start
FROM diary_work
) next
WHERE (
(next.row_ =
(appts.row_ + 1)
)
AND (next.diary_id = appts.diary_id)
);
Index -
Schema public
diary_entries Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
diaries.ix
diary_id
integer
NOT NULL
start
timestamp with time zone
finish
timestamp with time zone
comment
text
etype
diary_entry_type
NOT NULL
DEFAULT 'free'::diary_entry_type
diary_entries Constraints
Name
Constraint
ck_entries
CHECK ((start <= finish))
Tables referencing this one via Foreign Key Constraints:
Index -
Schema public
diary_in_office Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
diaries.ix
diary_id
integer
NOT NULL
start
timestamp with time zone
finish
timestamp with time zone
comment
text
NOT NULL
DEFAULT ''::text
diary_in_office Constraints
Name
Constraint
ck_sessions
CHECK ((start <= finish))
Index -
Schema public
diary_multi_day_entries Structure
F-Key
Name
Type
Description
ix
integer
diary_id
integer
start
timestamp with time zone
finish
timestamp with time zone
comment
text
etype
diary_entry_type
SELECT diary_entries.ix
, diary_entries.diary_id
, diary_entries.start
, diary_entries.finish
, diary_entries.comment
, diary_entries.etype
FROM diary_entries
WHERE (date
(diary_entries.start) <> date
(diary_entries.finish)
);
Index -
Schema public
diary_out_of_office Structure
F-Key
Name
Type
Description
diary_id
integer
start
timestamp with time zone
finish
timestamp with time zone
SELECT diary_id
, start
, finish
FROM (extremity_appointments NATURAL FULL
JOIN (
SELECT a.diary_id
, a.finish AS start
, b.start AS finish
FROM (
(
SELECT diary_in_office.diary_id
, row_number
() OVER
(
ORDER BY diary_in_office.diary_id
, diary_in_office.start
) AS row_
, diary_in_office.start
, diary_in_office.finish
FROM diary_in_office
) a
JOIN (
SELECT diary_in_office.diary_id
, row_number
() OVER
(
ORDER BY diary_in_office.diary_id
, diary_in_office.start
) AS row_
, diary_in_office.start
, diary_in_office.finish
FROM diary_in_office
) b
ON (
(
(a.diary_id = b.diary_id)
AND (b.row_ =
(a.row_ + 1)
)
)
)
)
) reversed_in_office
)
ORDER BY diary_id
, start;
Index -
Schema public
diary_patients Structure
F-Key
Name
Type
Description
ix
serial
UNIQUE
NOT NULL
patients.ix
patient
integer
NOT NULL
diary_entries.ix
appt_ix
integer
clinician_type
clinician_type
NOT NULL
DEFAULT 'dentist'::clinician_type
practitioners.ix
clinician_spec
integer
reason1
character varying(20)
reason2
character varying(20)
length
integer
NOT NULL
parent
integer
period
interval
comment
character varying(240)
time_stamp
timestamp without time zone
NOT NULL
DEFAULT now()
diary_patients Constraints
Name
Constraint
ck_diary_periods
CHECK ((((parent IS NOT NULL) AND (period IS NOT NULL)) OR ((period IS NULL) AND (parent IS NULL))))
Index -
Schema public
diary_slots Structure
F-Key
Name
Type
Description
start
timestamp with time zone
length
interval
Index -
Schema public
diary_work Structure
F-Key
Name
Type
Description
diary_id
integer
start
timestamp with time zone
finish
timestamp with time zone
etype
diary_entry_type
SELECT diary_id
, start
, finish
, diary_entries.etype
FROM (diary_entries NATURAL FULL
JOIN diary_out_of_office
)
ORDER BY diary_id
, start;
Index -
Schema public
extremity_appointments Structure
F-Key
Name
Type
Description
diary_id
integer
start
timestamp with time zone
finish
timestamp with time zone
SELECT diary_id
, start
, finish
FROM (before_sessions NATURAL FULL
JOIN after_sessions
);
Index -
Schema public
fees Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
NOT NULL
fee
numeric(10,2)
NOT NULL
type
fee_type
NOT NULL
DEFAULT 'other'::fee_type
comment
character varying(240)
time_stamp
timestamp without time zone
NOT NULL
DEFAULT now()
Index -
Schema public
invoice_status Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
status
character varying(80)
NOT NULL
Tables referencing this one via Foreign Key Constraints:
Index -
Schema public
invoices Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
NOT NULL
invoice_status.ix
status_id
integer
NOT NULL
date_issued
date
NOT NULL
DEFAULT ('now'::text)::date
date_paid
date
total_fees
numeric(10,2)
NOT NULL
discount
numeric(10,2)
NOT NULL
amount_payable
numeric(10,2)
NOT NULL
other_details
character varying(240)
Index -
Schema public
notes_clerical Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
NOT NULL
open_time
timestamp without time zone
NOT NULL
DEFAULT now()
commit_time
timestamp without time zone
NOT NULL
DEFAULT now()
type
notes_clerical_type
NOT NULL
DEFAULT 'observation'::notes_clerical_type
line
text
users.ix
author
integer
Index -
Schema public
notes_clinical Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
NOT NULL
open_time
timestamp without time zone
NOT NULL
DEFAULT now()
commit_time
timestamp without time zone
NOT NULL
DEFAULT now()
type
notes_clinical_type
NOT NULL
DEFAULT 'observation'::notes_clinical_type
line
text
users.ix
author
integer
NOT NULL
users.ix
co_author
integer
committed
boolean
NOT NULL
DEFAULT false
Index -
Schema public
patients Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
title
character varying(20)
NOT NULL
last_name
character varying(30)
NOT NULL
first_name
character varying(30)
NOT NULL
qualifications
character varying(30)
preferred_name
character varying(30)
correspondence_name
character varying(60)
sex
sex_type
NOT NULL
dob
date
NOT NULL
status
pt_status_type
NOT NULL
modified_by
character varying(20)
NOT NULL
DEFAULT "current_user"()
time_stamp
timestamp without time zone
NOT NULL
DEFAULT now()
patients Constraints
Name
Constraint
chk_first_name_case
CHECK (((first_name)::text = upper((first_name)::text)))
chk_first_name_len
CHECK (((first_name)::text <> ''::text))
chk_last_name_case
CHECK (((last_name)::text = upper((last_name)::text)))
chk_last_name_len
CHECK ((length((last_name)::text) > 1))
chk_title_case
CHECK (((title)::text = upper((title)::text)))
chk_title_len
CHECK ((length((title)::text) > 1))
Tables referencing this one via Foreign Key Constraints:
Index -
Schema public
perio_bleeding Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
UNIQUE#1
NOT NULL
tooth
smallint
UNIQUE#1
NOT NULL
checked_date
date
UNIQUE#1
NOT NULL
DEFAULT ('now'::text)::date
values
character varying(6)
comment
character varying(80)
checked_by
character varying(20)
NOT NULL
DEFAULT "current_user"()
perio_bleeding Constraints
Name
Constraint
bleeding_values_rule
CHECK ((("values")::text ~ '^[YN]{6}$'::text))
Index -
Schema public
perio_bpe Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
NOT NULL
checked_date
date
NOT NULL
DEFAULT ('now'::text)::date
values
character(6)
comment
character varying(80)
checked_by
character varying(20)
NOT NULL
DEFAULT "current_user"()
perio_bpe Constraints
Name
Constraint
bpe_values_rule
CHECK (("values" ~ '^[01234\*\-]{6}$'::text))
Index -
Schema public
perio_plaque Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
UNIQUE#1
NOT NULL
tooth
smallint
UNIQUE#1
NOT NULL
checked_date
date
UNIQUE#1
NOT NULL
DEFAULT ('now'::text)::date
values
character varying(6)
comment
character varying(80)
checked_by
character varying(20)
NOT NULL
DEFAULT "current_user"()
perio_plaque Constraints
Name
Constraint
plaque_values_rule
CHECK ((("values")::text ~ '^[YN]{6}$'::text))
Index -
Schema public
perio_pocketing Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
UNIQUE#1
NOT NULL
tooth
smallint
UNIQUE#1
NOT NULL
checked_date
date
UNIQUE#1
NOT NULL
DEFAULT ('now'::text)::date
values
character varying(6)
comment
character varying(80)
checked_by
character varying(20)
NOT NULL
DEFAULT "current_user"()
perio_pocketing Constraints
Name
Constraint
pocketing_values_rule
CHECK ((("values")::text ~ '^[ 0-9A-F]{6}$'::text))
Index -
Schema public
perio_recession Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
UNIQUE#1
NOT NULL
tooth
smallint
UNIQUE#1
NOT NULL
checked_date
date
UNIQUE#1
NOT NULL
DEFAULT ('now'::text)::date
values
character varying(6)
comment
character varying(80)
checked_by
character varying(20)
NOT NULL
DEFAULT "current_user"()
perio_recession Constraints
Name
Constraint
recession_values_rule
CHECK ((("values")::text ~ '^[0-9A-F]{6}$'::text))
Index -
Schema public
practices Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
name
character varying(50)
NOT NULL
addresses.ix
address_ix
integer
website
character varying(50)
DEFAULT NULL::character varying
email1
character varying(30)
DEFAULT NULL::character varying
email2
character varying(30)
DEFAULT NULL::character varying
tel1
character varying(30)
DEFAULT NULL::character varying
tel2
character varying(30)
DEFAULT NULL::character varying
tel3
character varying(30)
DEFAULT NULL::character varying
fax
character varying(30)
DEFAULT NULL::character varying
Index -
Schema public
practitioners Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
users.ix
user_id
integer
type
practitioner_type
NOT NULL
speciality
character varying(20)
status
character varying(20)
NOT NULL
comments
character varying(255)
modified_by
character varying(20)
NOT NULL
time_stamp
timestamp without time zone
NOT NULL
DEFAULT now()
Tables referencing this one via Foreign Key Constraints:
Index -
Schema public
procedure_codes Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
category
integer
NOT NULL
DEFAULT 1
code
character varying(8)
UNIQUE
description
character varying(140)
Index -
Schema public
settings Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
key
character varying(80)
data
text
Index -
Schema public
static_comments Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
NOT NULL
tooth
smallint
NOT NULL
comment
character varying(255)
checked_date
date
NOT NULL
DEFAULT ('now'::text)::date
checked_by
character varying(20)
NOT NULL
DEFAULT "current_user"()
Index -
Schema public
static_crowns Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
NOT NULL
tooth
smallint
NOT NULL
type
crown_type
NOT NULL
technition
character varying(30)
comment
character varying(80)
date_charted
date
NOT NULL
DEFAULT ('now'::text)::date
Index -
Schema public
static_fills Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
NOT NULL
tooth
smallint
NOT NULL
surfaces
character varying(5)
NOT NULL
material
fill_material_type
NOT NULL
comment
character varying(80)
date_charted
date
NOT NULL
DEFAULT ('now'::text)::date
static_fills Constraints
Name
Constraint
static_fills_surface_rule
CHECK (((surfaces)::text ~ '^[MODBL]*$'::text))
Index -
Schema public
static_roots Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
NOT NULL
tooth
smallint
NOT NULL
description
root_description_type
comment
character varying(80)
checked_date
date
NOT NULL
DEFAULT ('now'::text)::date
checked_by
character varying(20)
NOT NULL
DEFAULT "current_user"()
Index -
Schema public
static_supernumerary Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
NOT NULL
mesial_neighbour
smallint
distal_neighbour
smallint
is_erupted
boolean
NOT NULL
DEFAULT false
comment
character varying(240)
checked_date
date
NOT NULL
DEFAULT ('now'::text)::date
checked_by
character varying(20)
NOT NULL
DEFAULT "current_user"()
Index -
Schema public
teeth_present Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
NOT NULL
dent_key
bigint
NOT NULL
DEFAULT 281474976645120::bigint
checked_date
date
NOT NULL
DEFAULT ('now'::text)::date
checked_by
character varying(20)
NOT NULL
DEFAULT "current_user"()
Index -
Schema public
telephone Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
number
character varying(30)
NOT NULL
sms_capable
boolean
DEFAULT false
checked_date
date
DEFAULT ('now'::text)::date
checked_by
character varying(20)
NOT NULL
DEFAULT "current_user"()
telephone Constraints
Name
Constraint
telephone_nos_rule
CHECK (((number)::text ~ '^[\d+ \+]*'::text))
Tables referencing this one via Foreign Key Constraints:
Index -
Schema public
telephone_link Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
tel_cat
telephone_type
NOT NULL
DEFAULT 'home'::telephone_type
patients.ix
patient_id
integer
NOT NULL
telephone.ix
tel_id
integer
NOT NULL
comment
character varying(240)
Index -
Schema public
treatment_chart Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
treatment_teeth.ix
tooth_id
integer
type
tx_chart_type
NOT NULL
DEFAULT 'tooth'::tx_chart_type
draw_text
character varying(12)
svg
character varying(30)
treatment_chart Constraints
Name
Constraint
treatment_chart_data
CHECK (((draw_text IS NOT NULL) OR (svg IS NOT NULL)))
Index -
Schema public
treatment_crowns Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
treatment_teeth.ix
tooth_tx_id
integer
type
crown_type
NOT NULL
technition
character varying(30)
Index -
Schema public
treatment_fills Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
treatment_teeth.ix
tooth_tx_id
integer
surfaces
character varying(5)
NOT NULL
material
fill_material_type
treatment_fills Constraints
Name
Constraint
treatment_fills_surface_rule
CHECK (((surfaces)::text ~ '^[MODBL]*$'::text))
Index -
Schema public
treatment_teeth Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
treatments.ix
treatment_id
integer
tooth
smallint
NOT NULL
tx_type
tooth_tx_type
Tables referencing this one via Foreign Key Constraints:
Index -
Schema public
treatments Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
patients.ix
patient_id
integer
NOT NULL
om_code
character varying(5)
NOT NULL
completed
boolean
NOT NULL
DEFAULT false
practitioners.ix
px_clinician
integer
NOT NULL
px_date
date
NOT NULL
DEFAULT ('now'::text)::date
practitioners.ix
tx_clinician
integer
tx_date
date
added_by
character varying(20)
NOT NULL
DEFAULT "current_user"()
comment
character varying(240)
treatments Constraints
Name
Constraint
completed_treatment_rule
CHECK (((NOT completed) OR (tx_clinician IS NOT NULL)))
completed_treatment_rule2
CHECK (((NOT completed) OR (tx_date IS NOT NULL)))
Tables referencing this one via Foreign Key Constraints:
Index -
Schema public
users Structure
F-Key
Name
Type
Description
ix
serial
PRIMARY KEY
abbrv_name
character varying(20)
UNIQUE
NOT NULL
role
character varying(20)
title
character varying(20)
NOT NULL
last_name
character varying(30)
NOT NULL
middle_name
character varying(30)
first_name
character varying(30)
NOT NULL
qualifications
character varying(30)
NOT NULL
registration
character varying(240)
correspondence_name
character varying(60)
sex
sex_type
NOT NULL
dob
date
NOT NULL
status
character varying(20)
NOT NULL
comments
character varying(255)
DEFAULT NULL::character varying
avatar_id
integer
display_order
integer
modified_by
character varying(20)
NOT NULL
time_stamp
timestamp without time zone
NOT NULL
DEFAULT now()
Tables referencing this one via Foreign Key Constraints:
Index -
Schema public
view_addresses Structure
F-Key
Name
Type
Description
ix
integer
addr1
character varying(60)
addr2
character varying(60)
addr3
character varying(60)
city
character varying(60)
county
character varying(30)
country
character varying(30)
postal_cd
character varying(30)
address_cat
address_type
address_id
integer
patient_id
integer
present
boolean
known_residents
bigint
from_date
date
to_date
date
mailing_pref
mailing_pref_type
comments
character varying(255)
SELECT a.ix
, a.addr1
, a.addr2
, a.addr3
, a.city
, a.county
, a.country
, a.postal_cd
, l.address_cat
, l.address_id
, l.patient_id
, t2.present
, t2.known_residents
, l.from_date
, l.to_date
, l.mailing_pref
, l.comments
FROM (
(addresses a
JOIN address_link l
ON (
(a.ix = l.address_id)
)
)
JOIN (
SELECT address_link.address_id
, (
(address_link.from_date <=
('now'::text)::date
)
AND (
(address_link.to_date >=
('now'::text)::date
)
OR (address_link.to_date IS NULL)
)
) AS present
, count
(address_link.address_id) AS known_residents
FROM address_link
GROUP BY address_link.address_id
, (
(address_link.from_date <=
('now'::text)::date
)
AND (
(address_link.to_date >=
('now'::text)::date
)
OR (address_link.to_date IS NULL)
)
)
) t2
ON (
(a.ix = t2.address_id)
)
);
Index -
Schema public
view_practitioners Structure
F-Key
Name
Type
Description
practitioner_id
integer
user_id
integer
abbrv_name
character varying(20)
type
practitioner_type
title
character varying(20)
last_name
character varying(30)
first_name
character varying(30)
middle_name
character varying(30)
qualifications
character varying(30)
registration
character varying(240)
correspondence_name
character varying(60)
sex
sex_type
dob
date
avatar_id
integer
svg_data
text
status
character varying(20)
speciality
character varying(20)
display_order
integer
SELECT practitioners.ix AS practitioner_id
, practitioners.user_id
, users.abbrv_name
, practitioners.type
, users.title
, users.last_name
, users.first_name
, users.middle_name
, users.qualifications
, users.registration
, users.correspondence_name
, users.sex
, users.dob
, users.avatar_id
, avatars.svg_data
, practitioners.status
, practitioners.speciality
, users.display_order
FROM (
(practitioners
LEFT JOIN users
ON (
(practitioners.user_id = users.ix)
)
)
LEFT JOIN avatars
ON (
(avatars.ix = users.avatar_id)
)
)
ORDER BY users.display_order;
Index -
Schema public
Returns: SET OF date
Language: SQL
SELECT $1 + n FROM generate_series(0, $2 - $1, $3) n
Returns: SET OF diary_slots
Language: SQL
SELECT finish as start, gap from diary_adjacent_in_office_entries where finish >= $1 and next_appt <= $2 and diary_id=$3 and gap > '00:00:00'
Returns: SET OF diary_slots
Language: SQL
SELECT finish as start, gap from diary_adjacent_entries where finish >= $1 and next_appt <= $2 and diary_id=$3 and gap > '00:00:00'
Generated by PostgreSQL Autodoc
W3C HTML 4.01 Strict