v_citation_summary

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

citation_num

int4

√

null

citation_code

text

2147483647

√

null

authors

text

2147483647

√

null

datasets

json

2147483647

√

null

title

varchar

256

√

null

publisher

varchar

256

√

null

publication_year

int4

√

null

journal

varchar

256

√

null

issue

varchar

√

null

volume

varchar

√

null

pages

varchar

256

√

null

book_title

varchar

256

√

null

book_editor

varchar

256

√

null

doi

varchar

2000

√

null

doi_link

varchar

2000

√

null

data_status

varchar

√

null

data_entered_date

text

2147483647

√

null

public_comment

varchar

2000

√

null

expedition_list

_text

2147483647

√

null

station_list

_text

2147483647

√

null

specimen_list

_text

2147483647

√

null

method_list

_text

2147483647

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT c.citation_num,
    f.citation_code,
    author_all.authors,
    dataset_all.datasets,
    c.title,
    c.publisher,
    c.publication_year,
    c.journal,
    c.issue,
    c.volume,
    c.pages,
    c.book_title,
    c.book_editor,
    cei.citation_external_identifier AS doi,
    cei.citation_external_identifier_uri AS doi_link,
    esi.data_status,
    to_char(esi.data_entered_date, 'YYYY-MM-DD'::text) AS data_entered_date,
    esi.public_comment,
    vdrc.expedition_list,
    vdrc.station_list,
    vdrc.specimen_list,
    vdrc.method_list
   FROM ((((((citation c
     LEFT JOIN ( SELECT ci.citation_num,
            (((p.last_name)::text || ', '::text) || ci.publication_year) AS citation_code
           FROM citation ci,
            author_list al,
            person p
          WHERE ((ci.citation_num = al.citation_num) AND (al.author_order = 1) AND (al.person_num = p.person_num))) f ON ((f.citation_num = c.citation_num)))
     LEFT JOIN ( SELECT string_agg((((p.last_name)::text || ', '::text) || (p.first_name)::text), '; '::text ORDER BY al.author_order) AS authors,
            al.citation_num
           FROM person p,
            author_list al
          WHERE (al.person_num = p.person_num)
          GROUP BY al.citation_num) author_all ON ((c.citation_num = author_all.citation_num)))
     LEFT JOIN ( SELECT json_agg(json_build_object('citation_code', mds.citation_code, 'dataset_num', mds.dataset_num, 'dataset_code', mds.dataset_code, 'dataset_title', mds.dataset_title, 'count_batches', mds.count_batches, 'data_available', mds.data_available, 'item_count', mds.count_items, 'item_list', mds.item_list)) AS datasets,
            mds.citation_num
           FROM v_dataset_summary mds
          GROUP BY mds.citation_num) dataset_all ON ((c.citation_num = dataset_all.citation_num)))
     LEFT JOIN citation_external_identifier cei ON ((c.citation_num = cei.citation_num)))
     LEFT JOIN ec_status_info esi ON ((c.citation_num = esi.citation_num)))
     LEFT JOIN ( SELECT mdrs.citation_num,
            array_agg(DISTINCT ((((mdrs.expedition_num || '^'::text) || (mdrs.expedition_code)::text) || '^'::text) || (mdrs.expedition_name_leg_alias)::text)) AS expedition_list,
            array_agg(DISTINCT ((mdrs.station_num || '|'::text) || (mdrs.station_code)::text)) AS station_list,
            array_agg(DISTINCT ((mdrs.specimen_num || '|'::text) || (mdrs.specimen_code)::text)) AS specimen_list,
            array_agg(DISTINCT (((((((((methods.method_code)::text || '|'::text) || (methods.method_name)::text) || '|'::text) || (methods.laboratory)::text) || '|'::text) || (methods.method_comment)::text) || '|'::text) || (methods.item_list)::text)) AS method_list
           FROM (v_dataset_result_summary mdrs
             LEFT JOIN ( SELECT v_dataset_result_summary.citation_num,
                    v_dataset_result_summary.method_code,
                    v_dataset_result_summary.method_name,
                    v_dataset_result_summary.laboratory,
                    v_dataset_result_summary.method_comment,
                    array_agg(DISTINCT v_dataset_result_summary.variable_code) AS item_list
                   FROM v_dataset_result_summary
                  GROUP BY v_dataset_result_summary.citation_num, v_dataset_result_summary.method_name, v_dataset_result_summary.method_code, v_dataset_result_summary.laboratory, v_dataset_result_summary.method_comment) methods ON ((methods.citation_num = mdrs.citation_num)))
          GROUP BY mdrs.citation_num) vdrc ON ((vdrc.citation_num = c.citation_num)))
  WHERE (c.citation_num > 0);

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views