Revision 14275
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/.TNRS/schema.sql | ||
|---|---|---|
| 424 | 424 |
COMMENT ON COLUMN taxon_match."__accepted_{genus,specific_epithet}" IS '
|
| 425 | 425 |
derived column; = regexp_split_to_array("*Accepted_name_species", '' ''::text)
|
| 426 | 426 |
|
| 427 |
to modify: |
|
| 427 |
to modify expr:
|
|
| 428 | 428 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_{genus,specific_epithet}'')::util.col, $$regexp_split_to_array("*Accepted_name_species", '' ''::text)$$)::util.derived_col_def);
|
| 429 | 429 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 430 |
|
|
| 431 |
to rename: |
|
| 432 |
# rename column |
|
| 433 |
# rename CHECK constraint |
|
| 434 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 430 | 435 |
'; |
| 431 | 436 |
|
| 432 | 437 |
|
| ... | ... | |
| 437 | 442 |
COMMENT ON COLUMN taxon_match."[accepted_]genus__@DwC__@vegpath.org" IS ' |
| 438 | 443 |
derived column; = "__accepted_{genus,specific_epithet}"[1]
|
| 439 | 444 |
|
| 440 |
to modify: |
|
| 445 |
to modify expr:
|
|
| 441 | 446 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]genus__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet}"[1]$$)::util.derived_col_def);
|
| 442 | 447 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 448 |
|
|
| 449 |
to rename: |
|
| 450 |
# rename column |
|
| 451 |
# rename CHECK constraint |
|
| 452 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 443 | 453 |
'; |
| 444 | 454 |
|
| 445 | 455 |
|
| ... | ... | |
| 450 | 460 |
COMMENT ON COLUMN taxon_match."[accepted_]specificEpithet__@DwC__@vegpath.org" IS ' |
| 451 | 461 |
derived column; = "__accepted_{genus,specific_epithet}"[2]
|
| 452 | 462 |
|
| 453 |
to modify: |
|
| 463 |
to modify expr:
|
|
| 454 | 464 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]specificEpithet__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet}"[2]$$)::util.derived_col_def);
|
| 455 | 465 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 466 |
|
|
| 467 |
to rename: |
|
| 468 |
# rename column |
|
| 469 |
# rename CHECK constraint |
|
| 470 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 456 | 471 |
'; |
| 457 | 472 |
|
| 458 | 473 |
|
| ... | ... | |
| 463 | 478 |
COMMENT ON COLUMN taxon_match.__accepted_infraspecific_label IS ' |
| 464 | 479 |
derived column; = ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)
|
| 465 | 480 |
|
| 466 |
to modify: |
|
| 481 |
to modify expr:
|
|
| 467 | 482 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_label'')::util.col, $$ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)$$)::util.derived_col_def);
|
| 468 | 483 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 484 |
|
|
| 485 |
to rename: |
|
| 486 |
# rename column |
|
| 487 |
# rename CHECK constraint |
|
| 488 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 469 | 489 |
'; |
| 470 | 490 |
|
| 471 | 491 |
|
| ... | ... | |
| 476 | 496 |
COMMENT ON COLUMN taxon_match."__accepted_infraspecific_{rank,epithet}" IS '
|
| 477 | 497 |
derived column; = regexp_split_to_array(__accepted_infraspecific_label, '' ''::text) |
| 478 | 498 |
|
| 479 |
to modify: |
|
| 499 |
to modify expr:
|
|
| 480 | 500 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_{rank,epithet}'')::util.col, $$regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)$$)::util.derived_col_def);
|
| 481 | 501 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 502 |
|
|
| 503 |
to rename: |
|
| 504 |
# rename column |
|
| 505 |
# rename CHECK constraint |
|
| 506 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 482 | 507 |
'; |
| 483 | 508 |
|
| 484 | 509 |
|
| ... | ... | |
| 489 | 514 |
COMMENT ON COLUMN taxon_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS ' |
| 490 | 515 |
derived column; = "__accepted_infraspecific_{rank,epithet}"[1]
|
| 491 | 516 |
|
| 492 |
to modify: |
|
| 517 |
to modify expr:
|
|
| 493 | 518 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org'')::util.col, $$"__accepted_infraspecific_{rank,epithet}"[1]$$)::util.derived_col_def);
|
| 494 | 519 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 520 |
|
|
| 521 |
to rename: |
|
| 522 |
# rename column |
|
| 523 |
# rename CHECK constraint |
|
| 524 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 495 | 525 |
'; |
| 496 | 526 |
|
| 497 | 527 |
|
| ... | ... | |
| 502 | 532 |
COMMENT ON COLUMN taxon_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" IS ' |
| 503 | 533 |
derived column; = "__accepted_infraspecific_{rank,epithet}"[2]
|
| 504 | 534 |
|
| 505 |
to modify: |
|
| 535 |
to modify expr:
|
|
| 506 | 536 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]infraspecificEpithet__@DwC__@vegpath.org'')::util.col, $$"__accepted_infraspecific_{rank,epithet}"[2]$$)::util.derived_col_def);
|
| 507 | 537 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 538 |
|
|
| 539 |
to rename: |
|
| 540 |
# rename column |
|
| 541 |
# rename CHECK constraint |
|
| 542 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 508 | 543 |
'; |
| 509 | 544 |
|
| 510 | 545 |
|
| ... | ... | |
| 515 | 550 |
COMMENT ON COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS ' |
| 516 | 551 |
derived column; = "*Name_matched" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)
|
| 517 | 552 |
|
| 518 |
to modify: |
|
| 553 |
to modify expr:
|
|
| 519 | 554 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[matched_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"*Name_matched" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)$$)::util.derived_col_def);
|
| 520 | 555 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 556 |
|
|
| 557 |
to rename: |
|
| 558 |
# rename column |
|
| 559 |
# rename CHECK constraint |
|
| 560 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 521 | 561 |
'; |
| 522 | 562 |
|
| 523 | 563 |
|
| ... | ... | |
| 528 | 568 |
COMMENT ON COLUMN taxon_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" IS ' |
| 529 | 569 |
derived column; = "*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text)
|
| 530 | 570 |
|
| 531 |
to modify: |
|
| 571 |
to modify expr:
|
|
| 532 | 572 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text)$$)::util.derived_col_def);
|
| 533 | 573 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 574 |
|
|
| 575 |
to rename: |
|
| 576 |
# rename column |
|
| 577 |
# rename CHECK constraint |
|
| 578 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 534 | 579 |
'; |
| 535 | 580 |
|
| 536 | 581 |
|
| ... | ... | |
| 541 | 586 |
COMMENT ON COLUMN taxon_match._matched_has_accepted IS ' |
| 542 | 587 |
derived column; = "*Accepted_name" IS NOT NULL |
| 543 | 588 |
|
| 544 |
to modify: |
|
| 589 |
to modify expr:
|
|
| 545 | 590 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''_matched_has_accepted'')::util.col, $$"*Accepted_name" IS NOT NULL$$)::util.derived_col_def);
|
| 546 | 591 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 592 |
|
|
| 593 |
to rename: |
|
| 594 |
# rename column |
|
| 595 |
# rename CHECK constraint |
|
| 596 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 547 | 597 |
'; |
| 548 | 598 |
|
| 549 | 599 |
|
| trunk/schemas/util.sql | ||
|---|---|---|
| 1976 | 1976 |
SELECT util.set_comment(def.col, ' |
| 1977 | 1977 |
derived column; = '||util.derived_col_expr(def.col)||' |
| 1978 | 1978 |
|
| 1979 |
to modify: |
|
| 1979 |
to modify expr:
|
|
| 1980 | 1980 |
SELECT util.derived_col_update('||util.quote_typed(def)||');
|
| 1981 | 1981 |
SELECT util.derived_cols_populate('||util.quote_typed((def).col.table_)||');
|
| 1982 |
|
|
| 1983 |
to rename: |
|
| 1984 |
# rename column |
|
| 1985 |
# rename CHECK constraint |
|
| 1986 |
SELECT util.derived_cols_update('||util.quote_typed((def).col.table_)||');
|
|
| 1982 | 1987 |
'); |
| 1983 | 1988 |
|
| 1984 | 1989 |
SELECT util.derived_cols_trigger_update((def).col.table_); |
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 19536 | 19536 |
COMMENT ON COLUMN taxon_match."__accepted_{genus,specific_epithet}" IS '
|
| 19537 | 19537 |
derived column; = regexp_split_to_array("*Accepted_name_species", '' ''::text)
|
| 19538 | 19538 |
|
| 19539 |
to modify: |
|
| 19539 |
to modify expr:
|
|
| 19540 | 19540 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_{genus,specific_epithet}'')::util.col, $$regexp_split_to_array("*Accepted_name_species", '' ''::text)$$)::util.derived_col_def);
|
| 19541 | 19541 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 19542 |
|
|
| 19543 |
to rename: |
|
| 19544 |
# rename column |
|
| 19545 |
# rename CHECK constraint |
|
| 19546 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 19542 | 19547 |
'; |
| 19543 | 19548 |
|
| 19544 | 19549 |
|
| ... | ... | |
| 19549 | 19554 |
COMMENT ON COLUMN taxon_match."[accepted_]genus__@DwC__@vegpath.org" IS ' |
| 19550 | 19555 |
derived column; = "__accepted_{genus,specific_epithet}"[1]
|
| 19551 | 19556 |
|
| 19552 |
to modify: |
|
| 19557 |
to modify expr:
|
|
| 19553 | 19558 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]genus__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet}"[1]$$)::util.derived_col_def);
|
| 19554 | 19559 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 19560 |
|
|
| 19561 |
to rename: |
|
| 19562 |
# rename column |
|
| 19563 |
# rename CHECK constraint |
|
| 19564 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 19555 | 19565 |
'; |
| 19556 | 19566 |
|
| 19557 | 19567 |
|
| ... | ... | |
| 19562 | 19572 |
COMMENT ON COLUMN taxon_match."[accepted_]specificEpithet__@DwC__@vegpath.org" IS ' |
| 19563 | 19573 |
derived column; = "__accepted_{genus,specific_epithet}"[2]
|
| 19564 | 19574 |
|
| 19565 |
to modify: |
|
| 19575 |
to modify expr:
|
|
| 19566 | 19576 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]specificEpithet__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet}"[2]$$)::util.derived_col_def);
|
| 19567 | 19577 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 19578 |
|
|
| 19579 |
to rename: |
|
| 19580 |
# rename column |
|
| 19581 |
# rename CHECK constraint |
|
| 19582 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 19568 | 19583 |
'; |
| 19569 | 19584 |
|
| 19570 | 19585 |
|
| ... | ... | |
| 19575 | 19590 |
COMMENT ON COLUMN taxon_match.__accepted_infraspecific_label IS ' |
| 19576 | 19591 |
derived column; = ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)
|
| 19577 | 19592 |
|
| 19578 |
to modify: |
|
| 19593 |
to modify expr:
|
|
| 19579 | 19594 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_label'')::util.col, $$ltrim(NULLIF(util.remove_prefix("*Accepted_name_species", "*Accepted_name", require := true), ''''::text), '' ''::text)$$)::util.derived_col_def);
|
| 19580 | 19595 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 19596 |
|
|
| 19597 |
to rename: |
|
| 19598 |
# rename column |
|
| 19599 |
# rename CHECK constraint |
|
| 19600 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 19581 | 19601 |
'; |
| 19582 | 19602 |
|
| 19583 | 19603 |
|
| ... | ... | |
| 19588 | 19608 |
COMMENT ON COLUMN taxon_match."__accepted_infraspecific_{rank,epithet}" IS '
|
| 19589 | 19609 |
derived column; = regexp_split_to_array(__accepted_infraspecific_label, '' ''::text) |
| 19590 | 19610 |
|
| 19591 |
to modify: |
|
| 19611 |
to modify expr:
|
|
| 19592 | 19612 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_{rank,epithet}'')::util.col, $$regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)$$)::util.derived_col_def);
|
| 19593 | 19613 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 19614 |
|
|
| 19615 |
to rename: |
|
| 19616 |
# rename column |
|
| 19617 |
# rename CHECK constraint |
|
| 19618 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 19594 | 19619 |
'; |
| 19595 | 19620 |
|
| 19596 | 19621 |
|
| ... | ... | |
| 19601 | 19626 |
COMMENT ON COLUMN taxon_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS ' |
| 19602 | 19627 |
derived column; = "__accepted_infraspecific_{rank,epithet}"[1]
|
| 19603 | 19628 |
|
| 19604 |
to modify: |
|
| 19629 |
to modify expr:
|
|
| 19605 | 19630 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org'')::util.col, $$"__accepted_infraspecific_{rank,epithet}"[1]$$)::util.derived_col_def);
|
| 19606 | 19631 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 19632 |
|
|
| 19633 |
to rename: |
|
| 19634 |
# rename column |
|
| 19635 |
# rename CHECK constraint |
|
| 19636 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 19607 | 19637 |
'; |
| 19608 | 19638 |
|
| 19609 | 19639 |
|
| ... | ... | |
| 19614 | 19644 |
COMMENT ON COLUMN taxon_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" IS ' |
| 19615 | 19645 |
derived column; = "__accepted_infraspecific_{rank,epithet}"[2]
|
| 19616 | 19646 |
|
| 19617 |
to modify: |
|
| 19647 |
to modify expr:
|
|
| 19618 | 19648 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]infraspecificEpithet__@DwC__@vegpath.org'')::util.col, $$"__accepted_infraspecific_{rank,epithet}"[2]$$)::util.derived_col_def);
|
| 19619 | 19649 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 19650 |
|
|
| 19651 |
to rename: |
|
| 19652 |
# rename column |
|
| 19653 |
# rename CHECK constraint |
|
| 19654 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 19620 | 19655 |
'; |
| 19621 | 19656 |
|
| 19622 | 19657 |
|
| ... | ... | |
| 19627 | 19662 |
COMMENT ON COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS ' |
| 19628 | 19663 |
derived column; = "*Name_matched" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)
|
| 19629 | 19664 |
|
| 19630 |
to modify: |
|
| 19665 |
to modify expr:
|
|
| 19631 | 19666 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[matched_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"*Name_matched" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)$$)::util.derived_col_def);
|
| 19632 | 19667 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 19668 |
|
|
| 19669 |
to rename: |
|
| 19670 |
# rename column |
|
| 19671 |
# rename CHECK constraint |
|
| 19672 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 19633 | 19673 |
'; |
| 19634 | 19674 |
|
| 19635 | 19675 |
|
| ... | ... | |
| 19640 | 19680 |
COMMENT ON COLUMN taxon_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" IS ' |
| 19641 | 19681 |
derived column; = "*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text)
|
| 19642 | 19682 |
|
| 19643 |
to modify: |
|
| 19683 |
to modify expr:
|
|
| 19644 | 19684 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text)$$)::util.derived_col_def);
|
| 19645 | 19685 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 19686 |
|
|
| 19687 |
to rename: |
|
| 19688 |
# rename column |
|
| 19689 |
# rename CHECK constraint |
|
| 19690 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 19646 | 19691 |
'; |
| 19647 | 19692 |
|
| 19648 | 19693 |
|
| ... | ... | |
| 19653 | 19698 |
COMMENT ON COLUMN taxon_match._matched_has_accepted IS ' |
| 19654 | 19699 |
derived column; = "*Accepted_name" IS NOT NULL |
| 19655 | 19700 |
|
| 19656 |
to modify: |
|
| 19701 |
to modify expr:
|
|
| 19657 | 19702 |
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''_matched_has_accepted'')::util.col, $$"*Accepted_name" IS NOT NULL$$)::util.derived_col_def);
|
| 19658 | 19703 |
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
| 19704 |
|
|
| 19705 |
to rename: |
|
| 19706 |
# rename column |
|
| 19707 |
# rename CHECK constraint |
|
| 19708 |
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
|
| 19659 | 19709 |
'; |
| 19660 | 19710 |
|
| 19661 | 19711 |
|
Also available in: Unified diff
schemas/util.sql: derived_col_update(): add steps to rename column