Revision 8304
Added by Aaron Marcuse-Kubitza almost 12 years ago
| inputs/ACAD/Specimen/logs/steps.by_col.log.sql | ||
|---|---|---|
| 1 | 1 |
+ date |
| 2 |
Fri Jan 11 05:50:11 PST 2013
|
|
| 2 |
Tue Apr 2 15:19:52 PDT 2013
|
|
| 3 | 3 |
+ time env commit=1 env in_database=vegbien in_schema=ACAD in_table=Specimen out_database=vegbien ../../map Specimen/VegBIEN.csv |
| 4 | 4 |
Processing input rows 0-99 |
| 5 | 5 |
Using 1 parallel CPUs |
| ... | ... | |
| 21 | 21 |
<1>$id</1> |
| 22 | 22 |
<2> |
| 23 | 23 |
<_join> |
| 24 |
<1>$institutionCode</1> |
|
| 24 |
<1> |
|
| 25 |
<_map> |
|
| 26 |
<Acadia University>ACAD</Acadia University> |
|
| 27 |
<*>*</*> |
|
| 28 |
<value>$institutionCode</value> |
|
| 29 |
</_map> |
|
| 30 |
</1> |
|
| 25 | 31 |
<2> |
| 26 | 32 |
<_join> |
| 27 | 33 |
<1>$collectionCode</1> |
| ... | ... | |
| 56 | 62 |
<obsstartdate><_dateRangeStart><value>$eventDate</value></_dateRangeStart></obsstartdate> |
| 57 | 63 |
<taxonoccurrence> |
| 58 | 64 |
<aggregateoccurrence> |
| 65 |
<notes>$occurrenceRemarks</notes> |
|
| 59 | 66 |
<plantobservation> |
| 60 |
<collectionnumber>$recordNumber</collectionnumber> |
|
| 67 |
<authorplantcode>$recordNumber</authorplantcode> |
|
| 68 |
<reproductivecondition>$reproductiveCondition</reproductivecondition> |
|
| 61 | 69 |
<specimenreplicate> |
| 62 | 70 |
<catalognumber_dwc>$catalogNumber</catalognumber_dwc> |
| 63 | 71 |
<collectioncode_dwc> |
| 64 |
<_join>
|
|
| 72 |
<_alt>
|
|
| 65 | 73 |
<1>$collectionCode</1> |
| 66 | 74 |
<2>$collectionID</2> |
| 67 |
</_join>
|
|
| 75 |
</_alt>
|
|
| 68 | 76 |
</collectioncode_dwc> |
| 77 |
<collectionnumber>$recordNumber</collectionnumber> |
|
| 69 | 78 |
<institution_id> |
| 70 | 79 |
<sourcelist> |
| 71 |
<name>$institutionCode</name> |
|
| 80 |
<name> |
|
| 81 |
<_map> |
|
| 82 |
<Acadia University>ACAD</Acadia University> |
|
| 83 |
<*>*</*> |
|
| 84 |
<value>$institutionCode</value> |
|
| 85 |
</_map> |
|
| 86 |
</name> |
|
| 72 | 87 |
<sourcename> |
| 73 | 88 |
<name> |
| 74 | 89 |
<_split> |
| 75 |
<separator>, </separator> |
|
| 76 |
<value>$institutionCode</value> |
|
| 90 |
<separator>[,;] *</separator> |
|
| 91 |
<value> |
|
| 92 |
<_map> |
|
| 93 |
<Acadia University>ACAD</Acadia University> |
|
| 94 |
<*>*</*> |
|
| 95 |
<value>$institutionCode</value> |
|
| 96 |
</_map> |
|
| 97 |
</value> |
|
| 77 | 98 |
</_split> |
| 78 | 99 |
</name> |
| 79 | 100 |
</sourcename> |
| ... | ... | |
| 91 | 112 |
<taxonverbatim> |
| 92 | 113 |
<taxonlabel_id> |
| 93 | 114 |
<taxonlabel> |
| 94 |
<creationdate><_dateRangeStart><value>$dateIdentified</value></_dateRangeStart></creationdate> |
|
| 95 | 115 |
<parent_id> |
| 96 | 116 |
<taxonlabel> |
| 97 | 117 |
<rank>$taxonRank</rank> |
| ... | ... | |
| 216 | 236 |
<value>$habitat</value> |
| 217 | 237 |
</_label> |
| 218 | 238 |
</3> |
| 239 |
<4>$locationRemarks</4> |
|
| 219 | 240 |
</_merge> |
| 220 | 241 |
</locationnarrative> |
| 221 | 242 |
<locationplace> |
| ... | ... | |
| 294 | 315 |
<1>$id</1> |
| 295 | 316 |
<2> |
| 296 | 317 |
<_join> |
| 297 |
<1>$institutionCode</1> |
|
| 318 |
<1> |
|
| 319 |
<_map> |
|
| 320 |
<Acadia University>ACAD</Acadia University> |
|
| 321 |
<*>*</*> |
|
| 322 |
<value>$institutionCode</value> |
|
| 323 |
</_map> |
|
| 324 |
</1> |
|
| 298 | 325 |
<2> |
| 299 | 326 |
<_join> |
| 300 | 327 |
<1>$collectionCode</1> |
| ... | ... | |
| 323 | 350 |
<obsstartdate><_dateRangeStart><value>$eventDate</value></_dateRangeStart></obsstartdate> |
| 324 | 351 |
<taxonoccurrence> |
| 325 | 352 |
<aggregateoccurrence> |
| 353 |
<notes>$occurrenceRemarks</notes> |
|
| 326 | 354 |
<plantobservation> |
| 327 |
<collectionnumber>$recordNumber</collectionnumber> |
|
| 355 |
<authorplantcode>$recordNumber</authorplantcode> |
|
| 356 |
<reproductivecondition>$reproductiveCondition</reproductivecondition> |
|
| 328 | 357 |
<specimenreplicate> |
| 329 | 358 |
<catalognumber_dwc>$catalogNumber</catalognumber_dwc> |
| 330 | 359 |
<collectioncode_dwc> |
| 331 |
<_join>
|
|
| 360 |
<_alt>
|
|
| 332 | 361 |
<1>$collectionCode</1> |
| 333 | 362 |
<2>$collectionID</2> |
| 334 |
</_join>
|
|
| 363 |
</_alt>
|
|
| 335 | 364 |
</collectioncode_dwc> |
| 365 |
<collectionnumber>$recordNumber</collectionnumber> |
|
| 336 | 366 |
<institution_id> |
| 337 | 367 |
<sourcelist> |
| 338 |
<name>$institutionCode</name> |
|
| 368 |
<name> |
|
| 369 |
<_map> |
|
| 370 |
<Acadia University>ACAD</Acadia University> |
|
| 371 |
<*>*</*> |
|
| 372 |
<value>$institutionCode</value> |
|
| 373 |
</_map> |
|
| 374 |
</name> |
|
| 339 | 375 |
<sourcename> |
| 340 | 376 |
<name> |
| 341 | 377 |
<_split> |
| 342 |
<separator>, </separator> |
|
| 343 |
<value>$institutionCode</value> |
|
| 378 |
<separator>[,;] *</separator> |
|
| 379 |
<value> |
|
| 380 |
<_map> |
|
| 381 |
<Acadia University>ACAD</Acadia University> |
|
| 382 |
<*>*</*> |
|
| 383 |
<value>$institutionCode</value> |
|
| 384 |
</_map> |
|
| 385 |
</value> |
|
| 344 | 386 |
</_split> |
| 345 | 387 |
</name> |
| 346 | 388 |
</sourcename> |
| ... | ... | |
| 358 | 400 |
<taxonverbatim> |
| 359 | 401 |
<taxonlabel_id> |
| 360 | 402 |
<taxonlabel> |
| 361 |
<creationdate><_dateRangeStart><value>$dateIdentified</value></_dateRangeStart></creationdate> |
|
| 362 | 403 |
<parent_id> |
| 363 | 404 |
<taxonlabel> |
| 364 | 405 |
<rank>$taxonRank</rank> |
| ... | ... | |
| 483 | 524 |
<value>$habitat</value> |
| 484 | 525 |
</_label> |
| 485 | 526 |
</3> |
| 527 |
<4>$locationRemarks</4> |
|
| 486 | 528 |
</_merge> |
| 487 | 529 |
</locationnarrative> |
| 488 | 530 |
<locationplace> |
| ... | ... | |
| 559 | 601 |
LIMIT 100 |
| 560 | 602 |
</code></pre> |
| 561 | 603 |
# ********** New iteration ********** |
| 604 |
# Inserting these input columns into @"_map"@: |
|
| 605 |
|
|
| 606 |
|Output|Input| |
|
| 607 |
|@'map'@|@{'Acadia University': u'ACAD', '*': u'*'}@|
|
|
| 608 |
|@'value'@|@"ACAD.Specimen"."institutionCode"@| |
|
| 609 |
|
|
| 610 |
# Joining together input tables into temp table |
|
| 611 |
<pre><code class="SQL"> |
|
| 612 |
CREATE TEMP TABLE "in" AS |
|
| 613 |
SELECT |
|
| 614 |
"ACAD.Specimen"."row_num" AS "row_num" |
|
| 615 |
, "ACAD.Specimen"."institutionCode" AS "ACAD.Specimen.institutionCode" |
|
| 616 |
FROM "ACAD.Specimen" |
|
| 617 |
ORDER BY row_num |
|
| 618 |
</code></pre>Temp table: @"in"@ |
|
| 619 |
# Defining wrapper function |
|
| 620 |
<pre><code class="SQL"> |
|
| 621 |
CREATE TEMP TABLE "_map(institutionCode)" AS |
|
| 622 |
SELECT |
|
| 623 |
"in"."row_num" |
|
| 624 |
, "_map"("map" := hstore(ARRAY['Acadia University', '*'], ARRAY['ACAD', '*']), "value" := "in"."ACAD.Specimen.institutionCode") AS "result"
|
|
| 625 |
FROM "in" |
|
| 626 |
LIMIT 0 |
|
| 627 |
</code></pre><pre><code class="SQL"> |
|
| 628 |
CREATE FUNCTION "pg_temp"."_map(institutionCode)_wrap"() |
|
| 629 |
RETURNS SETOF "_map(institutionCode)" |
|
| 630 |
LANGUAGE plpgsql |
|
| 631 |
AS $$ |
|
| 632 |
DECLARE |
|
| 633 |
"row" "in"%ROWTYPE; |
|
| 634 |
BEGIN |
|
| 635 |
FOR "row" IN |
|
| 636 |
SELECT * FROM "in" |
|
| 637 |
LOOP |
|
| 638 |
BEGIN |
|
| 639 |
BEGIN |
|
| 640 |
RETURN QUERY |
|
| 641 |
SELECT |
|
| 642 |
"row"."row_num" |
|
| 643 |
, "_map"("map" := hstore(ARRAY['Acadia University', '*'], ARRAY['ACAD', '*']), "value" := "row"."ACAD.Specimen.institutionCode")
|
|
| 644 |
; |
|
| 645 |
EXCEPTION |
|
| 646 |
WHEN internal_error THEN |
|
| 647 |
-- Handle PL/Python exceptions |
|
| 648 |
DECLARE |
|
| 649 |
matches text[] := regexp_matches(SQLERRM, |
|
| 650 |
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n |
|
| 651 |
exc_name text := matches[1]; |
|
| 652 |
msg text := matches[2]; |
|
| 653 |
BEGIN |
|
| 654 |
/* Re-raise PL/Python exceptions with the PL/Python prefix removed. |
|
| 655 |
This allows the exception to be parsed like a native exception. |
|
| 656 |
Always raise as data_exception so it goes in the errors table. */ |
|
| 657 |
IF exc_name IS NOT NULL THEN |
|
| 658 |
RAISE data_exception USING MESSAGE = msg; |
|
| 659 |
-- Re-raise non-PL/Python exceptions |
|
| 660 |
ELSE |
|
| 661 |
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; |
|
| 662 |
END IF; |
|
| 663 |
END; |
|
| 664 |
END; |
|
| 665 |
EXCEPTION |
|
| 666 |
WHEN data_exception THEN |
|
| 667 |
-- Save error in errors table. |
|
| 668 |
DECLARE |
|
| 669 |
error_code text := SQLSTATE; |
|
| 670 |
error text := SQLERRM; |
|
| 671 |
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.institutionCode" AS text), CAST(NULL AS text))], ',');
|
|
| 672 |
"column" text; |
|
| 673 |
BEGIN |
|
| 674 |
-- Insert the value and error for *each* source column. |
|
| 675 |
FOR "column" IN |
|
| 676 |
SELECT * FROM (VALUES ('institutionCode')) AS "c"
|
|
| 677 |
LOOP |
|
| 678 |
BEGIN |
|
| 679 |
INSERT INTO "ACAD"."Specimen.errors" |
|
| 680 |
("column", "value", "error_code", "error")
|
|
| 681 |
VALUES ("column", "value", "error_code", "error");
|
|
| 682 |
EXCEPTION |
|
| 683 |
WHEN unique_violation THEN NULL; |
|
| 684 |
END; |
|
| 685 |
END LOOP; |
|
| 686 |
|
|
| 687 |
END; |
|
| 688 |
|
|
| 689 |
RETURN QUERY |
|
| 690 |
SELECT |
|
| 691 |
"row"."row_num" |
|
| 692 |
, CAST(NULL AS text) |
|
| 693 |
; |
|
| 694 |
END; |
|
| 695 |
END LOOP; |
|
| 696 |
END; |
|
| 697 |
|
|
| 698 |
$$; |
|
| 699 |
</code></pre> |
|
| 700 |
# Calling function |
|
| 701 |
<pre><code class="SQL"> |
|
| 702 |
INSERT INTO "_map(institutionCode)" |
|
| 703 |
("row_num", "result")
|
|
| 704 |
SELECT * FROM "pg_temp"."_map(institutionCode)_wrap"() |
|
| 705 |
</code></pre> |
|
| 706 |
# ********** New iteration ********** |
|
| 562 | 707 |
# Inserting these input columns into @"_join"@: |
| 563 | 708 |
|
| 564 | 709 |
|Output|Input| |
| ... | ... | |
| 567 | 712 |
|
| 568 | 713 |
# Joining together input tables into temp table |
| 569 | 714 |
<pre><code class="SQL"> |
| 570 |
CREATE TEMP TABLE "in" AS |
|
| 715 |
CREATE TEMP TABLE "in#1" AS
|
|
| 571 | 716 |
SELECT |
| 572 | 717 |
"ACAD.Specimen"."row_num" AS "row_num" |
| 573 | 718 |
, "ACAD.Specimen"."collectionCode" AS "ACAD.Specimen.collectionCode" |
| 574 | 719 |
, "ACAD.Specimen"."collectionID" AS "ACAD.Specimen.collectionID" |
| 575 | 720 |
FROM "ACAD.Specimen" |
| 576 | 721 |
ORDER BY row_num |
| 577 |
</code></pre>Temp table: @"in"@ |
|
| 722 |
</code></pre>Temp table: @"in#1"@
|
|
| 578 | 723 |
# Defining wrapper function |
| 579 | 724 |
<pre><code class="SQL"> |
| 580 | 725 |
CREATE TEMP TABLE "_join(1=collectionCode, 2=collectionID)" AS |
| 581 | 726 |
SELECT |
| 582 |
"in"."row_num" |
|
| 583 |
, "_join"("1" := "in"."ACAD.Specimen.collectionCode", "2" := "in"."ACAD.Specimen.collectionID") AS "result"
|
|
| 584 |
FROM "in" |
|
| 727 |
"in#1"."row_num"
|
|
| 728 |
, "_join"("1" := "in#1"."ACAD.Specimen.collectionCode", "2" := "in#1"."ACAD.Specimen.collectionID") AS "result"
|
|
| 729 |
FROM "in#1"
|
|
| 585 | 730 |
LIMIT 0 |
| 586 | 731 |
</code></pre><pre><code class="SQL"> |
| 587 | 732 |
CREATE FUNCTION "pg_temp"."_join(1=collectionCode, 2=collectionID)_wrap"() |
| ... | ... | |
| 589 | 734 |
LANGUAGE plpgsql |
| 590 | 735 |
AS $$ |
| 591 | 736 |
DECLARE |
| 592 |
"row" "in"%ROWTYPE; |
|
| 737 |
"row" "in#1"%ROWTYPE;
|
|
| 593 | 738 |
BEGIN |
| 594 | 739 |
FOR "row" IN |
| 595 |
SELECT * FROM "in" |
|
| 740 |
SELECT * FROM "in#1"
|
|
| 596 | 741 |
LOOP |
| 597 | 742 |
BEGIN |
| 598 | 743 |
BEGIN |
| ... | ... | |
| 666 | 811 |
# Inserting these input columns into @"_join"@: |
| 667 | 812 |
|
| 668 | 813 |
|Output|Input| |
| 669 |
|@'1'@|@"ACAD.Specimen"."institutionCode"@|
|
|
| 814 |
|@'1'@|@"_map(institutionCode)"."result"@|
|
|
| 670 | 815 |
|@'3'@|@"ACAD.Specimen"."catalogNumber"@| |
| 671 | 816 |
|@'2'@|@"_join(1=collectionCode, 2=collectionID)"."result"@| |
| 672 | 817 |
|
| 673 | 818 |
# Joining together input tables into temp table |
| 674 | 819 |
<pre><code class="SQL"> |
| 675 |
CREATE TEMP TABLE "in#1" AS
|
|
| 820 |
CREATE TEMP TABLE "in#2" AS
|
|
| 676 | 821 |
SELECT |
| 677 | 822 |
"ACAD.Specimen"."row_num" AS "row_num" |
| 678 |
, "ACAD.Specimen"."institutionCode" AS "ACAD.Specimen.institutionCode"
|
|
| 823 |
, "_map(institutionCode)"."result" AS "_map(institutionCode).result"
|
|
| 679 | 824 |
, "ACAD.Specimen"."catalogNumber" AS "ACAD.Specimen.catalogNumber" |
| 680 | 825 |
, "_join(1=collectionCode, 2=collectionID)"."result" AS "_join(1=collectionCode, 2=collectionID).result" |
| 681 | 826 |
FROM "ACAD.Specimen" |
| 827 |
JOIN "_map(institutionCode)" USING ("row_num")
|
|
| 682 | 828 |
JOIN "_join(1=collectionCode, 2=collectionID)" USING ("row_num")
|
| 683 | 829 |
ORDER BY row_num |
| 684 |
</code></pre>Temp table: @"in#1"@
|
|
| 830 |
</code></pre>Temp table: @"in#2"@
|
|
| 685 | 831 |
# Defining wrapper function |
| 686 | 832 |
<pre><code class="SQL"> |
| 687 |
CREATE TEMP TABLE "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC" AS
|
|
| 833 |
CREATE TEMP TABLE "_join(1=_map(institutionCode), 3=catalogNumber, 2=_join(1=colle" AS
|
|
| 688 | 834 |
SELECT |
| 689 |
"in#1"."row_num"
|
|
| 690 |
, "_join"("1" := "in#1"."ACAD.Specimen.institutionCode", "3" := "in#1"."ACAD.Specimen.catalogNumber", "2" := "in#1"."_join(1=collectionCode, 2=collectionID).result") AS "result"
|
|
| 691 |
FROM "in#1"
|
|
| 835 |
"in#2"."row_num"
|
|
| 836 |
, "_join"("1" := "in#2"."_map(institutionCode).result", "3" := "in#2"."ACAD.Specimen.catalogNumber", "2" := "in#2"."_join(1=collectionCode, 2=collectionID).result") AS "result"
|
|
| 837 |
FROM "in#2"
|
|
| 692 | 838 |
LIMIT 0 |
| 693 | 839 |
</code></pre><pre><code class="SQL"> |
| 694 |
CREATE FUNCTION "pg_temp"."_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collec_wrap"()
|
|
| 695 |
RETURNS SETOF "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC"
|
|
| 840 |
CREATE FUNCTION "pg_temp"."_join(1=_map(institutionCode), 3=catalogNumber, 2=_join(1=_wrap"()
|
|
| 841 |
RETURNS SETOF "_join(1=_map(institutionCode), 3=catalogNumber, 2=_join(1=colle"
|
|
| 696 | 842 |
LANGUAGE plpgsql |
| 697 | 843 |
AS $$ |
| 698 | 844 |
DECLARE |
| 699 |
"row" "in#1"%ROWTYPE;
|
|
| 845 |
"row" "in#2"%ROWTYPE;
|
|
| 700 | 846 |
BEGIN |
| 701 | 847 |
FOR "row" IN |
| 702 |
SELECT * FROM "in#1"
|
|
| 848 |
SELECT * FROM "in#2"
|
|
| 703 | 849 |
LOOP |
| 704 | 850 |
BEGIN |
| 705 | 851 |
BEGIN |
| 706 | 852 |
RETURN QUERY |
| 707 | 853 |
SELECT |
| 708 | 854 |
"row"."row_num" |
| 709 |
, "_join"("1" := "row"."ACAD.Specimen.institutionCode", "3" := "row"."ACAD.Specimen.catalogNumber", "2" := "row"."_join(1=collectionCode, 2=collectionID).result")
|
|
| 855 |
, "_join"("1" := "row"."_map(institutionCode).result", "3" := "row"."ACAD.Specimen.catalogNumber", "2" := "row"."_join(1=collectionCode, 2=collectionID).result")
|
|
| 710 | 856 |
; |
| 711 | 857 |
EXCEPTION |
| 712 | 858 |
WHEN internal_error THEN |
| ... | ... | |
| 734 | 880 |
DECLARE |
| 735 | 881 |
error_code text := SQLSTATE; |
| 736 | 882 |
error text := SQLERRM; |
| 737 |
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.institutionCode" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."ACAD.Specimen.catalogNumber" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."_join(1=collectionCode, 2=collectionID).result" AS text), CAST(NULL AS text))], ',');
|
|
| 883 |
value text := array_to_string(ARRAY[COALESCE(CAST("row"."_map(institutionCode).result" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."ACAD.Specimen.catalogNumber" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."_join(1=collectionCode, 2=collectionID).result" AS text), CAST(NULL AS text))], ',');
|
|
| 738 | 884 |
"column" text; |
| 739 | 885 |
BEGIN |
| 740 | 886 |
-- Insert the value and error for *each* source column. |
| ... | ... | |
| 765 | 911 |
</code></pre> |
| 766 | 912 |
# Calling function |
| 767 | 913 |
<pre><code class="SQL"> |
| 768 |
INSERT INTO "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC"
|
|
| 914 |
INSERT INTO "_join(1=_map(institutionCode), 3=catalogNumber, 2=_join(1=colle"
|
|
| 769 | 915 |
("row_num", "result")
|
| 770 |
SELECT * FROM "pg_temp"."_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collec_wrap"()
|
|
| 916 |
SELECT * FROM "pg_temp"."_join(1=_map(institutionCode), 3=catalogNumber, 2=_join(1=_wrap"()
|
|
| 771 | 917 |
</code></pre> |
| 772 | 918 |
# ********** New iteration ********** |
| 773 | 919 |
# Inserting these input columns into @"_alt"@: |
| 774 | 920 |
|
| 775 | 921 |
|Output|Input| |
| 776 | 922 |
|@'1'@|@"ACAD.Specimen"."id"@| |
| 777 |
|@'2'@|@"_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC"."result"@|
|
|
| 923 |
|@'2'@|@"_join(1=_map(institutionCode), 3=catalogNumber, 2=_join(1=colle"."result"@|
|
|
| 778 | 924 |
|
| 779 | 925 |
# Joining together input tables into temp table |
| 780 | 926 |
<pre><code class="SQL"> |
| 781 |
CREATE TEMP TABLE "in#2" AS
|
|
| 927 |
CREATE TEMP TABLE "in#3" AS
|
|
| 782 | 928 |
SELECT |
| 783 | 929 |
"ACAD.Specimen"."row_num" AS "row_num" |
| 784 | 930 |
, "ACAD.Specimen"."id" AS "ACAD.Specimen.id" |
| 785 |
, "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC"."result" AS "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=coll.result"
|
|
| 931 |
, "_join(1=_map(institutionCode), 3=catalogNumber, 2=_join(1=colle"."result" AS "_join(1=_map(institutionCode), 3=catalogNumber, 2=_join(.result"
|
|
| 786 | 932 |
FROM "ACAD.Specimen" |
| 787 |
JOIN "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC" USING ("row_num")
|
|
| 933 |
JOIN "_join(1=_map(institutionCode), 3=catalogNumber, 2=_join(1=colle" USING ("row_num")
|
|
| 788 | 934 |
ORDER BY row_num |
| 789 |
</code></pre>Temp table: @"in#2"@
|
|
| 935 |
</code></pre>Temp table: @"in#3"@
|
|
| 790 | 936 |
# Defining wrapper function |
| 791 | 937 |
<pre><code class="SQL"> |
| 792 |
CREATE TEMP TABLE "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join(" AS
|
|
| 938 |
CREATE TEMP TABLE "_alt(1=id, 2=_join(1=_map(institutionCode), 3=catalogNumber, 2=" AS
|
|
| 793 | 939 |
SELECT |
| 794 |
"in#2"."row_num"
|
|
| 795 |
, "_alt"("1" := "in#2"."ACAD.Specimen.id", "2" := "in#2"."_join(1=institutionCode, 3=catalogNumber, 2=_join(1=coll.result") AS "result"
|
|
| 796 |
FROM "in#2"
|
|
| 940 |
"in#3"."row_num"
|
|
| 941 |
, "_alt"("1" := "in#3"."ACAD.Specimen.id", "2" := "in#3"."_join(1=_map(institutionCode), 3=catalogNumber, 2=_join(.result") AS "result"
|
|
| 942 |
FROM "in#3"
|
|
| 797 | 943 |
LIMIT 0 |
| 798 | 944 |
</code></pre><pre><code class="SQL"> |
| 799 |
CREATE FUNCTION "pg_temp"."_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=__wrap"()
|
|
| 800 |
RETURNS SETOF "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join("
|
|
| 945 |
CREATE FUNCTION "pg_temp"."_alt(1=id, 2=_join(1=_map(institutionCode), 3=catalogNumbe_wrap"()
|
|
| 946 |
RETURNS SETOF "_alt(1=id, 2=_join(1=_map(institutionCode), 3=catalogNumber, 2="
|
|
| 801 | 947 |
LANGUAGE plpgsql |
| 802 | 948 |
AS $$ |
| 803 | 949 |
DECLARE |
| 804 |
"row" "in#2"%ROWTYPE;
|
|
| 950 |
"row" "in#3"%ROWTYPE;
|
|
| 805 | 951 |
BEGIN |
| 806 | 952 |
FOR "row" IN |
| 807 |
SELECT * FROM "in#2"
|
|
| 953 |
SELECT * FROM "in#3"
|
|
| 808 | 954 |
LOOP |
| 809 | 955 |
BEGIN |
| 810 | 956 |
BEGIN |
| 811 | 957 |
RETURN QUERY |
| 812 | 958 |
SELECT |
| 813 | 959 |
"row"."row_num" |
| 814 |
, "_alt"("1" := "row"."ACAD.Specimen.id", "2" := "row"."_join(1=institutionCode, 3=catalogNumber, 2=_join(1=coll.result")
|
|
| 960 |
, "_alt"("1" := "row"."ACAD.Specimen.id", "2" := "row"."_join(1=_map(institutionCode), 3=catalogNumber, 2=_join(.result")
|
|
| 815 | 961 |
; |
| 816 | 962 |
EXCEPTION |
| 817 | 963 |
WHEN internal_error THEN |
| ... | ... | |
| 839 | 985 |
DECLARE |
| 840 | 986 |
error_code text := SQLSTATE; |
| 841 | 987 |
error text := SQLERRM; |
| 842 |
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.id" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."_join(1=institutionCode, 3=catalogNumber, 2=_join(1=coll.result" AS text), CAST(NULL AS text))], ',');
|
|
| 988 |
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.id" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."_join(1=_map(institutionCode), 3=catalogNumber, 2=_join(.result" AS text), CAST(NULL AS text))], ',');
|
|
| 843 | 989 |
"column" text; |
| 844 | 990 |
BEGIN |
| 845 | 991 |
-- Insert the value and error for *each* source column. |
| ... | ... | |
| 870 | 1016 |
</code></pre> |
| 871 | 1017 |
# Calling function |
| 872 | 1018 |
<pre><code class="SQL"> |
| 873 |
INSERT INTO "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join("
|
|
| 1019 |
INSERT INTO "_alt(1=id, 2=_join(1=_map(institutionCode), 3=catalogNumber, 2="
|
|
| 874 | 1020 |
("row_num", "result")
|
| 875 |
SELECT * FROM "pg_temp"."_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=__wrap"()
|
|
| 1021 |
SELECT * FROM "pg_temp"."_alt(1=id, 2=_join(1=_map(institutionCode), 3=catalogNumbe_wrap"()
|
|
| 876 | 1022 |
</code></pre> |
| 877 | 1023 |
# ********** New iteration ********** |
| 878 | 1024 |
# Inserting these input columns into @"_label"@: |
| ... | ... | |
| 883 | 1029 |
|
| 884 | 1030 |
# Joining together input tables into temp table |
| 885 | 1031 |
<pre><code class="SQL"> |
| 886 |
CREATE TEMP TABLE "in#3" AS
|
|
| 1032 |
CREATE TEMP TABLE "in#4" AS
|
|
| 887 | 1033 |
SELECT |
| 888 | 1034 |
"ACAD.Specimen"."row_num" AS "row_num" |
| 889 | 1035 |
, "ACAD.Specimen"."habitat" AS "ACAD.Specimen.habitat" |
| 890 | 1036 |
FROM "ACAD.Specimen" |
| 891 | 1037 |
ORDER BY row_num |
| 892 |
</code></pre>Temp table: @"in#3"@
|
|
| 1038 |
</code></pre>Temp table: @"in#4"@
|
|
| 893 | 1039 |
# Defining wrapper function |
| 894 | 1040 |
<pre><code class="SQL"> |
| 895 | 1041 |
CREATE TEMP TABLE "_label(habitat)" AS |
| 896 | 1042 |
SELECT |
| 897 |
"in#3"."row_num"
|
|
| 898 |
, "_label"("value" := "in#3"."ACAD.Specimen.habitat", "label" := 'habitat') AS "result"
|
|
| 899 |
FROM "in#3"
|
|
| 1043 |
"in#4"."row_num"
|
|
| 1044 |
, "_label"("value" := "in#4"."ACAD.Specimen.habitat", "label" := 'habitat') AS "result"
|
|
| 1045 |
FROM "in#4"
|
|
| 900 | 1046 |
LIMIT 0 |
| 901 | 1047 |
</code></pre><pre><code class="SQL"> |
| 902 | 1048 |
CREATE FUNCTION "pg_temp"."_label(habitat)_wrap"() |
| ... | ... | |
| 904 | 1050 |
LANGUAGE plpgsql |
| 905 | 1051 |
AS $$ |
| 906 | 1052 |
DECLARE |
| 907 |
"row" "in#3"%ROWTYPE;
|
|
| 1053 |
"row" "in#4"%ROWTYPE;
|
|
| 908 | 1054 |
BEGIN |
| 909 | 1055 |
FOR "row" IN |
| 910 |
SELECT * FROM "in#3"
|
|
| 1056 |
SELECT * FROM "in#4"
|
|
| 911 | 1057 |
LOOP |
| 912 | 1058 |
BEGIN |
| 913 | 1059 |
BEGIN |
| ... | ... | |
| 983 | 1129 |
|Output|Input| |
| 984 | 1130 |
|@'1'@|@"ACAD.Specimen"."locality"@| |
| 985 | 1131 |
|@'3'@|@"_label(habitat)"."result"@| |
| 1132 |
|@'4'@|@"ACAD.Specimen"."locationRemarks"@| |
|
| 986 | 1133 |
|
| 987 | 1134 |
# Joining together input tables into temp table |
| 988 | 1135 |
<pre><code class="SQL"> |
| 989 |
CREATE TEMP TABLE "in#4" AS
|
|
| 1136 |
CREATE TEMP TABLE "in#5" AS
|
|
| 990 | 1137 |
SELECT |
| 991 | 1138 |
"ACAD.Specimen"."row_num" AS "row_num" |
| 992 | 1139 |
, "ACAD.Specimen"."locality" AS "ACAD.Specimen.locality" |
| 993 | 1140 |
, "_label(habitat)"."result" AS "_label(habitat).result" |
| 1141 |
, "ACAD.Specimen"."locationRemarks" AS "ACAD.Specimen.locationRemarks" |
|
| 994 | 1142 |
FROM "ACAD.Specimen" |
| 995 | 1143 |
JOIN "_label(habitat)" USING ("row_num")
|
| 996 | 1144 |
ORDER BY row_num |
| 997 |
</code></pre>Temp table: @"in#4"@
|
|
| 1145 |
</code></pre>Temp table: @"in#5"@
|
|
| 998 | 1146 |
# Defining wrapper function |
| 999 | 1147 |
<pre><code class="SQL"> |
| 1000 |
CREATE TEMP TABLE "_merge(1=locality, 3=_label(habitat))" AS |
|
| 1148 |
CREATE TEMP TABLE "_merge(1=locality, 3=_label(habitat), 4=locationRemarks)" AS
|
|
| 1001 | 1149 |
SELECT |
| 1002 |
"in#4"."row_num"
|
|
| 1003 |
, "_merge"("1" := "in#4"."ACAD.Specimen.locality", "3" := "in#4"."_label(habitat).result") AS "result"
|
|
| 1004 |
FROM "in#4"
|
|
| 1150 |
"in#5"."row_num"
|
|
| 1151 |
, "_merge"("1" := "in#5"."ACAD.Specimen.locality", "3" := "in#5"."_label(habitat).result", "4" := "in#5"."ACAD.Specimen.locationRemarks") AS "result"
|
|
| 1152 |
FROM "in#5"
|
|
| 1005 | 1153 |
LIMIT 0 |
| 1006 | 1154 |
</code></pre><pre><code class="SQL"> |
| 1007 |
CREATE FUNCTION "pg_temp"."_merge(1=locality, 3=_label(habitat))_wrap"() |
|
| 1008 |
RETURNS SETOF "_merge(1=locality, 3=_label(habitat))" |
|
| 1155 |
CREATE FUNCTION "pg_temp"."_merge(1=locality, 3=_label(habitat), 4=locationRemarks)_wrap"()
|
|
| 1156 |
RETURNS SETOF "_merge(1=locality, 3=_label(habitat), 4=locationRemarks)"
|
|
| 1009 | 1157 |
LANGUAGE plpgsql |
| 1010 | 1158 |
AS $$ |
| 1011 | 1159 |
DECLARE |
| 1012 |
"row" "in#4"%ROWTYPE;
|
|
| 1160 |
"row" "in#5"%ROWTYPE;
|
|
| 1013 | 1161 |
BEGIN |
| 1014 | 1162 |
FOR "row" IN |
| 1015 |
SELECT * FROM "in#4"
|
|
| 1163 |
SELECT * FROM "in#5"
|
|
| 1016 | 1164 |
LOOP |
| 1017 | 1165 |
BEGIN |
| 1018 | 1166 |
BEGIN |
| 1019 | 1167 |
RETURN QUERY |
| 1020 | 1168 |
SELECT |
| 1021 | 1169 |
"row"."row_num" |
| 1022 |
, "_merge"("1" := "row"."ACAD.Specimen.locality", "3" := "row"."_label(habitat).result")
|
|
| 1170 |
, "_merge"("1" := "row"."ACAD.Specimen.locality", "3" := "row"."_label(habitat).result", "4" := "row"."ACAD.Specimen.locationRemarks")
|
|
| 1023 | 1171 |
; |
| 1024 | 1172 |
EXCEPTION |
| 1025 | 1173 |
WHEN internal_error THEN |
| ... | ... | |
| 1047 | 1195 |
DECLARE |
| 1048 | 1196 |
error_code text := SQLSTATE; |
| 1049 | 1197 |
error text := SQLERRM; |
| 1050 |
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.locality" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."_label(habitat).result" AS text), CAST(NULL AS text))], ',');
|
|
| 1198 |
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.locality" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."_label(habitat).result" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."ACAD.Specimen.locationRemarks" AS text), CAST(NULL AS text))], ',');
|
|
| 1051 | 1199 |
"column" text; |
| 1052 | 1200 |
BEGIN |
| 1053 | 1201 |
-- Insert the value and error for *each* source column. |
| 1054 | 1202 |
FOR "column" IN |
| 1055 |
SELECT * FROM (VALUES ('locality,habitat')) AS "c"
|
|
| 1203 |
SELECT * FROM (VALUES ('locality,habitat,locationRemarks')) AS "c"
|
|
| 1056 | 1204 |
LOOP |
| 1057 | 1205 |
BEGIN |
| 1058 | 1206 |
INSERT INTO "ACAD"."Specimen.errors" |
| ... | ... | |
| 1078 | 1226 |
</code></pre> |
| 1079 | 1227 |
# Calling function |
| 1080 | 1228 |
<pre><code class="SQL"> |
| 1081 |
INSERT INTO "_merge(1=locality, 3=_label(habitat))" |
|
| 1229 |
INSERT INTO "_merge(1=locality, 3=_label(habitat), 4=locationRemarks)"
|
|
| 1082 | 1230 |
("row_num", "result")
|
| 1083 |
SELECT * FROM "pg_temp"."_merge(1=locality, 3=_label(habitat))_wrap"() |
|
| 1231 |
SELECT * FROM "pg_temp"."_merge(1=locality, 3=_label(habitat), 4=locationRemarks)_wrap"()
|
|
| 1084 | 1232 |
</code></pre> |
| 1085 | 1233 |
# ********** New iteration ********** |
| 1086 | 1234 |
# Inserting these input columns into @"_join"@: |
| ... | ... | |
| 1091 | 1239 |
|
| 1092 | 1240 |
# Joining together input tables into temp table |
| 1093 | 1241 |
<pre><code class="SQL"> |
| 1094 |
CREATE TEMP TABLE "in#5" AS
|
|
| 1242 |
CREATE TEMP TABLE "in#6" AS
|
|
| 1095 | 1243 |
SELECT |
| 1096 | 1244 |
"ACAD.Specimen"."row_num" AS "row_num" |
| 1097 | 1245 |
, "ACAD.Specimen"."locality" AS "ACAD.Specimen.locality" |
| 1098 | 1246 |
, "ACAD.Specimen"."habitat" AS "ACAD.Specimen.habitat" |
| 1099 | 1247 |
FROM "ACAD.Specimen" |
| 1100 | 1248 |
ORDER BY row_num |
| 1101 |
</code></pre>Temp table: @"in#5"@
|
|
| 1249 |
</code></pre>Temp table: @"in#6"@
|
|
| 1102 | 1250 |
# Defining wrapper function |
| 1103 | 1251 |
<pre><code class="SQL"> |
| 1104 | 1252 |
CREATE TEMP TABLE "_join(1=locality, 3=habitat)" AS |
| 1105 | 1253 |
SELECT |
| 1106 |
"in#5"."row_num"
|
|
| 1107 |
, "_join"("1" := "in#5"."ACAD.Specimen.locality", "3" := "in#5"."ACAD.Specimen.habitat") AS "result"
|
|
| 1108 |
FROM "in#5"
|
|
| 1254 |
"in#6"."row_num"
|
|
| 1255 |
, "_join"("1" := "in#6"."ACAD.Specimen.locality", "3" := "in#6"."ACAD.Specimen.habitat") AS "result"
|
|
| 1256 |
FROM "in#6"
|
|
| 1109 | 1257 |
LIMIT 0 |
| 1110 | 1258 |
</code></pre><pre><code class="SQL"> |
| 1111 | 1259 |
CREATE FUNCTION "pg_temp"."_join(1=locality, 3=habitat)_wrap"() |
| ... | ... | |
| 1113 | 1261 |
LANGUAGE plpgsql |
| 1114 | 1262 |
AS $$ |
| 1115 | 1263 |
DECLARE |
| 1116 |
"row" "in#5"%ROWTYPE;
|
|
| 1264 |
"row" "in#6"%ROWTYPE;
|
|
| 1117 | 1265 |
BEGIN |
| 1118 | 1266 |
FOR "row" IN |
| 1119 |
SELECT * FROM "in#5"
|
|
| 1267 |
SELECT * FROM "in#6"
|
|
| 1120 | 1268 |
LOOP |
| 1121 | 1269 |
BEGIN |
| 1122 | 1270 |
BEGIN |
| ... | ... | |
| 1194 | 1342 |
|
| 1195 | 1343 |
# Joining together input tables into temp table |
| 1196 | 1344 |
<pre><code class="SQL"> |
| 1197 |
CREATE TEMP TABLE "in#6" AS
|
|
| 1345 |
CREATE TEMP TABLE "in#7" AS
|
|
| 1198 | 1346 |
SELECT |
| 1199 | 1347 |
"ACAD.Specimen"."row_num" AS "row_num" |
| 1200 | 1348 |
, "_join(1=locality, 3=habitat)"."result" AS "_join(1=locality, 3=habitat).result" |
| 1201 | 1349 |
FROM "ACAD.Specimen" |
| 1202 | 1350 |
JOIN "_join(1=locality, 3=habitat)" USING ("row_num")
|
| 1203 | 1351 |
ORDER BY row_num |
| 1204 |
</code></pre>Temp table: @"in#6"@
|
|
| 1352 |
</code></pre>Temp table: @"in#7"@
|
|
| 1205 | 1353 |
# Defining wrapper function |
| 1206 | 1354 |
<pre><code class="SQL"> |
| 1207 | 1355 |
CREATE TEMP TABLE "_locationnarrative_is_cultivated(locationnarrative=_join(1=loca" AS |
| 1208 | 1356 |
SELECT |
| 1209 |
"in#6"."row_num"
|
|
| 1210 |
, "_locationnarrative_is_cultivated"("locationnarrative" := "in#6"."_join(1=locality, 3=habitat).result") AS "result"
|
|
| 1211 |
FROM "in#6"
|
|
| 1357 |
"in#7"."row_num"
|
|
| 1358 |
, "_locationnarrative_is_cultivated"("locationnarrative" := "in#7"."_join(1=locality, 3=habitat).result") AS "result"
|
|
| 1359 |
FROM "in#7"
|
|
| 1212 | 1360 |
LIMIT 0 |
| 1213 | 1361 |
</code></pre><pre><code class="SQL"> |
| 1214 | 1362 |
CREATE FUNCTION "pg_temp"."_locationnarrative_is_cultivated(locationnarrative=_join(1_wrap"() |
| ... | ... | |
| 1216 | 1364 |
LANGUAGE plpgsql |
| 1217 | 1365 |
AS $$ |
| 1218 | 1366 |
DECLARE |
| 1219 |
"row" "in#6"%ROWTYPE;
|
|
| 1367 |
"row" "in#7"%ROWTYPE;
|
|
| 1220 | 1368 |
BEGIN |
| 1221 | 1369 |
FOR "row" IN |
| 1222 |
SELECT * FROM "in#6"
|
|
| 1370 |
SELECT * FROM "in#7"
|
|
| 1223 | 1371 |
LOOP |
| 1224 | 1372 |
BEGIN |
| 1225 | 1373 |
BEGIN |
| ... | ... | |
| 1294 | 1442 |
|
| 1295 | 1443 |
|Output|Input| |
| 1296 | 1444 |
|@'verbatimelevation'@|@"ACAD.Specimen"."verbatimElevation"@| |
| 1297 |
|@'authorlocationcode'@|@"_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join("."result"@|
|
|
| 1445 |
|@'authorlocationcode'@|@"_alt(1=id, 2=_join(1=_map(institutionCode), 3=catalogNumber, 2="."result"@|
|
|
| 1298 | 1446 |
|@'elevation_m'@|@"ACAD.Specimen"."verbatimElevation"@| |
| 1299 |
|@'locationnarrative'@|@"_merge(1=locality, 3=_label(habitat))"."result"@| |
|
| 1447 |
|@'locationnarrative'@|@"_merge(1=locality, 3=_label(habitat), 4=locationRemarks)"."result"@|
|
|
| 1300 | 1448 |
|@'accessconditions'@|@"ACAD.Specimen"."rights"@| |
| 1301 | 1449 |
|@'iscultivated'@|@"_locationnarrative_is_cultivated(locationnarrative=_join(1=loca"."result"@| |
| 1302 | 1450 |
|
| 1303 | 1451 |
# Joining together input tables into temp table |
| 1304 | 1452 |
<pre><code class="SQL"> |
| 1305 |
CREATE TEMP TABLE "in#7" AS
|
|
| 1453 |
CREATE TEMP TABLE "in#8" AS
|
|
| 1306 | 1454 |
SELECT |
| 1307 | 1455 |
"ACAD.Specimen"."row_num" AS "row_num" |
| 1308 | 1456 |
, "ACAD.Specimen"."verbatimElevation" AS "ACAD.Specimen.verbatimElevation" |
| 1309 |
, "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join("."result" AS "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2.result"
|
|
| 1310 |
, "_merge(1=locality, 3=_label(habitat))"."result" AS "_merge(1=locality, 3=_label(habitat)).result"
|
|
| 1457 |
, "_alt(1=id, 2=_join(1=_map(institutionCode), 3=catalogNumber, 2="."result" AS "_alt(1=id, 2=_join(1=_map(institutionCode), 3=catalogNum.result"
|
|
| 1458 |
, "_merge(1=locality, 3=_label(habitat), 4=locationRemarks)"."result" AS "_merge(1=locality, 3=_label(habitat), 4=locationRemarks).result"
|
|
| 1311 | 1459 |
, "ACAD.Specimen"."rights" AS "ACAD.Specimen.rights" |
| 1312 | 1460 |
, "_locationnarrative_is_cultivated(locationnarrative=_join(1=loca"."result" AS "_locationnarrative_is_cultivated(locationnarrative=_join.result" |
| 1313 | 1461 |
FROM "ACAD.Specimen" |
| 1314 |
JOIN "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join(" USING ("row_num")
|
|
| 1315 |
JOIN "_merge(1=locality, 3=_label(habitat))" USING ("row_num")
|
|
| 1462 |
JOIN "_alt(1=id, 2=_join(1=_map(institutionCode), 3=catalogNumber, 2=" USING ("row_num")
|
|
| 1463 |
JOIN "_merge(1=locality, 3=_label(habitat), 4=locationRemarks)" USING ("row_num")
|
|
| 1316 | 1464 |
JOIN "_locationnarrative_is_cultivated(locationnarrative=_join(1=loca" USING ("row_num")
|
| 1317 | 1465 |
ORDER BY row_num |
| 1318 |
</code></pre>Temp table: @"in#7"@
|
|
| 1466 |
</code></pre>Temp table: @"in#8"@
|
|
| 1319 | 1467 |
<pre><code class="SQL"> |
| 1320 |
CREATE TEMP TABLE "in#7_full" (
|
|
| 1321 |
LIKE "in#7" INCLUDING ALL
|
|
| 1468 |
CREATE TEMP TABLE "in#8_full" (
|
|
| 1469 |
LIKE "in#8" INCLUDING ALL
|
|
| 1322 | 1470 |
); |
| 1323 | 1471 |
</code></pre><pre><code class="SQL"> |
| 1324 |
INSERT INTO "in#7_full"
|
|
| 1325 |
SELECT * FROM "in#7"
|
|
| 1472 |
INSERT INTO "in#8_full"
|
|
| 1473 |
SELECT * FROM "in#8"
|
|
| 1326 | 1474 |
</code></pre> |
| 1327 | 1475 |
# Trying to insert new rows |
| 1328 | 1476 |
<pre><code class="SQL"> |
| ... | ... | |
| 1333 | 1481 |
INSERT INTO "location" |
| 1334 | 1482 |
("verbatimelevation", "authorlocationcode", "elevation_m", "locationnarrative", "accessconditions", "iscultivated")
|
| 1335 | 1483 |
SELECT |
| 1336 |
"in#7"."ACAD.Specimen.verbatimElevation"
|
|
| 1337 |
, "in#7"."_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2.result"
|
|
| 1338 |
, "in#7"."ACAD.Specimen.verbatimElevation"
|
|
| 1339 |
, "in#7"."_merge(1=locality, 3=_label(habitat)).result"
|
|
| 1340 |
, "in#7"."ACAD.Specimen.rights"
|
|
| 1341 |
, "in#7"."_locationnarrative_is_cultivated(locationnarrative=_join.result"
|
|
| 1342 |
FROM "in#7"
|
|
| 1484 |
"in#8"."ACAD.Specimen.verbatimElevation"
|
|
| 1485 |
, "in#8"."_alt(1=id, 2=_join(1=_map(institutionCode), 3=catalogNum.result"
|
|
| 1486 |
, "in#8"."ACAD.Specimen.verbatimElevation"
|
|
| 1487 |
, "in#8"."_merge(1=locality, 3=_label(habitat), 4=locationRemarks).result"
|
|
| 1488 |
, "in#8"."ACAD.Specimen.rights"
|
|
| 1489 |
, "in#8"."_locationnarrative_is_cultivated(locationnarrative=_join.result"
|
|
| 1490 |
FROM "in#8"
|
|
| 1343 | 1491 |
RETURNING "location_id" |
| 1344 | 1492 |
$$; |
| 1345 | 1493 |
</code></pre> |
| ... | ... | |
| 1384 | 1532 |
END; |
| 1385 | 1533 |
$$; |
| 1386 | 1534 |
</code></pre><pre><code class="SQL"> |
| 1387 |
ALTER TABLE "in#7" ADD COLUMN "ACAD.Specimen.verbatimElevation::double" double precision /*"in#7"."ACAD.Specimen.verbatimElevation"::double precision*/
|
|
| 1535 |
ALTER TABLE "in#8" ADD COLUMN "ACAD.Specimen.verbatimElevation::double" double precision /*"in#8"."ACAD.Specimen.verbatimElevation"::double precision*/
|
|
| 1388 | 1536 |
</code></pre><pre><code class="SQL"> |
| 1389 |
ALTER TABLE "in#7"
|
|
| 1537 |
ALTER TABLE "in#8"
|
|
| 1390 | 1538 |
ALTER COLUMN "ACAD.Specimen.verbatimElevation::double" TYPE double precision |
| 1391 |
USING "pg_temp"."double(verbatimElevation)"("in#7"."ACAD.Specimen.verbatimElevation")
|
|
| 1539 |
USING "pg_temp"."double(verbatimElevation)"("in#8"."ACAD.Specimen.verbatimElevation")
|
|
| 1392 | 1540 |
</code></pre> |
| 1393 | 1541 |
# Trying to insert new rows |
| 1394 | 1542 |
<pre><code class="SQL"> |
| ... | ... | |
| 1399 | 1547 |
INSERT INTO "location" |
| 1400 | 1548 |
("verbatimelevation", "authorlocationcode", "elevation_m", "locationnarrative", "accessconditions", "iscultivated")
|
| 1401 | 1549 |
SELECT |
| 1402 |
"in#7"."ACAD.Specimen.verbatimElevation"
|
|
| 1403 |
, "in#7"."_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2.result"
|
|
| 1404 |
, "in#7"."ACAD.Specimen.verbatimElevation::double"
|
|
| 1405 |
, "in#7"."_merge(1=locality, 3=_label(habitat)).result"
|
|
| 1406 |
, "in#7"."ACAD.Specimen.rights"
|
|
| 1407 |
, "in#7"."_locationnarrative_is_cultivated(locationnarrative=_join.result"
|
|
| 1408 |
FROM "in#7"
|
|
| 1550 |
"in#8"."ACAD.Specimen.verbatimElevation"
|
|
| 1551 |
, "in#8"."_alt(1=id, 2=_join(1=_map(institutionCode), 3=catalogNum.result"
|
|
| 1552 |
, "in#8"."ACAD.Specimen.verbatimElevation::double"
|
|
| 1553 |
, "in#8"."_merge(1=locality, 3=_label(habitat), 4=locationRemarks).result"
|
|
| 1554 |
, "in#8"."ACAD.Specimen.rights"
|
|
| 1555 |
, "in#8"."_locationnarrative_is_cultivated(locationnarrative=_join.result"
|
|
| 1556 |
FROM "in#8"
|
|
| 1409 | 1557 |
RETURNING "location_id" |
| 1410 | 1558 |
$$; |
| 1411 | 1559 |
</code></pre><pre><code class="SQL"> |
| 1412 |
CREATE TEMP TABLE "in#7_insert_out_pkeys" AS
|
|
| 1560 |
CREATE TEMP TABLE "in#8_insert_out_pkeys" AS
|
|
| 1413 | 1561 |
SELECT * FROM "pg_temp"."INSERT INTO location"() AS "f" ("location_id")
|
| 1414 | 1562 |
</code></pre> |
| 1415 | 1563 |
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@ |
| ... | ... | |
| 1435 | 1583 |
INSERT INTO "location" |
| 1436 | 1584 |
("verbatimelevation", "authorlocationcode", "elevation_m", "locationnarrative", "accessconditions", "iscultivated", "source_id")
|
| 1437 | 1585 |
SELECT |
| 1438 |
"in#7"."ACAD.Specimen.verbatimElevation"
|
|
| 1439 |
, "in#7"."_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2.result"
|
|
| 1440 |
, "in#7"."ACAD.Specimen.verbatimElevation::double"
|
|
| 1441 |
, "in#7"."_merge(1=locality, 3=_label(habitat)).result"
|
|
| 1442 |
, "in#7"."ACAD.Specimen.rights"
|
|
| 1443 |
, "in#7"."_locationnarrative_is_cultivated(locationnarrative=_join.result"
|
|
| 1586 |
"in#8"."ACAD.Specimen.verbatimElevation"
|
|
| 1587 |
, "in#8"."_alt(1=id, 2=_join(1=_map(institutionCode), 3=catalogNum.result"
|
|
| 1588 |
, "in#8"."ACAD.Specimen.verbatimElevation::double"
|
|
| 1589 |
, "in#8"."_merge(1=locality, 3=_label(habitat), 4=locationRemarks).result"
|
|
| 1590 |
, "in#8"."ACAD.Specimen.rights"
|
|
| 1591 |
, "in#8"."_locationnarrative_is_cultivated(locationnarrative=_join.result"
|
|
| 1444 | 1592 |
, 1 AS "source_id" |
| 1445 |
FROM "in#7"
|
|
| 1593 |
FROM "in#8"
|
|
| 1446 | 1594 |
RETURNING "location_id" |
| 1447 | 1595 |
$$; |
| 1448 | 1596 |
</code></pre><pre><code class="SQL"> |
| 1449 |
CREATE TEMP TABLE "in#7_insert_out_pkeys" AS
|
|
| 1597 |
CREATE TEMP TABLE "in#8_insert_out_pkeys" AS
|
|
| 1450 | 1598 |
SELECT * FROM "pg_temp"."INSERT INTO location#1"() AS "f" ("location_id")
|
| 1451 | 1599 |
</code></pre> |
| 1452 | 1600 |
# Getting input table pkeys of inserted rows |
| 1453 | 1601 |
<pre><code class="SQL"> |
| 1454 |
CREATE TEMP TABLE "in#7_insert_in_pkeys" AS
|
|
| 1455 |
SELECT "row_num" FROM "in#7"
|
|
| 1602 |
CREATE TEMP TABLE "in#8_insert_in_pkeys" AS
|
|
| 1603 |
SELECT "row_num" FROM "in#8"
|
|
| 1456 | 1604 |
</code></pre> |
| 1457 | 1605 |
# Combining output and input pkeys in inserted order |
| 1458 | 1606 |
<pre><code class="SQL"> |
| 1459 | 1607 |
CREATE TEMP TABLE "location_pkeys" AS |
| 1460 | 1608 |
SELECT |
| 1461 |
"in#7_insert_in_pkeys"."row_num"
|
|
| 1462 |
, "in#7_insert_out_pkeys"."location_id" AS "out.location_id"
|
|
| 1463 |
FROM "in#7_insert_in_pkeys"
|
|
| 1464 |
JOIN "in#7_insert_out_pkeys" USING ("_row_num")
|
|
| 1609 |
"in#8_insert_in_pkeys"."row_num"
|
|
| 1610 |
, "in#8_insert_out_pkeys"."location_id" AS "out.location_id"
|
|
| 1611 |
FROM "in#8_insert_in_pkeys"
|
|
| 1612 |
JOIN "in#8_insert_out_pkeys" USING ("_row_num")
|
|
| 1465 | 1613 |
</code></pre> |
| 1466 | 1614 |
# Setting pkeys of missing rows to @None@ |
| 1467 | 1615 |
<pre><code class="SQL"> |
| 1468 | 1616 |
INSERT INTO "location_pkeys" |
| 1469 | 1617 |
("row_num", "out.location_id")
|
| 1470 | 1618 |
SELECT |
| 1471 |
"in#7_full"."row_num"
|
|
| 1619 |
"in#8_full"."row_num"
|
|
| 1472 | 1620 |
, NULL AS "out.location_id" |
| 1473 |
FROM "in#7_full"
|
|
| 1474 |
LEFT JOIN "location_pkeys" ON "location_pkeys"."row_num" = "in#7_full"."row_num"
|
|
| 1621 |
FROM "in#8_full"
|
|
| 1622 |
LEFT JOIN "location_pkeys" ON "location_pkeys"."row_num" = "in#8_full"."row_num"
|
|
| 1475 | 1623 |
WHERE "location_pkeys"."row_num" IS NULL |
| 1476 | 1624 |
</code></pre> |
| 1477 | 1625 |
# ********** New iteration ********** |
| ... | ... | |
| 1482 | 1630 |
|
| 1483 | 1631 |
# Joining together input tables into temp table |
| 1484 | 1632 |
<pre><code class="SQL"> |
| 1485 |
CREATE TEMP TABLE "in#8" AS
|
|
| 1633 |
CREATE TEMP TABLE "in#9" AS
|
|
| 1486 | 1634 |
SELECT |
| 1487 | 1635 |
"ACAD.Specimen"."row_num" AS "row_num" |
| 1488 | 1636 |
, "ACAD.Specimen"."eventDate" AS "ACAD.Specimen.eventDate" |
| 1489 | 1637 |
FROM "ACAD.Specimen" |
| 1490 | 1638 |
ORDER BY row_num |
| 1491 |
</code></pre>Temp table: @"in#8"@
|
|
| 1639 |
</code></pre>Temp table: @"in#9"@
|
|
| 1492 | 1640 |
# Defining wrapper function |
| 1493 | 1641 |
<pre><code class="SQL"> |
| 1494 | 1642 |
CREATE TEMP TABLE "_dateRangeEnd(eventDate)" AS |
| 1495 | 1643 |
SELECT |
| 1496 |
"in#8"."row_num"
|
|
| 1497 |
, "_dateRangeEnd"("value" := "in#8"."ACAD.Specimen.eventDate") AS "result"
|
|
| 1498 |
FROM "in#8"
|
|
| 1644 |
"in#9"."row_num"
|
|
| 1645 |
, "_dateRangeEnd"("value" := "in#9"."ACAD.Specimen.eventDate") AS "result"
|
|
| 1646 |
FROM "in#9"
|
|
| 1499 | 1647 |
LIMIT 0 |
| 1500 | 1648 |
</code></pre><pre><code class="SQL"> |
| 1501 | 1649 |
CREATE FUNCTION "pg_temp"."_dateRangeEnd(eventDate)_wrap"() |
| ... | ... | |
| 1503 | 1651 |
LANGUAGE plpgsql |
| 1504 | 1652 |
AS $$ |
| 1505 | 1653 |
DECLARE |
| 1506 |
"row" "in#8"%ROWTYPE;
|
|
| 1654 |
"row" "in#9"%ROWTYPE;
|
|
| 1507 | 1655 |
BEGIN |
| 1508 | 1656 |
FOR "row" IN |
| 1509 |
SELECT * FROM "in#8"
|
|
| 1657 |
SELECT * FROM "in#9"
|
|
| 1510 | 1658 |
LOOP |
| 1511 | 1659 |
BEGIN |
| 1512 | 1660 |
BEGIN |
| ... | ... | |
| 1584 | 1732 |
|
| 1585 | 1733 |
# Joining together input tables into temp table |
| 1586 | 1734 |
<pre><code class="SQL"> |
| 1587 |
CREATE TEMP TABLE "in#9" AS
|
|
| 1735 |
CREATE TEMP TABLE "in#10" AS
|
|
| 1588 | 1736 |
SELECT |
| 1589 | 1737 |
"ACAD.Specimen"."row_num" AS "row_num" |
| 1590 | 1738 |
, "ACAD.Specimen"."eventDate" AS "ACAD.Specimen.eventDate" |
| 1591 | 1739 |
FROM "ACAD.Specimen" |
| 1592 | 1740 |
ORDER BY row_num |
| 1593 |
</code></pre>Temp table: @"in#9"@
|
|
| 1741 |
</code></pre>Temp table: @"in#10"@
|
|
| 1594 | 1742 |
# Defining wrapper function |
| 1595 | 1743 |
<pre><code class="SQL"> |
| 1596 | 1744 |
CREATE TEMP TABLE "_dateRangeStart(eventDate)" AS |
| 1597 | 1745 |
SELECT |
| 1598 |
"in#9"."row_num"
|
|
| 1599 |
, "_dateRangeStart"("value" := "in#9"."ACAD.Specimen.eventDate") AS "result"
|
|
| 1600 |
FROM "in#9"
|
|
| 1746 |
"in#10"."row_num"
|
|
| 1747 |
, "_dateRangeStart"("value" := "in#10"."ACAD.Specimen.eventDate") AS "result"
|
|
| 1748 |
FROM "in#10"
|
|
| 1601 | 1749 |
LIMIT 0 |
| 1602 | 1750 |
</code></pre><pre><code class="SQL"> |
| 1603 | 1751 |
CREATE FUNCTION "pg_temp"."_dateRangeStart(eventDate)_wrap"() |
| ... | ... | |
| 1605 | 1753 |
LANGUAGE plpgsql |
| 1606 | 1754 |
AS $$ |
| 1607 | 1755 |
DECLARE |
| 1608 |
"row" "in#9"%ROWTYPE;
|
|
| 1756 |
"row" "in#10"%ROWTYPE;
|
|
| 1609 | 1757 |
BEGIN |
| 1610 | 1758 |
FOR "row" IN |
| 1611 |
SELECT * FROM "in#9"
|
|
| 1759 |
SELECT * FROM "in#10"
|
|
| 1612 | 1760 |
LOOP |
| 1613 | 1761 |
BEGIN |
| 1614 | 1762 |
BEGIN |
| ... | ... | |
| 1688 | 1836 |
|
| 1689 | 1837 |
# Joining together input tables into temp table |
| 1690 | 1838 |
<pre><code class="SQL"> |
| 1691 |
CREATE TEMP TABLE "in#10" AS
|
|
| 1839 |
CREATE TEMP TABLE "in#11" AS
|
|
| 1692 | 1840 |
SELECT |
| 1693 | 1841 |
"ACAD.Specimen"."row_num" AS "row_num" |
| 1694 | 1842 |
, "_dateRangeEnd(eventDate)"."result" AS "_dateRangeEnd(eventDate).result" |
| ... | ... | |
| 1699 | 1847 |
JOIN "location_pkeys" USING ("row_num")
|
| 1700 | 1848 |
JOIN "_dateRangeStart(eventDate)" USING ("row_num")
|
| 1701 | 1849 |
ORDER BY row_num |
| 1702 |
</code></pre>Temp table: @"in#10"@
|
|
| 1850 |
</code></pre>Temp table: @"in#11"@
|
|
| 1703 | 1851 |
<pre><code class="SQL"> |
| 1704 |
CREATE TEMP TABLE "in#10_full" (
|
|
| 1705 |
LIKE "in#10" INCLUDING ALL
|
|
| 1852 |
CREATE TEMP TABLE "in#11_full" (
|
|
| 1853 |
LIKE "in#11" INCLUDING ALL
|
|
| 1706 | 1854 |
); |
| 1707 | 1855 |
</code></pre><pre><code class="SQL"> |
| 1708 |
INSERT INTO "in#10_full"
|
|
| 1709 |
SELECT * FROM "in#10"
|
|
| 1856 |
INSERT INTO "in#11_full"
|
|
| 1857 |
SELECT * FROM "in#11"
|
|
| 1710 | 1858 |
</code></pre> |
| 1711 | 1859 |
# Trying to insert new rows |
| 1712 | 1860 |
<pre><code class="SQL"> |
| ... | ... | |
| 1717 | 1865 |
INSERT INTO "locationevent" |
| 1718 | 1866 |
("obsenddate", "location_id", "obsstartdate")
|
| 1719 | 1867 |
SELECT |
| 1720 |
"in#10"."_dateRangeEnd(eventDate).result"
|
|
| 1721 |
, "in#10"."location_pkeys.out.location_id"
|
|
| 1722 |
, "in#10"."_dateRangeStart(eventDate).result"
|
|
| 1723 |
FROM "in#10"
|
|
| 1868 |
"in#11"."_dateRangeEnd(eventDate).result"
|
|
| 1869 |
, "in#11"."location_pkeys.out.location_id"
|
|
| 1870 |
, "in#11"."_dateRangeStart(eventDate).result"
|
|
| 1871 |
FROM "in#11"
|
|
| 1724 | 1872 |
RETURNING "locationevent_id" |
| 1725 | 1873 |
$$; |
| 1726 | 1874 |
</code></pre> |
| ... | ... | |
| 1765 | 1913 |
END; |
| 1766 | 1914 |
$$; |
| 1767 | 1915 |
</code></pre><pre><code class="SQL"> |
| 1768 |
ALTER TABLE "in#10" ADD COLUMN "_dateRangeEnd(eventDate).result::date" date /*"in#10"."_dateRangeEnd(eventDate).result"::date*/
|
|
| 1916 |
ALTER TABLE "in#11" ADD COLUMN "_dateRangeEnd(eventDate).result::date" date /*"in#11"."_dateRangeEnd(eventDate).result"::date*/
|
|
| 1769 | 1917 |
</code></pre><pre><code class="SQL"> |
| 1770 |
ALTER TABLE "in#10"
|
|
| 1918 |
ALTER TABLE "in#11"
|
|
| 1771 | 1919 |
ALTER COLUMN "_dateRangeEnd(eventDate).result::date" TYPE date |
| 1772 |
USING "pg_temp"."date(eventDate)"("in#10"."_dateRangeEnd(eventDate).result")
|
|
| 1920 |
USING "pg_temp"."date(eventDate)"("in#11"."_dateRangeEnd(eventDate).result")
|
|
| 1773 | 1921 |
</code></pre> |
| 1774 | 1922 |
# Trying to insert new rows |
| 1775 | 1923 |
<pre><code class="SQL"> |
| ... | ... | |
| 1780 | 1928 |
INSERT INTO "locationevent" |
| 1781 | 1929 |
("obsenddate", "location_id", "obsstartdate")
|
| 1782 | 1930 |
SELECT |
| 1783 |
"in#10"."_dateRangeEnd(eventDate).result::date"
|
|
| 1784 |
, "in#10"."location_pkeys.out.location_id"
|
|
| 1785 |
, "in#10"."_dateRangeStart(eventDate).result"
|
|
| 1786 |
FROM "in#10"
|
|
| 1931 |
"in#11"."_dateRangeEnd(eventDate).result::date"
|
|
| 1932 |
, "in#11"."location_pkeys.out.location_id"
|
|
| 1933 |
, "in#11"."_dateRangeStart(eventDate).result"
|
|
| 1934 |
FROM "in#11"
|
|
| 1787 | 1935 |
RETURNING "locationevent_id" |
| 1788 | 1936 |
$$; |
| 1789 | 1937 |
</code></pre> |
| ... | ... | |
| 1828 | 1976 |
END; |
| 1829 | 1977 |
$$; |
| 1830 | 1978 |
</code></pre><pre><code class="SQL"> |
| 1831 |
ALTER TABLE "in#10" ADD COLUMN "_dateRangeStart(eventDate).result::date" date /*"in#10"."_dateRangeStart(eventDate).result"::date*/
|
|
| 1979 |
ALTER TABLE "in#11" ADD COLUMN "_dateRangeStart(eventDate).result::date" date /*"in#11"."_dateRangeStart(eventDate).result"::date*/
|
|
| 1832 | 1980 |
</code></pre><pre><code class="SQL"> |
| 1833 |
ALTER TABLE "in#10"
|
|
| 1981 |
ALTER TABLE "in#11"
|
|
| 1834 | 1982 |
ALTER COLUMN "_dateRangeStart(eventDate).result::date" TYPE date |
| 1835 |
USING "pg_temp"."date(eventDate)"("in#10"."_dateRangeStart(eventDate).result")
|
|
| 1983 |
USING "pg_temp"."date(eventDate)"("in#11"."_dateRangeStart(eventDate).result")
|
|
| 1836 | 1984 |
</code></pre> |
| 1837 | 1985 |
# Trying to insert new rows |
| 1838 | 1986 |
<pre><code class="SQL"> |
| ... | ... | |
| 1843 | 1991 |
INSERT INTO "locationevent" |
| 1844 | 1992 |
("obsenddate", "location_id", "obsstartdate")
|
| 1845 | 1993 |
SELECT |
| 1846 |
"in#10"."_dateRangeEnd(eventDate).result::date"
|
|
| 1847 |
, "in#10"."location_pkeys.out.location_id"
|
|
| 1848 |
, "in#10"."_dateRangeStart(eventDate).result::date"
|
|
| 1849 |
FROM "in#10"
|
|
| 1994 |
"in#11"."_dateRangeEnd(eventDate).result::date"
|
|
| 1995 |
, "in#11"."location_pkeys.out.location_id"
|
|
| 1996 |
, "in#11"."_dateRangeStart(eventDate).result::date"
|
|
| 1997 |
FROM "in#11"
|
|
| 1850 | 1998 |
RETURNING "locationevent_id" |
| 1851 | 1999 |
$$; |
| 1852 | 2000 |
</code></pre><pre><code class="SQL"> |
| 1853 |
CREATE TEMP TABLE "in#10_insert_out_pkeys" AS
|
|
| 2001 |
CREATE TEMP TABLE "in#11_insert_out_pkeys" AS
|
|
| 1854 | 2002 |
SELECT * FROM "pg_temp"."INSERT INTO locationevent"() AS "f" ("locationevent_id")
|
| 1855 | 2003 |
</code></pre> |
| 1856 | 2004 |
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@ |
| ... | ... | |
| 1885 | 2033 |
INSERT INTO "locationevent" |
| 1886 | 2034 |
("obsenddate", "location_id", "obsstartdate", "source_id")
|
| 1887 | 2035 |
SELECT |
| 1888 |
"in#10"."_dateRangeEnd(eventDate).result::date"
|
|
| 1889 |
, "in#10"."location_pkeys.out.location_id"
|
|
| 1890 |
, "in#10"."_dateRangeStart(eventDate).result::date"
|
|
| 2036 |
"in#11"."_dateRangeEnd(eventDate).result::date"
|
|
| 2037 |
, "in#11"."location_pkeys.out.location_id"
|
|
| 2038 |
, "in#11"."_dateRangeStart(eventDate).result::date"
|
|
| 1891 | 2039 |
, 1 AS "source_id" |
| 1892 |
FROM "in#10"
|
|
| 2040 |
FROM "in#11"
|
|
| 1893 | 2041 |
RETURNING "locationevent_id" |
| 1894 | 2042 |
$$; |
| 1895 | 2043 |
</code></pre><pre><code class="SQL"> |
| 1896 |
CREATE TEMP TABLE "in#10_insert_out_pkeys" AS
|
|
| 2044 |
CREATE TEMP TABLE "in#11_insert_out_pkeys" AS
|
|
| 1897 | 2045 |
SELECT * FROM "pg_temp"."INSERT INTO locationevent#1"() AS "f" ("locationevent_id")
|
| 1898 | 2046 |
</code></pre> |
| 1899 | 2047 |
# Getting input table pkeys of inserted rows |
| 1900 | 2048 |
<pre><code class="SQL"> |
| 1901 |
CREATE TEMP TABLE "in#10_insert_in_pkeys" AS
|
|
| 1902 |
SELECT "row_num" FROM "in#10"
|
|
| 2049 |
CREATE TEMP TABLE "in#11_insert_in_pkeys" AS
|
|
| 2050 |
SELECT "row_num" FROM "in#11"
|
|
| 1903 | 2051 |
</code></pre> |
| 1904 | 2052 |
# Combining output and input pkeys in inserted order |
| 1905 | 2053 |
<pre><code class="SQL"> |
| 1906 | 2054 |
CREATE TEMP TABLE "locationevent_pkeys" AS |
| 1907 | 2055 |
SELECT |
| 1908 |
"in#10_insert_in_pkeys"."row_num"
|
|
| 1909 |
, "in#10_insert_out_pkeys"."locationevent_id" AS "out.locationevent_id"
|
|
| 1910 |
FROM "in#10_insert_in_pkeys"
|
|
| 1911 |
JOIN "in#10_insert_out_pkeys" USING ("_row_num")
|
|
| 2056 |
"in#11_insert_in_pkeys"."row_num"
|
|
| 2057 |
, "in#11_insert_out_pkeys"."locationevent_id" AS "out.locationevent_id"
|
|
| 2058 |
FROM "in#11_insert_in_pkeys"
|
|
| 2059 |
JOIN "in#11_insert_out_pkeys" USING ("_row_num")
|
|
| 1912 | 2060 |
</code></pre> |
| 1913 | 2061 |
# Setting pkeys of missing rows to @None@ |
| 1914 | 2062 |
<pre><code class="SQL"> |
| 1915 | 2063 |
INSERT INTO "locationevent_pkeys" |
| 1916 | 2064 |
("row_num", "out.locationevent_id")
|
| 1917 | 2065 |
SELECT |
| 1918 |
"in#10_full"."row_num"
|
|
| 2066 |
"in#11_full"."row_num"
|
|
| 1919 | 2067 |
, NULL AS "out.locationevent_id" |
| 1920 |
FROM "in#10_full"
|
|
| 1921 |
LEFT JOIN "locationevent_pkeys" ON "locationevent_pkeys"."row_num" = "in#10_full"."row_num"
|
|
| 2068 |
FROM "in#11_full"
|
|
| 2069 |
LEFT JOIN "locationevent_pkeys" ON "locationevent_pkeys"."row_num" = "in#11_full"."row_num"
|
|
| 1922 | 2070 |
WHERE "locationevent_pkeys"."row_num" IS NULL |
| 1923 | 2071 |
</code></pre> |
| 1924 | 2072 |
# ********** New iteration ********** |
| ... | ... | |
| 1929 | 2077 |
|
| 1930 | 2078 |
# Joining together input tables into temp table |
| 1931 | 2079 |
<pre><code class="SQL"> |
| 1932 |
CREATE TEMP TABLE "in#11" AS
|
|
| 2080 |
CREATE TEMP TABLE "in#12" AS
|
|
| 1933 | 2081 |
SELECT |
| 1934 | 2082 |
"ACAD.Specimen"."row_num" AS "row_num" |
| 1935 | 2083 |
, "ACAD.Specimen"."recordedBy" AS "ACAD.Specimen.recordedBy" |
| 1936 | 2084 |
FROM "ACAD.Specimen" |
| 1937 | 2085 |
ORDER BY row_num |
| 1938 |
</code></pre>Temp table: @"in#11"@
|
|
| 2086 |
</code></pre>Temp table: @"in#12"@
|
|
| 1939 | 2087 |
<pre><code class="SQL"> |
| 1940 |
CREATE TEMP TABLE "in#11_full" (
|
|
| 1941 |
LIKE "in#11" INCLUDING ALL
|
|
| 2088 |
CREATE TEMP TABLE "in#12_full" (
|
|
| 2089 |
LIKE "in#12" INCLUDING ALL
|
|
| 1942 | 2090 |
); |
| 1943 | 2091 |
</code></pre><pre><code class="SQL"> |
| 1944 |
INSERT INTO "in#11_full"
|
|
| 1945 |
SELECT * FROM "in#11"
|
|
| 2092 |
INSERT INTO "in#12_full"
|
|
| 2093 |
SELECT * FROM "in#12"
|
|
| 1946 | 2094 |
</code></pre> |
| 1947 | 2095 |
# Trying to insert new rows |
| 1948 | 2096 |
<pre><code class="SQL"> |
| ... | ... | |
| 1952 | 2100 |
AS $$ |
| 1953 | 2101 |
INSERT INTO "party" |
| 1954 | 2102 |
("fullname")
|
| 1955 |
SELECT "in#11"."ACAD.Specimen.recordedBy" FROM "in#11"
|
|
| 2103 |
SELECT "in#12"."ACAD.Specimen.recordedBy" FROM "in#12"
|
|
| 1956 | 2104 |
RETURNING "party_id" |
| 1957 | 2105 |
$$; |
| 1958 | 2106 |
</code></pre><pre><code class="SQL"> |
| 1959 |
CREATE TEMP TABLE "in#11_insert_out_pkeys" AS
|
|
| 2107 |
CREATE TEMP TABLE "in#12_insert_out_pkeys" AS
|
|
| 1960 | 2108 |
SELECT * FROM "pg_temp"."INSERT INTO party"() AS "f" ("party_id")
|
| 1961 | 2109 |
</code></pre> |
| 1962 | 2110 |
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@ |
| ... | ... | |
| 1991 | 2139 |
INSERT INTO "party" |
| 1992 | 2140 |
("fullname", "source_id")
|
| 1993 | 2141 |
SELECT |
| 1994 |
"in#11"."ACAD.Specimen.recordedBy"
|
|
| 2142 |
"in#12"."ACAD.Specimen.recordedBy"
|
|
| 1995 | 2143 |
, 1 AS "source_id" |
| 1996 |
FROM "in#11"
|
|
| 2144 |
FROM "in#12"
|
|
| 1997 | 2145 |
RETURNING "party_id" |
| 1998 | 2146 |
$$; |
| 1999 | 2147 |
</code></pre><pre><code class="SQL"> |
| 2000 |
CREATE TEMP TABLE "in#11_insert_out_pkeys" AS
|
|
| 2148 |
CREATE TEMP TABLE "in#12_insert_out_pkeys" AS
|
|
| 2001 | 2149 |
SELECT * FROM "pg_temp"."INSERT INTO party#1"() AS "f" ("party_id")
|
| 2002 | 2150 |
</code></pre> |
| 2003 | 2151 |
# Caught exception: DuplicateKeyException: Violated @party_unique_name@ constraint with condition @sourceaccessioncode IS NULL@ on columns: @source_id, organizationname, fullname, surname, givenname, middlename, suffix@ |
| ... | ... | |
| 2007 | 2155 |
Output: Input |
| 2008 | 2156 |
"party"."source_id": 1 |
| 2009 | 2157 |
"party"."organizationname": None |
| 2010 |
"party"."fullname": "in#11"."ACAD.Specimen.recordedBy"
|
|
| 2158 |
"party"."fullname": "in#12"."ACAD.Specimen.recordedBy"
|
|
| 2011 | 2159 |
"party"."surname": None |
| 2012 | 2160 |
"party"."givenname": None |
| 2013 | 2161 |
"party"."middlename": None |
| 2014 | 2162 |
"party"."suffix": None |
| 2015 | 2163 |
</pre><pre><code class="SQL"> |
| 2016 |
CREATE TEMP TABLE "in#11_distinct" (
|
|
| 2017 |
LIKE "in#11" INCLUDING ALL
|
|
| 2164 |
CREATE TEMP TABLE "in#12_distinct" (
|
|
| 2165 |
LIKE "in#12" INCLUDING ALL
|
|
| 2018 | 2166 |
); |
| 2019 | 2167 |
</code></pre><pre><code class="SQL"> |
| 2020 |
INSERT INTO "in#11_distinct"
|
|
| 2168 |
INSERT INTO "in#12_distinct"
|
|
| 2021 | 2169 |
SELECT |
| 2022 |
DISTINCT ON ("in#11"."ACAD.Specimen.recordedBy")
|
|
| 2023 |
"in#11".*
|
|
| 2024 |
FROM "in#11"
|
|
| 2170 |
DISTINCT ON ("in#12"."ACAD.Specimen.recordedBy")
|
|
| 2171 |
"in#12".*
|
|
| 2172 |
FROM "in#12"
|
|
| 2025 | 2173 |
LEFT JOIN "party" ON |
| 2026 | 2174 |
"party"."source_id" = 1 |
| 2027 | 2175 |
AND COALESCE("party"."organizationname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
|
| 2028 |
AND COALESCE("party"."fullname", CAST('\N' AS text)) = COALESCE("in#11"."ACAD.Specimen.recordedBy", CAST('\N' AS text))
|
|
| 2176 |
AND COALESCE("party"."fullname", CAST('\N' AS text)) = COALESCE("in#12"."ACAD.Specimen.recordedBy", CAST('\N' AS text))
|
|
| 2029 | 2177 |
AND COALESCE("party"."surname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
|
| 2030 | 2178 |
AND COALESCE("party"."givenname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
|
| 2031 | 2179 |
AND COALESCE("party"."middlename", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
|
| ... | ... | |
| 2043 | 2191 |
BEGIN |
| 2044 | 2192 |
FOR "row"."fullname", "row"."source_id" IN |
| 2045 | 2193 |
SELECT |
| 2046 |
"in#11_distinct"."ACAD.Specimen.recordedBy"
|
|
| 2194 |
"in#12_distinct"."ACAD.Specimen.recordedBy"
|
|
| 2047 | 2195 |
, 1 AS "source_id" |
| 2048 |
FROM "in#11_distinct"
|
|
| 2196 |
FROM "in#12_distinct"
|
|
| 2049 | 2197 |
ORDER BY row_num |
| 2050 | 2198 |
LOOP |
| 2051 | 2199 |
BEGIN |
| ... | ... | |
| 2072 | 2220 |
<pre><code class="SQL"> |
| 2073 | 2221 |
CREATE TEMP TABLE "party_pkeys" AS |
| 2074 | 2222 |
SELECT |
| 2075 |
"in#11"."row_num"
|
|
| 2223 |
"in#12"."row_num"
|
|
| 2076 | 2224 |
, "party"."party_id" AS "out.party_id" |
| 2077 |
FROM "in#11"
|
|
| 2225 |
FROM "in#12"
|
|
| 2078 | 2226 |
JOIN "party" ON |
| 2079 | 2227 |
"party"."source_id" = 1 |
| 2080 | 2228 |
AND COALESCE("party"."organizationname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
|
| 2081 |
AND COALESCE("party"."fullname", CAST('\N' AS text)) = COALESCE("in#11"."ACAD.Specimen.recordedBy", CAST('\N' AS text))
|
|
| 2229 |
AND COALESCE("party"."fullname", CAST('\N' AS text)) = COALESCE("in#12"."ACAD.Specimen.recordedBy", CAST('\N' AS text))
|
|
| 2082 | 2230 |
AND COALESCE("party"."surname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
|
| 2083 | 2231 |
AND COALESCE("party"."givenname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
|
| 2084 | 2232 |
AND COALESCE("party"."middlename", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
|
| ... | ... | |
| 2089 | 2237 |
INSERT INTO "party_pkeys" |
| 2090 | 2238 |
("row_num", "out.party_id")
|
| 2091 | 2239 |
SELECT |
| 2092 |
"in#11_full"."row_num"
|
|
| 2240 |
"in#12_full"."row_num"
|
|
| 2093 | 2241 |
, NULL AS "out.party_id" |
| 2094 |
FROM "in#11_full"
|
|
| 2095 |
LEFT JOIN "party_pkeys" ON "party_pkeys"."row_num" = "in#11_full"."row_num"
|
|
| 2242 |
FROM "in#12_full"
|
|
| 2243 |
LEFT JOIN "party_pkeys" ON "party_pkeys"."row_num" = "in#12_full"."row_num"
|
|
| 2096 | 2244 |
WHERE "party_pkeys"."row_num" IS NULL |
| 2097 | 2245 |
</code></pre> |
| 2098 | 2246 |
# ********** New iteration ********** |
| ... | ... | |
| 2105 | 2253 |
|
| 2106 | 2254 |
# Joining together input tables into temp table |
| 2107 | 2255 |
<pre><code class="SQL"> |
| 2108 |
CREATE TEMP TABLE "in#12" AS
|
|
| 2256 |
CREATE TEMP TABLE "in#13" AS
|
|
| 2109 | 2257 |
SELECT |
| 2110 | 2258 |
"ACAD.Specimen"."row_num" AS "row_num" |
| 2111 | 2259 |
, "ACAD.Specimen"."id" AS "ACAD.Specimen.id" |
| ... | ... | |
| 2115 | 2263 |
JOIN "party_pkeys" USING ("row_num")
|
| 2116 | 2264 |
JOIN "locationevent_pkeys" USING ("row_num")
|
| 2117 | 2265 |
ORDER BY row_num |
| 2118 |
</code></pre>Temp table: @"in#12"@
|
|
| 2266 |
</code></pre>Temp table: @"in#13"@
|
|
| 2119 | 2267 |
<pre><code class="SQL"> |
| 2120 |
CREATE TEMP TABLE "in#12_full" (
|
|
| 2121 |
LIKE "in#12" INCLUDING ALL
|
|
| 2268 |
CREATE TEMP TABLE "in#13_full" (
|
|
| 2269 |
LIKE "in#13" INCLUDING ALL
|
|
| 2122 | 2270 |
); |
| 2123 | 2271 |
</code></pre><pre><code class="SQL"> |
| 2124 |
INSERT INTO "in#12_full"
|
|
| 2125 |
SELECT * FROM "in#12"
|
|
| 2272 |
INSERT INTO "in#13_full"
|
|
| 2273 |
SELECT * FROM "in#13"
|
|
| 2126 | 2274 |
</code></pre> |
| 2127 | 2275 |
# Trying to insert new rows |
| 2128 | 2276 |
<pre><code class="SQL"> |
| ... | ... | |
| 2133 | 2281 |
INSERT INTO "taxonoccurrence" |
| 2134 | 2282 |
("sourceaccessioncode", "collector_id", "locationevent_id")
|
| 2135 | 2283 |
SELECT |
| 2136 |
"in#12"."ACAD.Specimen.id"
|
|
| 2137 |
, "in#12"."party_pkeys.out.party_id"
|
|
| 2138 |
, "in#12"."locationevent_pkeys.out.locationevent_id"
|
|
| 2139 |
FROM "in#12"
|
|
| 2284 |
"in#13"."ACAD.Specimen.id"
|
|
| 2285 |
, "in#13"."party_pkeys.out.party_id"
|
|
| 2286 |
, "in#13"."locationevent_pkeys.out.locationevent_id"
|
|
| 2287 |
FROM "in#13"
|
|
| 2140 | 2288 |
RETURNING "taxonoccurrence_id" |
| 2141 | 2289 |
$$; |
| 2142 | 2290 |
</code></pre><pre><code class="SQL"> |
| 2143 |
CREATE TEMP TABLE "in#12_insert_out_pkeys" AS
|
|
| 2291 |
CREATE TEMP TABLE "in#13_insert_out_pkeys" AS
|
|
| 2144 | 2292 |
SELECT * FROM "pg_temp"."INSERT INTO taxonoccurrence"() AS "f" ("taxonoccurrence_id")
|
| 2145 | 2293 |
</code></pre> |
| 2146 | 2294 |
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@ |
| ... | ... | |
| 2175 | 2323 |
INSERT INTO "taxonoccurrence" |
| 2176 | 2324 |
("sourceaccessioncode", "collector_id", "locationevent_id", "source_id")
|
| 2177 | 2325 |
SELECT |
| 2178 |
"in#12"."ACAD.Specimen.id"
|
|
| 2179 |
, "in#12"."party_pkeys.out.party_id"
|
|
| 2180 |
, "in#12"."locationevent_pkeys.out.locationevent_id"
|
|
| 2326 |
"in#13"."ACAD.Specimen.id"
|
|
| 2327 |
, "in#13"."party_pkeys.out.party_id"
|
|
| 2328 |
, "in#13"."locationevent_pkeys.out.locationevent_id"
|
|
| 2181 | 2329 |
, 1 AS "source_id" |
| 2182 |
FROM "in#12"
|
|
| 2330 |
FROM "in#13"
|
|
| 2183 | 2331 |
RETURNING "taxonoccurrence_id" |
| 2184 | 2332 |
$$; |
| 2185 | 2333 |
</code></pre><pre><code class="SQL"> |
| 2186 |
CREATE TEMP TABLE "in#12_insert_out_pkeys" AS
|
|
| 2334 |
CREATE TEMP TABLE "in#13_insert_out_pkeys" AS
|
|
| 2187 | 2335 |
SELECT * FROM "pg_temp"."INSERT INTO taxonoccurrence#1"() AS "f" ("taxonoccurrence_id")
|
| 2188 | 2336 |
</code></pre> |
| 2189 | 2337 |
# Getting input table pkeys of inserted rows |
| 2190 | 2338 |
<pre><code class="SQL"> |
| 2191 |
CREATE TEMP TABLE "in#12_insert_in_pkeys" AS
|
|
| 2192 |
SELECT "row_num" FROM "in#12"
|
|
| 2339 |
CREATE TEMP TABLE "in#13_insert_in_pkeys" AS
|
|
| 2340 |
SELECT "row_num" FROM "in#13"
|
|
| 2193 | 2341 |
</code></pre> |
| 2194 | 2342 |
# Combining output and input pkeys in inserted order |
| 2195 | 2343 |
<pre><code class="SQL"> |
| 2196 | 2344 |
CREATE TEMP TABLE "taxonoccurrence_pkeys" AS |
| 2197 | 2345 |
SELECT |
| 2198 |
"in#12_insert_in_pkeys"."row_num"
|
|
| 2199 |
, "in#12_insert_out_pkeys"."taxonoccurrence_id" AS "out.taxonoccurrence_id"
|
|
| 2200 |
FROM "in#12_insert_in_pkeys"
|
|
| 2201 |
JOIN "in#12_insert_out_pkeys" USING ("_row_num")
|
|
| 2346 |
"in#13_insert_in_pkeys"."row_num"
|
|
| 2347 |
, "in#13_insert_out_pkeys"."taxonoccurrence_id" AS "out.taxonoccurrence_id"
|
|
| 2348 |
FROM "in#13_insert_in_pkeys"
|
|
| 2349 |
JOIN "in#13_insert_out_pkeys" USING ("_row_num")
|
|
| 2202 | 2350 |
</code></pre> |
| 2203 | 2351 |
# Setting pkeys of missing rows to @None@ |
| 2204 | 2352 |
<pre><code class="SQL"> |
| 2205 | 2353 |
INSERT INTO "taxonoccurrence_pkeys" |
| 2206 | 2354 |
("row_num", "out.taxonoccurrence_id")
|
| 2207 | 2355 |
SELECT |
| 2208 |
"in#12_full"."row_num"
|
|
| 2356 |
"in#13_full"."row_num"
|
|
| 2209 | 2357 |
, NULL AS "out.taxonoccurrence_id" |
| 2210 |
FROM "in#12_full"
|
|
| 2211 |
LEFT JOIN "taxonoccurrence_pkeys" ON "taxonoccurrence_pkeys"."row_num" = "in#12_full"."row_num"
|
|
| 2358 |
FROM "in#13_full"
|
|
| 2359 |
LEFT JOIN "taxonoccurrence_pkeys" ON "taxonoccurrence_pkeys"."row_num" = "in#13_full"."row_num"
|
|
| 2212 | 2360 |
WHERE "taxonoccurrence_pkeys"."row_num" IS NULL |
| 2213 | 2361 |
</code></pre> |
| 2214 | 2362 |
# ********** New iteration ********** |
| 2215 | 2363 |
# Inserting these input columns into @"aggregateoccurrence"@: |
| 2216 | 2364 |
|
| 2217 | 2365 |
|Output|Input| |
| 2366 |
|@'notes'@|@"ACAD.Specimen"."occurrenceRemarks"@| |
|
| 2218 | 2367 |
|@'taxonoccurrence_id'@|@"taxonoccurrence_pkeys"."out.taxonoccurrence_id"@| |
| 2219 | 2368 |
|
| 2220 | 2369 |
# Joining together input tables into temp table |
| 2221 | 2370 |
<pre><code class="SQL"> |
| 2222 |
CREATE TEMP TABLE "in#13" AS
|
|
| 2371 |
CREATE TEMP TABLE "in#14" AS
|
|
| 2223 | 2372 |
SELECT |
| 2224 | 2373 |
"ACAD.Specimen"."row_num" AS "row_num" |
| 2374 |
, "ACAD.Specimen"."occurrenceRemarks" AS "ACAD.Specimen.occurrenceRemarks" |
|
| 2225 | 2375 |
, "taxonoccurrence_pkeys"."out.taxonoccurrence_id" AS "taxonoccurrence_pkeys.out.taxonoccurrence_id" |
| 2226 | 2376 |
FROM "ACAD.Specimen" |
| 2227 | 2377 |
JOIN "taxonoccurrence_pkeys" USING ("row_num")
|
| 2228 | 2378 |
ORDER BY row_num |
| 2229 |
</code></pre>Temp table: @"in#13"@
|
|
| 2379 |
</code></pre>Temp table: @"in#14"@
|
|
| 2230 | 2380 |
<pre><code class="SQL"> |
| 2231 |
CREATE TEMP TABLE "in#13_full" (
|
|
| 2232 |
LIKE "in#13" INCLUDING ALL
|
|
| 2381 |
CREATE TEMP TABLE "in#14_full" (
|
|
| 2382 |
LIKE "in#14" INCLUDING ALL
|
|
| 2233 | 2383 |
); |
| 2234 | 2384 |
</code></pre><pre><code class="SQL"> |
| 2235 |
INSERT INTO "in#13_full"
|
|
| 2236 |
SELECT * FROM "in#13"
|
|
| 2385 |
INSERT INTO "in#14_full"
|
|
| 2386 |
SELECT * FROM "in#14"
|
|
| 2237 | 2387 |
</code></pre> |
| 2238 | 2388 |
# Trying to insert new rows |
| 2239 | 2389 |
<pre><code class="SQL"> |
| ... | ... | |
| 2242 | 2392 |
LANGUAGE sql |
| 2243 | 2393 |
AS $$ |
| 2244 | 2394 |
INSERT INTO "aggregateoccurrence" |
| 2245 |
("taxonoccurrence_id")
|
|
| 2246 |
SELECT "in#13"."taxonoccurrence_pkeys.out.taxonoccurrence_id" FROM "in#13" |
|
| 2395 |
("notes", "taxonoccurrence_id")
|
|
| 2396 |
SELECT |
|
| 2397 |
"in#14"."ACAD.Specimen.occurrenceRemarks" |
|
| 2398 |
, "in#14"."taxonoccurrence_pkeys.out.taxonoccurrence_id" |
|
| 2399 |
FROM "in#14" |
|
| 2247 | 2400 |
RETURNING "aggregateoccurrence_id" |
| 2248 | 2401 |
$$; |
| 2249 | 2402 |
</code></pre><pre><code class="SQL"> |
| 2250 |
CREATE TEMP TABLE "in#13_insert_out_pkeys" AS
|
|
| 2403 |
CREATE TEMP TABLE "in#14_insert_out_pkeys" AS
|
|
| 2251 | 2404 |
SELECT * FROM "pg_temp"."INSERT INTO aggregateoccurrence"() AS "f" ("aggregateoccurrence_id")
|
| 2252 | 2405 |
</code></pre> |
| 2253 | 2406 |
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@ |
| ... | ... | |
| 2280 | 2433 |
LANGUAGE sql |
| 2281 | 2434 |
AS $$ |
| 2282 | 2435 |
INSERT INTO "aggregateoccurrence" |
| 2283 |
("taxonoccurrence_id", "source_id")
|
|
| 2436 |
("notes", "taxonoccurrence_id", "source_id")
|
|
| 2284 | 2437 |
SELECT |
| 2285 |
"in#13"."taxonoccurrence_pkeys.out.taxonoccurrence_id" |
|
| 2438 |
"in#14"."ACAD.Specimen.occurrenceRemarks" |
|
| 2439 |
, "in#14"."taxonoccurrence_pkeys.out.taxonoccurrence_id" |
|
| 2286 | 2440 |
, 1 AS "source_id" |
| 2287 |
FROM "in#13"
|
|
| 2441 |
FROM "in#14"
|
|
| 2288 | 2442 |
RETURNING "aggregateoccurrence_id" |
| 2289 | 2443 |
$$; |
| 2290 | 2444 |
</code></pre><pre><code class="SQL"> |
| 2291 |
CREATE TEMP TABLE "in#13_insert_out_pkeys" AS
|
|
| 2445 |
CREATE TEMP TABLE "in#14_insert_out_pkeys" AS
|
|
| 2292 | 2446 |
SELECT * FROM "pg_temp"."INSERT INTO aggregateoccurrence#1"() AS "f" ("aggregateoccurrence_id")
|
| 2293 | 2447 |
</code></pre> |
| 2294 | 2448 |
# Getting input table pkeys of inserted rows |
| 2295 | 2449 |
<pre><code class="SQL"> |
| 2296 |
CREATE TEMP TABLE "in#13_insert_in_pkeys" AS
|
|
| 2297 |
SELECT "row_num" FROM "in#13"
|
|
| 2450 |
CREATE TEMP TABLE "in#14_insert_in_pkeys" AS
|
|
| 2451 |
SELECT "row_num" FROM "in#14"
|
|
| 2298 | 2452 |
</code></pre> |
| 2299 | 2453 |
# Combining output and input pkeys in inserted order |
| 2300 | 2454 |
<pre><code class="SQL"> |
| 2301 | 2455 |
CREATE TEMP TABLE "aggregateoccurrence_pkeys" AS |
| 2302 | 2456 |
SELECT |
| 2303 |
"in#13_insert_in_pkeys"."row_num"
|
|
| 2304 |
, "in#13_insert_out_pkeys"."aggregateoccurrence_id" AS "out.aggregateoccurrence_id"
|
|
| 2305 |
FROM "in#13_insert_in_pkeys"
|
|
| 2306 |
JOIN "in#13_insert_out_pkeys" USING ("_row_num")
|
|
| 2457 |
"in#14_insert_in_pkeys"."row_num"
|
|
| 2458 |
, "in#14_insert_out_pkeys"."aggregateoccurrence_id" AS "out.aggregateoccurrence_id"
|
|
| 2459 |
FROM "in#14_insert_in_pkeys"
|
|
| 2460 |
JOIN "in#14_insert_out_pkeys" USING ("_row_num")
|
|
| 2307 | 2461 |
</code></pre> |
| 2308 | 2462 |
# Setting pkeys of missing rows to @None@ |
| 2309 | 2463 |
<pre><code class="SQL"> |
| 2310 | 2464 |
INSERT INTO "aggregateoccurrence_pkeys" |
| 2311 | 2465 |
("row_num", "out.aggregateoccurrence_id")
|
| 2312 | 2466 |
SELECT |
| 2313 |
"in#13_full"."row_num"
|
|
| 2467 |
"in#14_full"."row_num"
|
|
| 2314 | 2468 |
, NULL AS "out.aggregateoccurrence_id" |
| 2315 |
FROM "in#13_full"
|
|
| 2316 |
LEFT JOIN "aggregateoccurrence_pkeys" ON "aggregateoccurrence_pkeys"."row_num" = "in#13_full"."row_num"
|
|
| 2469 |
FROM "in#14_full"
|
|
| 2470 |
LEFT JOIN "aggregateoccurrence_pkeys" ON "aggregateoccurrence_pkeys"."row_num" = "in#14_full"."row_num"
|
|
| 2317 | 2471 |
WHERE "aggregateoccurrence_pkeys"."row_num" IS NULL |
| 2318 | 2472 |
</code></pre> |
| 2319 | 2473 |
# ********** New iteration ********** |
| 2320 | 2474 |
# Inserting these input columns into @"plantobservation"@: |
| 2321 | 2475 |
|
| 2322 | 2476 |
|Output|Input| |
| 2477 |
|@'authorplantcode'@|@"ACAD.Specimen"."recordNumber"@| |
|
| 2323 | 2478 |
|@'aggregateoccurrence_id'@|@"aggregateoccurrence_pkeys"."out.aggregateoccurrence_id"@| |
| 2324 |
|@'collectionnumber'@|@"ACAD.Specimen"."recordNumber"@|
|
|
| 2479 |
|@'reproductivecondition'@|@"ACAD.Specimen"."reproductiveCondition"@|
|
|
| 2325 | 2480 |
|
| 2326 | 2481 |
# Joining together input tables into temp table |
| 2327 | 2482 |
<pre><code class="SQL"> |
| 2328 |
CREATE TEMP TABLE "in#14" AS
|
|
| 2483 |
CREATE TEMP TABLE "in#15" AS
|
|
| 2329 | 2484 |
SELECT |
| 2330 | 2485 |
"ACAD.Specimen"."row_num" AS "row_num" |
| 2486 |
, "ACAD.Specimen"."recordNumber" AS "ACAD.Specimen.recordNumber" |
|
| 2331 | 2487 |
, "aggregateoccurrence_pkeys"."out.aggregateoccurrence_id" AS "aggregateoccurrence_pkeys.out.aggregateoccurrence_id" |
| 2332 |
, "ACAD.Specimen"."recordNumber" AS "ACAD.Specimen.recordNumber"
|
|
| 2488 |
, "ACAD.Specimen"."reproductiveCondition" AS "ACAD.Specimen.reproductiveCondition"
|
|
| 2333 | 2489 |
FROM "ACAD.Specimen" |
| 2334 | 2490 |
JOIN "aggregateoccurrence_pkeys" USING ("row_num")
|
| 2335 | 2491 |
ORDER BY row_num |
| 2336 |
</code></pre>Temp table: @"in#14"@
|
|
| 2492 |
</code></pre>Temp table: @"in#15"@
|
|
| 2337 | 2493 |
<pre><code class="SQL"> |
| 2338 |
CREATE TEMP TABLE "in#14_full" (
|
|
| 2339 |
LIKE "in#14" INCLUDING ALL
|
|
| 2494 |
CREATE TEMP TABLE "in#15_full" (
|
|
| 2495 |
LIKE "in#15" INCLUDING ALL
|
|
| 2340 | 2496 |
); |
| 2341 | 2497 |
</code></pre><pre><code class="SQL"> |
| 2342 |
INSERT INTO "in#14_full"
|
|
| 2343 |
SELECT * FROM "in#14"
|
|
| 2498 |
INSERT INTO "in#15_full"
|
|
| 2499 |
SELECT * FROM "in#15"
|
|
| 2344 | 2500 |
</code></pre> |
| 2345 | 2501 |
# Trying to insert new rows |
| 2346 | 2502 |
<pre><code class="SQL"> |
| ... | ... | |
| 2349 | 2505 |
LANGUAGE sql |
| 2350 | 2506 |
AS $$ |
| 2351 | 2507 |
INSERT INTO "plantobservation" |
| 2352 |
("aggregateoccurrence_id", "collectionnumber")
|
|
| 2508 |
("authorplantcode", "aggregateoccurrence_id", "reproductivecondition")
|
|
| 2353 | 2509 |
SELECT |
| 2354 |
"in#14"."aggregateoccurrence_pkeys.out.aggregateoccurrence_id" |
|
| 2355 |
, "in#14"."ACAD.Specimen.recordNumber" |
|
| 2356 |
FROM "in#14" |
|
| 2510 |
"in#15"."ACAD.Specimen.recordNumber" |
|
| 2511 |
, "in#15"."aggregateoccurrence_pkeys.out.aggregateoccurrence_id" |
|
Also available in: Unified diff
Regenerated inputs/ACAD/Specimen/logs/steps.by_col.log.sql