v_expedition_summary

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

expedition_num

int4

√

null

expedition_code

varchar

256

√

null

expedition_name

varchar

2000

√

null

expedition_alias

varchar

2000

√

null

cruise_leg

varchar

2000

√

null

description

varchar

2000

√

null

expedition_type

varchar

256

√

null

citations

_text

2147483647

√

null

datasets

json

2147483647

√

null

stations

_text

2147483647

√

null

specimens

_text

2147483647

√

null

Relationships

Close relationships within degrees of separation

View Definition

SELECT a.action_num AS expedition_num,
    a.action_name AS expedition_code,
    expname.expedition_name,
    expalias.expedition_alias,
    cruiseleg.cruise_leg,
    a.action_description AS description,
    at.action_type_name AS expedition_type,
    f.citations,
    dataset_all.datasets,
    f.stations,
    f.specimens
   FROM ((((((action a
     JOIN action_type at ON ((a.action_type_num = at.action_type_num)))
     LEFT JOIN ( SELECT aa.action_num,
            ant1.annotation_text AS expedition_name
           FROM action_annotation aa,
            annotation ant1
          WHERE ((ant1.annotation_num = aa.annotation_num) AND (ant1.annotation_type_num = 89))) expname ON ((expname.action_num = a.action_num)))
     LEFT JOIN ( SELECT aa.action_num,
            ant1.annotation_text AS expedition_alias
           FROM action_annotation aa,
            annotation ant1
          WHERE ((ant1.annotation_num = aa.annotation_num) AND (ant1.annotation_type_num = 40))) expalias ON ((expalias.action_num = a.action_num)))
     LEFT JOIN ( SELECT aa.action_num,
            ant1.annotation_text AS cruise_leg
           FROM action_annotation aa,
            annotation ant1
          WHERE ((ant1.annotation_num = aa.annotation_num) AND (ant1.annotation_type_num = 90))) cruiseleg ON ((cruiseleg.action_num = a.action_num)))
     LEFT JOIN ( SELECT v_dataset_result_summary.expedition_num,
            array_agg(DISTINCT ((v_dataset_result_summary.citation_num || '|'::text) || v_dataset_result_summary.citation_code)) AS citations,
            array_agg(DISTINCT ((v_dataset_result_summary.station_num || '|'::text) || (v_dataset_result_summary.station_code)::text)) AS stations,
            array_agg(DISTINCT ((v_dataset_result_summary.specimen_num || '|'::text) || (v_dataset_result_summary.specimen_code)::text)) AS specimens
           FROM v_dataset_result_summary
          GROUP BY v_dataset_result_summary.expedition_num) f ON ((f.expedition_num = a.action_num)))
     LEFT JOIN ( SELECT json_agg(json_build_object('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,
            (substr(unnest(mds.expedition_list), 0, strpos(unnest(mds.expedition_list), '|'::text)))::integer AS expedition_num
           FROM v_dataset_summary mds
          GROUP BY (substr(unnest(mds.expedition_list), 0, strpos(unnest(mds.expedition_list), '|'::text)))::integer) dataset_all ON ((a.action_num = dataset_all.expedition_num)))
  WHERE (a.action_type_num = ANY (ARRAY[3, 11, 12, 25]));

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views