CREATE TEMP TABLE "ACAD.Specimen" (
LIKE "ACAD"."Specimen" INCLUDING ALL
);
INSERT INTO "ACAD.Specimen"
SELECT * FROM "ACAD"."Specimen"
ORDER BY row_num
LIMIT 100
# ********** New iteration **********
# Inserting these input columns into @"_join"@:
|Output|Input|
|@'1'@|@"ACAD.Specimen"."collectionCode"@|
|@'2'@|@"ACAD.Specimen"."collectionID"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."collectionCode" AS "ACAD.Specimen.collectionCode"
, "ACAD.Specimen"."collectionID" AS "ACAD.Specimen.collectionID"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in"@
# Defining wrapper function
CREATE TEMP TABLE "_join(1=collectionCode, 2=collectionID)" AS
SELECT
"in"."row_num"
, "_join"("1" := "in"."ACAD.Specimen.collectionCode", "2" := "in"."ACAD.Specimen.collectionID") AS "result"
FROM "in"
LIMIT 0
CREATE FUNCTION "pg_temp"."_join(1=collectionCode, 2=collectionID)_wrap"()
RETURNS SETOF "_join(1=collectionCode, 2=collectionID)"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_join"("1" := "row"."ACAD.Specimen.collectionCode", "2" := "row"."ACAD.Specimen.collectionID")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.collectionCode" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."ACAD.Specimen.collectionID" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('collectionCode,collectionID')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_join(1=collectionCode, 2=collectionID)"
("row_num", "result")
SELECT * FROM "pg_temp"."_join(1=collectionCode, 2=collectionID)_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"_join"@:
|Output|Input|
|@'1'@|@"ACAD.Specimen"."institutionCode"@|
|@'3'@|@"ACAD.Specimen"."catalogNumber"@|
|@'2'@|@"_join(1=collectionCode, 2=collectionID)"."result"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#1" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."institutionCode" AS "ACAD.Specimen.institutionCode"
, "ACAD.Specimen"."catalogNumber" AS "ACAD.Specimen.catalogNumber"
, "_join(1=collectionCode, 2=collectionID)"."result" AS "_join(1=collectionCode, 2=collectionID).result"
FROM "ACAD.Specimen"
JOIN "_join(1=collectionCode, 2=collectionID)" USING ("row_num")
ORDER BY row_num
Temp table: @"in#1"@
# Defining wrapper function
CREATE TEMP TABLE "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC" AS
SELECT
"in#1"."row_num"
, "_join"("1" := "in#1"."ACAD.Specimen.institutionCode", "3" := "in#1"."ACAD.Specimen.catalogNumber", "2" := "in#1"."_join(1=collectionCode, 2=collectionID).result") AS "result"
FROM "in#1"
LIMIT 0
CREATE FUNCTION "pg_temp"."_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collec_wrap"()
RETURNS SETOF "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#1"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#1"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_join"("1" := "row"."ACAD.Specimen.institutionCode", "3" := "row"."ACAD.Specimen.catalogNumber", "2" := "row"."_join(1=collectionCode, 2=collectionID).result")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
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))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('institutionCode,catalogNumber,collectionCode'), ('institutionCode,catalogNumber,collectionID')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC"
("row_num", "result")
SELECT * FROM "pg_temp"."_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collec_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"_alt"@:
|Output|Input|
|@'1'@|@"ACAD.Specimen"."id"@|
|@'2'@|@"_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC"."result"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#2" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."id" AS "ACAD.Specimen.id"
, "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC"."result" AS "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=coll.result"
FROM "ACAD.Specimen"
JOIN "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC" USING ("row_num")
ORDER BY row_num
Temp table: @"in#2"@
# Defining wrapper function
CREATE TEMP TABLE "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join(" AS
SELECT
"in#2"."row_num"
, "_alt"("1" := "in#2"."ACAD.Specimen.id", "2" := "in#2"."_join(1=institutionCode, 3=catalogNumber, 2=_join(1=coll.result") AS "result"
FROM "in#2"
LIMIT 0
CREATE FUNCTION "pg_temp"."_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=__wrap"()
RETURNS SETOF "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join("
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#2"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#2"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_alt"("1" := "row"."ACAD.Specimen.id", "2" := "row"."_join(1=institutionCode, 3=catalogNumber, 2=_join(1=coll.result")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
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))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('id,institutionCode'), ('id,catalogNumber'), ('id,collectionCode'), ('id,collectionID')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join("
("row_num", "result")
SELECT * FROM "pg_temp"."_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=__wrap"()
# ********** New iteration **********
# Inserting these input columns into @"_label"@:
|Output|Input|
|@'value'@|@"ACAD.Specimen"."habitat"@|
|@'label'@|@'habitat'@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#3" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."habitat" AS "ACAD.Specimen.habitat"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#3"@
# Defining wrapper function
CREATE TEMP TABLE "_label(habitat)" AS
SELECT
"in#3"."row_num"
, "_label"("value" := "in#3"."ACAD.Specimen.habitat", "label" := 'habitat') AS "result"
FROM "in#3"
LIMIT 0
CREATE FUNCTION "pg_temp"."_label(habitat)_wrap"()
RETURNS SETOF "_label(habitat)"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#3"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#3"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_label"("value" := "row"."ACAD.Specimen.habitat", "label" := 'habitat')
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.habitat" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('habitat')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_label(habitat)"
("row_num", "result")
SELECT * FROM "pg_temp"."_label(habitat)_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"_merge"@:
|Output|Input|
|@'1'@|@"ACAD.Specimen"."locality"@|
|@'3'@|@"_label(habitat)"."result"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#4" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."locality" AS "ACAD.Specimen.locality"
, "_label(habitat)"."result" AS "_label(habitat).result"
FROM "ACAD.Specimen"
JOIN "_label(habitat)" USING ("row_num")
ORDER BY row_num
Temp table: @"in#4"@
# Defining wrapper function
CREATE TEMP TABLE "_merge(1=locality, 3=_label(habitat))" AS
SELECT
"in#4"."row_num"
, "_merge"("1" := "in#4"."ACAD.Specimen.locality", "3" := "in#4"."_label(habitat).result") AS "result"
FROM "in#4"
LIMIT 0
CREATE FUNCTION "pg_temp"."_merge(1=locality, 3=_label(habitat))_wrap"()
RETURNS SETOF "_merge(1=locality, 3=_label(habitat))"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#4"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#4"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_merge"("1" := "row"."ACAD.Specimen.locality", "3" := "row"."_label(habitat).result")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
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))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('locality,habitat')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_merge(1=locality, 3=_label(habitat))"
("row_num", "result")
SELECT * FROM "pg_temp"."_merge(1=locality, 3=_label(habitat))_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"_join"@:
|Output|Input|
|@'1'@|@"ACAD.Specimen"."locality"@|
|@'3'@|@"ACAD.Specimen"."habitat"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#5" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."locality" AS "ACAD.Specimen.locality"
, "ACAD.Specimen"."habitat" AS "ACAD.Specimen.habitat"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#5"@
# Defining wrapper function
CREATE TEMP TABLE "_join(1=locality, 3=habitat)" AS
SELECT
"in#5"."row_num"
, "_join"("1" := "in#5"."ACAD.Specimen.locality", "3" := "in#5"."ACAD.Specimen.habitat") AS "result"
FROM "in#5"
LIMIT 0
CREATE FUNCTION "pg_temp"."_join(1=locality, 3=habitat)_wrap"()
RETURNS SETOF "_join(1=locality, 3=habitat)"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#5"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#5"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_join"("1" := "row"."ACAD.Specimen.locality", "3" := "row"."ACAD.Specimen.habitat")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.locality" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."ACAD.Specimen.habitat" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('locality,habitat')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_join(1=locality, 3=habitat)"
("row_num", "result")
SELECT * FROM "pg_temp"."_join(1=locality, 3=habitat)_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"_locationnarrative_is_cultivated"@:
|Output|Input|
|@'locationnarrative'@|@"_join(1=locality, 3=habitat)"."result"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#6" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "_join(1=locality, 3=habitat)"."result" AS "_join(1=locality, 3=habitat).result"
FROM "ACAD.Specimen"
JOIN "_join(1=locality, 3=habitat)" USING ("row_num")
ORDER BY row_num
Temp table: @"in#6"@
# Defining wrapper function
CREATE TEMP TABLE "_locationnarrative_is_cultivated(locationnarrative=_join(1=loca" AS
SELECT
"in#6"."row_num"
, "_locationnarrative_is_cultivated"("locationnarrative" := "in#6"."_join(1=locality, 3=habitat).result") AS "result"
FROM "in#6"
LIMIT 0
CREATE FUNCTION "pg_temp"."_locationnarrative_is_cultivated(locationnarrative=_join(1_wrap"()
RETURNS SETOF "_locationnarrative_is_cultivated(locationnarrative=_join(1=loca"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#6"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#6"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_locationnarrative_is_cultivated"("locationnarrative" := "row"."_join(1=locality, 3=habitat).result")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."_join(1=locality, 3=habitat).result" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('locality'), ('habitat')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS boolean)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_locationnarrative_is_cultivated(locationnarrative=_join(1=loca"
("row_num", "result")
SELECT * FROM "pg_temp"."_locationnarrative_is_cultivated(locationnarrative=_join(1_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"location"@:
|Output|Input|
|@'verbatimelevation'@|@"ACAD.Specimen"."verbatimElevation"@|
|@'authorlocationcode'@|@"_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join("."result"@|
|@'elevation_m'@|@"ACAD.Specimen"."verbatimElevation"@|
|@'locationnarrative'@|@"_merge(1=locality, 3=_label(habitat))"."result"@|
|@'accessconditions'@|@"ACAD.Specimen"."rights"@|
|@'iscultivated'@|@"_locationnarrative_is_cultivated(locationnarrative=_join(1=loca"."result"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#7" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."verbatimElevation" AS "ACAD.Specimen.verbatimElevation"
, "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join("."result" AS "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2.result"
, "_merge(1=locality, 3=_label(habitat))"."result" AS "_merge(1=locality, 3=_label(habitat)).result"
, "ACAD.Specimen"."rights" AS "ACAD.Specimen.rights"
, "_locationnarrative_is_cultivated(locationnarrative=_join(1=loca"."result" AS "_locationnarrative_is_cultivated(locationnarrative=_join.result"
FROM "ACAD.Specimen"
JOIN "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join(" USING ("row_num")
JOIN "_merge(1=locality, 3=_label(habitat))" USING ("row_num")
JOIN "_locationnarrative_is_cultivated(locationnarrative=_join(1=loca" USING ("row_num")
ORDER BY row_num
Temp table: @"in#7"@
CREATE TEMP TABLE "in#7_full" (
LIKE "in#7" INCLUDING ALL
);
INSERT INTO "in#7_full"
SELECT * FROM "in#7"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO location"()
RETURNS SETOF "location"."location_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "location"
("verbatimelevation", "authorlocationcode", "elevation_m", "locationnarrative", "accessconditions", "iscultivated")
SELECT
"in#7"."ACAD.Specimen.verbatimElevation"
, "in#7"."_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2.result"
, "in#7"."ACAD.Specimen.verbatimElevation"
, "in#7"."_merge(1=locality, 3=_label(habitat)).result"
, "in#7"."ACAD.Specimen.rights"
, "in#7"."_locationnarrative_is_cultivated(locationnarrative=_join.result"
FROM "in#7"
RETURNING "location_id"
$$;
# Caught exception: MissingCastException: Missing cast to type @double precision@ on column: @elevation_m@
# Casting @'elevation_m'@ input to @double precision@
CREATE FUNCTION "pg_temp"."double(verbatimElevation)"("value" anyelement)
RETURNS double precision
LANGUAGE plpgsql
STRICT
AS $$
BEGIN
/* The explicit cast to the return type is needed to make the cast happen
inside the try block. (Implicit casts to the return type happen at the end
of the function, outside any block.) */
RETURN CAST(value AS double precision);
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := value;
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('verbatimElevation')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN NULL;
END;
$$;
ALTER TABLE "in#7" ADD COLUMN "ACAD.Specimen.verbatimElevation::double" double precision /*"in#7"."ACAD.Specimen.verbatimElevation"::double precision*/
ALTER TABLE "in#7"
ALTER COLUMN "ACAD.Specimen.verbatimElevation::double" TYPE double precision
USING "pg_temp"."double(verbatimElevation)"("in#7"."ACAD.Specimen.verbatimElevation")
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO location"()
RETURNS SETOF "location"."location_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "location"
("verbatimelevation", "authorlocationcode", "elevation_m", "locationnarrative", "accessconditions", "iscultivated")
SELECT
"in#7"."ACAD.Specimen.verbatimElevation"
, "in#7"."_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2.result"
, "in#7"."ACAD.Specimen.verbatimElevation::double"
, "in#7"."_merge(1=locality, 3=_label(habitat)).result"
, "in#7"."ACAD.Specimen.rights"
, "in#7"."_locationnarrative_is_cultivated(locationnarrative=_join.result"
FROM "in#7"
RETURNING "location_id"
$$;
CREATE TEMP TABLE "in#7_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO location"() AS "f" ("location_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO location#1"()
RETURNS SETOF "location"."location_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "location"
("verbatimelevation", "authorlocationcode", "elevation_m", "locationnarrative", "accessconditions", "iscultivated", "source_id")
SELECT
"in#7"."ACAD.Specimen.verbatimElevation"
, "in#7"."_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2.result"
, "in#7"."ACAD.Specimen.verbatimElevation::double"
, "in#7"."_merge(1=locality, 3=_label(habitat)).result"
, "in#7"."ACAD.Specimen.rights"
, "in#7"."_locationnarrative_is_cultivated(locationnarrative=_join.result"
, 1 AS "source_id"
FROM "in#7"
RETURNING "location_id"
$$;
CREATE TEMP TABLE "in#7_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO location#1"() AS "f" ("location_id")
# Getting input table pkeys of inserted rows
CREATE TEMP TABLE "in#7_insert_in_pkeys" AS
SELECT "row_num" FROM "in#7"
# Combining output and input pkeys in inserted order
CREATE TEMP TABLE "location_pkeys" AS
SELECT
"in#7_insert_in_pkeys"."row_num"
, "in#7_insert_out_pkeys"."location_id" AS "out.location_id"
FROM "in#7_insert_in_pkeys"
JOIN "in#7_insert_out_pkeys" USING ("_row_num")
# Setting pkeys of missing rows to @None@
INSERT INTO "location_pkeys"
("row_num", "out.location_id")
SELECT
"in#7_full"."row_num"
, NULL AS "out.location_id"
FROM "in#7_full"
LEFT JOIN "location_pkeys" ON "location_pkeys"."row_num" = "in#7_full"."row_num"
WHERE "location_pkeys"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"_dateRangeEnd"@:
|Output|Input|
|@'value'@|@"ACAD.Specimen"."eventDate"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#8" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."eventDate" AS "ACAD.Specimen.eventDate"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#8"@
# Defining wrapper function
CREATE TEMP TABLE "_dateRangeEnd(eventDate)" AS
SELECT
"in#8"."row_num"
, "_dateRangeEnd"("value" := "in#8"."ACAD.Specimen.eventDate") AS "result"
FROM "in#8"
LIMIT 0
CREATE FUNCTION "pg_temp"."_dateRangeEnd(eventDate)_wrap"()
RETURNS SETOF "_dateRangeEnd(eventDate)"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#8"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#8"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_dateRangeEnd"("value" := "row"."ACAD.Specimen.eventDate")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.eventDate" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('eventDate')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_dateRangeEnd(eventDate)"
("row_num", "result")
SELECT * FROM "pg_temp"."_dateRangeEnd(eventDate)_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"_dateRangeStart"@:
|Output|Input|
|@'value'@|@"ACAD.Specimen"."eventDate"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#9" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."eventDate" AS "ACAD.Specimen.eventDate"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#9"@
# Defining wrapper function
CREATE TEMP TABLE "_dateRangeStart(eventDate)" AS
SELECT
"in#9"."row_num"
, "_dateRangeStart"("value" := "in#9"."ACAD.Specimen.eventDate") AS "result"
FROM "in#9"
LIMIT 0
CREATE FUNCTION "pg_temp"."_dateRangeStart(eventDate)_wrap"()
RETURNS SETOF "_dateRangeStart(eventDate)"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#9"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#9"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_dateRangeStart"("value" := "row"."ACAD.Specimen.eventDate")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.eventDate" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('eventDate')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_dateRangeStart(eventDate)"
("row_num", "result")
SELECT * FROM "pg_temp"."_dateRangeStart(eventDate)_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"locationevent"@:
|Output|Input|
|@'obsenddate'@|@"_dateRangeEnd(eventDate)"."result"@|
|@'location_id'@|@"location_pkeys"."out.location_id"@|
|@'obsstartdate'@|@"_dateRangeStart(eventDate)"."result"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#10" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "_dateRangeEnd(eventDate)"."result" AS "_dateRangeEnd(eventDate).result"
, "location_pkeys"."out.location_id" AS "location_pkeys.out.location_id"
, "_dateRangeStart(eventDate)"."result" AS "_dateRangeStart(eventDate).result"
FROM "ACAD.Specimen"
JOIN "_dateRangeEnd(eventDate)" USING ("row_num")
JOIN "location_pkeys" USING ("row_num")
JOIN "_dateRangeStart(eventDate)" USING ("row_num")
ORDER BY row_num
Temp table: @"in#10"@
CREATE TEMP TABLE "in#10_full" (
LIKE "in#10" INCLUDING ALL
);
INSERT INTO "in#10_full"
SELECT * FROM "in#10"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO locationevent"()
RETURNS SETOF "locationevent"."locationevent_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "locationevent"
("obsenddate", "location_id", "obsstartdate")
SELECT
"in#10"."_dateRangeEnd(eventDate).result"
, "in#10"."location_pkeys.out.location_id"
, "in#10"."_dateRangeStart(eventDate).result"
FROM "in#10"
RETURNING "locationevent_id"
$$;
# Caught exception: MissingCastException: Missing cast to type @date@ on column: @obsenddate@
# Casting @'obsenddate'@ input to @date@
CREATE FUNCTION "pg_temp"."date(eventDate)"("value" anyelement)
RETURNS date
LANGUAGE plpgsql
STRICT
AS $$
BEGIN
/* The explicit cast to the return type is needed to make the cast happen
inside the try block. (Implicit casts to the return type happen at the end
of the function, outside any block.) */
RETURN CAST(value AS date);
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := value;
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('eventDate')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN NULL;
END;
$$;
ALTER TABLE "in#10" ADD COLUMN "_dateRangeEnd(eventDate).result::date" date /*"in#10"."_dateRangeEnd(eventDate).result"::date*/
ALTER TABLE "in#10"
ALTER COLUMN "_dateRangeEnd(eventDate).result::date" TYPE date
USING "pg_temp"."date(eventDate)"("in#10"."_dateRangeEnd(eventDate).result")
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO locationevent"()
RETURNS SETOF "locationevent"."locationevent_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "locationevent"
("obsenddate", "location_id", "obsstartdate")
SELECT
"in#10"."_dateRangeEnd(eventDate).result::date"
, "in#10"."location_pkeys.out.location_id"
, "in#10"."_dateRangeStart(eventDate).result"
FROM "in#10"
RETURNING "locationevent_id"
$$;
# Caught exception: MissingCastException: Missing cast to type @date@ on column: @obsstartdate@
# Casting @'obsstartdate'@ input to @date@
CREATE FUNCTION "pg_temp"."date(eventDate)"("value" anyelement)
RETURNS date
LANGUAGE plpgsql
STRICT
AS $$
BEGIN
/* The explicit cast to the return type is needed to make the cast happen
inside the try block. (Implicit casts to the return type happen at the end
of the function, outside any block.) */
RETURN CAST(value AS date);
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := value;
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('eventDate')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN NULL;
END;
$$;
ALTER TABLE "in#10" ADD COLUMN "_dateRangeStart(eventDate).result::date" date /*"in#10"."_dateRangeStart(eventDate).result"::date*/
ALTER TABLE "in#10"
ALTER COLUMN "_dateRangeStart(eventDate).result::date" TYPE date
USING "pg_temp"."date(eventDate)"("in#10"."_dateRangeStart(eventDate).result")
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO locationevent"()
RETURNS SETOF "locationevent"."locationevent_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "locationevent"
("obsenddate", "location_id", "obsstartdate")
SELECT
"in#10"."_dateRangeEnd(eventDate).result::date"
, "in#10"."location_pkeys.out.location_id"
, "in#10"."_dateRangeStart(eventDate).result::date"
FROM "in#10"
RETURNING "locationevent_id"
$$;
CREATE TEMP TABLE "in#10_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO locationevent"() AS "f" ("locationevent_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO locationevent#1"()
RETURNS SETOF "locationevent"."locationevent_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "locationevent"
("obsenddate", "location_id", "obsstartdate", "source_id")
SELECT
"in#10"."_dateRangeEnd(eventDate).result::date"
, "in#10"."location_pkeys.out.location_id"
, "in#10"."_dateRangeStart(eventDate).result::date"
, 1 AS "source_id"
FROM "in#10"
RETURNING "locationevent_id"
$$;
CREATE TEMP TABLE "in#10_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO locationevent#1"() AS "f" ("locationevent_id")
# Getting input table pkeys of inserted rows
CREATE TEMP TABLE "in#10_insert_in_pkeys" AS
SELECT "row_num" FROM "in#10"
# Combining output and input pkeys in inserted order
CREATE TEMP TABLE "locationevent_pkeys" AS
SELECT
"in#10_insert_in_pkeys"."row_num"
, "in#10_insert_out_pkeys"."locationevent_id" AS "out.locationevent_id"
FROM "in#10_insert_in_pkeys"
JOIN "in#10_insert_out_pkeys" USING ("_row_num")
# Setting pkeys of missing rows to @None@
INSERT INTO "locationevent_pkeys"
("row_num", "out.locationevent_id")
SELECT
"in#10_full"."row_num"
, NULL AS "out.locationevent_id"
FROM "in#10_full"
LEFT JOIN "locationevent_pkeys" ON "locationevent_pkeys"."row_num" = "in#10_full"."row_num"
WHERE "locationevent_pkeys"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"party"@:
|Output|Input|
|@'fullname'@|@"ACAD.Specimen"."recordedBy"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#11" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."recordedBy" AS "ACAD.Specimen.recordedBy"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#11"@
CREATE TEMP TABLE "in#11_full" (
LIKE "in#11" INCLUDING ALL
);
INSERT INTO "in#11_full"
SELECT * FROM "in#11"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO party"()
RETURNS SETOF "party"."party_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "party"
("fullname")
SELECT "in#11"."ACAD.Specimen.recordedBy" FROM "in#11"
RETURNING "party_id"
$$;
CREATE TEMP TABLE "in#11_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO party"() AS "f" ("party_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO party#1"()
RETURNS SETOF "party"."party_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "party"
("fullname", "source_id")
SELECT
"in#11"."ACAD.Specimen.recordedBy"
, 1 AS "source_id"
FROM "in#11"
RETURNING "party_id"
$$;
CREATE TEMP TABLE "in#11_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO party#1"() AS "f" ("party_id")
# Caught exception: DuplicateKeyException: Violated @party_unique_name@ constraint with condition @sourceaccessioncode IS NULL@ on columns: @source_id, organizationname, fullname, surname, givenname, middlename, suffix@
# Ignoring rows that do not satisfy @true@
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#11_distinct" (
LIKE "in#11" INCLUDING ALL
);
INSERT INTO "in#11_distinct"
SELECT
DISTINCT ON ("in#11"."ACAD.Specimen.recordedBy")
"in#11".*
FROM "in#11"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO party#2"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "party"%ROWTYPE;
BEGIN
FOR "row"."fullname", "row"."source_id" IN
SELECT
"in#11_distinct"."ACAD.Specimen.recordedBy"
, 1 AS "source_id"
FROM "in#11_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "party"
("fullname", "source_id")
VALUES ("row"."fullname", "row"."source_id")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
WARNING: column "NULL" has type "unknown"
DETAIL: Proceeding with relation creation anyway.
CREATE TEMP TABLE "rowcount" AS
SELECT * FROM "pg_temp"."INSERT INTO party#2"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "party_pkeys" AS
SELECT
"in#11"."row_num"
, "party"."party_id" AS "out.party_id"
FROM "in#11"
JOIN "party" ON
"party"."source_id" = 1
AND COALESCE("party"."organizationname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("party"."fullname", CAST('\N' AS text)) = COALESCE("in#11"."ACAD.Specimen.recordedBy", CAST('\N' AS text))
AND COALESCE("party"."surname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("party"."givenname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("party"."middlename", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("party"."suffix", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
# Setting pkeys of missing rows to @None@
INSERT INTO "party_pkeys"
("row_num", "out.party_id")
SELECT
"in#11_full"."row_num"
, NULL AS "out.party_id"
FROM "in#11_full"
LEFT JOIN "party_pkeys" ON "party_pkeys"."row_num" = "in#11_full"."row_num"
WHERE "party_pkeys"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"taxonoccurrence"@:
|Output|Input|
|@'sourceaccessioncode'@|@"ACAD.Specimen"."id"@|
|@'collector_id'@|@"party_pkeys"."out.party_id"@|
|@'locationevent_id'@|@"locationevent_pkeys"."out.locationevent_id"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#12" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."id" AS "ACAD.Specimen.id"
, "party_pkeys"."out.party_id" AS "party_pkeys.out.party_id"
, "locationevent_pkeys"."out.locationevent_id" AS "locationevent_pkeys.out.locationevent_id"
FROM "ACAD.Specimen"
JOIN "party_pkeys" USING ("row_num")
JOIN "locationevent_pkeys" USING ("row_num")
ORDER BY row_num
Temp table: @"in#12"@
CREATE TEMP TABLE "in#12_full" (
LIKE "in#12" INCLUDING ALL
);
INSERT INTO "in#12_full"
SELECT * FROM "in#12"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonoccurrence"()
RETURNS SETOF "taxonoccurrence"."taxonoccurrence_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonoccurrence"
("sourceaccessioncode", "collector_id", "locationevent_id")
SELECT
"in#12"."ACAD.Specimen.id"
, "in#12"."party_pkeys.out.party_id"
, "in#12"."locationevent_pkeys.out.locationevent_id"
FROM "in#12"
RETURNING "taxonoccurrence_id"
$$;
CREATE TEMP TABLE "in#12_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonoccurrence"() AS "f" ("taxonoccurrence_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonoccurrence#1"()
RETURNS SETOF "taxonoccurrence"."taxonoccurrence_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonoccurrence"
("sourceaccessioncode", "collector_id", "locationevent_id", "source_id")
SELECT
"in#12"."ACAD.Specimen.id"
, "in#12"."party_pkeys.out.party_id"
, "in#12"."locationevent_pkeys.out.locationevent_id"
, 1 AS "source_id"
FROM "in#12"
RETURNING "taxonoccurrence_id"
$$;
CREATE TEMP TABLE "in#12_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonoccurrence#1"() AS "f" ("taxonoccurrence_id")
# Getting input table pkeys of inserted rows
CREATE TEMP TABLE "in#12_insert_in_pkeys" AS
SELECT "row_num" FROM "in#12"
# Combining output and input pkeys in inserted order
CREATE TEMP TABLE "taxonoccurrence_pkeys" AS
SELECT
"in#12_insert_in_pkeys"."row_num"
, "in#12_insert_out_pkeys"."taxonoccurrence_id" AS "out.taxonoccurrence_id"
FROM "in#12_insert_in_pkeys"
JOIN "in#12_insert_out_pkeys" USING ("_row_num")
# Setting pkeys of missing rows to @None@
INSERT INTO "taxonoccurrence_pkeys"
("row_num", "out.taxonoccurrence_id")
SELECT
"in#12_full"."row_num"
, NULL AS "out.taxonoccurrence_id"
FROM "in#12_full"
LEFT JOIN "taxonoccurrence_pkeys" ON "taxonoccurrence_pkeys"."row_num" = "in#12_full"."row_num"
WHERE "taxonoccurrence_pkeys"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"aggregateoccurrence"@:
|Output|Input|
|@'taxonoccurrence_id'@|@"taxonoccurrence_pkeys"."out.taxonoccurrence_id"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#13" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "taxonoccurrence_pkeys"."out.taxonoccurrence_id" AS "taxonoccurrence_pkeys.out.taxonoccurrence_id"
FROM "ACAD.Specimen"
JOIN "taxonoccurrence_pkeys" USING ("row_num")
ORDER BY row_num
Temp table: @"in#13"@
CREATE TEMP TABLE "in#13_full" (
LIKE "in#13" INCLUDING ALL
);
INSERT INTO "in#13_full"
SELECT * FROM "in#13"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO aggregateoccurrence"()
RETURNS SETOF "aggregateoccurrence"."aggregateoccurrence_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "aggregateoccurrence"
("taxonoccurrence_id")
SELECT "in#13"."taxonoccurrence_pkeys.out.taxonoccurrence_id" FROM "in#13"
RETURNING "aggregateoccurrence_id"
$$;
CREATE TEMP TABLE "in#13_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO aggregateoccurrence"() AS "f" ("aggregateoccurrence_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO aggregateoccurrence#1"()
RETURNS SETOF "aggregateoccurrence"."aggregateoccurrence_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "aggregateoccurrence"
("taxonoccurrence_id", "source_id")
SELECT
"in#13"."taxonoccurrence_pkeys.out.taxonoccurrence_id"
, 1 AS "source_id"
FROM "in#13"
RETURNING "aggregateoccurrence_id"
$$;
CREATE TEMP TABLE "in#13_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO aggregateoccurrence#1"() AS "f" ("aggregateoccurrence_id")
# Getting input table pkeys of inserted rows
CREATE TEMP TABLE "in#13_insert_in_pkeys" AS
SELECT "row_num" FROM "in#13"
# Combining output and input pkeys in inserted order
CREATE TEMP TABLE "aggregateoccurrence_pkeys" AS
SELECT
"in#13_insert_in_pkeys"."row_num"
, "in#13_insert_out_pkeys"."aggregateoccurrence_id" AS "out.aggregateoccurrence_id"
FROM "in#13_insert_in_pkeys"
JOIN "in#13_insert_out_pkeys" USING ("_row_num")
# Setting pkeys of missing rows to @None@
INSERT INTO "aggregateoccurrence_pkeys"
("row_num", "out.aggregateoccurrence_id")
SELECT
"in#13_full"."row_num"
, NULL AS "out.aggregateoccurrence_id"
FROM "in#13_full"
LEFT JOIN "aggregateoccurrence_pkeys" ON "aggregateoccurrence_pkeys"."row_num" = "in#13_full"."row_num"
WHERE "aggregateoccurrence_pkeys"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"plantobservation"@:
|Output|Input|
|@'aggregateoccurrence_id'@|@"aggregateoccurrence_pkeys"."out.aggregateoccurrence_id"@|
|@'collectionnumber'@|@"ACAD.Specimen"."recordNumber"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#14" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "aggregateoccurrence_pkeys"."out.aggregateoccurrence_id" AS "aggregateoccurrence_pkeys.out.aggregateoccurrence_id"
, "ACAD.Specimen"."recordNumber" AS "ACAD.Specimen.recordNumber"
FROM "ACAD.Specimen"
JOIN "aggregateoccurrence_pkeys" USING ("row_num")
ORDER BY row_num
Temp table: @"in#14"@
CREATE TEMP TABLE "in#14_full" (
LIKE "in#14" INCLUDING ALL
);
INSERT INTO "in#14_full"
SELECT * FROM "in#14"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO plantobservation"()
RETURNS SETOF "plantobservation"."plantobservation_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "plantobservation"
("aggregateoccurrence_id", "collectionnumber")
SELECT
"in#14"."aggregateoccurrence_pkeys.out.aggregateoccurrence_id"
, "in#14"."ACAD.Specimen.recordNumber"
FROM "in#14"
RETURNING "plantobservation_id"
$$;
CREATE TEMP TABLE "in#14_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO plantobservation"() AS "f" ("plantobservation_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO plantobservation#1"()
RETURNS SETOF "plantobservation"."plantobservation_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "plantobservation"
("aggregateoccurrence_id", "collectionnumber", "source_id")
SELECT
"in#14"."aggregateoccurrence_pkeys.out.aggregateoccurrence_id"
, "in#14"."ACAD.Specimen.recordNumber"
, 1 AS "source_id"
FROM "in#14"
RETURNING "plantobservation_id"
$$;
CREATE TEMP TABLE "in#14_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO plantobservation#1"() AS "f" ("plantobservation_id")
# Getting input table pkeys of inserted rows
CREATE TEMP TABLE "in#14_insert_in_pkeys" AS
SELECT "row_num" FROM "in#14"
# Combining output and input pkeys in inserted order
CREATE TEMP TABLE "plantobservation_pkeys" AS
SELECT
"in#14_insert_in_pkeys"."row_num"
, "in#14_insert_out_pkeys"."plantobservation_id" AS "out.plantobservation_id"
FROM "in#14_insert_in_pkeys"
JOIN "in#14_insert_out_pkeys" USING ("_row_num")
# Setting pkeys of missing rows to @None@
INSERT INTO "plantobservation_pkeys"
("row_num", "out.plantobservation_id")
SELECT
"in#14_full"."row_num"
, NULL AS "out.plantobservation_id"
FROM "in#14_full"
LEFT JOIN "plantobservation_pkeys" ON "plantobservation_pkeys"."row_num" = "in#14_full"."row_num"
WHERE "plantobservation_pkeys"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"sourcelist"@:
|Output|Input|
|@'name'@|@"ACAD.Specimen"."institutionCode"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#15" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."institutionCode" AS "ACAD.Specimen.institutionCode"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#15"@
CREATE TEMP TABLE "in#15_full" (
LIKE "in#15" INCLUDING ALL
);
INSERT INTO "in#15_full"
SELECT * FROM "in#15"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO sourcelist"()
RETURNS SETOF "sourcelist"."sourcelist_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "sourcelist"
("name")
SELECT "in#15"."ACAD.Specimen.institutionCode" FROM "in#15"
RETURNING "sourcelist_id"
$$;
CREATE TEMP TABLE "in#15_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO sourcelist"() AS "f" ("sourcelist_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO sourcelist#1"()
RETURNS SETOF "sourcelist"."sourcelist_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "sourcelist"
("name", "source_id")
SELECT
"in#15"."ACAD.Specimen.institutionCode"
, 1 AS "source_id"
FROM "in#15"
RETURNING "sourcelist_id"
$$;
CREATE TEMP TABLE "in#15_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO sourcelist#1"() AS "f" ("sourcelist_id")
# Caught exception: DuplicateKeyException: Violated @sourcelist_unique@ constraint on columns: @source_id, name@
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#15_distinct" (
LIKE "in#15" INCLUDING ALL
);
INSERT INTO "in#15_distinct"
SELECT
DISTINCT ON ("in#15"."ACAD.Specimen.institutionCode")
"in#15".*
FROM "in#15"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO sourcelist#2"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "sourcelist"%ROWTYPE;
BEGIN
FOR "row"."name", "row"."source_id" IN
SELECT
"in#15_distinct"."ACAD.Specimen.institutionCode"
, 1 AS "source_id"
FROM "in#15_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "sourcelist"
("name", "source_id")
VALUES ("row"."name", "row"."source_id")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
CREATE TEMP TABLE "rowcount#1" AS
SELECT * FROM "pg_temp"."INSERT INTO sourcelist#2"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "sourcelist_pkeys" AS
SELECT
"in#15"."row_num"
, "sourcelist"."sourcelist_id" AS "out.sourcelist_id"
FROM "in#15"
JOIN "sourcelist" ON
"sourcelist"."source_id" = 1
AND "sourcelist"."name" = "in#15"."ACAD.Specimen.institutionCode"
# Setting pkeys of missing rows to @None@
INSERT INTO "sourcelist_pkeys"
("row_num", "out.sourcelist_id")
SELECT
"in#15_full"."row_num"
, NULL AS "out.sourcelist_id"
FROM "in#15_full"
LEFT JOIN "sourcelist_pkeys" ON "sourcelist_pkeys"."row_num" = "in#15_full"."row_num"
WHERE "sourcelist_pkeys"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"_split"@:
|Output|Input|
|@'separator'@|@', '@|
|@'value'@|@"ACAD.Specimen"."institutionCode"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#16" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."institutionCode" AS "ACAD.Specimen.institutionCode"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#16"@
# Defining wrapper function
CREATE TEMP TABLE "_split(institutionCode)" AS
SELECT
"in#16"."row_num"
, "_split"("separator" := ', ', "value" := "in#16"."ACAD.Specimen.institutionCode") AS "result"
FROM "in#16"
LIMIT 0
CREATE FUNCTION "pg_temp"."_split(institutionCode)_wrap"()
RETURNS SETOF "_split(institutionCode)"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#16"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#16"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_split"("separator" := ', ', "value" := "row"."ACAD.Specimen.institutionCode")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.institutionCode" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('institutionCode')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_split(institutionCode)"
("row_num", "result")
SELECT * FROM "pg_temp"."_split(institutionCode)_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"sourcename"@:
|Output|Input|
|@'sourcelist_id'@|@"sourcelist_pkeys"."out.sourcelist_id"@|
|@'name'@|@"_split(institutionCode)"."result"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#17" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "sourcelist_pkeys"."out.sourcelist_id" AS "sourcelist_pkeys.out.sourcelist_id"
, "_split(institutionCode)"."result" AS "_split(institutionCode).result"
FROM "ACAD.Specimen"
JOIN "sourcelist_pkeys" USING ("row_num")
JOIN "_split(institutionCode)" USING ("row_num")
ORDER BY row_num
Temp table: @"in#17"@
CREATE TEMP TABLE "in#17_full" (
LIKE "in#17" INCLUDING ALL
);
INSERT INTO "in#17_full"
SELECT * FROM "in#17"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO sourcename"()
RETURNS SETOF "sourcename"."sourcelist_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "sourcename"
("sourcelist_id", "name")
SELECT
"in#17"."sourcelist_pkeys.out.sourcelist_id"
, "in#17"."_split(institutionCode).result"
FROM "in#17"
RETURNING "sourcelist_id"
$$;
CREATE TEMP TABLE "in#17_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO sourcename"() AS "f" ("sourcelist_id")
# Caught exception: DuplicateKeyException: Violated @sourcename_pkey@ constraint on columns: @sourcelist_id, name@
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#17_distinct" (
LIKE "in#17" INCLUDING ALL
);
INSERT INTO "in#17_distinct"
SELECT
DISTINCT ON ("in#17"."sourcelist_pkeys.out.sourcelist_id", "in#17"."_split(institutionCode).result")
"in#17".*
FROM "in#17"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO sourcename#1"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "sourcename"%ROWTYPE;
BEGIN
FOR "row"."sourcelist_id", "row"."name" IN
SELECT
"in#17_distinct"."sourcelist_pkeys.out.sourcelist_id"
, "in#17_distinct"."_split(institutionCode).result"
FROM "in#17_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "sourcename"
("sourcelist_id", "name")
VALUES ("row"."sourcelist_id", "row"."name")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
CREATE TEMP TABLE "rowcount#2" AS
SELECT * FROM "pg_temp"."INSERT INTO sourcename#1"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "sourcename_pkeys" AS
SELECT
"in#17"."row_num"
, "sourcename"."sourcelist_id" AS "out.sourcelist_id"
FROM "in#17"
JOIN "sourcename" ON
"sourcename"."sourcelist_id" = "in#17"."sourcelist_pkeys.out.sourcelist_id"
AND "sourcename"."name" = "in#17"."_split(institutionCode).result"
# Setting pkeys of missing rows to @None@
INSERT INTO "sourcename_pkeys"
("row_num", "out.sourcelist_id")
SELECT
"in#17_full"."row_num"
, NULL AS "out.sourcelist_id"
FROM "in#17_full"
LEFT JOIN "sourcename_pkeys" ON "sourcename_pkeys"."row_num" = "in#17_full"."row_num"
WHERE "sourcename_pkeys"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"_join"@:
|Output|Input|
|@'1'@|@"ACAD.Specimen"."collectionCode"@|
|@'2'@|@"ACAD.Specimen"."collectionID"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#18" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."collectionCode" AS "ACAD.Specimen.collectionCode"
, "ACAD.Specimen"."collectionID" AS "ACAD.Specimen.collectionID"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#18"@
# Defining wrapper function
CREATE TEMP TABLE "_join(1=collectionCode, 2=collectionID)#1" AS
SELECT
"in#18"."row_num"
, "_join"("1" := "in#18"."ACAD.Specimen.collectionCode", "2" := "in#18"."ACAD.Specimen.collectionID") AS "result"
FROM "in#18"
LIMIT 0
CREATE FUNCTION "pg_temp"."_join(1=collectionCode, 2=collectionID)#1_wrap"()
RETURNS SETOF "_join(1=collectionCode, 2=collectionID)#1"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#18"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#18"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_join"("1" := "row"."ACAD.Specimen.collectionCode", "2" := "row"."ACAD.Specimen.collectionID")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.collectionCode" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."ACAD.Specimen.collectionID" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('collectionCode,collectionID')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_join(1=collectionCode, 2=collectionID)#1"
("row_num", "result")
SELECT * FROM "pg_temp"."_join(1=collectionCode, 2=collectionID)#1_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"specimenreplicate"@:
|Output|Input|
|@'institution_id'@|@"sourcelist_pkeys"."out.sourcelist_id"@|
|@'sourceaccessioncode'@|@"ACAD.Specimen"."id"@|
|@'plantobservation_id'@|@"plantobservation_pkeys"."out.plantobservation_id"@|
|@'catalognumber_dwc'@|@"ACAD.Specimen"."catalogNumber"@|
|@'collectioncode_dwc'@|@"_join(1=collectionCode, 2=collectionID)#1"."result"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#19" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "sourcelist_pkeys"."out.sourcelist_id" AS "sourcelist_pkeys.out.sourcelist_id"
, "ACAD.Specimen"."id" AS "ACAD.Specimen.id"
, "plantobservation_pkeys"."out.plantobservation_id" AS "plantobservation_pkeys.out.plantobservation_id"
, "ACAD.Specimen"."catalogNumber" AS "ACAD.Specimen.catalogNumber"
, "_join(1=collectionCode, 2=collectionID)#1"."result" AS "_join(1=collectionCode, 2=collectionID)#1.result"
FROM "ACAD.Specimen"
JOIN "sourcelist_pkeys" USING ("row_num")
JOIN "plantobservation_pkeys" USING ("row_num")
JOIN "_join(1=collectionCode, 2=collectionID)#1" USING ("row_num")
ORDER BY row_num
Temp table: @"in#19"@
CREATE TEMP TABLE "in#19_full" (
LIKE "in#19" INCLUDING ALL
);
INSERT INTO "in#19_full"
SELECT * FROM "in#19"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO specimenreplicate"()
RETURNS SETOF "specimenreplicate"."specimenreplicate_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "specimenreplicate"
("institution_id", "sourceaccessioncode", "plantobservation_id", "catalognumber_dwc", "collectioncode_dwc")
SELECT
"in#19"."sourcelist_pkeys.out.sourcelist_id"
, "in#19"."ACAD.Specimen.id"
, "in#19"."plantobservation_pkeys.out.plantobservation_id"
, "in#19"."ACAD.Specimen.catalogNumber"
, "in#19"."_join(1=collectionCode, 2=collectionID)#1.result"
FROM "in#19"
RETURNING "specimenreplicate_id"
$$;
CREATE TEMP TABLE "in#19_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO specimenreplicate"() AS "f" ("specimenreplicate_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO specimenreplicate#1"()
RETURNS SETOF "specimenreplicate"."specimenreplicate_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "specimenreplicate"
("institution_id", "sourceaccessioncode", "plantobservation_id", "catalognumber_dwc", "collectioncode_dwc", "source_id")
SELECT
"in#19"."sourcelist_pkeys.out.sourcelist_id"
, "in#19"."ACAD.Specimen.id"
, "in#19"."plantobservation_pkeys.out.plantobservation_id"
, "in#19"."ACAD.Specimen.catalogNumber"
, "in#19"."_join(1=collectionCode, 2=collectionID)#1.result"
, 1 AS "source_id"
FROM "in#19"
RETURNING "specimenreplicate_id"
$$;
CREATE TEMP TABLE "in#19_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO specimenreplicate#1"() AS "f" ("specimenreplicate_id")
# Getting input table pkeys of inserted rows
CREATE TEMP TABLE "in#19_insert_in_pkeys" AS
SELECT "row_num" FROM "in#19"
# Combining output and input pkeys in inserted order
CREATE TEMP TABLE "specimenreplicate_pkeys" AS
SELECT
"in#19_insert_in_pkeys"."row_num"
, "in#19_insert_out_pkeys"."specimenreplicate_id" AS "out.specimenreplicate_id"
FROM "in#19_insert_in_pkeys"
JOIN "in#19_insert_out_pkeys" USING ("_row_num")
# Setting pkeys of missing rows to @None@
INSERT INTO "specimenreplicate_pkeys"
("row_num", "out.specimenreplicate_id")
SELECT
"in#19_full"."row_num"
, NULL AS "out.specimenreplicate_id"
FROM "in#19_full"
LEFT JOIN "specimenreplicate_pkeys" ON "specimenreplicate_pkeys"."row_num" = "in#19_full"."row_num"
WHERE "specimenreplicate_pkeys"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"_dateRangeStart"@:
|Output|Input|
|@'value'@|@"ACAD.Specimen"."dateIdentified"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#20" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."dateIdentified" AS "ACAD.Specimen.dateIdentified"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#20"@
# Defining wrapper function
CREATE TEMP TABLE "_dateRangeStart(dateIdentified)" AS
SELECT
"in#20"."row_num"
, "_dateRangeStart"("value" := "in#20"."ACAD.Specimen.dateIdentified") AS "result"
FROM "in#20"
LIMIT 0
CREATE FUNCTION "pg_temp"."_dateRangeStart(dateIdentified)_wrap"()
RETURNS SETOF "_dateRangeStart(dateIdentified)"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#20"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#20"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_dateRangeStart"("value" := "row"."ACAD.Specimen.dateIdentified")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.dateIdentified" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('dateIdentified')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_dateRangeStart(dateIdentified)"
("row_num", "result")
SELECT * FROM "pg_temp"."_dateRangeStart(dateIdentified)_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"party"@:
|Output|Input|
|@'fullname'@|@"ACAD.Specimen"."identifiedBy"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#21" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."identifiedBy" AS "ACAD.Specimen.identifiedBy"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#21"@
CREATE TEMP TABLE "in#21_full" (
LIKE "in#21" INCLUDING ALL
);
INSERT INTO "in#21_full"
SELECT * FROM "in#21"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO party#3"()
RETURNS SETOF "party"."party_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "party"
("fullname")
SELECT "in#21"."ACAD.Specimen.identifiedBy" FROM "in#21"
RETURNING "party_id"
$$;
CREATE TEMP TABLE "in#21_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO party#3"() AS "f" ("party_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO party#4"()
RETURNS SETOF "party"."party_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "party"
("fullname", "source_id")
SELECT
"in#21"."ACAD.Specimen.identifiedBy"
, 1 AS "source_id"
FROM "in#21"
RETURNING "party_id"
$$;
CREATE TEMP TABLE "in#21_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO party#4"() AS "f" ("party_id")
# Caught exception: CheckException: Violated @"party"."party_required_key"@ constraint with condition @((organizationname IS NOT NULL) OR ((source_id <> party_id) AND ((fullname IS NOT NULL) OR (surname IS NOT NULL))))@
# Ignoring rows that do not satisfy @(((1 <> nextval('party_party_id_seq'::regclass)) AND ("ACAD.Specimen.identifiedBy" IS NOT NULL)))@
DELETE FROM "in#21"
WHERE NOT COALESCE((((1 <> nextval('party_party_id_seq'::regclass)) AND ("ACAD.Specimen.identifiedBy" IS NOT NULL))), false)
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO party#4"()
RETURNS SETOF "party"."party_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "party"
("fullname", "source_id")
SELECT
"in#21"."ACAD.Specimen.identifiedBy"
, 1 AS "source_id"
FROM "in#21"
RETURNING "party_id"
$$;
CREATE TEMP TABLE "in#21_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO party#4"() AS "f" ("party_id")
# Caught exception: DuplicateKeyException: Violated @party_unique_name@ constraint with condition @sourceaccessioncode IS NULL@ on columns: @source_id, organizationname, fullname, surname, givenname, middlename, suffix@
# Ignoring rows that do not satisfy @true@
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#21_distinct" (
LIKE "in#21" INCLUDING ALL
);
INSERT INTO "in#21_distinct"
SELECT
DISTINCT ON ("in#21"."ACAD.Specimen.identifiedBy")
"in#21".*
FROM "in#21"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO party#5"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "party"%ROWTYPE;
BEGIN
FOR "row"."fullname", "row"."source_id" IN
SELECT
"in#21_distinct"."ACAD.Specimen.identifiedBy"
, 1 AS "source_id"
FROM "in#21_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "party"
("fullname", "source_id")
VALUES ("row"."fullname", "row"."source_id")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
CREATE TEMP TABLE "rowcount#3" AS
SELECT * FROM "pg_temp"."INSERT INTO party#5"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "party_pkeys#1" AS
SELECT
"in#21"."row_num"
, "party"."party_id" AS "out.party_id"
FROM "in#21"
JOIN "party" ON
"party"."source_id" = 1
AND COALESCE("party"."organizationname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("party"."fullname", CAST('\N' AS text)) = COALESCE("in#21"."ACAD.Specimen.identifiedBy", CAST('\N' AS text))
AND COALESCE("party"."surname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("party"."givenname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("party"."middlename", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("party"."suffix", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
# Setting pkeys of missing rows to @None@
INSERT INTO "party_pkeys#1"
("row_num", "out.party_id")
SELECT
"in#21_full"."row_num"
, NULL AS "out.party_id"
FROM "in#21_full"
LEFT JOIN "party_pkeys#1" ON "party_pkeys#1"."row_num" = "in#21_full"."row_num"
WHERE "party_pkeys#1"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"taxonlabel"@:
|Output|Input|
|@'rank'@|@'kingdom'@|
|@'taxonepithet'@|@"ACAD.Specimen"."kingdom"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#22" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."kingdom" AS "ACAD.Specimen.kingdom"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#22"@
CREATE TEMP TABLE "in#22_full" (
LIKE "in#22" INCLUDING ALL
);
INSERT INTO "in#22_full"
SELECT * FROM "in#22"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("rank", "taxonepithet")
SELECT
'kingdom' AS "rank"
, "in#22"."ACAD.Specimen.kingdom"
FROM "in#22"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#22_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel"() AS "f" ("taxonlabel_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#1"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("rank", "taxonepithet", "source_id")
SELECT
'kingdom' AS "rank"
, "in#22"."ACAD.Specimen.kingdom"
, 1 AS "source_id"
FROM "in#22"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#22_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#1"() AS "f" ("taxonlabel_id")
# Caught exception: DuplicateKeyException: Violated @taxonlabel_unique@ constraint on columns: @parent_id, taxonepithet, rank, source_id, sourceaccessioncode, taxonomicname@
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#22_distinct" (
LIKE "in#22" INCLUDING ALL
);
INSERT INTO "in#22_distinct"
SELECT
DISTINCT ON ("in#22"."ACAD.Specimen.kingdom")
"in#22".*
FROM "in#22"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#2"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "taxonlabel"%ROWTYPE;
BEGIN
FOR "row"."rank", "row"."taxonepithet", "row"."source_id" IN
SELECT
'kingdom' AS "rank"
, "in#22_distinct"."ACAD.Specimen.kingdom"
, 1 AS "source_id"
FROM "in#22_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "taxonlabel"
("rank", "taxonepithet", "source_id")
VALUES ("row"."rank", "row"."taxonepithet", "row"."source_id")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
CREATE TEMP TABLE "rowcount#4" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#2"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "taxonlabel[rank='kingdom']" AS
SELECT
"in#22"."row_num"
, "taxonlabel"."taxonlabel_id" AS "out.taxonlabel_id"
FROM "in#22"
JOIN "taxonlabel" ON
COALESCE("taxonlabel"."parent_id", CAST(2147483647 AS integer)) = COALESCE(NULL, CAST(2147483647 AS integer))
AND COALESCE("taxonlabel"."taxonepithet", CAST('\N' AS text)) = COALESCE("in#22"."ACAD.Specimen.kingdom", CAST('\N' AS text))
AND COALESCE("taxonlabel"."rank", CAST('unknown' AS taxonrank)) = 'kingdom'
AND "taxonlabel"."source_id" = 1
AND COALESCE("taxonlabel"."sourceaccessioncode", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
# Setting pkeys of missing rows to @None@
INSERT INTO "taxonlabel[rank='kingdom']"
("row_num", "out.taxonlabel_id")
SELECT
"in#22_full"."row_num"
, NULL AS "out.taxonlabel_id"
FROM "in#22_full"
LEFT JOIN "taxonlabel[rank='kingdom']" ON "taxonlabel[rank='kingdom']"."row_num" = "in#22_full"."row_num"
WHERE "taxonlabel[rank='kingdom']"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"taxonlabel"@:
|Output|Input|
|@'parent_id'@|@"taxonlabel[rank='kingdom']"."out.taxonlabel_id"@|
|@'rank'@|@'phylum'@|
|@'taxonepithet'@|@"ACAD.Specimen"."phylum"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#23" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "taxonlabel[rank='kingdom']"."out.taxonlabel_id" AS "taxonlabel[rank='kingdom'].out.taxonlabel_id"
, "ACAD.Specimen"."phylum" AS "ACAD.Specimen.phylum"
FROM "ACAD.Specimen"
JOIN "taxonlabel[rank='kingdom']" USING ("row_num")
ORDER BY row_num
Temp table: @"in#23"@
CREATE TEMP TABLE "in#23_full" (
LIKE "in#23" INCLUDING ALL
);
INSERT INTO "in#23_full"
SELECT * FROM "in#23"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#3"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet")
SELECT
"in#23"."taxonlabel[rank='kingdom'].out.taxonlabel_id"
, 'phylum' AS "rank"
, "in#23"."ACAD.Specimen.phylum"
FROM "in#23"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#23_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#3"() AS "f" ("taxonlabel_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#4"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet", "source_id")
SELECT
"in#23"."taxonlabel[rank='kingdom'].out.taxonlabel_id"
, 'phylum' AS "rank"
, "in#23"."ACAD.Specimen.phylum"
, 1 AS "source_id"
FROM "in#23"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#23_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#4"() AS "f" ("taxonlabel_id")
# Caught exception: DuplicateKeyException: Violated @taxonlabel_unique@ constraint on columns: @parent_id, taxonepithet, rank, source_id, sourceaccessioncode, taxonomicname@
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#23_distinct" (
LIKE "in#23" INCLUDING ALL
);
INSERT INTO "in#23_distinct"
SELECT
DISTINCT ON ("in#23"."taxonlabel[rank='kingdom'].out.taxonlabel_id", "in#23"."ACAD.Specimen.phylum")
"in#23".*
FROM "in#23"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#5"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "taxonlabel"%ROWTYPE;
BEGIN
FOR "row"."parent_id", "row"."rank", "row"."taxonepithet", "row"."source_id" IN
SELECT
"in#23_distinct"."taxonlabel[rank='kingdom'].out.taxonlabel_id"
, 'phylum' AS "rank"
, "in#23_distinct"."ACAD.Specimen.phylum"
, 1 AS "source_id"
FROM "in#23_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet", "source_id")
VALUES ("row"."parent_id", "row"."rank", "row"."taxonepithet", "row"."source_id")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
CREATE TEMP TABLE "rowcount#5" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#5"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "taxonlabel[rank='phylum']" AS
SELECT
"in#23"."row_num"
, "taxonlabel"."taxonlabel_id" AS "out.taxonlabel_id"
FROM "in#23"
JOIN "taxonlabel" ON
COALESCE("taxonlabel"."parent_id", CAST(2147483647 AS integer)) = COALESCE("in#23"."taxonlabel[rank='kingdom'].out.taxonlabel_id", CAST(2147483647 AS integer))
AND COALESCE("taxonlabel"."taxonepithet", CAST('\N' AS text)) = COALESCE("in#23"."ACAD.Specimen.phylum", CAST('\N' AS text))
AND COALESCE("taxonlabel"."rank", CAST('unknown' AS taxonrank)) = 'phylum'
AND "taxonlabel"."source_id" = 1
AND COALESCE("taxonlabel"."sourceaccessioncode", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
# Setting pkeys of missing rows to @"in#23"."taxonlabel[rank='kingdom'].out.taxonlabel_id"@
INSERT INTO "taxonlabel[rank='phylum']"
("row_num", "out.taxonlabel_id")
SELECT
"in#23_full"."row_num"
, "in#23_full"."taxonlabel[rank='kingdom'].out.taxonlabel_id" AS "out.taxonlabel_id"
FROM "in#23_full"
LEFT JOIN "taxonlabel[rank='phylum']" ON "taxonlabel[rank='phylum']"."row_num" = "in#23_full"."row_num"
WHERE "taxonlabel[rank='phylum']"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"taxonlabel"@:
|Output|Input|
|@'parent_id'@|@"taxonlabel[rank='phylum']"."out.taxonlabel_id"@|
|@'rank'@|@'class'@|
|@'taxonepithet'@|@"ACAD.Specimen"."class"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#24" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "taxonlabel[rank='phylum']"."out.taxonlabel_id" AS "taxonlabel[rank='phylum'].out.taxonlabel_id"
, "ACAD.Specimen"."class" AS "ACAD.Specimen.class"
FROM "ACAD.Specimen"
JOIN "taxonlabel[rank='phylum']" USING ("row_num")
ORDER BY row_num
Temp table: @"in#24"@
CREATE TEMP TABLE "in#24_full" (
LIKE "in#24" INCLUDING ALL
);
INSERT INTO "in#24_full"
SELECT * FROM "in#24"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#6"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet")
SELECT
"in#24"."taxonlabel[rank='phylum'].out.taxonlabel_id"
, 'class' AS "rank"
, "in#24"."ACAD.Specimen.class"
FROM "in#24"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#24_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#6"() AS "f" ("taxonlabel_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#7"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet", "source_id")
SELECT
"in#24"."taxonlabel[rank='phylum'].out.taxonlabel_id"
, 'class' AS "rank"
, "in#24"."ACAD.Specimen.class"
, 1 AS "source_id"
FROM "in#24"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#24_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#7"() AS "f" ("taxonlabel_id")
# Caught exception: DuplicateKeyException: Violated @taxonlabel_unique@ constraint on columns: @parent_id, taxonepithet, rank, source_id, sourceaccessioncode, taxonomicname@
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#24_distinct" (
LIKE "in#24" INCLUDING ALL
);
INSERT INTO "in#24_distinct"
SELECT
DISTINCT ON ("in#24"."taxonlabel[rank='phylum'].out.taxonlabel_id", "in#24"."ACAD.Specimen.class")
"in#24".*
FROM "in#24"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#8"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "taxonlabel"%ROWTYPE;
BEGIN
FOR "row"."parent_id", "row"."rank", "row"."taxonepithet", "row"."source_id" IN
SELECT
"in#24_distinct"."taxonlabel[rank='phylum'].out.taxonlabel_id"
, 'class' AS "rank"
, "in#24_distinct"."ACAD.Specimen.class"
, 1 AS "source_id"
FROM "in#24_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet", "source_id")
VALUES ("row"."parent_id", "row"."rank", "row"."taxonepithet", "row"."source_id")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
CREATE TEMP TABLE "rowcount#6" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#8"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "taxonlabel[rank='class']" AS
SELECT
"in#24"."row_num"
, "taxonlabel"."taxonlabel_id" AS "out.taxonlabel_id"
FROM "in#24"
JOIN "taxonlabel" ON
COALESCE("taxonlabel"."parent_id", CAST(2147483647 AS integer)) = COALESCE("in#24"."taxonlabel[rank='phylum'].out.taxonlabel_id", CAST(2147483647 AS integer))
AND COALESCE("taxonlabel"."taxonepithet", CAST('\N' AS text)) = COALESCE("in#24"."ACAD.Specimen.class", CAST('\N' AS text))
AND COALESCE("taxonlabel"."rank", CAST('unknown' AS taxonrank)) = 'class'
AND "taxonlabel"."source_id" = 1
AND COALESCE("taxonlabel"."sourceaccessioncode", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
# Setting pkeys of missing rows to @"in#24"."taxonlabel[rank='phylum'].out.taxonlabel_id"@
INSERT INTO "taxonlabel[rank='class']"
("row_num", "out.taxonlabel_id")
SELECT
"in#24_full"."row_num"
, "in#24_full"."taxonlabel[rank='phylum'].out.taxonlabel_id" AS "out.taxonlabel_id"
FROM "in#24_full"
LEFT JOIN "taxonlabel[rank='class']" ON "taxonlabel[rank='class']"."row_num" = "in#24_full"."row_num"
WHERE "taxonlabel[rank='class']"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"taxonlabel"@:
|Output|Input|
|@'parent_id'@|@"taxonlabel[rank='class']"."out.taxonlabel_id"@|
|@'rank'@|@'order'@|
|@'taxonepithet'@|@"ACAD.Specimen"."order"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#25" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "taxonlabel[rank='class']"."out.taxonlabel_id" AS "taxonlabel[rank='class'].out.taxonlabel_id"
, "ACAD.Specimen"."order" AS "ACAD.Specimen.order"
FROM "ACAD.Specimen"
JOIN "taxonlabel[rank='class']" USING ("row_num")
ORDER BY row_num
Temp table: @"in#25"@
CREATE TEMP TABLE "in#25_full" (
LIKE "in#25" INCLUDING ALL
);
INSERT INTO "in#25_full"
SELECT * FROM "in#25"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#9"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet")
SELECT
"in#25"."taxonlabel[rank='class'].out.taxonlabel_id"
, 'order' AS "rank"
, "in#25"."ACAD.Specimen.order"
FROM "in#25"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#25_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#9"() AS "f" ("taxonlabel_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#10"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet", "source_id")
SELECT
"in#25"."taxonlabel[rank='class'].out.taxonlabel_id"
, 'order' AS "rank"
, "in#25"."ACAD.Specimen.order"
, 1 AS "source_id"
FROM "in#25"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#25_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#10"() AS "f" ("taxonlabel_id")
# Caught exception: DuplicateKeyException: Violated @taxonlabel_unique@ constraint on columns: @parent_id, taxonepithet, rank, source_id, sourceaccessioncode, taxonomicname@
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#25_distinct" (
LIKE "in#25" INCLUDING ALL
);
INSERT INTO "in#25_distinct"
SELECT
DISTINCT ON ("in#25"."taxonlabel[rank='class'].out.taxonlabel_id", "in#25"."ACAD.Specimen.order")
"in#25".*
FROM "in#25"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#11"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "taxonlabel"%ROWTYPE;
BEGIN
FOR "row"."parent_id", "row"."rank", "row"."taxonepithet", "row"."source_id" IN
SELECT
"in#25_distinct"."taxonlabel[rank='class'].out.taxonlabel_id"
, 'order' AS "rank"
, "in#25_distinct"."ACAD.Specimen.order"
, 1 AS "source_id"
FROM "in#25_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet", "source_id")
VALUES ("row"."parent_id", "row"."rank", "row"."taxonepithet", "row"."source_id")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
CREATE TEMP TABLE "rowcount#7" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#11"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "taxonlabel[rank='order']" AS
SELECT
"in#25"."row_num"
, "taxonlabel"."taxonlabel_id" AS "out.taxonlabel_id"
FROM "in#25"
JOIN "taxonlabel" ON
COALESCE("taxonlabel"."parent_id", CAST(2147483647 AS integer)) = COALESCE("in#25"."taxonlabel[rank='class'].out.taxonlabel_id", CAST(2147483647 AS integer))
AND COALESCE("taxonlabel"."taxonepithet", CAST('\N' AS text)) = COALESCE("in#25"."ACAD.Specimen.order", CAST('\N' AS text))
AND COALESCE("taxonlabel"."rank", CAST('unknown' AS taxonrank)) = 'order'
AND "taxonlabel"."source_id" = 1
AND COALESCE("taxonlabel"."sourceaccessioncode", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
# Setting pkeys of missing rows to @"in#25"."taxonlabel[rank='class'].out.taxonlabel_id"@
INSERT INTO "taxonlabel[rank='order']"
("row_num", "out.taxonlabel_id")
SELECT
"in#25_full"."row_num"
, "in#25_full"."taxonlabel[rank='class'].out.taxonlabel_id" AS "out.taxonlabel_id"
FROM "in#25_full"
LEFT JOIN "taxonlabel[rank='order']" ON "taxonlabel[rank='order']"."row_num" = "in#25_full"."row_num"
WHERE "taxonlabel[rank='order']"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"taxonlabel"@:
|Output|Input|
|@'parent_id'@|@"taxonlabel[rank='order']"."out.taxonlabel_id"@|
|@'rank'@|@'family'@|
|@'taxonepithet'@|@"ACAD.Specimen"."family"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#26" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "taxonlabel[rank='order']"."out.taxonlabel_id" AS "taxonlabel[rank='order'].out.taxonlabel_id"
, "ACAD.Specimen"."family" AS "ACAD.Specimen.family"
FROM "ACAD.Specimen"
JOIN "taxonlabel[rank='order']" USING ("row_num")
ORDER BY row_num
Temp table: @"in#26"@
CREATE TEMP TABLE "in#26_full" (
LIKE "in#26" INCLUDING ALL
);
INSERT INTO "in#26_full"
SELECT * FROM "in#26"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#12"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet")
SELECT
"in#26"."taxonlabel[rank='order'].out.taxonlabel_id"
, 'family' AS "rank"
, "in#26"."ACAD.Specimen.family"
FROM "in#26"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#26_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#12"() AS "f" ("taxonlabel_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#13"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet", "source_id")
SELECT
"in#26"."taxonlabel[rank='order'].out.taxonlabel_id"
, 'family' AS "rank"
, "in#26"."ACAD.Specimen.family"
, 1 AS "source_id"
FROM "in#26"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#26_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#13"() AS "f" ("taxonlabel_id")
# Caught exception: DuplicateKeyException: Violated @taxonlabel_unique@ constraint on columns: @parent_id, taxonepithet, rank, source_id, sourceaccessioncode, taxonomicname@
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#26_distinct" (
LIKE "in#26" INCLUDING ALL
);
INSERT INTO "in#26_distinct"
SELECT
DISTINCT ON ("in#26"."taxonlabel[rank='order'].out.taxonlabel_id", "in#26"."ACAD.Specimen.family")
"in#26".*
FROM "in#26"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#14"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "taxonlabel"%ROWTYPE;
BEGIN
FOR "row"."parent_id", "row"."rank", "row"."taxonepithet", "row"."source_id" IN
SELECT
"in#26_distinct"."taxonlabel[rank='order'].out.taxonlabel_id"
, 'family' AS "rank"
, "in#26_distinct"."ACAD.Specimen.family"
, 1 AS "source_id"
FROM "in#26_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet", "source_id")
VALUES ("row"."parent_id", "row"."rank", "row"."taxonepithet", "row"."source_id")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
CREATE TEMP TABLE "rowcount#8" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#14"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "taxonlabel[rank='family']" AS
SELECT
"in#26"."row_num"
, "taxonlabel"."taxonlabel_id" AS "out.taxonlabel_id"
FROM "in#26"
JOIN "taxonlabel" ON
COALESCE("taxonlabel"."parent_id", CAST(2147483647 AS integer)) = COALESCE("in#26"."taxonlabel[rank='order'].out.taxonlabel_id", CAST(2147483647 AS integer))
AND COALESCE("taxonlabel"."taxonepithet", CAST('\N' AS text)) = COALESCE("in#26"."ACAD.Specimen.family", CAST('\N' AS text))
AND COALESCE("taxonlabel"."rank", CAST('unknown' AS taxonrank)) = 'family'
AND "taxonlabel"."source_id" = 1
AND COALESCE("taxonlabel"."sourceaccessioncode", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
# Setting pkeys of missing rows to @"in#26"."taxonlabel[rank='order'].out.taxonlabel_id"@
INSERT INTO "taxonlabel[rank='family']"
("row_num", "out.taxonlabel_id")
SELECT
"in#26_full"."row_num"
, "in#26_full"."taxonlabel[rank='order'].out.taxonlabel_id" AS "out.taxonlabel_id"
FROM "in#26_full"
LEFT JOIN "taxonlabel[rank='family']" ON "taxonlabel[rank='family']"."row_num" = "in#26_full"."row_num"
WHERE "taxonlabel[rank='family']"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"taxonlabel"@:
|Output|Input|
|@'parent_id'@|@"taxonlabel[rank='family']"."out.taxonlabel_id"@|
|@'rank'@|@'genus'@|
|@'taxonepithet'@|@"ACAD.Specimen"."genus"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#27" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "taxonlabel[rank='family']"."out.taxonlabel_id" AS "taxonlabel[rank='family'].out.taxonlabel_id"
, "ACAD.Specimen"."genus" AS "ACAD.Specimen.genus"
FROM "ACAD.Specimen"
JOIN "taxonlabel[rank='family']" USING ("row_num")
ORDER BY row_num
Temp table: @"in#27"@
CREATE TEMP TABLE "in#27_full" (
LIKE "in#27" INCLUDING ALL
);
INSERT INTO "in#27_full"
SELECT * FROM "in#27"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#15"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet")
SELECT
"in#27"."taxonlabel[rank='family'].out.taxonlabel_id"
, 'genus' AS "rank"
, "in#27"."ACAD.Specimen.genus"
FROM "in#27"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#27_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#15"() AS "f" ("taxonlabel_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#16"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet", "source_id")
SELECT
"in#27"."taxonlabel[rank='family'].out.taxonlabel_id"
, 'genus' AS "rank"
, "in#27"."ACAD.Specimen.genus"
, 1 AS "source_id"
FROM "in#27"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#27_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#16"() AS "f" ("taxonlabel_id")
# Caught exception: DuplicateKeyException: Violated @taxonlabel_unique@ constraint on columns: @parent_id, taxonepithet, rank, source_id, sourceaccessioncode, taxonomicname@
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#27_distinct" (
LIKE "in#27" INCLUDING ALL
);
INSERT INTO "in#27_distinct"
SELECT
DISTINCT ON ("in#27"."taxonlabel[rank='family'].out.taxonlabel_id", "in#27"."ACAD.Specimen.genus")
"in#27".*
FROM "in#27"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#17"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "taxonlabel"%ROWTYPE;
BEGIN
FOR "row"."parent_id", "row"."rank", "row"."taxonepithet", "row"."source_id" IN
SELECT
"in#27_distinct"."taxonlabel[rank='family'].out.taxonlabel_id"
, 'genus' AS "rank"
, "in#27_distinct"."ACAD.Specimen.genus"
, 1 AS "source_id"
FROM "in#27_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet", "source_id")
VALUES ("row"."parent_id", "row"."rank", "row"."taxonepithet", "row"."source_id")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
CREATE TEMP TABLE "rowcount#9" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#17"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "taxonlabel[rank='genus']" AS
SELECT
"in#27"."row_num"
, "taxonlabel"."taxonlabel_id" AS "out.taxonlabel_id"
FROM "in#27"
JOIN "taxonlabel" ON
COALESCE("taxonlabel"."parent_id", CAST(2147483647 AS integer)) = COALESCE("in#27"."taxonlabel[rank='family'].out.taxonlabel_id", CAST(2147483647 AS integer))
AND COALESCE("taxonlabel"."taxonepithet", CAST('\N' AS text)) = COALESCE("in#27"."ACAD.Specimen.genus", CAST('\N' AS text))
AND COALESCE("taxonlabel"."rank", CAST('unknown' AS taxonrank)) = 'genus'
AND "taxonlabel"."source_id" = 1
AND COALESCE("taxonlabel"."sourceaccessioncode", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
# Setting pkeys of missing rows to @"in#27"."taxonlabel[rank='family'].out.taxonlabel_id"@
INSERT INTO "taxonlabel[rank='genus']"
("row_num", "out.taxonlabel_id")
SELECT
"in#27_full"."row_num"
, "in#27_full"."taxonlabel[rank='family'].out.taxonlabel_id" AS "out.taxonlabel_id"
FROM "in#27_full"
LEFT JOIN "taxonlabel[rank='genus']" ON "taxonlabel[rank='genus']"."row_num" = "in#27_full"."row_num"
WHERE "taxonlabel[rank='genus']"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"taxonlabel"@:
|Output|Input|
|@'parent_id'@|@"taxonlabel[rank='genus']"."out.taxonlabel_id"@|
|@'rank'@|@'species'@|
|@'taxonepithet'@|@"ACAD.Specimen"."specificEpithet"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#28" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "taxonlabel[rank='genus']"."out.taxonlabel_id" AS "taxonlabel[rank='genus'].out.taxonlabel_id"
, "ACAD.Specimen"."specificEpithet" AS "ACAD.Specimen.specificEpithet"
FROM "ACAD.Specimen"
JOIN "taxonlabel[rank='genus']" USING ("row_num")
ORDER BY row_num
Temp table: @"in#28"@
CREATE TEMP TABLE "in#28_full" (
LIKE "in#28" INCLUDING ALL
);
INSERT INTO "in#28_full"
SELECT * FROM "in#28"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#18"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet")
SELECT
"in#28"."taxonlabel[rank='genus'].out.taxonlabel_id"
, 'species' AS "rank"
, "in#28"."ACAD.Specimen.specificEpithet"
FROM "in#28"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#28_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#18"() AS "f" ("taxonlabel_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#19"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet", "source_id")
SELECT
"in#28"."taxonlabel[rank='genus'].out.taxonlabel_id"
, 'species' AS "rank"
, "in#28"."ACAD.Specimen.specificEpithet"
, 1 AS "source_id"
FROM "in#28"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#28_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#19"() AS "f" ("taxonlabel_id")
# Caught exception: DuplicateKeyException: Violated @taxonlabel_unique@ constraint on columns: @parent_id, taxonepithet, rank, source_id, sourceaccessioncode, taxonomicname@
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#28_distinct" (
LIKE "in#28" INCLUDING ALL
);
INSERT INTO "in#28_distinct"
SELECT
DISTINCT ON ("in#28"."taxonlabel[rank='genus'].out.taxonlabel_id", "in#28"."ACAD.Specimen.specificEpithet")
"in#28".*
FROM "in#28"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#20"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "taxonlabel"%ROWTYPE;
BEGIN
FOR "row"."parent_id", "row"."rank", "row"."taxonepithet", "row"."source_id" IN
SELECT
"in#28_distinct"."taxonlabel[rank='genus'].out.taxonlabel_id"
, 'species' AS "rank"
, "in#28_distinct"."ACAD.Specimen.specificEpithet"
, 1 AS "source_id"
FROM "in#28_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet", "source_id")
VALUES ("row"."parent_id", "row"."rank", "row"."taxonepithet", "row"."source_id")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
CREATE TEMP TABLE "rowcount#10" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#20"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "taxonlabel[rank='species']" AS
SELECT
"in#28"."row_num"
, "taxonlabel"."taxonlabel_id" AS "out.taxonlabel_id"
FROM "in#28"
JOIN "taxonlabel" ON
COALESCE("taxonlabel"."parent_id", CAST(2147483647 AS integer)) = COALESCE("in#28"."taxonlabel[rank='genus'].out.taxonlabel_id", CAST(2147483647 AS integer))
AND COALESCE("taxonlabel"."taxonepithet", CAST('\N' AS text)) = COALESCE("in#28"."ACAD.Specimen.specificEpithet", CAST('\N' AS text))
AND COALESCE("taxonlabel"."rank", CAST('unknown' AS taxonrank)) = 'species'
AND "taxonlabel"."source_id" = 1
AND COALESCE("taxonlabel"."sourceaccessioncode", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
# Setting pkeys of missing rows to @"in#28"."taxonlabel[rank='genus'].out.taxonlabel_id"@
INSERT INTO "taxonlabel[rank='species']"
("row_num", "out.taxonlabel_id")
SELECT
"in#28_full"."row_num"
, "in#28_full"."taxonlabel[rank='genus'].out.taxonlabel_id" AS "out.taxonlabel_id"
FROM "in#28_full"
LEFT JOIN "taxonlabel[rank='species']" ON "taxonlabel[rank='species']"."row_num" = "in#28_full"."row_num"
WHERE "taxonlabel[rank='species']"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"taxonlabel"@:
|Output|Input|
|@'parent_id'@|@"taxonlabel[rank='species']"."out.taxonlabel_id"@|
|@'rank'@|@'subspecies'@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#29" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "taxonlabel[rank='species']"."out.taxonlabel_id" AS "taxonlabel[rank='species'].out.taxonlabel_id"
FROM "ACAD.Specimen"
JOIN "taxonlabel[rank='species']" USING ("row_num")
ORDER BY row_num
Temp table: @"in#29"@
CREATE TEMP TABLE "in#29_full" (
LIKE "in#29" INCLUDING ALL
);
INSERT INTO "in#29_full"
SELECT * FROM "in#29"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#21"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank")
SELECT
"in#29"."taxonlabel[rank='species'].out.taxonlabel_id"
, 'subspecies' AS "rank"
FROM "in#29"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#29_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#21"() AS "f" ("taxonlabel_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#22"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "source_id")
SELECT
"in#29"."taxonlabel[rank='species'].out.taxonlabel_id"
, 'subspecies' AS "rank"
, 1 AS "source_id"
FROM "in#29"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#29_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#22"() AS "f" ("taxonlabel_id")
# Caught exception: CheckException: Violated @"taxonlabel"."taxonlabel_required_key"@ constraint with condition @(((sourceaccessioncode IS NOT NULL) OR (taxonepithet IS NOT NULL)) OR (taxonomicname IS NOT NULL))@
# Ignoring rows that do not satisfy @false@
# Ignoring all rows
# Returning default: @"taxonlabel[rank='species']"."out.taxonlabel_id"@
# ********** New iteration **********
# Inserting these input columns into @"taxonlabel"@:
|Output|Input|
|@'parent_id'@|@"taxonlabel[rank='species']"."out.taxonlabel_id"@|
|@'rank'@|@'variety'@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#30" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "taxonlabel[rank='species']"."out.taxonlabel_id" AS "taxonlabel[rank='species'].out.taxonlabel_id"
FROM "ACAD.Specimen"
JOIN "taxonlabel[rank='species']" USING ("row_num")
ORDER BY row_num
Temp table: @"in#30"@
CREATE TEMP TABLE "in#30_full" (
LIKE "in#30" INCLUDING ALL
);
INSERT INTO "in#30_full"
SELECT * FROM "in#30"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#23"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank")
SELECT
"in#30"."taxonlabel[rank='species'].out.taxonlabel_id"
, 'variety' AS "rank"
FROM "in#30"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#30_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#23"() AS "f" ("taxonlabel_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#24"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "source_id")
SELECT
"in#30"."taxonlabel[rank='species'].out.taxonlabel_id"
, 'variety' AS "rank"
, 1 AS "source_id"
FROM "in#30"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#30_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#24"() AS "f" ("taxonlabel_id")
# Caught exception: CheckException: Violated @"taxonlabel"."taxonlabel_required_key"@ constraint with condition @(((sourceaccessioncode IS NOT NULL) OR (taxonepithet IS NOT NULL)) OR (taxonomicname IS NOT NULL))@
# Ignoring rows that do not satisfy @false@
# Ignoring all rows
# Returning default: @"taxonlabel[rank='species']"."out.taxonlabel_id"@
# ********** New iteration **********
# Inserting these input columns into @"taxonlabel"@:
|Output|Input|
|@'parent_id'@|@"taxonlabel[rank='species']"."out.taxonlabel_id"@|
|@'rank'@|@'forma'@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#31" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "taxonlabel[rank='species']"."out.taxonlabel_id" AS "taxonlabel[rank='species'].out.taxonlabel_id"
FROM "ACAD.Specimen"
JOIN "taxonlabel[rank='species']" USING ("row_num")
ORDER BY row_num
Temp table: @"in#31"@
CREATE TEMP TABLE "in#31_full" (
LIKE "in#31" INCLUDING ALL
);
INSERT INTO "in#31_full"
SELECT * FROM "in#31"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#25"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank")
SELECT
"in#31"."taxonlabel[rank='species'].out.taxonlabel_id"
, 'forma' AS "rank"
FROM "in#31"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#31_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#25"() AS "f" ("taxonlabel_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#26"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "source_id")
SELECT
"in#31"."taxonlabel[rank='species'].out.taxonlabel_id"
, 'forma' AS "rank"
, 1 AS "source_id"
FROM "in#31"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#31_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#26"() AS "f" ("taxonlabel_id")
# Caught exception: CheckException: Violated @"taxonlabel"."taxonlabel_required_key"@ constraint with condition @(((sourceaccessioncode IS NOT NULL) OR (taxonepithet IS NOT NULL)) OR (taxonomicname IS NOT NULL))@
# Ignoring rows that do not satisfy @false@
# Ignoring all rows
# Returning default: @"taxonlabel[rank='species']"."out.taxonlabel_id"@
# ********** New iteration **********
# Inserting these input columns into @"taxonlabel"@:
|Output|Input|
|@'parent_id'@|@"taxonlabel[rank='species']"."out.taxonlabel_id"@|
|@'rank'@|@'cultivar'@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#32" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "taxonlabel[rank='species']"."out.taxonlabel_id" AS "taxonlabel[rank='species'].out.taxonlabel_id"
FROM "ACAD.Specimen"
JOIN "taxonlabel[rank='species']" USING ("row_num")
ORDER BY row_num
Temp table: @"in#32"@
CREATE TEMP TABLE "in#32_full" (
LIKE "in#32" INCLUDING ALL
);
INSERT INTO "in#32_full"
SELECT * FROM "in#32"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#27"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank")
SELECT
"in#32"."taxonlabel[rank='species'].out.taxonlabel_id"
, 'cultivar' AS "rank"
FROM "in#32"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#32_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#27"() AS "f" ("taxonlabel_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#28"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "source_id")
SELECT
"in#32"."taxonlabel[rank='species'].out.taxonlabel_id"
, 'cultivar' AS "rank"
, 1 AS "source_id"
FROM "in#32"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#32_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#28"() AS "f" ("taxonlabel_id")
# Caught exception: CheckException: Violated @"taxonlabel"."taxonlabel_required_key"@ constraint with condition @(((sourceaccessioncode IS NOT NULL) OR (taxonepithet IS NOT NULL)) OR (taxonomicname IS NOT NULL))@
# Ignoring rows that do not satisfy @false@
# Ignoring all rows
# Returning default: @"taxonlabel[rank='species']"."out.taxonlabel_id"@
# ********** New iteration **********
# Inserting these input columns into @"taxonlabel"@:
|Output|Input|
|@'parent_id'@|@"taxonlabel[rank='species']"."out.taxonlabel_id"@|
|@'rank'@|@"ACAD.Specimen"."taxonRank"@|
|@'taxonepithet'@|@"ACAD.Specimen"."infraspecificEpithet"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#33" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "taxonlabel[rank='species']"."out.taxonlabel_id" AS "taxonlabel[rank='species'].out.taxonlabel_id"
, "ACAD.Specimen"."taxonRank" AS "ACAD.Specimen.taxonRank"
, "ACAD.Specimen"."infraspecificEpithet" AS "ACAD.Specimen.infraspecificEpithet"
FROM "ACAD.Specimen"
JOIN "taxonlabel[rank='species']" USING ("row_num")
ORDER BY row_num
Temp table: @"in#33"@
CREATE TEMP TABLE "in#33_full" (
LIKE "in#33" INCLUDING ALL
);
INSERT INTO "in#33_full"
SELECT * FROM "in#33"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#29"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet")
SELECT
"in#33"."taxonlabel[rank='species'].out.taxonlabel_id"
, "in#33"."ACAD.Specimen.taxonRank"
, "in#33"."ACAD.Specimen.infraspecificEpithet"
FROM "in#33"
RETURNING "taxonlabel_id"
$$;
# Caught exception: MissingCastException: Missing cast to type @taxonrank@ on column: @rank@
# Casting @'rank'@ input to @taxonrank@
CREATE FUNCTION "pg_temp"."taxonrank(taxonRank)"("value" anyelement)
RETURNS taxonrank
LANGUAGE plpgsql
STRICT
AS $$
BEGIN
/* The explicit cast to the return type is needed to make the cast happen
inside the try block. (Implicit casts to the return type happen at the end
of the function, outside any block.) */
RETURN CAST(value AS taxonrank);
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := value;
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('taxonRank')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN NULL;
END;
$$;
ALTER TABLE "in#33" ADD COLUMN "ACAD.Specimen.taxonRank::taxonrank" taxonrank /*"in#33"."ACAD.Specimen.taxonRank"::taxonrank*/
ALTER TABLE "in#33"
ALTER COLUMN "ACAD.Specimen.taxonRank::taxonrank" TYPE taxonrank
USING "pg_temp"."taxonrank(taxonRank)"("in#33"."ACAD.Specimen.taxonRank")
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#29"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet")
SELECT
"in#33"."taxonlabel[rank='species'].out.taxonlabel_id"
, "in#33"."ACAD.Specimen.taxonRank::taxonrank"
, "in#33"."ACAD.Specimen.infraspecificEpithet"
FROM "in#33"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#33_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#29"() AS "f" ("taxonlabel_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#30"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet", "source_id")
SELECT
"in#33"."taxonlabel[rank='species'].out.taxonlabel_id"
, "in#33"."ACAD.Specimen.taxonRank::taxonrank"
, "in#33"."ACAD.Specimen.infraspecificEpithet"
, 1 AS "source_id"
FROM "in#33"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#33_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#30"() AS "f" ("taxonlabel_id")
# Caught exception: CheckException: Violated @"taxonlabel"."taxonlabel_required_key"@ constraint with condition @(((sourceaccessioncode IS NOT NULL) OR (taxonepithet IS NOT NULL)) OR (taxonomicname IS NOT NULL))@
# Ignoring rows that do not satisfy @("ACAD.Specimen.infraspecificEpithet" IS NOT NULL)@
DELETE FROM "in#33"
WHERE NOT COALESCE(("ACAD.Specimen.infraspecificEpithet" IS NOT NULL), false)
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#30"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet", "source_id")
SELECT
"in#33"."taxonlabel[rank='species'].out.taxonlabel_id"
, "in#33"."ACAD.Specimen.taxonRank::taxonrank"
, "in#33"."ACAD.Specimen.infraspecificEpithet"
, 1 AS "source_id"
FROM "in#33"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#33_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#30"() AS "f" ("taxonlabel_id")
# Caught exception: DuplicateKeyException: Violated @taxonlabel_unique@ constraint on columns: @parent_id, taxonepithet, rank, source_id, sourceaccessioncode, taxonomicname@
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#33_distinct" (
LIKE "in#33" INCLUDING ALL
);
INSERT INTO "in#33_distinct"
SELECT
DISTINCT ON ("in#33"."taxonlabel[rank='species'].out.taxonlabel_id", "in#33"."ACAD.Specimen.infraspecificEpithet", "in#33"."ACAD.Specimen.taxonRank::taxonrank")
"in#33".*
FROM "in#33"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#31"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "taxonlabel"%ROWTYPE;
BEGIN
FOR "row"."parent_id", "row"."rank", "row"."taxonepithet", "row"."source_id" IN
SELECT
"in#33_distinct"."taxonlabel[rank='species'].out.taxonlabel_id"
, "in#33_distinct"."ACAD.Specimen.taxonRank::taxonrank"
, "in#33_distinct"."ACAD.Specimen.infraspecificEpithet"
, 1 AS "source_id"
FROM "in#33_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "taxonlabel"
("parent_id", "rank", "taxonepithet", "source_id")
VALUES ("row"."parent_id", "row"."rank", "row"."taxonepithet", "row"."source_id")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
CREATE TEMP TABLE "rowcount#11" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#31"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "taxonlabel[rank=taxonRank]" AS
SELECT
"in#33"."row_num"
, "taxonlabel"."taxonlabel_id" AS "out.taxonlabel_id"
FROM "in#33"
JOIN "taxonlabel" ON
COALESCE("taxonlabel"."parent_id", CAST(2147483647 AS integer)) = COALESCE("in#33"."taxonlabel[rank='species'].out.taxonlabel_id", CAST(2147483647 AS integer))
AND COALESCE("taxonlabel"."taxonepithet", CAST('\N' AS text)) = COALESCE("in#33"."ACAD.Specimen.infraspecificEpithet", CAST('\N' AS text))
AND COALESCE("taxonlabel"."rank", CAST('unknown' AS taxonrank)) = COALESCE("in#33"."ACAD.Specimen.taxonRank::taxonrank", CAST('unknown' AS taxonrank))
AND "taxonlabel"."source_id" = 1
AND COALESCE("taxonlabel"."sourceaccessioncode", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
# Setting pkeys of missing rows to @"in#33"."taxonlabel[rank='species'].out.taxonlabel_id"@
INSERT INTO "taxonlabel[rank=taxonRank]"
("row_num", "out.taxonlabel_id")
SELECT
"in#33_full"."row_num"
, "in#33_full"."taxonlabel[rank='species'].out.taxonlabel_id" AS "out.taxonlabel_id"
FROM "in#33_full"
LEFT JOIN "taxonlabel[rank=taxonRank]" ON "taxonlabel[rank=taxonRank]"."row_num" = "in#33_full"."row_num"
WHERE "taxonlabel[rank=taxonRank]"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"_dateRangeStart"@:
|Output|Input|
|@'value'@|@"ACAD.Specimen"."dateIdentified"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#34" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."dateIdentified" AS "ACAD.Specimen.dateIdentified"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#34"@
# Defining wrapper function
CREATE TEMP TABLE "_dateRangeStart(dateIdentified)#1" AS
SELECT
"in#34"."row_num"
, "_dateRangeStart"("value" := "in#34"."ACAD.Specimen.dateIdentified") AS "result"
FROM "in#34"
LIMIT 0
CREATE FUNCTION "pg_temp"."_dateRangeStart(dateIdentified)#1_wrap"()
RETURNS SETOF "_dateRangeStart(dateIdentified)#1"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#34"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#34"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_dateRangeStart"("value" := "row"."ACAD.Specimen.dateIdentified")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.dateIdentified" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('dateIdentified')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_dateRangeStart(dateIdentified)#1"
("row_num", "result")
SELECT * FROM "pg_temp"."_dateRangeStart(dateIdentified)#1_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"_taxon_family_require_std"@:
|Output|Input|
|@'family'@|@"ACAD.Specimen"."family"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#35" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."family" AS "ACAD.Specimen.family"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#35"@
# Defining wrapper function
CREATE TEMP TABLE "_taxon_family_require_std(family=family)" AS
SELECT
"in#35"."row_num"
, "_taxon_family_require_std"("family" := "in#35"."ACAD.Specimen.family") AS "result"
FROM "in#35"
LIMIT 0
CREATE FUNCTION "pg_temp"."_taxon_family_require_std(family=family)_wrap"()
RETURNS SETOF "_taxon_family_require_std(family=family)"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#35"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#35"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_taxon_family_require_std"("family" := "row"."ACAD.Specimen.family")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.family" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('family')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_taxon_family_require_std(family=family)"
("row_num", "result")
SELECT * FROM "pg_temp"."_taxon_family_require_std(family=family)_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"_if"@:
|Output|Input|
|@'then'@|@"ACAD.Specimen"."taxonRank"@|
|@'cond'@|@"ACAD.Specimen"."infraspecificEpithet"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#36" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."taxonRank" AS "ACAD.Specimen.taxonRank"
, "ACAD.Specimen"."infraspecificEpithet" AS "ACAD.Specimen.infraspecificEpithet"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#36"@
# Defining wrapper function
CREATE TEMP TABLE "_if(then=taxonRank, cond=infraspecificEpithet)" AS
SELECT
"in#36"."row_num"
, "_if"("then" := "in#36"."ACAD.Specimen.taxonRank", "cond" := "in#36"."ACAD.Specimen.infraspecificEpithet") AS "result"
FROM "in#36"
LIMIT 0
CREATE FUNCTION "pg_temp"."_if(then=taxonRank, cond=infraspecificEpithet)_wrap"()
RETURNS SETOF "_if(then=taxonRank, cond=infraspecificEpithet)"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#36"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#36"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_if"("then" := "row"."ACAD.Specimen.taxonRank", "cond" := "row"."ACAD.Specimen.infraspecificEpithet")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.taxonRank" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."ACAD.Specimen.infraspecificEpithet" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('taxonRank,infraspecificEpithet')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_if(then=taxonRank, cond=infraspecificEpithet)"
("row_num", "result")
SELECT * FROM "pg_temp"."_if(then=taxonRank, cond=infraspecificEpithet)_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"_join_words"@:
|Output|Input|
|@'1'@|@"_if(then=taxonRank, cond=infraspecificEpithet)"."result"@|
|@'2'@|@"ACAD.Specimen"."infraspecificEpithet"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#37" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "_if(then=taxonRank, cond=infraspecificEpithet)"."result" AS "_if(then=taxonRank, cond=infraspecificEpithet).result"
, "ACAD.Specimen"."infraspecificEpithet" AS "ACAD.Specimen.infraspecificEpithet"
FROM "ACAD.Specimen"
JOIN "_if(then=taxonRank, cond=infraspecificEpithet)" USING ("row_num")
ORDER BY row_num
Temp table: @"in#37"@
# Defining wrapper function
CREATE TEMP TABLE "_join_words(1=_if(then=taxonRank, cond=infraspecificEpithet), 2" AS
SELECT
"in#37"."row_num"
, "_join_words"("1" := "in#37"."_if(then=taxonRank, cond=infraspecificEpithet).result", "2" := "in#37"."ACAD.Specimen.infraspecificEpithet") AS "result"
FROM "in#37"
LIMIT 0
CREATE FUNCTION "pg_temp"."_join_words(1=_if(then=taxonRank, cond=infraspecificEpithe_wrap"()
RETURNS SETOF "_join_words(1=_if(then=taxonRank, cond=infraspecificEpithet), 2"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#37"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#37"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_join_words"("1" := "row"."_if(then=taxonRank, cond=infraspecificEpithet).result", "2" := "row"."ACAD.Specimen.infraspecificEpithet")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."_if(then=taxonRank, cond=infraspecificEpithet).result" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."ACAD.Specimen.infraspecificEpithet" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('taxonRank,infraspecificEpithet'), ('infraspecificEpithet,infraspecificEpithet')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_join_words(1=_if(then=taxonRank, cond=infraspecificEpithet), 2"
("row_num", "result")
SELECT * FROM "pg_temp"."_join_words(1=_if(then=taxonRank, cond=infraspecificEpithe_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"_join_words"@:
|Output|Input|
|@'3'@|@"ACAD.Specimen"."specificEpithet"@|
|@'2'@|@"ACAD.Specimen"."genus"@|
|@'5'@|@"_join_words(1=_if(then=taxonRank, cond=infraspecificEpithet), 2"."result"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#38" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."specificEpithet" AS "ACAD.Specimen.specificEpithet"
, "ACAD.Specimen"."genus" AS "ACAD.Specimen.genus"
, "_join_words(1=_if(then=taxonRank, cond=infraspecificEpithet), 2"."result" AS "_join_words(1=_if(then=taxonRank, cond=infraspecificEpit.result"
FROM "ACAD.Specimen"
JOIN "_join_words(1=_if(then=taxonRank, cond=infraspecificEpithet), 2" USING ("row_num")
ORDER BY row_num
Temp table: @"in#38"@
# Defining wrapper function
CREATE TEMP TABLE "_join_words(3=specificEpithet, 2=genus, 5=_join_words(1=_if(the" AS
SELECT
"in#38"."row_num"
, "_join_words"("3" := "in#38"."ACAD.Specimen.specificEpithet", "2" := "in#38"."ACAD.Specimen.genus", "5" := "in#38"."_join_words(1=_if(then=taxonRank, cond=infraspecificEpit.result") AS "result"
FROM "in#38"
LIMIT 0
CREATE FUNCTION "pg_temp"."_join_words(3=specificEpithet, 2=genus, 5=_join_words(1=_i_wrap"()
RETURNS SETOF "_join_words(3=specificEpithet, 2=genus, 5=_join_words(1=_if(the"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#38"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#38"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_join_words"("3" := "row"."ACAD.Specimen.specificEpithet", "2" := "row"."ACAD.Specimen.genus", "5" := "row"."_join_words(1=_if(then=taxonRank, cond=infraspecificEpit.result")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.specificEpithet" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."ACAD.Specimen.genus" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."_join_words(1=_if(then=taxonRank, cond=infraspecificEpit.result" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('specificEpithet,genus,taxonRank'), ('specificEpithet,genus,infraspecificEpithet')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_join_words(3=specificEpithet, 2=genus, 5=_join_words(1=_if(the"
("row_num", "result")
SELECT * FROM "pg_temp"."_join_words(3=specificEpithet, 2=genus, 5=_join_words(1=_i_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"_join_words"@:
|Output|Input|
|@'1'@|@"_join_words(3=specificEpithet, 2=genus, 5=_join_words(1=_if(the"."result"@|
|@'2'@|@"ACAD.Specimen"."scientificNameAuthorship"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#39" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "_join_words(3=specificEpithet, 2=genus, 5=_join_words(1=_if(the"."result" AS "_join_words(3=specificEpithet, 2=genus, 5=_join_words(1=.result"
, "ACAD.Specimen"."scientificNameAuthorship" AS "ACAD.Specimen.scientificNameAuthorship"
FROM "ACAD.Specimen"
JOIN "_join_words(3=specificEpithet, 2=genus, 5=_join_words(1=_if(the" USING ("row_num")
ORDER BY row_num
Temp table: @"in#39"@
# Defining wrapper function
CREATE TEMP TABLE "_join_words(1=_join_words(3=specificEpithet, 2=genus, 5=_join_w" AS
SELECT
"in#39"."row_num"
, "_join_words"("1" := "in#39"."_join_words(3=specificEpithet, 2=genus, 5=_join_words(1=.result", "2" := "in#39"."ACAD.Specimen.scientificNameAuthorship") AS "result"
FROM "in#39"
LIMIT 0
CREATE FUNCTION "pg_temp"."_join_words(1=_join_words(3=specificEpithet, 2=genus, 5=_j_wrap"()
RETURNS SETOF "_join_words(1=_join_words(3=specificEpithet, 2=genus, 5=_join_w"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#39"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#39"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_join_words"("1" := "row"."_join_words(3=specificEpithet, 2=genus, 5=_join_words(1=.result", "2" := "row"."ACAD.Specimen.scientificNameAuthorship")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."_join_words(3=specificEpithet, 2=genus, 5=_join_words(1=.result" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."ACAD.Specimen.scientificNameAuthorship" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('specificEpithet,scientificNameAuthorship'), ('genus,scientificNameAuthorship'), ('taxonRank,scientificNameAuthorship'), ('infraspecificEpithet,scientificNameAuthorship')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_join_words(1=_join_words(3=specificEpithet, 2=genus, 5=_join_w"
("row_num", "result")
SELECT * FROM "pg_temp"."_join_words(1=_join_words(3=specificEpithet, 2=genus, 5=_j_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"_alt"@:
|Output|Input|
|@'1'@|@"ACAD.Specimen"."scientificName"@|
|@'2'@|@"_join_words(1=_join_words(3=specificEpithet, 2=genus, 5=_join_w"."result"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#40" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."scientificName" AS "ACAD.Specimen.scientificName"
, "_join_words(1=_join_words(3=specificEpithet, 2=genus, 5=_join_w"."result" AS "_join_words(1=_join_words(3=specificEpithet, 2=genus, 5=.result"
FROM "ACAD.Specimen"
JOIN "_join_words(1=_join_words(3=specificEpithet, 2=genus, 5=_join_w" USING ("row_num")
ORDER BY row_num
Temp table: @"in#40"@
# Defining wrapper function
CREATE TEMP TABLE "_alt(1=scientificName, 2=_join_words(1=_join_words(3=specificEp" AS
SELECT
"in#40"."row_num"
, "_alt"("1" := "in#40"."ACAD.Specimen.scientificName", "2" := "in#40"."_join_words(1=_join_words(3=specificEpithet, 2=genus, 5=.result") AS "result"
FROM "in#40"
LIMIT 0
CREATE FUNCTION "pg_temp"."_alt(1=scientificName, 2=_join_words(1=_join_words(3=speci_wrap"()
RETURNS SETOF "_alt(1=scientificName, 2=_join_words(1=_join_words(3=specificEp"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#40"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#40"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_alt"("1" := "row"."ACAD.Specimen.scientificName", "2" := "row"."_join_words(1=_join_words(3=specificEpithet, 2=genus, 5=.result")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.scientificName" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."_join_words(1=_join_words(3=specificEpithet, 2=genus, 5=.result" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('scientificName,specificEpithet'), ('scientificName,genus'), ('scientificName,taxonRank'), ('scientificName,infraspecificEpithet'), ('scientificName,scientificNameAuthorship')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_alt(1=scientificName, 2=_join_words(1=_join_words(3=specificEp"
("row_num", "result")
SELECT * FROM "pg_temp"."_alt(1=scientificName, 2=_join_words(1=_join_words(3=speci_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"_merge_prefix"@:
|Output|Input|
|@'prefix'@|@"_taxon_family_require_std(family=family)"."result"@|
|@'value'@|@"_alt(1=scientificName, 2=_join_words(1=_join_words(3=specificEp"."result"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#41" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "_taxon_family_require_std(family=family)"."result" AS "_taxon_family_require_std(family=family).result"
, "_alt(1=scientificName, 2=_join_words(1=_join_words(3=specificEp"."result" AS "_alt(1=scientificName, 2=_join_words(1=_join_words(3=spe.result"
FROM "ACAD.Specimen"
JOIN "_taxon_family_require_std(family=family)" USING ("row_num")
JOIN "_alt(1=scientificName, 2=_join_words(1=_join_words(3=specificEp" USING ("row_num")
ORDER BY row_num
Temp table: @"in#41"@
# Defining wrapper function
CREATE TEMP TABLE "_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_join_word" AS
SELECT
"in#41"."row_num"
, "_merge_prefix"("prefix" := "in#41"."_taxon_family_require_std(family=family).result", "value" := "in#41"."_alt(1=scientificName, 2=_join_words(1=_join_words(3=spe.result") AS "result"
FROM "in#41"
LIMIT 0
CREATE FUNCTION "pg_temp"."_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_join_wrap"()
RETURNS SETOF "_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_join_word"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#41"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#41"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_merge_prefix"("prefix" := "row"."_taxon_family_require_std(family=family).result", "value" := "row"."_alt(1=scientificName, 2=_join_words(1=_join_words(3=spe.result")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."_taxon_family_require_std(family=family).result" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."_alt(1=scientificName, 2=_join_words(1=_join_words(3=spe.result" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('family,scientificName'), ('family,specificEpithet'), ('family,genus'), ('family,taxonRank'), ('family,infraspecificEpithet'), ('family,scientificNameAuthorship')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_join_word"
("row_num", "result")
SELECT * FROM "pg_temp"."_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_join_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"taxonlabel"@:
|Output|Input|
|@'parent_id'@|@"taxonlabel[rank=taxonRank]"."out.taxonlabel_id"@|
|@'creationdate'@|@"_dateRangeStart(dateIdentified)#1"."result"@|
|@'taxonomicname'@|@"_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_join_word"."result"@|
|@'rank'@|@"ACAD.Specimen"."taxonRank"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#42" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "taxonlabel[rank=taxonRank]"."out.taxonlabel_id" AS "taxonlabel[rank=taxonRank].out.taxonlabel_id"
, "_dateRangeStart(dateIdentified)#1"."result" AS "_dateRangeStart(dateIdentified)#1.result"
, "_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_join_word"."result" AS "_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_jo.result"
, "ACAD.Specimen"."taxonRank" AS "ACAD.Specimen.taxonRank"
FROM "ACAD.Specimen"
JOIN "taxonlabel[rank=taxonRank]" USING ("row_num")
JOIN "_dateRangeStart(dateIdentified)#1" USING ("row_num")
JOIN "_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_join_word" USING ("row_num")
ORDER BY row_num
Temp table: @"in#42"@
CREATE TEMP TABLE "in#42_full" (
LIKE "in#42" INCLUDING ALL
);
INSERT INTO "in#42_full"
SELECT * FROM "in#42"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#32"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "creationdate", "taxonomicname", "rank")
SELECT
"in#42"."taxonlabel[rank=taxonRank].out.taxonlabel_id"
, "in#42"."_dateRangeStart(dateIdentified)#1.result"
, "in#42"."_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_jo.result"
, "in#42"."ACAD.Specimen.taxonRank"
FROM "in#42"
RETURNING "taxonlabel_id"
$$;
# Caught exception: MissingCastException: Missing cast to type @date@ on column: @creationdate@
# Casting @'creationdate'@ input to @date@
CREATE FUNCTION "pg_temp"."date(dateIdentified)"("value" anyelement)
RETURNS date
LANGUAGE plpgsql
STRICT
AS $$
BEGIN
/* The explicit cast to the return type is needed to make the cast happen
inside the try block. (Implicit casts to the return type happen at the end
of the function, outside any block.) */
RETURN CAST(value AS date);
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := value;
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('dateIdentified')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN NULL;
END;
$$;
ALTER TABLE "in#42" ADD COLUMN "_dateRangeStart(dateIdentified)#1.result::date" date /*"in#42"."_dateRangeStart(dateIdentified)#1.result"::date*/
ALTER TABLE "in#42"
ALTER COLUMN "_dateRangeStart(dateIdentified)#1.result::date" TYPE date
USING "pg_temp"."date(dateIdentified)"("in#42"."_dateRangeStart(dateIdentified)#1.result")
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#32"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "creationdate", "taxonomicname", "rank")
SELECT
"in#42"."taxonlabel[rank=taxonRank].out.taxonlabel_id"
, "in#42"."_dateRangeStart(dateIdentified)#1.result::date"
, "in#42"."_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_jo.result"
, "in#42"."ACAD.Specimen.taxonRank"
FROM "in#42"
RETURNING "taxonlabel_id"
$$;
# Caught exception: MissingCastException: Missing cast to type @taxonrank@ on column: @rank@
# Casting @'rank'@ input to @taxonrank@
CREATE FUNCTION "pg_temp"."taxonrank(taxonRank)"("value" anyelement)
RETURNS taxonrank
LANGUAGE plpgsql
STRICT
AS $$
BEGIN
/* The explicit cast to the return type is needed to make the cast happen
inside the try block. (Implicit casts to the return type happen at the end
of the function, outside any block.) */
RETURN CAST(value AS taxonrank);
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := value;
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('taxonRank')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN NULL;
END;
$$;
ALTER TABLE "in#42" ADD COLUMN "ACAD.Specimen.taxonRank::taxonrank" taxonrank /*"in#42"."ACAD.Specimen.taxonRank"::taxonrank*/
ALTER TABLE "in#42"
ALTER COLUMN "ACAD.Specimen.taxonRank::taxonrank" TYPE taxonrank
USING "pg_temp"."taxonrank(taxonRank)"("in#42"."ACAD.Specimen.taxonRank")
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#32"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "creationdate", "taxonomicname", "rank")
SELECT
"in#42"."taxonlabel[rank=taxonRank].out.taxonlabel_id"
, "in#42"."_dateRangeStart(dateIdentified)#1.result::date"
, "in#42"."_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_jo.result"
, "in#42"."ACAD.Specimen.taxonRank::taxonrank"
FROM "in#42"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#42_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#32"() AS "f" ("taxonlabel_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#33"()
RETURNS SETOF "taxonlabel"."taxonlabel_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonlabel"
("parent_id", "creationdate", "taxonomicname", "rank", "source_id")
SELECT
"in#42"."taxonlabel[rank=taxonRank].out.taxonlabel_id"
, "in#42"."_dateRangeStart(dateIdentified)#1.result::date"
, "in#42"."_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_jo.result"
, "in#42"."ACAD.Specimen.taxonRank::taxonrank"
, 1 AS "source_id"
FROM "in#42"
RETURNING "taxonlabel_id"
$$;
CREATE TEMP TABLE "in#42_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#33"() AS "f" ("taxonlabel_id")
# Caught exception: DuplicateKeyException: Violated @taxonlabel_0_unique_identifying_name@ constraint with condition @taxonomicname IS NOT NULL@ on columns: @source_id, taxonomicname@
# Ignoring rows that do not satisfy @"_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_jo.result" IS NOT NULL@
DELETE FROM "in#42"
WHERE NOT COALESCE("_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_jo.result" IS NOT NULL, false)
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#42_distinct" (
LIKE "in#42" INCLUDING ALL
);
INSERT INTO "in#42_distinct"
SELECT
DISTINCT ON ("in#42"."_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_jo.result")
"in#42".*
FROM "in#42"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonlabel#34"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "taxonlabel"%ROWTYPE;
BEGIN
FOR "row"."parent_id", "row"."creationdate", "row"."taxonomicname", "row"."rank", "row"."source_id" IN
SELECT
"in#42_distinct"."taxonlabel[rank=taxonRank].out.taxonlabel_id"
, "in#42_distinct"."_dateRangeStart(dateIdentified)#1.result::date"
, "in#42_distinct"."_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_jo.result"
, "in#42_distinct"."ACAD.Specimen.taxonRank::taxonrank"
, 1 AS "source_id"
FROM "in#42_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "taxonlabel"
("parent_id", "creationdate", "taxonomicname", "rank", "source_id")
VALUES ("row"."parent_id", "row"."creationdate", "row"."taxonomicname", "row"."rank", "row"."source_id")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
CREATE TEMP TABLE "rowcount#12" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonlabel#34"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "taxonlabel[rank=taxonRank]#1" AS
SELECT
"in#42"."row_num"
, "taxonlabel"."taxonlabel_id" AS "out.taxonlabel_id"
FROM "in#42"
JOIN "taxonlabel" ON
"taxonlabel"."source_id" = 1
AND COALESCE("taxonlabel"."taxonomicname", CAST('\N' AS text)) = COALESCE("in#42"."_merge_prefix(_alt(1=scientificName, 2=_join_words(1=_jo.result", CAST('\N' AS text))
# Setting pkeys of missing rows to @"in#42"."taxonlabel[rank=taxonRank].out.taxonlabel_id"@
INSERT INTO "taxonlabel[rank=taxonRank]#1"
("row_num", "out.taxonlabel_id")
SELECT
"in#42_full"."row_num"
, "in#42_full"."taxonlabel[rank=taxonRank].out.taxonlabel_id" AS "out.taxonlabel_id"
FROM "in#42_full"
LEFT JOIN "taxonlabel[rank=taxonRank]#1" ON "taxonlabel[rank=taxonRank]#1"."row_num" = "in#42_full"."row_num"
WHERE "taxonlabel[rank=taxonRank]#1"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"taxonverbatim"@:
|Output|Input|
|@'family'@|@"ACAD.Specimen"."family"@|
|@'author'@|@"ACAD.Specimen"."scientificNameAuthorship"@|
|@'specific_epithet'@|@"ACAD.Specimen"."specificEpithet"@|
|@'taxonlabel_id'@|@"taxonlabel[rank=taxonRank]#1"."out.taxonlabel_id"@|
|@'verbatimrank'@|@"ACAD.Specimen"."taxonRank"@|
|@'genus'@|@"ACAD.Specimen"."genus"@|
|@'taxonomicname'@|@"ACAD.Specimen"."scientificName"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#43" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."family" AS "ACAD.Specimen.family"
, "ACAD.Specimen"."scientificNameAuthorship" AS "ACAD.Specimen.scientificNameAuthorship"
, "ACAD.Specimen"."specificEpithet" AS "ACAD.Specimen.specificEpithet"
, "taxonlabel[rank=taxonRank]#1"."out.taxonlabel_id" AS "taxonlabel[rank=taxonRank]#1.out.taxonlabel_id"
, "ACAD.Specimen"."taxonRank" AS "ACAD.Specimen.taxonRank"
, "ACAD.Specimen"."genus" AS "ACAD.Specimen.genus"
, "ACAD.Specimen"."scientificName" AS "ACAD.Specimen.scientificName"
FROM "ACAD.Specimen"
JOIN "taxonlabel[rank=taxonRank]#1" USING ("row_num")
ORDER BY row_num
Temp table: @"in#43"@
CREATE TEMP TABLE "in#43_full" (
LIKE "in#43" INCLUDING ALL
);
INSERT INTO "in#43_full"
SELECT * FROM "in#43"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonverbatim"()
RETURNS SETOF "taxonverbatim"."taxonverbatim_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxonverbatim"
("family", "author", "specific_epithet", "taxonlabel_id", "verbatimrank", "genus", "taxonomicname")
SELECT
"in#43"."ACAD.Specimen.family"
, "in#43"."ACAD.Specimen.scientificNameAuthorship"
, "in#43"."ACAD.Specimen.specificEpithet"
, "in#43"."taxonlabel[rank=taxonRank]#1.out.taxonlabel_id"
, "in#43"."ACAD.Specimen.taxonRank"
, "in#43"."ACAD.Specimen.genus"
, "in#43"."ACAD.Specimen.scientificName"
FROM "in#43"
RETURNING "taxonverbatim_id"
$$;
CREATE TEMP TABLE "in#43_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonverbatim"() AS "f" ("taxonverbatim_id")
# Caught exception: DuplicateKeyException: Violated @taxonverbatim_unique@ constraint on columns: @taxonlabel_id, morphospecies@
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#43_distinct" (
LIKE "in#43" INCLUDING ALL
);
INSERT INTO "in#43_distinct"
SELECT
DISTINCT ON ("in#43"."taxonlabel[rank=taxonRank]#1.out.taxonlabel_id")
"in#43".*
FROM "in#43"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxonverbatim#1"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "taxonverbatim"%ROWTYPE;
BEGIN
FOR "row"."family", "row"."author", "row"."specific_epithet", "row"."taxonlabel_id", "row"."verbatimrank", "row"."genus", "row"."taxonomicname" IN
SELECT
"in#43_distinct"."ACAD.Specimen.family"
, "in#43_distinct"."ACAD.Specimen.scientificNameAuthorship"
, "in#43_distinct"."ACAD.Specimen.specificEpithet"
, "in#43_distinct"."taxonlabel[rank=taxonRank]#1.out.taxonlabel_id"
, "in#43_distinct"."ACAD.Specimen.taxonRank"
, "in#43_distinct"."ACAD.Specimen.genus"
, "in#43_distinct"."ACAD.Specimen.scientificName"
FROM "in#43_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "taxonverbatim"
("family", "author", "specific_epithet", "taxonlabel_id", "verbatimrank", "genus", "taxonomicname")
VALUES ("row"."family", "row"."author", "row"."specific_epithet", "row"."taxonlabel_id", "row"."verbatimrank", "row"."genus", "row"."taxonomicname")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
CREATE TEMP TABLE "rowcount#13" AS
SELECT * FROM "pg_temp"."INSERT INTO taxonverbatim#1"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "taxonverbatim_pkeys" AS
SELECT
"in#43"."row_num"
, "taxonverbatim"."taxonverbatim_id" AS "out.taxonverbatim_id"
FROM "in#43"
JOIN "taxonverbatim" ON
"taxonverbatim"."taxonlabel_id" = "in#43"."taxonlabel[rank=taxonRank]#1.out.taxonlabel_id"
AND COALESCE("taxonverbatim"."morphospecies", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
# Setting pkeys of missing rows to @None@
INSERT INTO "taxonverbatim_pkeys"
("row_num", "out.taxonverbatim_id")
SELECT
"in#43_full"."row_num"
, NULL AS "out.taxonverbatim_id"
FROM "in#43_full"
LEFT JOIN "taxonverbatim_pkeys" ON "taxonverbatim_pkeys"."row_num" = "in#43_full"."row_num"
WHERE "taxonverbatim_pkeys"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"taxondetermination"@:
|Output|Input|
|@'determinationdate'@|@"_dateRangeStart(dateIdentified)"."result"@|
|@'taxonoccurrence_id'@|@"taxonoccurrence_pkeys"."out.taxonoccurrence_id"@|
|@'party_id'@|@"party_pkeys#1"."out.party_id"@|
|@'taxonverbatim_id'@|@"taxonverbatim_pkeys"."out.taxonverbatim_id"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#44" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "_dateRangeStart(dateIdentified)"."result" AS "_dateRangeStart(dateIdentified).result"
, "taxonoccurrence_pkeys"."out.taxonoccurrence_id" AS "taxonoccurrence_pkeys.out.taxonoccurrence_id"
, "party_pkeys#1"."out.party_id" AS "party_pkeys#1.out.party_id"
, "taxonverbatim_pkeys"."out.taxonverbatim_id" AS "taxonverbatim_pkeys.out.taxonverbatim_id"
FROM "ACAD.Specimen"
JOIN "_dateRangeStart(dateIdentified)" USING ("row_num")
JOIN "taxonoccurrence_pkeys" USING ("row_num")
JOIN "party_pkeys#1" USING ("row_num")
JOIN "taxonverbatim_pkeys" USING ("row_num")
ORDER BY row_num
Temp table: @"in#44"@
CREATE TEMP TABLE "in#44_full" (
LIKE "in#44" INCLUDING ALL
);
INSERT INTO "in#44_full"
SELECT * FROM "in#44"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxondetermination"()
RETURNS SETOF "taxondetermination"."taxondetermination_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxondetermination"
("party_id", "taxonoccurrence_id", "determinationdate", "taxonverbatim_id")
SELECT
"in#44"."party_pkeys#1.out.party_id"
, "in#44"."taxonoccurrence_pkeys.out.taxonoccurrence_id"
, "in#44"."_dateRangeStart(dateIdentified).result"
, "in#44"."taxonverbatim_pkeys.out.taxonverbatim_id"
FROM "in#44"
RETURNING "taxondetermination_id"
$$;
# Caught exception: MissingCastException: Missing cast to type @date@ on column: @determinationdate@
# Casting @'determinationdate'@ input to @date@
CREATE FUNCTION "pg_temp"."date(dateIdentified)"("value" anyelement)
RETURNS date
LANGUAGE plpgsql
STRICT
AS $$
BEGIN
/* The explicit cast to the return type is needed to make the cast happen
inside the try block. (Implicit casts to the return type happen at the end
of the function, outside any block.) */
RETURN CAST(value AS date);
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := value;
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('dateIdentified')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN NULL;
END;
$$;
ALTER TABLE "in#44" ADD COLUMN "_dateRangeStart(dateIdentified).result::date" date /*"in#44"."_dateRangeStart(dateIdentified).result"::date*/
ALTER TABLE "in#44"
ALTER COLUMN "_dateRangeStart(dateIdentified).result::date" TYPE date
USING "pg_temp"."date(dateIdentified)"("in#44"."_dateRangeStart(dateIdentified).result")
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxondetermination"()
RETURNS SETOF "taxondetermination"."taxondetermination_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxondetermination"
("party_id", "taxonoccurrence_id", "determinationdate", "taxonverbatim_id")
SELECT
"in#44"."party_pkeys#1.out.party_id"
, "in#44"."taxonoccurrence_pkeys.out.taxonoccurrence_id"
, "in#44"."_dateRangeStart(dateIdentified).result::date"
, "in#44"."taxonverbatim_pkeys.out.taxonverbatim_id"
FROM "in#44"
RETURNING "taxondetermination_id"
$$;
CREATE TEMP TABLE "in#44_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxondetermination"() AS "f" ("taxondetermination_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO taxondetermination#1"()
RETURNS SETOF "taxondetermination"."taxondetermination_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "taxondetermination"
("party_id", "taxonoccurrence_id", "determinationdate", "taxonverbatim_id", "source_id")
SELECT
"in#44"."party_pkeys#1.out.party_id"
, "in#44"."taxonoccurrence_pkeys.out.taxonoccurrence_id"
, "in#44"."_dateRangeStart(dateIdentified).result::date"
, "in#44"."taxonverbatim_pkeys.out.taxonverbatim_id"
, 1 AS "source_id"
FROM "in#44"
RETURNING "taxondetermination_id"
$$;
CREATE TEMP TABLE "in#44_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO taxondetermination#1"() AS "f" ("taxondetermination_id")
# Getting input table pkeys of inserted rows
CREATE TEMP TABLE "in#44_insert_in_pkeys" AS
SELECT "row_num" FROM "in#44"
# Combining output and input pkeys in inserted order
CREATE TEMP TABLE "taxondetermination_pkeys" AS
SELECT
"in#44_insert_in_pkeys"."row_num"
, "in#44_insert_out_pkeys"."taxondetermination_id" AS "out.taxondetermination_id"
FROM "in#44_insert_in_pkeys"
JOIN "in#44_insert_out_pkeys" USING ("_row_num")
# Setting pkeys of missing rows to @None@
INSERT INTO "taxondetermination_pkeys"
("row_num", "out.taxondetermination_id")
SELECT
"in#44_full"."row_num"
, NULL AS "out.taxondetermination_id"
FROM "in#44_full"
LEFT JOIN "taxondetermination_pkeys" ON "taxondetermination_pkeys"."row_num" = "in#44_full"."row_num"
WHERE "taxondetermination_pkeys"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"_nullIf"@:
|Output|Input|
|@'null'@|@'0'@|
|@'type'@|@'float'@|
|@'value'@|@"ACAD.Specimen"."decimalLatitude"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#45" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."decimalLatitude" AS "ACAD.Specimen.decimalLatitude"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#45"@
# Defining wrapper function
CREATE TEMP TABLE "_nullIf(decimalLatitude)" AS
SELECT
"in#45"."row_num"
, "_nullIf"("null" := '0', "type" := 'float', "value" := "in#45"."ACAD.Specimen.decimalLatitude") AS "result"
FROM "in#45"
LIMIT 0
CREATE FUNCTION "pg_temp"."_nullIf(decimalLatitude)_wrap"()
RETURNS SETOF "_nullIf(decimalLatitude)"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#45"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#45"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_nullIf"("null" := '0', "type" := 'float', "value" := "row"."ACAD.Specimen.decimalLatitude")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.decimalLatitude" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('decimalLatitude')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_nullIf(decimalLatitude)"
("row_num", "result")
SELECT * FROM "pg_temp"."_nullIf(decimalLatitude)_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"_nullIf"@:
|Output|Input|
|@'null'@|@'0'@|
|@'type'@|@'float'@|
|@'value'@|@"ACAD.Specimen"."decimalLongitude"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#46" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."decimalLongitude" AS "ACAD.Specimen.decimalLongitude"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#46"@
# Defining wrapper function
CREATE TEMP TABLE "_nullIf(decimalLongitude)" AS
SELECT
"in#46"."row_num"
, "_nullIf"("null" := '0', "type" := 'float', "value" := "in#46"."ACAD.Specimen.decimalLongitude") AS "result"
FROM "in#46"
LIMIT 0
CREATE FUNCTION "pg_temp"."_nullIf(decimalLongitude)_wrap"()
RETURNS SETOF "_nullIf(decimalLongitude)"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#46"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#46"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_nullIf"("null" := '0', "type" := 'float', "value" := "row"."ACAD.Specimen.decimalLongitude")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.decimalLongitude" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('decimalLongitude')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_nullIf(decimalLongitude)"
("row_num", "result")
SELECT * FROM "pg_temp"."_nullIf(decimalLongitude)_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"coordinates"@:
|Output|Input|
|@'latitude_deg'@|@"_nullIf(decimalLatitude)"."result"@|
|@'longitude_deg'@|@"_nullIf(decimalLongitude)"."result"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#47" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "_nullIf(decimalLatitude)"."result" AS "_nullIf(decimalLatitude).result"
, "_nullIf(decimalLongitude)"."result" AS "_nullIf(decimalLongitude).result"
FROM "ACAD.Specimen"
JOIN "_nullIf(decimalLatitude)" USING ("row_num")
JOIN "_nullIf(decimalLongitude)" USING ("row_num")
ORDER BY row_num
Temp table: @"in#47"@
CREATE TEMP TABLE "in#47_full" (
LIKE "in#47" INCLUDING ALL
);
INSERT INTO "in#47_full"
SELECT * FROM "in#47"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO coordinates"()
RETURNS SETOF "coordinates"."coordinates_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "coordinates"
("latitude_deg", "longitude_deg")
SELECT
"in#47"."_nullIf(decimalLatitude).result"
, "in#47"."_nullIf(decimalLongitude).result"
FROM "in#47"
RETURNING "coordinates_id"
$$;
# Caught exception: MissingCastException: Missing cast to type @double precision@ on column: @latitude_deg@
# Casting @'latitude_deg'@ input to @double precision@
CREATE FUNCTION "pg_temp"."double(decimalLatitude)"("value" anyelement)
RETURNS double precision
LANGUAGE plpgsql
STRICT
AS $$
BEGIN
/* The explicit cast to the return type is needed to make the cast happen
inside the try block. (Implicit casts to the return type happen at the end
of the function, outside any block.) */
RETURN CAST(value AS double precision);
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := value;
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('decimalLatitude')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN NULL;
END;
$$;
ALTER TABLE "in#47" ADD COLUMN "_nullIf(decimalLatitude).result::double" double precision /*"in#47"."_nullIf(decimalLatitude).result"::double precision*/
ALTER TABLE "in#47"
ALTER COLUMN "_nullIf(decimalLatitude).result::double" TYPE double precision
USING "pg_temp"."double(decimalLatitude)"("in#47"."_nullIf(decimalLatitude).result")
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO coordinates"()
RETURNS SETOF "coordinates"."coordinates_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "coordinates"
("latitude_deg", "longitude_deg")
SELECT
"in#47"."_nullIf(decimalLatitude).result::double"
, "in#47"."_nullIf(decimalLongitude).result"
FROM "in#47"
RETURNING "coordinates_id"
$$;
# Caught exception: MissingCastException: Missing cast to type @double precision@ on column: @longitude_deg@
# Casting @'longitude_deg'@ input to @double precision@
CREATE FUNCTION "pg_temp"."double(decimalLongitude)"("value" anyelement)
RETURNS double precision
LANGUAGE plpgsql
STRICT
AS $$
BEGIN
/* The explicit cast to the return type is needed to make the cast happen
inside the try block. (Implicit casts to the return type happen at the end
of the function, outside any block.) */
RETURN CAST(value AS double precision);
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := value;
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('decimalLongitude')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN NULL;
END;
$$;
ALTER TABLE "in#47" ADD COLUMN "_nullIf(decimalLongitude).result::double" double precision /*"in#47"."_nullIf(decimalLongitude).result"::double precision*/
ALTER TABLE "in#47"
ALTER COLUMN "_nullIf(decimalLongitude).result::double" TYPE double precision
USING "pg_temp"."double(decimalLongitude)"("in#47"."_nullIf(decimalLongitude).result")
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO coordinates"()
RETURNS SETOF "coordinates"."coordinates_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "coordinates"
("latitude_deg", "longitude_deg")
SELECT
"in#47"."_nullIf(decimalLatitude).result::double"
, "in#47"."_nullIf(decimalLongitude).result::double"
FROM "in#47"
RETURNING "coordinates_id"
$$;
CREATE TEMP TABLE "in#47_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO coordinates"() AS "f" ("coordinates_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO coordinates#1"()
RETURNS SETOF "coordinates"."coordinates_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "coordinates"
("latitude_deg", "longitude_deg", "source_id")
SELECT
"in#47"."_nullIf(decimalLatitude).result::double"
, "in#47"."_nullIf(decimalLongitude).result::double"
, 1 AS "source_id"
FROM "in#47"
RETURNING "coordinates_id"
$$;
CREATE TEMP TABLE "in#47_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO coordinates#1"() AS "f" ("coordinates_id")
# Caught exception: DuplicateKeyException: Violated @coordinates_unique@ constraint on columns: @source_id, latitude_deg, longitude_deg, verbatimlatitude, verbatimlongitude, verbatimcoordinates, footprintgeometry_dwc@
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#47_distinct" (
LIKE "in#47" INCLUDING ALL
);
INSERT INTO "in#47_distinct"
SELECT
DISTINCT ON ("in#47"."_nullIf(decimalLatitude).result::double", "in#47"."_nullIf(decimalLongitude).result::double")
"in#47".*
FROM "in#47"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO coordinates#2"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "coordinates"%ROWTYPE;
BEGIN
FOR "row"."latitude_deg", "row"."longitude_deg", "row"."source_id" IN
SELECT
"in#47_distinct"."_nullIf(decimalLatitude).result::double"
, "in#47_distinct"."_nullIf(decimalLongitude).result::double"
, 1 AS "source_id"
FROM "in#47_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "coordinates"
("latitude_deg", "longitude_deg", "source_id")
VALUES ("row"."latitude_deg", "row"."longitude_deg", "row"."source_id")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
CREATE TEMP TABLE "rowcount#14" AS
SELECT * FROM "pg_temp"."INSERT INTO coordinates#2"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "coordinates_pkeys" AS
SELECT
"in#47"."row_num"
, "coordinates"."coordinates_id" AS "out.coordinates_id"
FROM "in#47"
JOIN "coordinates" ON
"coordinates"."source_id" = 1
AND "coordinates"."latitude_deg" = "in#47"."_nullIf(decimalLatitude).result::double"
AND "coordinates"."longitude_deg" = "in#47"."_nullIf(decimalLongitude).result::double"
AND COALESCE("coordinates"."verbatimlatitude", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("coordinates"."verbatimlongitude", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("coordinates"."verbatimcoordinates", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("coordinates"."footprintgeometry_dwc", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
# Setting pkeys of missing rows to @None@
INSERT INTO "coordinates_pkeys"
("row_num", "out.coordinates_id")
SELECT
"in#47_full"."row_num"
, NULL AS "out.coordinates_id"
FROM "in#47_full"
LEFT JOIN "coordinates_pkeys" ON "coordinates_pkeys"."row_num" = "in#47_full"."row_num"
WHERE "coordinates_pkeys"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'geoscrub'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'geoscrub' AS "shortname"
RETURNING "source_id"
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'geoscrub'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'geoscrub' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'geoscrub'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'geoscrub'
# ********** New iteration **********
# Inserting these input columns into @"_nullIf"@:
|Output|Input|
|@'null'@|@'0'@|
|@'type'@|@'float'@|
|@'value'@|@"ACAD.Specimen"."decimalLatitude"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#48" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."decimalLatitude" AS "ACAD.Specimen.decimalLatitude"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#48"@
# Defining wrapper function
CREATE TEMP TABLE "_nullIf(decimalLatitude)#1" AS
SELECT
"in#48"."row_num"
, "_nullIf"("null" := '0', "type" := 'float', "value" := "in#48"."ACAD.Specimen.decimalLatitude") AS "result"
FROM "in#48"
LIMIT 0
CREATE FUNCTION "pg_temp"."_nullIf(decimalLatitude)#1_wrap"()
RETURNS SETOF "_nullIf(decimalLatitude)#1"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#48"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#48"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_nullIf"("null" := '0', "type" := 'float', "value" := "row"."ACAD.Specimen.decimalLatitude")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.decimalLatitude" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('decimalLatitude')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_nullIf(decimalLatitude)#1"
("row_num", "result")
SELECT * FROM "pg_temp"."_nullIf(decimalLatitude)#1_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"_nullIf"@:
|Output|Input|
|@'null'@|@'0'@|
|@'type'@|@'float'@|
|@'value'@|@"ACAD.Specimen"."decimalLongitude"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#49" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "ACAD.Specimen"."decimalLongitude" AS "ACAD.Specimen.decimalLongitude"
FROM "ACAD.Specimen"
ORDER BY row_num
Temp table: @"in#49"@
# Defining wrapper function
CREATE TEMP TABLE "_nullIf(decimalLongitude)#1" AS
SELECT
"in#49"."row_num"
, "_nullIf"("null" := '0', "type" := 'float', "value" := "in#49"."ACAD.Specimen.decimalLongitude") AS "result"
FROM "in#49"
LIMIT 0
CREATE FUNCTION "pg_temp"."_nullIf(decimalLongitude)#1_wrap"()
RETURNS SETOF "_nullIf(decimalLongitude)#1"
LANGUAGE plpgsql
AS $$
DECLARE
"row" "in#49"%ROWTYPE;
BEGIN
FOR "row" IN
SELECT * FROM "in#49"
LOOP
BEGIN
BEGIN
RETURN QUERY
SELECT
"row"."row_num"
, "_nullIf"("null" := '0', "type" := 'float', "value" := "row"."ACAD.Specimen.decimalLongitude")
;
EXCEPTION
WHEN internal_error THEN
-- Handle PL/Python exceptions
DECLARE
matches text[] := regexp_matches(SQLERRM,
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n
exc_name text := matches[1];
msg text := matches[2];
BEGIN
/* Re-raise PL/Python exceptions with the PL/Python prefix removed.
This allows the exception to be parsed like a native exception.
Always raise as data_exception so it goes in the errors table. */
IF exc_name IS NOT NULL THEN
RAISE data_exception USING MESSAGE = msg;
-- Re-raise non-PL/Python exceptions
ELSE
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM;
END IF;
END;
END;
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.decimalLongitude" AS text), CAST(NULL AS text))], ',');
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('decimalLongitude')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN QUERY
SELECT
"row"."row_num"
, CAST(NULL AS text)
;
END;
END LOOP;
END;
$$;
# Calling function
INSERT INTO "_nullIf(decimalLongitude)#1"
("row_num", "result")
SELECT * FROM "pg_temp"."_nullIf(decimalLongitude)#1_wrap"()
# ********** New iteration **********
# Inserting these input columns into @"coordinates"@:
|Output|Input|
|@'source_id'@|@25@|
|@'latitude_deg'@|@"_nullIf(decimalLatitude)#1"."result"@|
|@'longitude_deg'@|@"_nullIf(decimalLongitude)#1"."result"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#50" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "_nullIf(decimalLatitude)#1"."result" AS "_nullIf(decimalLatitude)#1.result"
, "_nullIf(decimalLongitude)#1"."result" AS "_nullIf(decimalLongitude)#1.result"
FROM "ACAD.Specimen"
JOIN "_nullIf(decimalLatitude)#1" USING ("row_num")
JOIN "_nullIf(decimalLongitude)#1" USING ("row_num")
ORDER BY row_num
Temp table: @"in#50"@
CREATE TEMP TABLE "in#50_full" (
LIKE "in#50" INCLUDING ALL
);
INSERT INTO "in#50_full"
SELECT * FROM "in#50"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO coordinates#3"()
RETURNS SETOF "coordinates"."coordinates_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "coordinates"
("source_id", "latitude_deg", "longitude_deg")
SELECT
25 AS "source_id"
, "in#50"."_nullIf(decimalLatitude)#1.result"
, "in#50"."_nullIf(decimalLongitude)#1.result"
FROM "in#50"
RETURNING "coordinates_id"
$$;
# Caught exception: MissingCastException: Missing cast to type @double precision@ on column: @latitude_deg@
# Casting @'latitude_deg'@ input to @double precision@
CREATE FUNCTION "pg_temp"."double(decimalLatitude)"("value" anyelement)
RETURNS double precision
LANGUAGE plpgsql
STRICT
AS $$
BEGIN
/* The explicit cast to the return type is needed to make the cast happen
inside the try block. (Implicit casts to the return type happen at the end
of the function, outside any block.) */
RETURN CAST(value AS double precision);
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := value;
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('decimalLatitude')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN NULL;
END;
$$;
ALTER TABLE "in#50" ADD COLUMN "_nullIf(decimalLatitude)#1.result::double" double precision /*"in#50"."_nullIf(decimalLatitude)#1.result"::double precision*/
ALTER TABLE "in#50"
ALTER COLUMN "_nullIf(decimalLatitude)#1.result::double" TYPE double precision
USING "pg_temp"."double(decimalLatitude)"("in#50"."_nullIf(decimalLatitude)#1.result")
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO coordinates#3"()
RETURNS SETOF "coordinates"."coordinates_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "coordinates"
("source_id", "latitude_deg", "longitude_deg")
SELECT
25 AS "source_id"
, "in#50"."_nullIf(decimalLatitude)#1.result::double"
, "in#50"."_nullIf(decimalLongitude)#1.result"
FROM "in#50"
RETURNING "coordinates_id"
$$;
# Caught exception: MissingCastException: Missing cast to type @double precision@ on column: @longitude_deg@
# Casting @'longitude_deg'@ input to @double precision@
CREATE FUNCTION "pg_temp"."double(decimalLongitude)"("value" anyelement)
RETURNS double precision
LANGUAGE plpgsql
STRICT
AS $$
BEGIN
/* The explicit cast to the return type is needed to make the cast happen
inside the try block. (Implicit casts to the return type happen at the end
of the function, outside any block.) */
RETURN CAST(value AS double precision);
EXCEPTION
WHEN data_exception THEN
-- Save error in errors table.
DECLARE
error_code text := SQLSTATE;
error text := SQLERRM;
value text := value;
"column" text;
BEGIN
-- Insert the value and error for *each* source column.
FOR "column" IN
SELECT * FROM (VALUES ('decimalLongitude')) AS "c"
LOOP
BEGIN
INSERT INTO "ACAD"."Specimen.errors"
("column", "value", "error_code", "error")
VALUES ("column", "value", "error_code", "error");
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
RETURN NULL;
END;
$$;
ALTER TABLE "in#50" ADD COLUMN "_nullIf(decimalLongitude)#1.result::double" double precision /*"in#50"."_nullIf(decimalLongitude)#1.result"::double precision*/
ALTER TABLE "in#50"
ALTER COLUMN "_nullIf(decimalLongitude)#1.result::double" TYPE double precision
USING "pg_temp"."double(decimalLongitude)"("in#50"."_nullIf(decimalLongitude)#1.result")
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO coordinates#3"()
RETURNS SETOF "coordinates"."coordinates_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "coordinates"
("source_id", "latitude_deg", "longitude_deg")
SELECT
25 AS "source_id"
, "in#50"."_nullIf(decimalLatitude)#1.result::double"
, "in#50"."_nullIf(decimalLongitude)#1.result::double"
FROM "in#50"
RETURNING "coordinates_id"
$$;
CREATE TEMP TABLE "in#50_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO coordinates#3"() AS "f" ("coordinates_id")
# Caught exception: DuplicateKeyException: Violated @coordinates_unique@ constraint on columns: @source_id, latitude_deg, longitude_deg, verbatimlatitude, verbatimlongitude, verbatimcoordinates, footprintgeometry_dwc@
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#50_distinct" (
LIKE "in#50" INCLUDING ALL
);
INSERT INTO "in#50_distinct"
SELECT
DISTINCT ON ("in#50"."_nullIf(decimalLatitude)#1.result::double", "in#50"."_nullIf(decimalLongitude)#1.result::double")
"in#50".*
FROM "in#50"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO coordinates#4"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "coordinates"%ROWTYPE;
BEGIN
FOR "row"."source_id", "row"."latitude_deg", "row"."longitude_deg" IN
SELECT
25 AS "source_id"
, "in#50_distinct"."_nullIf(decimalLatitude)#1.result::double"
, "in#50_distinct"."_nullIf(decimalLongitude)#1.result::double"
FROM "in#50_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "coordinates"
("source_id", "latitude_deg", "longitude_deg")
VALUES ("row"."source_id", "row"."latitude_deg", "row"."longitude_deg")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
CREATE TEMP TABLE "rowcount#15" AS
SELECT * FROM "pg_temp"."INSERT INTO coordinates#4"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "coordinates_pkeys#1" AS
SELECT
"in#50"."row_num"
, "coordinates"."coordinates_id" AS "out.coordinates_id"
FROM "in#50"
JOIN "coordinates" ON
"coordinates"."source_id" = 25
AND "coordinates"."latitude_deg" = "in#50"."_nullIf(decimalLatitude)#1.result::double"
AND "coordinates"."longitude_deg" = "in#50"."_nullIf(decimalLongitude)#1.result::double"
AND COALESCE("coordinates"."verbatimlatitude", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("coordinates"."verbatimlongitude", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("coordinates"."verbatimcoordinates", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("coordinates"."footprintgeometry_dwc", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
# Setting pkeys of missing rows to @None@
INSERT INTO "coordinates_pkeys#1"
("row_num", "out.coordinates_id")
SELECT
"in#50_full"."row_num"
, NULL AS "out.coordinates_id"
FROM "in#50_full"
LEFT JOIN "coordinates_pkeys#1" ON "coordinates_pkeys#1"."row_num" = "in#50_full"."row_num"
WHERE "coordinates_pkeys#1"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"place"@:
|Output|Input|
|@'source_id'@|@25@|
|@'coordinates_id'@|@"coordinates_pkeys#1"."out.coordinates_id"@|
|@'stateprovince'@|@"ACAD.Specimen"."stateProvince"@|
|@'county'@|@"ACAD.Specimen"."county"@|
|@'country'@|@"ACAD.Specimen"."country"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#51" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "coordinates_pkeys#1"."out.coordinates_id" AS "coordinates_pkeys#1.out.coordinates_id"
, "ACAD.Specimen"."stateProvince" AS "ACAD.Specimen.stateProvince"
, "ACAD.Specimen"."county" AS "ACAD.Specimen.county"
, "ACAD.Specimen"."country" AS "ACAD.Specimen.country"
FROM "ACAD.Specimen"
JOIN "coordinates_pkeys#1" USING ("row_num")
ORDER BY row_num
Temp table: @"in#51"@
CREATE TEMP TABLE "in#51_full" (
LIKE "in#51" INCLUDING ALL
);
INSERT INTO "in#51_full"
SELECT * FROM "in#51"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO place"()
RETURNS SETOF "place"."place_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "place"
("source_id", "coordinates_id", "stateprovince", "county", "country")
SELECT
25 AS "source_id"
, "in#51"."coordinates_pkeys#1.out.coordinates_id"
, "in#51"."ACAD.Specimen.stateProvince"
, "in#51"."ACAD.Specimen.county"
, "in#51"."ACAD.Specimen.country"
FROM "in#51"
RETURNING "place_id"
$$;
CREATE TEMP TABLE "in#51_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO place"() AS "f" ("place_id")
# Caught exception: DuplicateKeyException: Violated @place_unique_within_creator_by_name@ constraint on columns: @source_id, continent, country, stateprovince, county, coordinates_id@
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#51_distinct" (
LIKE "in#51" INCLUDING ALL
);
INSERT INTO "in#51_distinct"
SELECT
DISTINCT ON ("in#51"."ACAD.Specimen.country", "in#51"."ACAD.Specimen.stateProvince", "in#51"."ACAD.Specimen.county", "in#51"."coordinates_pkeys#1.out.coordinates_id")
"in#51".*
FROM "in#51"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO place#1"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "place"%ROWTYPE;
BEGIN
FOR "row"."source_id", "row"."coordinates_id", "row"."stateprovince", "row"."county", "row"."country" IN
SELECT
25 AS "source_id"
, "in#51_distinct"."coordinates_pkeys#1.out.coordinates_id"
, "in#51_distinct"."ACAD.Specimen.stateProvince"
, "in#51_distinct"."ACAD.Specimen.county"
, "in#51_distinct"."ACAD.Specimen.country"
FROM "in#51_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "place"
("source_id", "coordinates_id", "stateprovince", "county", "country")
VALUES ("row"."source_id", "row"."coordinates_id", "row"."stateprovince", "row"."county", "row"."country")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
CREATE TEMP TABLE "rowcount#16" AS
SELECT * FROM "pg_temp"."INSERT INTO place#1"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "place_pkeys" AS
SELECT
"in#51"."row_num"
, "place"."place_id" AS "out.place_id"
FROM "in#51"
JOIN "place" ON
"place"."source_id" = 25
AND COALESCE("place"."continent", CAST('\N' AS text)) = COALESCE(NULL, CAST('\N' AS text))
AND COALESCE("place"."country", CAST('\N' AS text)) = COALESCE("in#51"."ACAD.Specimen.country", CAST('\N' AS text))
AND COALESCE("place"."stateprovince", CAST('\N' AS text)) = COALESCE("in#51"."ACAD.Specimen.stateProvince", CAST('\N' AS text))
AND COALESCE("place"."county", CAST('\N' AS text)) = COALESCE("in#51"."ACAD.Specimen.county", CAST('\N' AS text))
AND COALESCE("place"."coordinates_id", CAST(2147483647 AS integer)) = COALESCE("in#51"."coordinates_pkeys#1.out.coordinates_id", CAST(2147483647 AS integer))
# Setting pkeys of missing rows to @None@
INSERT INTO "place_pkeys"
("row_num", "out.place_id")
SELECT
"in#51_full"."row_num"
, NULL AS "out.place_id"
FROM "in#51_full"
LEFT JOIN "place_pkeys" ON "place_pkeys"."row_num" = "in#51_full"."row_num"
WHERE "place_pkeys"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"place"@:
|Output|Input|
|@'coordinates_id'@|@"coordinates_pkeys"."out.coordinates_id"@|
|@'matched_place_id'@|@"place_pkeys"."out.place_id"@|
|@'country'@|@"ACAD.Specimen"."country"@|
|@'stateprovince'@|@"ACAD.Specimen"."stateProvince"@|
|@'county'@|@"ACAD.Specimen"."county"@|
|@'continent'@|@"ACAD.Specimen"."continent"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#52" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "coordinates_pkeys"."out.coordinates_id" AS "coordinates_pkeys.out.coordinates_id"
, "place_pkeys"."out.place_id" AS "place_pkeys.out.place_id"
, "ACAD.Specimen"."country" AS "ACAD.Specimen.country"
, "ACAD.Specimen"."stateProvince" AS "ACAD.Specimen.stateProvince"
, "ACAD.Specimen"."county" AS "ACAD.Specimen.county"
, "ACAD.Specimen"."continent" AS "ACAD.Specimen.continent"
FROM "ACAD.Specimen"
JOIN "coordinates_pkeys" USING ("row_num")
JOIN "place_pkeys" USING ("row_num")
ORDER BY row_num
Temp table: @"in#52"@
CREATE TEMP TABLE "in#52_full" (
LIKE "in#52" INCLUDING ALL
);
INSERT INTO "in#52_full"
SELECT * FROM "in#52"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO place#2"()
RETURNS SETOF "place"."place_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "place"
("coordinates_id", "matched_place_id", "country", "stateprovince", "county", "continent")
SELECT
"in#52"."coordinates_pkeys.out.coordinates_id"
, "in#52"."place_pkeys.out.place_id"
, "in#52"."ACAD.Specimen.country"
, "in#52"."ACAD.Specimen.stateProvince"
, "in#52"."ACAD.Specimen.county"
, "in#52"."ACAD.Specimen.continent"
FROM "in#52"
RETURNING "place_id"
$$;
CREATE TEMP TABLE "in#52_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO place#2"() AS "f" ("place_id")
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@
# ********** New iteration **********
# Inserting these input columns into @"source"@:
|Output|Input|
|@'shortname'@|@'ACAD'@|
# Trying to insert new rows
INSERT INTO "source"
("shortname")
SELECT 'ACAD' AS "shortname"
RETURNING "source_id"
# Caught exception: DuplicateKeyException: Violated @source_unique@ constraint on columns: @shortname@
# Ignoring existing rows, comparing on these columns:
Output: Input
"source"."shortname": 'ACAD'
SELECT "source"."source_id" FROM "source"
WHERE "source"."shortname" = 'ACAD'
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO place#3"()
RETURNS SETOF "place"."place_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "place"
("coordinates_id", "matched_place_id", "country", "stateprovince", "county", "continent", "source_id")
SELECT
"in#52"."coordinates_pkeys.out.coordinates_id"
, "in#52"."place_pkeys.out.place_id"
, "in#52"."ACAD.Specimen.country"
, "in#52"."ACAD.Specimen.stateProvince"
, "in#52"."ACAD.Specimen.county"
, "in#52"."ACAD.Specimen.continent"
, 1 AS "source_id"
FROM "in#52"
RETURNING "place_id"
$$;
CREATE TEMP TABLE "in#52_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO place#3"() AS "f" ("place_id")
# Caught exception: DuplicateKeyException: Violated @place_unique_within_creator_by_name@ constraint on columns: @source_id, continent, country, stateprovince, county, coordinates_id@
# Ignoring existing rows, comparing on these columns:
CREATE TEMP TABLE "in#52_distinct" (
LIKE "in#52" INCLUDING ALL
);
INSERT INTO "in#52_distinct"
SELECT
DISTINCT ON ("in#52"."ACAD.Specimen.continent", "in#52"."ACAD.Specimen.country", "in#52"."ACAD.Specimen.stateProvince", "in#52"."ACAD.Specimen.county", "in#52"."coordinates_pkeys.out.coordinates_id")
"in#52".*
FROM "in#52"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO place#4"()
RETURNS SETOF unknown
LANGUAGE plpgsql
AS $$
DECLARE
"row" "place"%ROWTYPE;
BEGIN
FOR "row"."coordinates_id", "row"."matched_place_id", "row"."country", "row"."stateprovince", "row"."county", "row"."continent", "row"."source_id" IN
SELECT
"in#52_distinct"."coordinates_pkeys.out.coordinates_id"
, "in#52_distinct"."place_pkeys.out.place_id"
, "in#52_distinct"."ACAD.Specimen.country"
, "in#52_distinct"."ACAD.Specimen.stateProvince"
, "in#52_distinct"."ACAD.Specimen.county"
, "in#52_distinct"."ACAD.Specimen.continent"
, 1 AS "source_id"
FROM "in#52_distinct"
ORDER BY row_num
LOOP
BEGIN
RETURN QUERY
INSERT INTO "place"
("coordinates_id", "matched_place_id", "country", "stateprovince", "county", "continent", "source_id")
VALUES ("row"."coordinates_id", "row"."matched_place_id", "row"."country", "row"."stateprovince", "row"."county", "row"."continent", "row"."source_id")
RETURNING NULL AS "NULL"
;
EXCEPTION
WHEN unique_violation THEN NULL;
END;
END LOOP;
END;
$$;
CREATE TEMP TABLE "rowcount#17" AS
SELECT * FROM "pg_temp"."INSERT INTO place#4"() AS "f" ("NULL")
# Getting output table pkeys of existing/inserted rows
CREATE TEMP TABLE "place_pkeys#1" AS
SELECT
"in#52"."row_num"
, "place"."place_id" AS "out.place_id"
FROM "in#52"
JOIN "place" ON
"place"."source_id" = 1
AND COALESCE("place"."continent", CAST('\N' AS text)) = COALESCE("in#52"."ACAD.Specimen.continent", CAST('\N' AS text))
AND COALESCE("place"."country", CAST('\N' AS text)) = COALESCE("in#52"."ACAD.Specimen.country", CAST('\N' AS text))
AND COALESCE("place"."stateprovince", CAST('\N' AS text)) = COALESCE("in#52"."ACAD.Specimen.stateProvince", CAST('\N' AS text))
AND COALESCE("place"."county", CAST('\N' AS text)) = COALESCE("in#52"."ACAD.Specimen.county", CAST('\N' AS text))
AND COALESCE("place"."coordinates_id", CAST(2147483647 AS integer)) = COALESCE("in#52"."coordinates_pkeys.out.coordinates_id", CAST(2147483647 AS integer))
# Setting pkeys of missing rows to @None@
INSERT INTO "place_pkeys#1"
("row_num", "out.place_id")
SELECT
"in#52_full"."row_num"
, NULL AS "out.place_id"
FROM "in#52_full"
LEFT JOIN "place_pkeys#1" ON "place_pkeys#1"."row_num" = "in#52_full"."row_num"
WHERE "place_pkeys#1"."row_num" IS NULL
# ********** New iteration **********
# Inserting these input columns into @"locationplace"@:
|Output|Input|
|@'place_id'@|@"place_pkeys#1"."out.place_id"@|
|@'location_id'@|@"location_pkeys"."out.location_id"@|
# Joining together input tables into temp table
CREATE TEMP TABLE "in#53" AS
SELECT
"ACAD.Specimen"."row_num" AS "row_num"
, "place_pkeys#1"."out.place_id" AS "place_pkeys#1.out.place_id"
, "location_pkeys"."out.location_id" AS "location_pkeys.out.location_id"
FROM "ACAD.Specimen"
JOIN "place_pkeys#1" USING ("row_num")
JOIN "location_pkeys" USING ("row_num")
ORDER BY row_num
Temp table: @"in#53"@
CREATE TEMP TABLE "in#53_full" (
LIKE "in#53" INCLUDING ALL
);
INSERT INTO "in#53_full"
SELECT * FROM "in#53"
# Trying to insert new rows
CREATE FUNCTION "pg_temp"."INSERT INTO locationplace"()
RETURNS SETOF "locationplace"."locationplace_id"%TYPE
LANGUAGE sql
AS $$
INSERT INTO "locationplace"
("place_id", "location_id")
SELECT
"in#53"."place_pkeys#1.out.place_id"
, "in#53"."location_pkeys.out.location_id"
FROM "in#53"
RETURNING "locationplace_id"
$$;
CREATE TEMP TABLE "in#53_insert_out_pkeys" AS
SELECT * FROM "pg_temp"."INSERT INTO locationplace"() AS "f" ("locationplace_id")
# Getting input table pkeys of inserted rows
CREATE TEMP TABLE "in#53_insert_in_pkeys" AS
SELECT "row_num" FROM "in#53"
# Combining output and input pkeys in inserted order
CREATE TEMP TABLE "locationplace_pkeys" AS
SELECT
"in#53_insert_in_pkeys"."row_num"
, "in#53_insert_out_pkeys"."locationplace_id" AS "out.locationplace_id"
FROM "in#53_insert_in_pkeys"
JOIN "in#53_insert_out_pkeys" USING ("_row_num")
# Setting pkeys of missing rows to @None@
INSERT INTO "locationplace_pkeys"
("row_num", "out.locationplace_id")
SELECT
"in#53_full"."row_num"
, NULL AS "out.locationplace_id"
FROM "in#53_full"
LEFT JOIN "locationplace_pkeys" ON "locationplace_pkeys"."row_num" = "in#53_full"."row_num"
WHERE "locationplace_pkeys"."row_num" IS NULL
Inserted 1325 new rows into database
Processed 100 input rows
Encountered 0 error(s)
11.82 real 4.23 user 0.45 sys