Navigation

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

Index of database - openmolar_demo

Table: address_link

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

Table: addresses

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

View: after_sessions

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

Table: avatars

avatars Structure
F-Key Name Type Description
ix serial PRIMARY KEY
description character varying(50) NOT NULL
svg_data text

Index - Schema public

View: before_sessions

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

Table: calendar

calendar Structure
F-Key Name Type Description
date_id date PRIMARY KEY
event character varying(255) NOT NULL DEFAULT ''::character varying

Index - Schema public

Table: clerical_memos

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

Table: clinical_memos

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

Table: contracted_practitioners

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

Table: diaries

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

View: diary_adjacent_entries

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

View: diary_adjacent_in_office_entries

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

Table: diary_entries

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

Table: diary_in_office

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

View: diary_multi_day_entries

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

View: diary_out_of_office

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

Table: diary_patients

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

Table: diary_slots

diary_slots Structure
F-Key Name Type Description
start timestamp with time zone
length interval

Index - Schema public

View: diary_work

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

View: extremity_appointments

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

Table: fees

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

Table: invoice_status

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

Table: invoices

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

Table: notes_clerical

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

Table: notes_clinical

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

Table: patients

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

Table: perio_bleeding

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

Table: perio_bpe

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

Table: perio_plaque

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

Table: perio_pocketing

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

Table: perio_recession

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

Table: practices

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

Table: practitioners

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

Table: procedure_codes

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

Table: settings

settings Structure
F-Key Name Type Description
ix serial PRIMARY KEY
key character varying(80)
data text

Index - Schema public

Table: static_comments

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

Table: static_crowns

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

Table: static_fills

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

Table: static_roots

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

Table: static_supernumerary

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

Table: teeth_present

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

Table: telephone

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

Table: telephone_link

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

Table: treatment_chart

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

Table: treatment_crowns

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

Table: treatment_fills

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

Table: treatment_teeth

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

Table: treatments

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

Table: users

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: view_addresses

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: view_practitioners

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

Function: generate_dates(n date, dt2 date, dt1 integer)

Returns: SET OF date

Language: SQL

SELECT $1 + n FROM generate_series(0, $2 - $1, $3) n

Function: get_available_in_office_slots(id timestamp without time zone, t2 timestamp without time zone, t1 integer)

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'

Function: get_available_slots(id timestamp with time zone, t2 timestamp with time zone, t1 integer)

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

Previous topic

Tooth Notation

Next topic

Classes

Navigation

© Copyright 2010-2011, Neil Wallace. Created using Sphinx 1.1.3.