simple_record

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

int8

√

null

materialized_simple_record.id

Implied Constraint

metarecord

int8

√

null

fingerprint

text

2147483647

√

null

quality

int4

√

null

tcn_source

text

2147483647

√

null

tcn_value

text

2147483647

√

null

title

text

2147483647

√

null

uniform_title

text

2147483647

√

null

author

text

2147483647

√

null

publisher

text

2147483647

√

null

pubdate

text

2147483647

√

null

series_title

text

2147483647

√

null

series_statement

text

2147483647

√

null

summary

text

2147483647

√

null

isbn

_text

2147483647

√

null

issn

_text

2147483647

√

null

topic_subject

_text

2147483647

√

null

geographic_subject

_text

2147483647

√

null

genre

_text

2147483647

√

null

name_subject

_text

2147483647

√

null

corporate_subject

_text

2147483647

√

null

external_uri

_text

2147483647

√

null

Relationships

Close relationships within degrees of separation

One
One implied

View Definition

SELECT r.id,
    s.metarecord,
    r.fingerprint,
    r.quality,
    r.tcn_source,
    r.tcn_value,
    title.value AS title,
    uniform_title.value AS uniform_title,
    author.value AS author,
    publisher.value AS publisher,
    "substring"(pubdate.value, '\d+'::text) AS pubdate,
    series_title.value AS series_title,
    series_statement.value AS series_statement,
    summary.value AS summary,
    array_agg(DISTINCT replace("substring"(isbn.value, '^\S+'::text), '-'::text, ''::text)) AS isbn,
    array_agg(DISTINCT regexp_replace(issn.value, '^\S*(\d{4})[-\s](\d{3,4}x?)'::text, '\1 \2'::text)) AS issn,
    ARRAY( SELECT DISTINCT full_rec.value
           FROM metabib.full_rec
          WHERE ((full_rec.tag = '650'::bpchar) AND (full_rec.subfield = 'a'::text) AND (full_rec.record = r.id))) AS topic_subject,
    ARRAY( SELECT DISTINCT full_rec.value
           FROM metabib.full_rec
          WHERE ((full_rec.tag = '651'::bpchar) AND (full_rec.subfield = 'a'::text) AND (full_rec.record = r.id))) AS geographic_subject,
    ARRAY( SELECT DISTINCT full_rec.value
           FROM metabib.full_rec
          WHERE ((full_rec.tag = '655'::bpchar) AND (full_rec.subfield = 'a'::text) AND (full_rec.record = r.id))) AS genre,
    ARRAY( SELECT DISTINCT full_rec.value
           FROM metabib.full_rec
          WHERE ((full_rec.tag = '600'::bpchar) AND (full_rec.subfield = 'a'::text) AND (full_rec.record = r.id))) AS name_subject,
    ARRAY( SELECT DISTINCT full_rec.value
           FROM metabib.full_rec
          WHERE ((full_rec.tag = '610'::bpchar) AND (full_rec.subfield = 'a'::text) AND (full_rec.record = r.id))) AS corporate_subject,
    ARRAY( SELECT full_rec.value
           FROM metabib.full_rec
          WHERE ((full_rec.tag = '856'::bpchar) AND (full_rec.subfield = ANY (ARRAY['3'::text, 'y'::text, 'u'::text])) AND (full_rec.record = r.id))
          ORDER BY
                CASE
                    WHEN (full_rec.subfield = ANY (ARRAY['3'::text, 'y'::text])) THEN 0
                    ELSE 1
                END) AS external_uri
   FROM (((((((((((biblio.record_entry r
     JOIN metabib.metarecord_source_map s ON ((s.source = r.id)))
     LEFT JOIN metabib.full_rec uniform_title ON (((r.id = uniform_title.record) AND (uniform_title.tag = '240'::bpchar) AND (uniform_title.subfield = 'a'::text))))
     LEFT JOIN metabib.full_rec title ON (((r.id = title.record) AND (title.tag = '245'::bpchar) AND (title.subfield = 'a'::text))))
     LEFT JOIN metabib.full_rec author ON (((r.id = author.record) AND (author.tag = '100'::bpchar) AND (author.subfield = 'a'::text))))
     LEFT JOIN metabib.full_rec publisher ON (((r.id = publisher.record) AND ((publisher.tag = '260'::bpchar) OR ((publisher.tag = '264'::bpchar) AND (publisher.ind2 = '1'::text))) AND (publisher.subfield = 'b'::text))))
     LEFT JOIN metabib.full_rec pubdate ON (((r.id = pubdate.record) AND ((pubdate.tag = '260'::bpchar) OR ((pubdate.tag = '264'::bpchar) AND (pubdate.ind2 = '1'::text))) AND (pubdate.subfield = 'c'::text))))
     LEFT JOIN metabib.full_rec isbn ON (((r.id = isbn.record) AND (isbn.tag = ANY (ARRAY['024'::bpchar, '020'::bpchar])) AND (isbn.subfield = ANY (ARRAY['a'::text, 'z'::text])))))
     LEFT JOIN metabib.full_rec issn ON (((r.id = issn.record) AND (issn.tag = '022'::bpchar) AND (issn.subfield = 'a'::text))))
     LEFT JOIN metabib.full_rec series_title ON (((r.id = series_title.record) AND (series_title.tag = ANY (ARRAY['830'::bpchar, '440'::bpchar])) AND (series_title.subfield = 'a'::text))))
     LEFT JOIN metabib.full_rec series_statement ON (((r.id = series_statement.record) AND (series_statement.tag = '490'::bpchar) AND (series_statement.subfield = 'a'::text))))
     LEFT JOIN metabib.full_rec summary ON (((r.id = summary.record) AND (summary.tag = '520'::bpchar) AND (summary.subfield = 'a'::text))))
  GROUP BY r.id, s.metarecord, r.fingerprint, r.quality, r.tcn_source, r.tcn_value, title.value, uniform_title.value, author.value, publisher.value, ("substring"(pubdate.value, '\d+'::text)), series_title.value, series_statement.value, summary.value;

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views