Revision 5847
Added by Aaron Marcuse-Kubitza over 12 years ago
| schemas/vegbien.my.sql | ||
|---|---|---|
| 250 | 250 |
|
| 251 | 251 |
|
| 252 | 252 |
-- |
| 253 |
-- Name: sync_analytical_db_to_view(); Type: FUNCTION; Schema: public; Owner: - |
|
| 254 |
-- |
|
| 255 |
|
|
| 256 |
|
|
| 257 |
|
|
| 258 |
|
|
| 259 |
-- |
|
| 253 | 260 |
-- Name: taxonlabel_0_matched_label_id_self_ref(); Type: FUNCTION; Schema: public; Owner: - |
| 254 | 261 |
-- |
| 255 | 262 |
|
| ... | ... | |
| 516 | 523 |
|
| 517 | 524 |
|
| 518 | 525 |
-- |
| 526 |
-- Name: analytical_db; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 527 |
-- |
|
| 528 |
|
|
| 529 |
CREATE TABLE analytical_db ( |
|
| 530 |
`institutionCode` text, |
|
| 531 |
country text, |
|
| 532 |
`stateProvince` text, |
|
| 533 |
county text, |
|
| 534 |
`decimalLatitude` double precision, |
|
| 535 |
`decimalLongitude` double precision, |
|
| 536 |
`plotName` text, |
|
| 537 |
`elevationInMeters` double precision, |
|
| 538 |
`plotArea_ha` double precision, |
|
| 539 |
`samplingProtocol` text, |
|
| 540 |
`dateCollected` date, |
|
| 541 |
family text, |
|
| 542 |
genus text, |
|
| 543 |
`speciesBinomial` text, |
|
| 544 |
`scientificName` text, |
|
| 545 |
`scientificNameAuthorship` text, |
|
| 546 |
`scientificNameWithMorphospecies` text, |
|
| 547 |
`identifiedBy` text, |
|
| 548 |
`growthForm` growthform, |
|
| 549 |
cultivated int(1), |
|
| 550 |
`cultivatedBasis` text, |
|
| 551 |
`coverPercent` double precision, |
|
| 552 |
`diameterBreastHeight_cm` double precision, |
|
| 553 |
height_m double precision, |
|
| 554 |
tag text, |
|
| 555 |
`organismX_m` double precision, |
|
| 556 |
`organismY_m` double precision, |
|
| 557 |
`recordedBy` text, |
|
| 558 |
`recordNumber` text |
|
| 559 |
); |
|
| 560 |
|
|
| 561 |
|
|
| 562 |
-- |
|
| 519 | 563 |
-- Name: location; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 520 | 564 |
-- |
| 521 | 565 |
|
| schemas/vegbien.sql | ||
|---|---|---|
| 443 | 443 |
|
| 444 | 444 |
CREATE FUNCTION make_analytical_db() RETURNS void |
| 445 | 445 |
LANGUAGE sql |
| 446 |
AS $$CREATE TABLE analytical_db AS SELECT * FROM analytical_db_view$$; |
|
| 446 |
AS $$ |
|
| 447 |
INSERT INTO analytical_db SELECT * FROM analytical_db_view |
|
| 448 |
$$; |
|
| 447 | 449 |
|
| 448 | 450 |
|
| 449 | 451 |
-- |
| ... | ... | |
| 554 | 556 |
|
| 555 | 557 |
|
| 556 | 558 |
-- |
| 559 |
-- Name: sync_analytical_db_to_view(); Type: FUNCTION; Schema: public; Owner: - |
|
| 560 |
-- |
|
| 561 |
|
|
| 562 |
CREATE FUNCTION sync_analytical_db_to_view() RETURNS void |
|
| 563 |
LANGUAGE sql |
|
| 564 |
AS $$ |
|
| 565 |
DROP TABLE analytical_db; |
|
| 566 |
CREATE TABLE analytical_db AS SELECT * FROM analytical_db_view; |
|
| 567 |
$$; |
|
| 568 |
|
|
| 569 |
|
|
| 570 |
-- |
|
| 557 | 571 |
-- Name: taxonlabel_0_matched_label_id_self_ref(); Type: FUNCTION; Schema: public; Owner: - |
| 558 | 572 |
-- |
| 559 | 573 |
|
| ... | ... | |
| 1007 | 1021 |
|
| 1008 | 1022 |
|
| 1009 | 1023 |
-- |
| 1024 |
-- Name: analytical_db; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1025 |
-- |
|
| 1026 |
|
|
| 1027 |
CREATE TABLE analytical_db ( |
|
| 1028 |
"institutionCode" text, |
|
| 1029 |
country text, |
|
| 1030 |
"stateProvince" text, |
|
| 1031 |
county text, |
|
| 1032 |
"decimalLatitude" double precision, |
|
| 1033 |
"decimalLongitude" double precision, |
|
| 1034 |
"plotName" text, |
|
| 1035 |
"elevationInMeters" double precision, |
|
| 1036 |
"plotArea_ha" double precision, |
|
| 1037 |
"samplingProtocol" text, |
|
| 1038 |
"dateCollected" date, |
|
| 1039 |
family text, |
|
| 1040 |
genus text, |
|
| 1041 |
"speciesBinomial" text, |
|
| 1042 |
"scientificName" text, |
|
| 1043 |
"scientificNameAuthorship" text, |
|
| 1044 |
"scientificNameWithMorphospecies" text, |
|
| 1045 |
"identifiedBy" text, |
|
| 1046 |
"growthForm" growthform, |
|
| 1047 |
cultivated boolean, |
|
| 1048 |
"cultivatedBasis" text, |
|
| 1049 |
"coverPercent" double precision, |
|
| 1050 |
"diameterBreastHeight_cm" double precision, |
|
| 1051 |
height_m double precision, |
|
| 1052 |
tag text, |
|
| 1053 |
"organismX_m" double precision, |
|
| 1054 |
"organismY_m" double precision, |
|
| 1055 |
"recordedBy" text, |
|
| 1056 |
"recordNumber" text |
|
| 1057 |
); |
|
| 1058 |
|
|
| 1059 |
|
|
| 1060 |
-- |
|
| 1010 | 1061 |
-- Name: location; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 1011 | 1062 |
-- |
| 1012 | 1063 |
|
Also available in: Unified diff
schemas/vegbien.sql: Store materialized analytical_db table in schema so aggregating views can reference it. Added sync_analytical_db_to_view() to maintain analytical_db table.