Revision 7177
Added by Aaron Marcuse-Kubitza almost 12 years ago
| inputs/ACAD/Specimen/logs/steps.by_col.log.sql | ||
|---|---|---|
| 1 |
+ date |
|
| 2 |
Fri Jan 11 05:47:34 PST 2013 |
|
| 3 |
+ time env commit=1 env in_database=vegbien in_schema=ACAD in_table=Specimen out_database=vegbien ../../map Specimen/VegBIEN.csv |
|
| 4 |
Processing input rows 0-99 |
|
| 5 |
Using 1 parallel CPUs |
|
| 6 |
Connecting to PostgreSQL database vegbien |
|
| 7 |
<pre><code class="SQL"> |
|
| 8 |
SELECT * FROM "ACAD"."Specimen" |
|
| 9 |
LIMIT 0 |
|
| 10 |
</code></pre>Put template: |
|
| 11 |
<VegBIEN> |
|
| 12 |
<_setDefault id="-1"> |
|
| 13 |
<source_id><source><shortname>ACAD</shortname></source></source_id> |
|
| 14 |
<path> |
|
| 15 |
<_simplifyPath> |
|
| 16 |
<next>parent_id</next> |
|
| 17 |
<path> |
|
| 18 |
<location> |
|
| 19 |
<authorlocationcode> |
|
| 20 |
<_alt> |
|
| 21 |
<1>$id</1> |
|
| 22 |
<2> |
|
| 23 |
<_join> |
|
| 24 |
<1>$institutionCode</1> |
|
| 25 |
<2> |
|
| 26 |
<_join> |
|
| 27 |
<1>$collectionCode</1> |
|
| 28 |
<2>$collectionID</2> |
|
| 29 |
</_join> |
|
| 30 |
</2> |
|
| 31 |
<3>$catalogNumber</3> |
|
| 32 |
</_join> |
|
| 33 |
</2> |
|
| 34 |
</_alt> |
|
| 35 |
</authorlocationcode> |
|
| 36 |
<accessconditions>$rights</accessconditions> |
|
| 37 |
<elevation_m> |
|
| 38 |
<_units> |
|
| 39 |
<to>m</to> |
|
| 40 |
<to></to> |
|
| 41 |
<value>$verbatimElevation</value> |
|
| 42 |
</_units> |
|
| 43 |
</elevation_m> |
|
| 44 |
<iscultivated> |
|
| 45 |
<_locationnarrative_is_cultivated> |
|
| 46 |
<locationnarrative> |
|
| 47 |
<_join> |
|
| 48 |
<1>$locality</1> |
|
| 49 |
<3>$habitat</3> |
|
| 50 |
</_join> |
|
| 51 |
</locationnarrative> |
|
| 52 |
</_locationnarrative_is_cultivated> |
|
| 53 |
</iscultivated> |
|
| 54 |
<locationevent> |
|
| 55 |
<obsenddate><_dateRangeEnd><value>$eventDate</value></_dateRangeEnd></obsenddate> |
|
| 56 |
<obsstartdate><_dateRangeStart><value>$eventDate</value></_dateRangeStart></obsstartdate> |
|
| 57 |
<taxonoccurrence> |
|
| 58 |
<aggregateoccurrence> |
|
| 59 |
<plantobservation> |
|
| 60 |
<collectionnumber>$recordNumber</collectionnumber> |
|
| 61 |
<specimenreplicate> |
|
| 62 |
<catalognumber_dwc>$catalogNumber</catalognumber_dwc> |
|
| 63 |
<collectioncode_dwc> |
|
| 64 |
<_join> |
|
| 65 |
<1>$collectionCode</1> |
|
| 66 |
<2>$collectionID</2> |
|
| 67 |
</_join> |
|
| 68 |
</collectioncode_dwc> |
|
| 69 |
<institution_id> |
|
| 70 |
<sourcelist> |
|
| 71 |
<name>$institutionCode</name> |
|
| 72 |
<sourcename> |
|
| 73 |
<name> |
|
| 74 |
<_split> |
|
| 75 |
<separator>, </separator> |
|
| 76 |
<value>$institutionCode</value> |
|
| 77 |
</_split> |
|
| 78 |
</name> |
|
| 79 |
</sourcename> |
|
| 80 |
</sourcelist> |
|
| 81 |
</institution_id> |
|
| 82 |
<sourceaccessioncode>$id</sourceaccessioncode> |
|
| 83 |
</specimenreplicate> |
|
| 84 |
</plantobservation> |
|
| 85 |
</aggregateoccurrence> |
|
| 86 |
<collector_id><party><fullname>$recordedBy</fullname></party></collector_id> |
|
| 87 |
<sourceaccessioncode>$id</sourceaccessioncode> |
|
| 88 |
<taxondetermination> |
|
| 89 |
<party_id><party><fullname>$identifiedBy</fullname></party></party_id> |
|
| 90 |
<taxonverbatim_id> |
|
| 91 |
<taxonverbatim> |
|
| 92 |
<taxonlabel_id> |
|
| 93 |
<taxonlabel> |
|
| 94 |
<creationdate><_dateRangeStart><value>$dateIdentified</value></_dateRangeStart></creationdate> |
|
| 95 |
<parent_id> |
|
| 96 |
<taxonlabel> |
|
| 97 |
<rank>$taxonRank</rank> |
|
| 98 |
<parent_id> |
|
| 99 |
<taxonlabel> |
|
| 100 |
<rank>cultivar</rank> |
|
| 101 |
<parent_id> |
|
| 102 |
<taxonlabel> |
|
| 103 |
<rank>forma</rank> |
|
| 104 |
<parent_id> |
|
| 105 |
<taxonlabel> |
|
| 106 |
<rank>variety</rank> |
|
| 107 |
<parent_id> |
|
| 108 |
<taxonlabel> |
|
| 109 |
<rank>subspecies</rank> |
|
| 110 |
<parent_id> |
|
| 111 |
<taxonlabel> |
|
| 112 |
<rank>species</rank> |
|
| 113 |
<taxonepithet>$specificEpithet</taxonepithet> |
|
| 114 |
<parent_id> |
|
| 115 |
<taxonlabel> |
|
| 116 |
<rank>genus</rank> |
|
| 117 |
<taxonepithet>$genus</taxonepithet> |
|
| 118 |
<parent_id> |
|
| 119 |
<taxonlabel> |
|
| 120 |
<rank>family</rank> |
|
| 121 |
<taxonepithet>$family</taxonepithet> |
|
| 122 |
<parent_id> |
|
| 123 |
<taxonlabel> |
|
| 124 |
<rank>order</rank> |
|
| 125 |
<taxonepithet>$order</taxonepithet> |
|
| 126 |
<parent_id> |
|
| 127 |
<taxonlabel> |
|
| 128 |
<rank>class</rank> |
|
| 129 |
<taxonepithet>$class</taxonepithet> |
|
| 130 |
<parent_id> |
|
| 131 |
<taxonlabel> |
|
| 132 |
<rank>phylum</rank> |
|
| 133 |
<taxonepithet>$phylum</taxonepithet> |
|
| 134 |
<parent_id> |
|
| 135 |
<taxonlabel> |
|
| 136 |
<rank>kingdom</rank> |
|
| 137 |
<taxonepithet>$kingdom</taxonepithet> |
|
| 138 |
</taxonlabel> |
|
| 139 |
</parent_id> |
|
| 140 |
</taxonlabel> |
|
| 141 |
</parent_id> |
|
| 142 |
</taxonlabel> |
|
| 143 |
</parent_id> |
|
| 144 |
</taxonlabel> |
|
| 145 |
</parent_id> |
|
| 146 |
</taxonlabel> |
|
| 147 |
</parent_id> |
|
| 148 |
</taxonlabel> |
|
| 149 |
</parent_id> |
|
| 150 |
</taxonlabel> |
|
| 151 |
</parent_id> |
|
| 152 |
</taxonlabel> |
|
| 153 |
</parent_id> |
|
| 154 |
</taxonlabel> |
|
| 155 |
</parent_id> |
|
| 156 |
</taxonlabel> |
|
| 157 |
</parent_id> |
|
| 158 |
</taxonlabel> |
|
| 159 |
</parent_id> |
|
| 160 |
<taxonepithet>$infraspecificEpithet</taxonepithet> |
|
| 161 |
</taxonlabel> |
|
| 162 |
</parent_id> |
|
| 163 |
<rank>$taxonRank</rank> |
|
| 164 |
<taxonomicname> |
|
| 165 |
<_merge_prefix> |
|
| 166 |
<prefix><_taxon_family_require_std><family>$family</family></_taxon_family_require_std></prefix> |
|
| 167 |
<value> |
|
| 168 |
<_alt> |
|
| 169 |
<1>$scientificName</1> |
|
| 170 |
<2> |
|
| 171 |
<_join_words> |
|
| 172 |
<1> |
|
| 173 |
<_join_words> |
|
| 174 |
<2>$genus</2> |
|
| 175 |
<3>$specificEpithet</3> |
|
| 176 |
<5> |
|
| 177 |
<_join_words> |
|
| 178 |
<1> |
|
| 179 |
<_if name="if has infraspecificEpithet"> |
|
| 180 |
<cond>$infraspecificEpithet</cond> |
|
| 181 |
<then>$taxonRank</then> |
|
| 182 |
</_if> |
|
| 183 |
</1> |
|
| 184 |
<2>$infraspecificEpithet</2> |
|
| 185 |
</_join_words> |
|
| 186 |
</5> |
|
| 187 |
</_join_words> |
|
| 188 |
</1> |
|
| 189 |
<2>$scientificNameAuthorship</2> |
|
| 190 |
</_join_words> |
|
| 191 |
</2> |
|
| 192 |
</_alt> |
|
| 193 |
</value> |
|
| 194 |
</_merge_prefix> |
|
| 195 |
</taxonomicname> |
|
| 196 |
</taxonlabel> |
|
| 197 |
</taxonlabel_id> |
|
| 198 |
<author>$scientificNameAuthorship</author> |
|
| 199 |
<family>$family</family> |
|
| 200 |
<genus>$genus</genus> |
|
| 201 |
<specific_epithet>$specificEpithet</specific_epithet> |
|
| 202 |
<taxonomicname>$scientificName</taxonomicname> |
|
| 203 |
<verbatimrank>$taxonRank</verbatimrank> |
|
| 204 |
</taxonverbatim> |
|
| 205 |
</taxonverbatim_id> |
|
| 206 |
<determinationdate><_dateRangeStart><value>$dateIdentified</value></_dateRangeStart></determinationdate> |
|
| 207 |
</taxondetermination> |
|
| 208 |
</taxonoccurrence> |
|
| 209 |
</locationevent> |
|
| 210 |
<locationnarrative> |
|
| 211 |
<_merge> |
|
| 212 |
<1>$locality</1> |
|
| 213 |
<3> |
|
| 214 |
<_label> |
|
| 215 |
<label>habitat</label> |
|
| 216 |
<value>$habitat</value> |
|
| 217 |
</_label> |
|
| 218 |
</3> |
|
| 219 |
</_merge> |
|
| 220 |
</locationnarrative> |
|
| 221 |
<locationplace> |
|
| 222 |
<place_id> |
|
| 223 |
<place> |
|
| 224 |
<coordinates_id> |
|
| 225 |
<coordinates> |
|
| 226 |
<latitude_deg> |
|
| 227 |
<_nullIf> |
|
| 228 |
<null>0</null> |
|
| 229 |
<type>float</type> |
|
| 230 |
<value>$decimalLatitude</value> |
|
| 231 |
</_nullIf> |
|
| 232 |
</latitude_deg> |
|
| 233 |
<longitude_deg> |
|
| 234 |
<_nullIf> |
|
| 235 |
<null>0</null> |
|
| 236 |
<type>float</type> |
|
| 237 |
<value>$decimalLongitude</value> |
|
| 238 |
</_nullIf> |
|
| 239 |
</longitude_deg> |
|
| 240 |
</coordinates> |
|
| 241 |
</coordinates_id> |
|
| 242 |
<continent>$continent</continent> |
|
| 243 |
<country>$country</country> |
|
| 244 |
<county>$county</county> |
|
| 245 |
<matched_place_id> |
|
| 246 |
<place> |
|
| 247 |
<source_id><source><shortname>geoscrub</shortname></source></source_id> |
|
| 248 |
<coordinates_id> |
|
| 249 |
<coordinates> |
|
| 250 |
<source_id><source><shortname>geoscrub</shortname></source></source_id> |
|
| 251 |
<latitude_deg> |
|
| 252 |
<_nullIf> |
|
| 253 |
<null>0</null> |
|
| 254 |
<type>float</type> |
|
| 255 |
<value>$decimalLatitude</value> |
|
| 256 |
</_nullIf> |
|
| 257 |
</latitude_deg> |
|
| 258 |
<longitude_deg> |
|
| 259 |
<_nullIf> |
|
| 260 |
<null>0</null> |
|
| 261 |
<type>float</type> |
|
| 262 |
<value>$decimalLongitude</value> |
|
| 263 |
</_nullIf> |
|
| 264 |
</longitude_deg> |
|
| 265 |
</coordinates> |
|
| 266 |
</coordinates_id> |
|
| 267 |
<country>$country</country> |
|
| 268 |
<county>$county</county> |
|
| 269 |
<stateprovince>$stateProvince</stateprovince> |
|
| 270 |
</place> |
|
| 271 |
</matched_place_id> |
|
| 272 |
<stateprovince>$stateProvince</stateprovince> |
|
| 273 |
</place> |
|
| 274 |
</place_id> |
|
| 275 |
</locationplace> |
|
| 276 |
<verbatimelevation>$verbatimElevation</verbatimelevation> |
|
| 277 |
</location> |
|
| 278 |
</path> |
|
| 279 |
</_simplifyPath> |
|
| 280 |
</path> |
|
| 281 |
</_setDefault> |
|
| 282 |
</VegBIEN> |
|
| 283 |
Putting stripped: |
|
| 284 |
<VegBIEN> |
|
| 285 |
<_setDefault id="-1"> |
|
| 286 |
<source_id><source><shortname>ACAD</shortname></source></source_id> |
|
| 287 |
<path> |
|
| 288 |
<_simplifyPath> |
|
| 289 |
<next>parent_id</next> |
|
| 290 |
<path> |
|
| 291 |
<location> |
|
| 292 |
<authorlocationcode> |
|
| 293 |
<_alt> |
|
| 294 |
<1>$id</1> |
|
| 295 |
<2> |
|
| 296 |
<_join> |
|
| 297 |
<1>$institutionCode</1> |
|
| 298 |
<2> |
|
| 299 |
<_join> |
|
| 300 |
<1>$collectionCode</1> |
|
| 301 |
<2>$collectionID</2> |
|
| 302 |
</_join> |
|
| 303 |
</2> |
|
| 304 |
<3>$catalogNumber</3> |
|
| 305 |
</_join> |
|
| 306 |
</2> |
|
| 307 |
</_alt> |
|
| 308 |
</authorlocationcode> |
|
| 309 |
<accessconditions>$rights</accessconditions> |
|
| 310 |
<elevation_m>$verbatimElevation</elevation_m> |
|
| 311 |
<iscultivated> |
|
| 312 |
<_locationnarrative_is_cultivated> |
|
| 313 |
<locationnarrative> |
|
| 314 |
<_join> |
|
| 315 |
<1>$locality</1> |
|
| 316 |
<3>$habitat</3> |
|
| 317 |
</_join> |
|
| 318 |
</locationnarrative> |
|
| 319 |
</_locationnarrative_is_cultivated> |
|
| 320 |
</iscultivated> |
|
| 321 |
<locationevent> |
|
| 322 |
<obsenddate><_dateRangeEnd><value>$eventDate</value></_dateRangeEnd></obsenddate> |
|
| 323 |
<obsstartdate><_dateRangeStart><value>$eventDate</value></_dateRangeStart></obsstartdate> |
|
| 324 |
<taxonoccurrence> |
|
| 325 |
<aggregateoccurrence> |
|
| 326 |
<plantobservation> |
|
| 327 |
<collectionnumber>$recordNumber</collectionnumber> |
|
| 328 |
<specimenreplicate> |
|
| 329 |
<catalognumber_dwc>$catalogNumber</catalognumber_dwc> |
|
| 330 |
<collectioncode_dwc> |
|
| 331 |
<_join> |
|
| 332 |
<1>$collectionCode</1> |
|
| 333 |
<2>$collectionID</2> |
|
| 334 |
</_join> |
|
| 335 |
</collectioncode_dwc> |
|
| 336 |
<institution_id> |
|
| 337 |
<sourcelist> |
|
| 338 |
<name>$institutionCode</name> |
|
| 339 |
<sourcename> |
|
| 340 |
<name> |
|
| 341 |
<_split> |
|
| 342 |
<separator>, </separator> |
|
| 343 |
<value>$institutionCode</value> |
|
| 344 |
</_split> |
|
| 345 |
</name> |
|
| 346 |
</sourcename> |
|
| 347 |
</sourcelist> |
|
| 348 |
</institution_id> |
|
| 349 |
<sourceaccessioncode>$id</sourceaccessioncode> |
|
| 350 |
</specimenreplicate> |
|
| 351 |
</plantobservation> |
|
| 352 |
</aggregateoccurrence> |
|
| 353 |
<collector_id><party><fullname>$recordedBy</fullname></party></collector_id> |
|
| 354 |
<sourceaccessioncode>$id</sourceaccessioncode> |
|
| 355 |
<taxondetermination> |
|
| 356 |
<party_id><party><fullname>$identifiedBy</fullname></party></party_id> |
|
| 357 |
<taxonverbatim_id> |
|
| 358 |
<taxonverbatim> |
|
| 359 |
<taxonlabel_id> |
|
| 360 |
<taxonlabel> |
|
| 361 |
<creationdate><_dateRangeStart><value>$dateIdentified</value></_dateRangeStart></creationdate> |
|
| 362 |
<parent_id> |
|
| 363 |
<taxonlabel> |
|
| 364 |
<rank>$taxonRank</rank> |
|
| 365 |
<parent_id> |
|
| 366 |
<taxonlabel> |
|
| 367 |
<rank>cultivar</rank> |
|
| 368 |
<parent_id> |
|
| 369 |
<taxonlabel> |
|
| 370 |
<rank>forma</rank> |
|
| 371 |
<parent_id> |
|
| 372 |
<taxonlabel> |
|
| 373 |
<rank>variety</rank> |
|
| 374 |
<parent_id> |
|
| 375 |
<taxonlabel> |
|
| 376 |
<rank>subspecies</rank> |
|
| 377 |
<parent_id> |
|
| 378 |
<taxonlabel> |
|
| 379 |
<rank>species</rank> |
|
| 380 |
<taxonepithet>$specificEpithet</taxonepithet> |
|
| 381 |
<parent_id> |
|
| 382 |
<taxonlabel> |
|
| 383 |
<rank>genus</rank> |
|
| 384 |
<taxonepithet>$genus</taxonepithet> |
|
| 385 |
<parent_id> |
|
| 386 |
<taxonlabel> |
|
| 387 |
<rank>family</rank> |
|
| 388 |
<taxonepithet>$family</taxonepithet> |
|
| 389 |
<parent_id> |
|
| 390 |
<taxonlabel> |
|
| 391 |
<rank>order</rank> |
|
| 392 |
<taxonepithet>$order</taxonepithet> |
|
| 393 |
<parent_id> |
|
| 394 |
<taxonlabel> |
|
| 395 |
<rank>class</rank> |
|
| 396 |
<taxonepithet>$class</taxonepithet> |
|
| 397 |
<parent_id> |
|
| 398 |
<taxonlabel> |
|
| 399 |
<rank>phylum</rank> |
|
| 400 |
<taxonepithet>$phylum</taxonepithet> |
|
| 401 |
<parent_id> |
|
| 402 |
<taxonlabel> |
|
| 403 |
<rank>kingdom</rank> |
|
| 404 |
<taxonepithet>$kingdom</taxonepithet> |
|
| 405 |
</taxonlabel> |
|
| 406 |
</parent_id> |
|
| 407 |
</taxonlabel> |
|
| 408 |
</parent_id> |
|
| 409 |
</taxonlabel> |
|
| 410 |
</parent_id> |
|
| 411 |
</taxonlabel> |
|
| 412 |
</parent_id> |
|
| 413 |
</taxonlabel> |
|
| 414 |
</parent_id> |
|
| 415 |
</taxonlabel> |
|
| 416 |
</parent_id> |
|
| 417 |
</taxonlabel> |
|
| 418 |
</parent_id> |
|
| 419 |
</taxonlabel> |
|
| 420 |
</parent_id> |
|
| 421 |
</taxonlabel> |
|
| 422 |
</parent_id> |
|
| 423 |
</taxonlabel> |
|
| 424 |
</parent_id> |
|
| 425 |
</taxonlabel> |
|
| 426 |
</parent_id> |
|
| 427 |
<taxonepithet>$infraspecificEpithet</taxonepithet> |
|
| 428 |
</taxonlabel> |
|
| 429 |
</parent_id> |
|
| 430 |
<rank>$taxonRank</rank> |
|
| 431 |
<taxonomicname> |
|
| 432 |
<_merge_prefix> |
|
| 433 |
<prefix><_taxon_family_require_std><family>$family</family></_taxon_family_require_std></prefix> |
|
| 434 |
<value> |
|
| 435 |
<_alt> |
|
| 436 |
<1>$scientificName</1> |
|
| 437 |
<2> |
|
| 438 |
<_join_words> |
|
| 439 |
<1> |
|
| 440 |
<_join_words> |
|
| 441 |
<2>$genus</2> |
|
| 442 |
<3>$specificEpithet</3> |
|
| 443 |
<5> |
|
| 444 |
<_join_words> |
|
| 445 |
<1> |
|
| 446 |
<_if name="if has infraspecificEpithet"> |
|
| 447 |
<cond>$infraspecificEpithet</cond> |
|
| 448 |
<then>$taxonRank</then> |
|
| 449 |
</_if> |
|
| 450 |
</1> |
|
| 451 |
<2>$infraspecificEpithet</2> |
|
| 452 |
</_join_words> |
|
| 453 |
</5> |
|
| 454 |
</_join_words> |
|
| 455 |
</1> |
|
| 456 |
<2>$scientificNameAuthorship</2> |
|
| 457 |
</_join_words> |
|
| 458 |
</2> |
|
| 459 |
</_alt> |
|
| 460 |
</value> |
|
| 461 |
</_merge_prefix> |
|
| 462 |
</taxonomicname> |
|
| 463 |
</taxonlabel> |
|
| 464 |
</taxonlabel_id> |
|
| 465 |
<author>$scientificNameAuthorship</author> |
|
| 466 |
<family>$family</family> |
|
| 467 |
<genus>$genus</genus> |
|
| 468 |
<specific_epithet>$specificEpithet</specific_epithet> |
|
| 469 |
<taxonomicname>$scientificName</taxonomicname> |
|
| 470 |
<verbatimrank>$taxonRank</verbatimrank> |
|
| 471 |
</taxonverbatim> |
|
| 472 |
</taxonverbatim_id> |
|
| 473 |
<determinationdate><_dateRangeStart><value>$dateIdentified</value></_dateRangeStart></determinationdate> |
|
| 474 |
</taxondetermination> |
|
| 475 |
</taxonoccurrence> |
|
| 476 |
</locationevent> |
|
| 477 |
<locationnarrative> |
|
| 478 |
<_merge> |
|
| 479 |
<1>$locality</1> |
|
| 480 |
<3> |
|
| 481 |
<_label> |
|
| 482 |
<label>habitat</label> |
|
| 483 |
<value>$habitat</value> |
|
| 484 |
</_label> |
|
| 485 |
</3> |
|
| 486 |
</_merge> |
|
| 487 |
</locationnarrative> |
|
| 488 |
<locationplace> |
|
| 489 |
<place_id> |
|
| 490 |
<place> |
|
| 491 |
<coordinates_id> |
|
| 492 |
<coordinates> |
|
| 493 |
<latitude_deg> |
|
| 494 |
<_nullIf> |
|
| 495 |
<null>0</null> |
|
| 496 |
<type>float</type> |
|
| 497 |
<value>$decimalLatitude</value> |
|
| 498 |
</_nullIf> |
|
| 499 |
</latitude_deg> |
|
| 500 |
<longitude_deg> |
|
| 501 |
<_nullIf> |
|
| 502 |
<null>0</null> |
|
| 503 |
<type>float</type> |
|
| 504 |
<value>$decimalLongitude</value> |
|
| 505 |
</_nullIf> |
|
| 506 |
</longitude_deg> |
|
| 507 |
</coordinates> |
|
| 508 |
</coordinates_id> |
|
| 509 |
<continent>$continent</continent> |
|
| 510 |
<country>$country</country> |
|
| 511 |
<county>$county</county> |
|
| 512 |
<matched_place_id> |
|
| 513 |
<place> |
|
| 514 |
<source_id><source><shortname>geoscrub</shortname></source></source_id> |
|
| 515 |
<coordinates_id> |
|
| 516 |
<coordinates> |
|
| 517 |
<source_id><source><shortname>geoscrub</shortname></source></source_id> |
|
| 518 |
<latitude_deg> |
|
| 519 |
<_nullIf> |
|
| 520 |
<null>0</null> |
|
| 521 |
<type>float</type> |
|
| 522 |
<value>$decimalLatitude</value> |
|
| 523 |
</_nullIf> |
|
| 524 |
</latitude_deg> |
|
| 525 |
<longitude_deg> |
|
| 526 |
<_nullIf> |
|
| 527 |
<null>0</null> |
|
| 528 |
<type>float</type> |
|
| 529 |
<value>$decimalLongitude</value> |
|
| 530 |
</_nullIf> |
|
| 531 |
</longitude_deg> |
|
| 532 |
</coordinates> |
|
| 533 |
</coordinates_id> |
|
| 534 |
<country>$country</country> |
|
| 535 |
<county>$county</county> |
|
| 536 |
<stateprovince>$stateProvince</stateprovince> |
|
| 537 |
</place> |
|
| 538 |
</matched_place_id> |
|
| 539 |
<stateprovince>$stateProvince</stateprovince> |
|
| 540 |
</place> |
|
| 541 |
</place_id> |
|
| 542 |
</locationplace> |
|
| 543 |
<verbatimelevation>$verbatimElevation</verbatimelevation> |
|
| 544 |
</location> |
|
| 545 |
</path> |
|
| 546 |
</_simplifyPath> |
|
| 547 |
</path> |
|
| 548 |
</_setDefault> |
|
| 549 |
</VegBIEN> |
|
| 550 |
********** Partition: rows 1-100 ********** |
|
| 551 |
<pre><code class="SQL"> |
|
| 552 |
CREATE TEMP TABLE "ACAD.Specimen" ( |
|
| 553 |
LIKE "ACAD"."Specimen" INCLUDING ALL |
|
| 554 |
); |
|
| 555 |
</code></pre><pre><code class="SQL"> |
|
| 556 |
INSERT INTO "ACAD.Specimen" |
|
| 557 |
SELECT * FROM "ACAD"."Specimen" |
|
| 558 |
ORDER BY row_num |
|
| 559 |
LIMIT 100 |
|
| 560 |
</code></pre> |
|
| 561 |
# ********** New iteration ********** |
|
| 562 |
# Inserting these input columns into @"_join"@: |
|
| 563 |
|
|
| 564 |
|Output|Input| |
|
| 565 |
|@'1'@|@"ACAD.Specimen"."collectionCode"@| |
|
| 566 |
|@'2'@|@"ACAD.Specimen"."collectionID"@| |
|
| 567 |
|
|
| 568 |
# Joining together input tables into temp table |
|
| 569 |
<pre><code class="SQL"> |
|
| 570 |
CREATE TEMP TABLE "in" AS |
|
| 571 |
SELECT |
|
| 572 |
"ACAD.Specimen"."row_num" AS "row_num" |
|
| 573 |
, "ACAD.Specimen"."collectionCode" AS "ACAD.Specimen.collectionCode" |
|
| 574 |
, "ACAD.Specimen"."collectionID" AS "ACAD.Specimen.collectionID" |
|
| 575 |
FROM "ACAD.Specimen" |
|
| 576 |
ORDER BY row_num |
|
| 577 |
</code></pre>Temp table: @"in"@ |
|
| 578 |
# Defining wrapper function |
|
| 579 |
<pre><code class="SQL"> |
|
| 580 |
CREATE TEMP TABLE "_join(1=collectionCode, 2=collectionID)" AS |
|
| 581 |
SELECT |
|
| 582 |
"in"."row_num" |
|
| 583 |
, "_join"("1" := "in"."ACAD.Specimen.collectionCode", "2" := "in"."ACAD.Specimen.collectionID") AS "result"
|
|
| 584 |
FROM "in" |
|
| 585 |
LIMIT 0 |
|
| 586 |
</code></pre><pre><code class="SQL"> |
|
| 587 |
CREATE FUNCTION "pg_temp"."_join(1=collectionCode, 2=collectionID)_wrap"() |
|
| 588 |
RETURNS SETOF "_join(1=collectionCode, 2=collectionID)" |
|
| 589 |
LANGUAGE plpgsql |
|
| 590 |
AS $$ |
|
| 591 |
DECLARE |
|
| 592 |
"row" "in"%ROWTYPE; |
|
| 593 |
BEGIN |
|
| 594 |
FOR "row" IN |
|
| 595 |
SELECT * FROM "in" |
|
| 596 |
LOOP |
|
| 597 |
BEGIN |
|
| 598 |
BEGIN |
|
| 599 |
RETURN QUERY |
|
| 600 |
SELECT |
|
| 601 |
"row"."row_num" |
|
| 602 |
, "_join"("1" := "row"."ACAD.Specimen.collectionCode", "2" := "row"."ACAD.Specimen.collectionID")
|
|
| 603 |
; |
|
| 604 |
EXCEPTION |
|
| 605 |
WHEN internal_error THEN |
|
| 606 |
-- Handle PL/Python exceptions |
|
| 607 |
DECLARE |
|
| 608 |
matches text[] := regexp_matches(SQLERRM, |
|
| 609 |
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n |
|
| 610 |
exc_name text := matches[1]; |
|
| 611 |
msg text := matches[2]; |
|
| 612 |
BEGIN |
|
| 613 |
/* Re-raise PL/Python exceptions with the PL/Python prefix removed. |
|
| 614 |
This allows the exception to be parsed like a native exception. |
|
| 615 |
Always raise as data_exception so it goes in the errors table. */ |
|
| 616 |
IF exc_name IS NOT NULL THEN |
|
| 617 |
RAISE data_exception USING MESSAGE = msg; |
|
| 618 |
-- Re-raise non-PL/Python exceptions |
|
| 619 |
ELSE |
|
| 620 |
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; |
|
| 621 |
END IF; |
|
| 622 |
END; |
|
| 623 |
END; |
|
| 624 |
EXCEPTION |
|
| 625 |
WHEN data_exception THEN |
|
| 626 |
-- Save error in errors table. |
|
| 627 |
DECLARE |
|
| 628 |
error_code text := SQLSTATE; |
|
| 629 |
error text := SQLERRM; |
|
| 630 |
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))], ',');
|
|
| 631 |
"column" text; |
|
| 632 |
BEGIN |
|
| 633 |
-- Insert the value and error for *each* source column. |
|
| 634 |
FOR "column" IN |
|
| 635 |
SELECT * FROM (VALUES ('collectionCode,collectionID')) AS "c"
|
|
| 636 |
LOOP |
|
| 637 |
BEGIN |
|
| 638 |
INSERT INTO "ACAD"."Specimen.errors" |
|
| 639 |
("column", "value", "error_code", "error")
|
|
| 640 |
VALUES ("column", "value", "error_code", "error");
|
|
| 641 |
EXCEPTION |
|
| 642 |
WHEN unique_violation THEN NULL; |
|
| 643 |
END; |
|
| 644 |
END LOOP; |
|
| 645 |
|
|
| 646 |
END; |
|
| 647 |
|
|
| 648 |
RETURN QUERY |
|
| 649 |
SELECT |
|
| 650 |
"row"."row_num" |
|
| 651 |
, CAST(NULL AS text) |
|
| 652 |
; |
|
| 653 |
END; |
|
| 654 |
END LOOP; |
|
| 655 |
END; |
|
| 656 |
|
|
| 657 |
$$; |
|
| 658 |
</code></pre> |
|
| 659 |
# Calling function |
|
| 660 |
<pre><code class="SQL"> |
|
| 661 |
INSERT INTO "_join(1=collectionCode, 2=collectionID)" |
|
| 662 |
("row_num", "result")
|
|
| 663 |
SELECT * FROM "pg_temp"."_join(1=collectionCode, 2=collectionID)_wrap"() |
|
| 664 |
</code></pre> |
|
| 665 |
# ********** New iteration ********** |
|
| 666 |
# Inserting these input columns into @"_join"@: |
|
| 667 |
|
|
| 668 |
|Output|Input| |
|
| 669 |
|@'1'@|@"ACAD.Specimen"."institutionCode"@| |
|
| 670 |
|@'3'@|@"ACAD.Specimen"."catalogNumber"@| |
|
| 671 |
|@'2'@|@"_join(1=collectionCode, 2=collectionID)"."result"@| |
|
| 672 |
|
|
| 673 |
# Joining together input tables into temp table |
|
| 674 |
<pre><code class="SQL"> |
|
| 675 |
CREATE TEMP TABLE "in#1" AS |
|
| 676 |
SELECT |
|
| 677 |
"ACAD.Specimen"."row_num" AS "row_num" |
|
| 678 |
, "ACAD.Specimen"."institutionCode" AS "ACAD.Specimen.institutionCode" |
|
| 679 |
, "ACAD.Specimen"."catalogNumber" AS "ACAD.Specimen.catalogNumber" |
|
| 680 |
, "_join(1=collectionCode, 2=collectionID)"."result" AS "_join(1=collectionCode, 2=collectionID).result" |
|
| 681 |
FROM "ACAD.Specimen" |
|
| 682 |
JOIN "_join(1=collectionCode, 2=collectionID)" USING ("row_num")
|
|
| 683 |
ORDER BY row_num |
|
| 684 |
</code></pre>Temp table: @"in#1"@ |
|
| 685 |
# Defining wrapper function |
|
| 686 |
<pre><code class="SQL"> |
|
| 687 |
CREATE TEMP TABLE "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC" AS |
|
| 688 |
SELECT |
|
| 689 |
"in#1"."row_num" |
|
| 690 |
, "_join"("1" := "in#1"."ACAD.Specimen.institutionCode", "3" := "in#1"."ACAD.Specimen.catalogNumber", "2" := "in#1"."_join(1=collectionCode, 2=collectionID).result") AS "result"
|
|
| 691 |
FROM "in#1" |
|
| 692 |
LIMIT 0 |
|
| 693 |
</code></pre><pre><code class="SQL"> |
|
| 694 |
CREATE FUNCTION "pg_temp"."_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collec_wrap"() |
|
| 695 |
RETURNS SETOF "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC" |
|
| 696 |
LANGUAGE plpgsql |
|
| 697 |
AS $$ |
|
| 698 |
DECLARE |
|
| 699 |
"row" "in#1"%ROWTYPE; |
|
| 700 |
BEGIN |
|
| 701 |
FOR "row" IN |
|
| 702 |
SELECT * FROM "in#1" |
|
| 703 |
LOOP |
|
| 704 |
BEGIN |
|
| 705 |
BEGIN |
|
| 706 |
RETURN QUERY |
|
| 707 |
SELECT |
|
| 708 |
"row"."row_num" |
|
| 709 |
, "_join"("1" := "row"."ACAD.Specimen.institutionCode", "3" := "row"."ACAD.Specimen.catalogNumber", "2" := "row"."_join(1=collectionCode, 2=collectionID).result")
|
|
| 710 |
; |
|
| 711 |
EXCEPTION |
|
| 712 |
WHEN internal_error THEN |
|
| 713 |
-- Handle PL/Python exceptions |
|
| 714 |
DECLARE |
|
| 715 |
matches text[] := regexp_matches(SQLERRM, |
|
| 716 |
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n |
|
| 717 |
exc_name text := matches[1]; |
|
| 718 |
msg text := matches[2]; |
|
| 719 |
BEGIN |
|
| 720 |
/* Re-raise PL/Python exceptions with the PL/Python prefix removed. |
|
| 721 |
This allows the exception to be parsed like a native exception. |
|
| 722 |
Always raise as data_exception so it goes in the errors table. */ |
|
| 723 |
IF exc_name IS NOT NULL THEN |
|
| 724 |
RAISE data_exception USING MESSAGE = msg; |
|
| 725 |
-- Re-raise non-PL/Python exceptions |
|
| 726 |
ELSE |
|
| 727 |
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; |
|
| 728 |
END IF; |
|
| 729 |
END; |
|
| 730 |
END; |
|
| 731 |
EXCEPTION |
|
| 732 |
WHEN data_exception THEN |
|
| 733 |
-- Save error in errors table. |
|
| 734 |
DECLARE |
|
| 735 |
error_code text := SQLSTATE; |
|
| 736 |
error text := SQLERRM; |
|
| 737 |
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.institutionCode" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."ACAD.Specimen.catalogNumber" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."_join(1=collectionCode, 2=collectionID).result" AS text), CAST(NULL AS text))], ',');
|
|
| 738 |
"column" text; |
|
| 739 |
BEGIN |
|
| 740 |
-- Insert the value and error for *each* source column. |
|
| 741 |
FOR "column" IN |
|
| 742 |
SELECT * FROM (VALUES ('institutionCode,catalogNumber,collectionCode'), ('institutionCode,catalogNumber,collectionID')) AS "c"
|
|
| 743 |
LOOP |
|
| 744 |
BEGIN |
|
| 745 |
INSERT INTO "ACAD"."Specimen.errors" |
|
| 746 |
("column", "value", "error_code", "error")
|
|
| 747 |
VALUES ("column", "value", "error_code", "error");
|
|
| 748 |
EXCEPTION |
|
| 749 |
WHEN unique_violation THEN NULL; |
|
| 750 |
END; |
|
| 751 |
END LOOP; |
|
| 752 |
|
|
| 753 |
END; |
|
| 754 |
|
|
| 755 |
RETURN QUERY |
|
| 756 |
SELECT |
|
| 757 |
"row"."row_num" |
|
| 758 |
, CAST(NULL AS text) |
|
| 759 |
; |
|
| 760 |
END; |
|
| 761 |
END LOOP; |
|
| 762 |
END; |
|
| 763 |
|
|
| 764 |
$$; |
|
| 765 |
</code></pre> |
|
| 766 |
# Calling function |
|
| 767 |
<pre><code class="SQL"> |
|
| 768 |
INSERT INTO "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC" |
|
| 769 |
("row_num", "result")
|
|
| 770 |
SELECT * FROM "pg_temp"."_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collec_wrap"() |
|
| 771 |
</code></pre> |
|
| 772 |
# ********** New iteration ********** |
|
| 773 |
# Inserting these input columns into @"_alt"@: |
|
| 774 |
|
|
| 775 |
|Output|Input| |
|
| 776 |
|@'1'@|@"ACAD.Specimen"."id"@| |
|
| 777 |
|@'2'@|@"_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC"."result"@| |
|
| 778 |
|
|
| 779 |
# Joining together input tables into temp table |
|
| 780 |
<pre><code class="SQL"> |
|
| 781 |
CREATE TEMP TABLE "in#2" AS |
|
| 782 |
SELECT |
|
| 783 |
"ACAD.Specimen"."row_num" AS "row_num" |
|
| 784 |
, "ACAD.Specimen"."id" AS "ACAD.Specimen.id" |
|
| 785 |
, "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC"."result" AS "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=coll.result" |
|
| 786 |
FROM "ACAD.Specimen" |
|
| 787 |
JOIN "_join(1=institutionCode, 3=catalogNumber, 2=_join(1=collectionC" USING ("row_num")
|
|
| 788 |
ORDER BY row_num |
|
| 789 |
</code></pre>Temp table: @"in#2"@ |
|
| 790 |
# Defining wrapper function |
|
| 791 |
<pre><code class="SQL"> |
|
| 792 |
CREATE TEMP TABLE "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join(" AS
|
|
| 793 |
SELECT |
|
| 794 |
"in#2"."row_num" |
|
| 795 |
, "_alt"("1" := "in#2"."ACAD.Specimen.id", "2" := "in#2"."_join(1=institutionCode, 3=catalogNumber, 2=_join(1=coll.result") AS "result"
|
|
| 796 |
FROM "in#2" |
|
| 797 |
LIMIT 0 |
|
| 798 |
</code></pre><pre><code class="SQL"> |
|
| 799 |
CREATE FUNCTION "pg_temp"."_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=__wrap"() |
|
| 800 |
RETURNS SETOF "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join("
|
|
| 801 |
LANGUAGE plpgsql |
|
| 802 |
AS $$ |
|
| 803 |
DECLARE |
|
| 804 |
"row" "in#2"%ROWTYPE; |
|
| 805 |
BEGIN |
|
| 806 |
FOR "row" IN |
|
| 807 |
SELECT * FROM "in#2" |
|
| 808 |
LOOP |
|
| 809 |
BEGIN |
|
| 810 |
BEGIN |
|
| 811 |
RETURN QUERY |
|
| 812 |
SELECT |
|
| 813 |
"row"."row_num" |
|
| 814 |
, "_alt"("1" := "row"."ACAD.Specimen.id", "2" := "row"."_join(1=institutionCode, 3=catalogNumber, 2=_join(1=coll.result")
|
|
| 815 |
; |
|
| 816 |
EXCEPTION |
|
| 817 |
WHEN internal_error THEN |
|
| 818 |
-- Handle PL/Python exceptions |
|
| 819 |
DECLARE |
|
| 820 |
matches text[] := regexp_matches(SQLERRM, |
|
| 821 |
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n |
|
| 822 |
exc_name text := matches[1]; |
|
| 823 |
msg text := matches[2]; |
|
| 824 |
BEGIN |
|
| 825 |
/* Re-raise PL/Python exceptions with the PL/Python prefix removed. |
|
| 826 |
This allows the exception to be parsed like a native exception. |
|
| 827 |
Always raise as data_exception so it goes in the errors table. */ |
|
| 828 |
IF exc_name IS NOT NULL THEN |
|
| 829 |
RAISE data_exception USING MESSAGE = msg; |
|
| 830 |
-- Re-raise non-PL/Python exceptions |
|
| 831 |
ELSE |
|
| 832 |
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; |
|
| 833 |
END IF; |
|
| 834 |
END; |
|
| 835 |
END; |
|
| 836 |
EXCEPTION |
|
| 837 |
WHEN data_exception THEN |
|
| 838 |
-- Save error in errors table. |
|
| 839 |
DECLARE |
|
| 840 |
error_code text := SQLSTATE; |
|
| 841 |
error text := SQLERRM; |
|
| 842 |
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.id" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."_join(1=institutionCode, 3=catalogNumber, 2=_join(1=coll.result" AS text), CAST(NULL AS text))], ',');
|
|
| 843 |
"column" text; |
|
| 844 |
BEGIN |
|
| 845 |
-- Insert the value and error for *each* source column. |
|
| 846 |
FOR "column" IN |
|
| 847 |
SELECT * FROM (VALUES ('id,institutionCode'), ('id,catalogNumber'), ('id,collectionCode'), ('id,collectionID')) AS "c"
|
|
| 848 |
LOOP |
|
| 849 |
BEGIN |
|
| 850 |
INSERT INTO "ACAD"."Specimen.errors" |
|
| 851 |
("column", "value", "error_code", "error")
|
|
| 852 |
VALUES ("column", "value", "error_code", "error");
|
|
| 853 |
EXCEPTION |
|
| 854 |
WHEN unique_violation THEN NULL; |
|
| 855 |
END; |
|
| 856 |
END LOOP; |
|
| 857 |
|
|
| 858 |
END; |
|
| 859 |
|
|
| 860 |
RETURN QUERY |
|
| 861 |
SELECT |
|
| 862 |
"row"."row_num" |
|
| 863 |
, CAST(NULL AS text) |
|
| 864 |
; |
|
| 865 |
END; |
|
| 866 |
END LOOP; |
|
| 867 |
END; |
|
| 868 |
|
|
| 869 |
$$; |
|
| 870 |
</code></pre> |
|
| 871 |
# Calling function |
|
| 872 |
<pre><code class="SQL"> |
|
| 873 |
INSERT INTO "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join("
|
|
| 874 |
("row_num", "result")
|
|
| 875 |
SELECT * FROM "pg_temp"."_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=__wrap"() |
|
| 876 |
</code></pre> |
|
| 877 |
# ********** New iteration ********** |
|
| 878 |
# Inserting these input columns into @"_label"@: |
|
| 879 |
|
|
| 880 |
|Output|Input| |
|
| 881 |
|@'value'@|@"ACAD.Specimen"."habitat"@| |
|
| 882 |
|@'label'@|@'habitat'@| |
|
| 883 |
|
|
| 884 |
# Joining together input tables into temp table |
|
| 885 |
<pre><code class="SQL"> |
|
| 886 |
CREATE TEMP TABLE "in#3" AS |
|
| 887 |
SELECT |
|
| 888 |
"ACAD.Specimen"."row_num" AS "row_num" |
|
| 889 |
, "ACAD.Specimen"."habitat" AS "ACAD.Specimen.habitat" |
|
| 890 |
FROM "ACAD.Specimen" |
|
| 891 |
ORDER BY row_num |
|
| 892 |
</code></pre>Temp table: @"in#3"@ |
|
| 893 |
# Defining wrapper function |
|
| 894 |
<pre><code class="SQL"> |
|
| 895 |
CREATE TEMP TABLE "_label(habitat)" AS |
|
| 896 |
SELECT |
|
| 897 |
"in#3"."row_num" |
|
| 898 |
, "_label"("value" := "in#3"."ACAD.Specimen.habitat", "label" := 'habitat') AS "result"
|
|
| 899 |
FROM "in#3" |
|
| 900 |
LIMIT 0 |
|
| 901 |
</code></pre><pre><code class="SQL"> |
|
| 902 |
CREATE FUNCTION "pg_temp"."_label(habitat)_wrap"() |
|
| 903 |
RETURNS SETOF "_label(habitat)" |
|
| 904 |
LANGUAGE plpgsql |
|
| 905 |
AS $$ |
|
| 906 |
DECLARE |
|
| 907 |
"row" "in#3"%ROWTYPE; |
|
| 908 |
BEGIN |
|
| 909 |
FOR "row" IN |
|
| 910 |
SELECT * FROM "in#3" |
|
| 911 |
LOOP |
|
| 912 |
BEGIN |
|
| 913 |
BEGIN |
|
| 914 |
RETURN QUERY |
|
| 915 |
SELECT |
|
| 916 |
"row"."row_num" |
|
| 917 |
, "_label"("value" := "row"."ACAD.Specimen.habitat", "label" := 'habitat')
|
|
| 918 |
; |
|
| 919 |
EXCEPTION |
|
| 920 |
WHEN internal_error THEN |
|
| 921 |
-- Handle PL/Python exceptions |
|
| 922 |
DECLARE |
|
| 923 |
matches text[] := regexp_matches(SQLERRM, |
|
| 924 |
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n |
|
| 925 |
exc_name text := matches[1]; |
|
| 926 |
msg text := matches[2]; |
|
| 927 |
BEGIN |
|
| 928 |
/* Re-raise PL/Python exceptions with the PL/Python prefix removed. |
|
| 929 |
This allows the exception to be parsed like a native exception. |
|
| 930 |
Always raise as data_exception so it goes in the errors table. */ |
|
| 931 |
IF exc_name IS NOT NULL THEN |
|
| 932 |
RAISE data_exception USING MESSAGE = msg; |
|
| 933 |
-- Re-raise non-PL/Python exceptions |
|
| 934 |
ELSE |
|
| 935 |
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; |
|
| 936 |
END IF; |
|
| 937 |
END; |
|
| 938 |
END; |
|
| 939 |
EXCEPTION |
|
| 940 |
WHEN data_exception THEN |
|
| 941 |
-- Save error in errors table. |
|
| 942 |
DECLARE |
|
| 943 |
error_code text := SQLSTATE; |
|
| 944 |
error text := SQLERRM; |
|
| 945 |
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.habitat" AS text), CAST(NULL AS text))], ',');
|
|
| 946 |
"column" text; |
|
| 947 |
BEGIN |
|
| 948 |
-- Insert the value and error for *each* source column. |
|
| 949 |
FOR "column" IN |
|
| 950 |
SELECT * FROM (VALUES ('habitat')) AS "c"
|
|
| 951 |
LOOP |
|
| 952 |
BEGIN |
|
| 953 |
INSERT INTO "ACAD"."Specimen.errors" |
|
| 954 |
("column", "value", "error_code", "error")
|
|
| 955 |
VALUES ("column", "value", "error_code", "error");
|
|
| 956 |
EXCEPTION |
|
| 957 |
WHEN unique_violation THEN NULL; |
|
| 958 |
END; |
|
| 959 |
END LOOP; |
|
| 960 |
|
|
| 961 |
END; |
|
| 962 |
|
|
| 963 |
RETURN QUERY |
|
| 964 |
SELECT |
|
| 965 |
"row"."row_num" |
|
| 966 |
, CAST(NULL AS text) |
|
| 967 |
; |
|
| 968 |
END; |
|
| 969 |
END LOOP; |
|
| 970 |
END; |
|
| 971 |
|
|
| 972 |
$$; |
|
| 973 |
</code></pre> |
|
| 974 |
# Calling function |
|
| 975 |
<pre><code class="SQL"> |
|
| 976 |
INSERT INTO "_label(habitat)" |
|
| 977 |
("row_num", "result")
|
|
| 978 |
SELECT * FROM "pg_temp"."_label(habitat)_wrap"() |
|
| 979 |
</code></pre> |
|
| 980 |
# ********** New iteration ********** |
|
| 981 |
# Inserting these input columns into @"_merge"@: |
|
| 982 |
|
|
| 983 |
|Output|Input| |
|
| 984 |
|@'1'@|@"ACAD.Specimen"."locality"@| |
|
| 985 |
|@'3'@|@"_label(habitat)"."result"@| |
|
| 986 |
|
|
| 987 |
# Joining together input tables into temp table |
|
| 988 |
<pre><code class="SQL"> |
|
| 989 |
CREATE TEMP TABLE "in#4" AS |
|
| 990 |
SELECT |
|
| 991 |
"ACAD.Specimen"."row_num" AS "row_num" |
|
| 992 |
, "ACAD.Specimen"."locality" AS "ACAD.Specimen.locality" |
|
| 993 |
, "_label(habitat)"."result" AS "_label(habitat).result" |
|
| 994 |
FROM "ACAD.Specimen" |
|
| 995 |
JOIN "_label(habitat)" USING ("row_num")
|
|
| 996 |
ORDER BY row_num |
|
| 997 |
</code></pre>Temp table: @"in#4"@ |
|
| 998 |
# Defining wrapper function |
|
| 999 |
<pre><code class="SQL"> |
|
| 1000 |
CREATE TEMP TABLE "_merge(1=locality, 3=_label(habitat))" AS |
|
| 1001 |
SELECT |
|
| 1002 |
"in#4"."row_num" |
|
| 1003 |
, "_merge"("1" := "in#4"."ACAD.Specimen.locality", "3" := "in#4"."_label(habitat).result") AS "result"
|
|
| 1004 |
FROM "in#4" |
|
| 1005 |
LIMIT 0 |
|
| 1006 |
</code></pre><pre><code class="SQL"> |
|
| 1007 |
CREATE FUNCTION "pg_temp"."_merge(1=locality, 3=_label(habitat))_wrap"() |
|
| 1008 |
RETURNS SETOF "_merge(1=locality, 3=_label(habitat))" |
|
| 1009 |
LANGUAGE plpgsql |
|
| 1010 |
AS $$ |
|
| 1011 |
DECLARE |
|
| 1012 |
"row" "in#4"%ROWTYPE; |
|
| 1013 |
BEGIN |
|
| 1014 |
FOR "row" IN |
|
| 1015 |
SELECT * FROM "in#4" |
|
| 1016 |
LOOP |
|
| 1017 |
BEGIN |
|
| 1018 |
BEGIN |
|
| 1019 |
RETURN QUERY |
|
| 1020 |
SELECT |
|
| 1021 |
"row"."row_num" |
|
| 1022 |
, "_merge"("1" := "row"."ACAD.Specimen.locality", "3" := "row"."_label(habitat).result")
|
|
| 1023 |
; |
|
| 1024 |
EXCEPTION |
|
| 1025 |
WHEN internal_error THEN |
|
| 1026 |
-- Handle PL/Python exceptions |
|
| 1027 |
DECLARE |
|
| 1028 |
matches text[] := regexp_matches(SQLERRM, |
|
| 1029 |
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n |
|
| 1030 |
exc_name text := matches[1]; |
|
| 1031 |
msg text := matches[2]; |
|
| 1032 |
BEGIN |
|
| 1033 |
/* Re-raise PL/Python exceptions with the PL/Python prefix removed. |
|
| 1034 |
This allows the exception to be parsed like a native exception. |
|
| 1035 |
Always raise as data_exception so it goes in the errors table. */ |
|
| 1036 |
IF exc_name IS NOT NULL THEN |
|
| 1037 |
RAISE data_exception USING MESSAGE = msg; |
|
| 1038 |
-- Re-raise non-PL/Python exceptions |
|
| 1039 |
ELSE |
|
| 1040 |
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; |
|
| 1041 |
END IF; |
|
| 1042 |
END; |
|
| 1043 |
END; |
|
| 1044 |
EXCEPTION |
|
| 1045 |
WHEN data_exception THEN |
|
| 1046 |
-- Save error in errors table. |
|
| 1047 |
DECLARE |
|
| 1048 |
error_code text := SQLSTATE; |
|
| 1049 |
error text := SQLERRM; |
|
| 1050 |
value text := array_to_string(ARRAY[COALESCE(CAST("row"."ACAD.Specimen.locality" AS text), CAST(NULL AS text)), COALESCE(CAST("row"."_label(habitat).result" AS text), CAST(NULL AS text))], ',');
|
|
| 1051 |
"column" text; |
|
| 1052 |
BEGIN |
|
| 1053 |
-- Insert the value and error for *each* source column. |
|
| 1054 |
FOR "column" IN |
|
| 1055 |
SELECT * FROM (VALUES ('locality,habitat')) AS "c"
|
|
| 1056 |
LOOP |
|
| 1057 |
BEGIN |
|
| 1058 |
INSERT INTO "ACAD"."Specimen.errors" |
|
| 1059 |
("column", "value", "error_code", "error")
|
|
| 1060 |
VALUES ("column", "value", "error_code", "error");
|
|
| 1061 |
EXCEPTION |
|
| 1062 |
WHEN unique_violation THEN NULL; |
|
| 1063 |
END; |
|
| 1064 |
END LOOP; |
|
| 1065 |
|
|
| 1066 |
END; |
|
| 1067 |
|
|
| 1068 |
RETURN QUERY |
|
| 1069 |
SELECT |
|
| 1070 |
"row"."row_num" |
|
| 1071 |
, CAST(NULL AS text) |
|
| 1072 |
; |
|
| 1073 |
END; |
|
| 1074 |
END LOOP; |
|
| 1075 |
END; |
|
| 1076 |
|
|
| 1077 |
$$; |
|
| 1078 |
</code></pre> |
|
| 1079 |
# Calling function |
|
| 1080 |
<pre><code class="SQL"> |
|
| 1081 |
INSERT INTO "_merge(1=locality, 3=_label(habitat))" |
|
| 1082 |
("row_num", "result")
|
|
| 1083 |
SELECT * FROM "pg_temp"."_merge(1=locality, 3=_label(habitat))_wrap"() |
|
| 1084 |
</code></pre> |
|
| 1085 |
# ********** New iteration ********** |
|
| 1086 |
# Inserting these input columns into @"_join"@: |
|
| 1087 |
|
|
| 1088 |
|Output|Input| |
|
| 1089 |
|@'1'@|@"ACAD.Specimen"."locality"@| |
|
| 1090 |
|@'3'@|@"ACAD.Specimen"."habitat"@| |
|
| 1091 |
|
|
| 1092 |
# Joining together input tables into temp table |
|
| 1093 |
<pre><code class="SQL"> |
|
| 1094 |
CREATE TEMP TABLE "in#5" AS |
|
| 1095 |
SELECT |
|
| 1096 |
"ACAD.Specimen"."row_num" AS "row_num" |
|
| 1097 |
, "ACAD.Specimen"."locality" AS "ACAD.Specimen.locality" |
|
| 1098 |
, "ACAD.Specimen"."habitat" AS "ACAD.Specimen.habitat" |
|
| 1099 |
FROM "ACAD.Specimen" |
|
| 1100 |
ORDER BY row_num |
|
| 1101 |
</code></pre>Temp table: @"in#5"@ |
|
| 1102 |
# Defining wrapper function |
|
| 1103 |
<pre><code class="SQL"> |
|
| 1104 |
CREATE TEMP TABLE "_join(1=locality, 3=habitat)" AS |
|
| 1105 |
SELECT |
|
| 1106 |
"in#5"."row_num" |
|
| 1107 |
, "_join"("1" := "in#5"."ACAD.Specimen.locality", "3" := "in#5"."ACAD.Specimen.habitat") AS "result"
|
|
| 1108 |
FROM "in#5" |
|
| 1109 |
LIMIT 0 |
|
| 1110 |
</code></pre><pre><code class="SQL"> |
|
| 1111 |
CREATE FUNCTION "pg_temp"."_join(1=locality, 3=habitat)_wrap"() |
|
| 1112 |
RETURNS SETOF "_join(1=locality, 3=habitat)" |
|
| 1113 |
LANGUAGE plpgsql |
|
| 1114 |
AS $$ |
|
| 1115 |
DECLARE |
|
| 1116 |
"row" "in#5"%ROWTYPE; |
|
| 1117 |
BEGIN |
|
| 1118 |
FOR "row" IN |
|
| 1119 |
SELECT * FROM "in#5" |
|
| 1120 |
LOOP |
|
| 1121 |
BEGIN |
|
| 1122 |
BEGIN |
|
| 1123 |
RETURN QUERY |
|
| 1124 |
SELECT |
|
| 1125 |
"row"."row_num" |
|
| 1126 |
, "_join"("1" := "row"."ACAD.Specimen.locality", "3" := "row"."ACAD.Specimen.habitat")
|
|
| 1127 |
; |
|
| 1128 |
EXCEPTION |
|
| 1129 |
WHEN internal_error THEN |
|
| 1130 |
-- Handle PL/Python exceptions |
|
| 1131 |
DECLARE |
|
| 1132 |
matches text[] := regexp_matches(SQLERRM, |
|
| 1133 |
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n |
|
| 1134 |
exc_name text := matches[1]; |
|
| 1135 |
msg text := matches[2]; |
|
| 1136 |
BEGIN |
|
| 1137 |
/* Re-raise PL/Python exceptions with the PL/Python prefix removed. |
|
| 1138 |
This allows the exception to be parsed like a native exception. |
|
| 1139 |
Always raise as data_exception so it goes in the errors table. */ |
|
| 1140 |
IF exc_name IS NOT NULL THEN |
|
| 1141 |
RAISE data_exception USING MESSAGE = msg; |
|
| 1142 |
-- Re-raise non-PL/Python exceptions |
|
| 1143 |
ELSE |
|
| 1144 |
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; |
|
| 1145 |
END IF; |
|
| 1146 |
END; |
|
| 1147 |
END; |
|
| 1148 |
EXCEPTION |
|
| 1149 |
WHEN data_exception THEN |
|
| 1150 |
-- Save error in errors table. |
|
| 1151 |
DECLARE |
|
| 1152 |
error_code text := SQLSTATE; |
|
| 1153 |
error text := SQLERRM; |
|
| 1154 |
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))], ',');
|
|
| 1155 |
"column" text; |
|
| 1156 |
BEGIN |
|
| 1157 |
-- Insert the value and error for *each* source column. |
|
| 1158 |
FOR "column" IN |
|
| 1159 |
SELECT * FROM (VALUES ('locality,habitat')) AS "c"
|
|
| 1160 |
LOOP |
|
| 1161 |
BEGIN |
|
| 1162 |
INSERT INTO "ACAD"."Specimen.errors" |
|
| 1163 |
("column", "value", "error_code", "error")
|
|
| 1164 |
VALUES ("column", "value", "error_code", "error");
|
|
| 1165 |
EXCEPTION |
|
| 1166 |
WHEN unique_violation THEN NULL; |
|
| 1167 |
END; |
|
| 1168 |
END LOOP; |
|
| 1169 |
|
|
| 1170 |
END; |
|
| 1171 |
|
|
| 1172 |
RETURN QUERY |
|
| 1173 |
SELECT |
|
| 1174 |
"row"."row_num" |
|
| 1175 |
, CAST(NULL AS text) |
|
| 1176 |
; |
|
| 1177 |
END; |
|
| 1178 |
END LOOP; |
|
| 1179 |
END; |
|
| 1180 |
|
|
| 1181 |
$$; |
|
| 1182 |
</code></pre> |
|
| 1183 |
# Calling function |
|
| 1184 |
<pre><code class="SQL"> |
|
| 1185 |
INSERT INTO "_join(1=locality, 3=habitat)" |
|
| 1186 |
("row_num", "result")
|
|
| 1187 |
SELECT * FROM "pg_temp"."_join(1=locality, 3=habitat)_wrap"() |
|
| 1188 |
</code></pre> |
|
| 1189 |
# ********** New iteration ********** |
|
| 1190 |
# Inserting these input columns into @"_locationnarrative_is_cultivated"@: |
|
| 1191 |
|
|
| 1192 |
|Output|Input| |
|
| 1193 |
|@'locationnarrative'@|@"_join(1=locality, 3=habitat)"."result"@| |
|
| 1194 |
|
|
| 1195 |
# Joining together input tables into temp table |
|
| 1196 |
<pre><code class="SQL"> |
|
| 1197 |
CREATE TEMP TABLE "in#6" AS |
|
| 1198 |
SELECT |
|
| 1199 |
"ACAD.Specimen"."row_num" AS "row_num" |
|
| 1200 |
, "_join(1=locality, 3=habitat)"."result" AS "_join(1=locality, 3=habitat).result" |
|
| 1201 |
FROM "ACAD.Specimen" |
|
| 1202 |
JOIN "_join(1=locality, 3=habitat)" USING ("row_num")
|
|
| 1203 |
ORDER BY row_num |
|
| 1204 |
</code></pre>Temp table: @"in#6"@ |
|
| 1205 |
# Defining wrapper function |
|
| 1206 |
<pre><code class="SQL"> |
|
| 1207 |
CREATE TEMP TABLE "_locationnarrative_is_cultivated(locationnarrative=_join(1=loca" AS |
|
| 1208 |
SELECT |
|
| 1209 |
"in#6"."row_num" |
|
| 1210 |
, "_locationnarrative_is_cultivated"("locationnarrative" := "in#6"."_join(1=locality, 3=habitat).result") AS "result"
|
|
| 1211 |
FROM "in#6" |
|
| 1212 |
LIMIT 0 |
|
| 1213 |
</code></pre><pre><code class="SQL"> |
|
| 1214 |
CREATE FUNCTION "pg_temp"."_locationnarrative_is_cultivated(locationnarrative=_join(1_wrap"() |
|
| 1215 |
RETURNS SETOF "_locationnarrative_is_cultivated(locationnarrative=_join(1=loca" |
|
| 1216 |
LANGUAGE plpgsql |
|
| 1217 |
AS $$ |
|
| 1218 |
DECLARE |
|
| 1219 |
"row" "in#6"%ROWTYPE; |
|
| 1220 |
BEGIN |
|
| 1221 |
FOR "row" IN |
|
| 1222 |
SELECT * FROM "in#6" |
|
| 1223 |
LOOP |
|
| 1224 |
BEGIN |
|
| 1225 |
BEGIN |
|
| 1226 |
RETURN QUERY |
|
| 1227 |
SELECT |
|
| 1228 |
"row"."row_num" |
|
| 1229 |
, "_locationnarrative_is_cultivated"("locationnarrative" := "row"."_join(1=locality, 3=habitat).result")
|
|
| 1230 |
; |
|
| 1231 |
EXCEPTION |
|
| 1232 |
WHEN internal_error THEN |
|
| 1233 |
-- Handle PL/Python exceptions |
|
| 1234 |
DECLARE |
|
| 1235 |
matches text[] := regexp_matches(SQLERRM, |
|
| 1236 |
E'^(?:PL/Python: )?(\\w+): (.*)$'); -- .* also matches \n |
|
| 1237 |
exc_name text := matches[1]; |
|
| 1238 |
msg text := matches[2]; |
|
| 1239 |
BEGIN |
|
| 1240 |
/* Re-raise PL/Python exceptions with the PL/Python prefix removed. |
|
| 1241 |
This allows the exception to be parsed like a native exception. |
|
| 1242 |
Always raise as data_exception so it goes in the errors table. */ |
|
| 1243 |
IF exc_name IS NOT NULL THEN |
|
| 1244 |
RAISE data_exception USING MESSAGE = msg; |
|
| 1245 |
-- Re-raise non-PL/Python exceptions |
|
| 1246 |
ELSE |
|
| 1247 |
RAISE USING ERRCODE = SQLSTATE, MESSAGE = SQLERRM; |
|
| 1248 |
END IF; |
|
| 1249 |
END; |
|
| 1250 |
END; |
|
| 1251 |
EXCEPTION |
|
| 1252 |
WHEN data_exception THEN |
|
| 1253 |
-- Save error in errors table. |
|
| 1254 |
DECLARE |
|
| 1255 |
error_code text := SQLSTATE; |
|
| 1256 |
error text := SQLERRM; |
|
| 1257 |
value text := array_to_string(ARRAY[COALESCE(CAST("row"."_join(1=locality, 3=habitat).result" AS text), CAST(NULL AS text))], ',');
|
|
| 1258 |
"column" text; |
|
| 1259 |
BEGIN |
|
| 1260 |
-- Insert the value and error for *each* source column. |
|
| 1261 |
FOR "column" IN |
|
| 1262 |
SELECT * FROM (VALUES ('locality'), ('habitat')) AS "c"
|
|
| 1263 |
LOOP |
|
| 1264 |
BEGIN |
|
| 1265 |
INSERT INTO "ACAD"."Specimen.errors" |
|
| 1266 |
("column", "value", "error_code", "error")
|
|
| 1267 |
VALUES ("column", "value", "error_code", "error");
|
|
| 1268 |
EXCEPTION |
|
| 1269 |
WHEN unique_violation THEN NULL; |
|
| 1270 |
END; |
|
| 1271 |
END LOOP; |
|
| 1272 |
|
|
| 1273 |
END; |
|
| 1274 |
|
|
| 1275 |
RETURN QUERY |
|
| 1276 |
SELECT |
|
| 1277 |
"row"."row_num" |
|
| 1278 |
, CAST(NULL AS boolean) |
|
| 1279 |
; |
|
| 1280 |
END; |
|
| 1281 |
END LOOP; |
|
| 1282 |
END; |
|
| 1283 |
|
|
| 1284 |
$$; |
|
| 1285 |
</code></pre> |
|
| 1286 |
# Calling function |
|
| 1287 |
<pre><code class="SQL"> |
|
| 1288 |
INSERT INTO "_locationnarrative_is_cultivated(locationnarrative=_join(1=loca" |
|
| 1289 |
("row_num", "result")
|
|
| 1290 |
SELECT * FROM "pg_temp"."_locationnarrative_is_cultivated(locationnarrative=_join(1_wrap"() |
|
| 1291 |
</code></pre> |
|
| 1292 |
# ********** New iteration ********** |
|
| 1293 |
# Inserting these input columns into @"location"@: |
|
| 1294 |
|
|
| 1295 |
|Output|Input| |
|
| 1296 |
|@'verbatimelevation'@|@"ACAD.Specimen"."verbatimElevation"@| |
|
| 1297 |
|@'authorlocationcode'@|@"_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join("."result"@|
|
|
| 1298 |
|@'elevation_m'@|@"ACAD.Specimen"."verbatimElevation"@| |
|
| 1299 |
|@'locationnarrative'@|@"_merge(1=locality, 3=_label(habitat))"."result"@| |
|
| 1300 |
|@'accessconditions'@|@"ACAD.Specimen"."rights"@| |
|
| 1301 |
|@'iscultivated'@|@"_locationnarrative_is_cultivated(locationnarrative=_join(1=loca"."result"@| |
|
| 1302 |
|
|
| 1303 |
# Joining together input tables into temp table |
|
| 1304 |
<pre><code class="SQL"> |
|
| 1305 |
CREATE TEMP TABLE "in#7" AS |
|
| 1306 |
SELECT |
|
| 1307 |
"ACAD.Specimen"."row_num" AS "row_num" |
|
| 1308 |
, "ACAD.Specimen"."verbatimElevation" AS "ACAD.Specimen.verbatimElevation" |
|
| 1309 |
, "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join("."result" AS "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2.result"
|
|
| 1310 |
, "_merge(1=locality, 3=_label(habitat))"."result" AS "_merge(1=locality, 3=_label(habitat)).result" |
|
| 1311 |
, "ACAD.Specimen"."rights" AS "ACAD.Specimen.rights" |
|
| 1312 |
, "_locationnarrative_is_cultivated(locationnarrative=_join(1=loca"."result" AS "_locationnarrative_is_cultivated(locationnarrative=_join.result" |
|
| 1313 |
FROM "ACAD.Specimen" |
|
| 1314 |
JOIN "_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2=_join(" USING ("row_num")
|
|
| 1315 |
JOIN "_merge(1=locality, 3=_label(habitat))" USING ("row_num")
|
|
| 1316 |
JOIN "_locationnarrative_is_cultivated(locationnarrative=_join(1=loca" USING ("row_num")
|
|
| 1317 |
ORDER BY row_num |
|
| 1318 |
</code></pre>Temp table: @"in#7"@ |
|
| 1319 |
<pre><code class="SQL"> |
|
| 1320 |
CREATE TEMP TABLE "in#7_full" ( |
|
| 1321 |
LIKE "in#7" INCLUDING ALL |
|
| 1322 |
); |
|
| 1323 |
</code></pre><pre><code class="SQL"> |
|
| 1324 |
INSERT INTO "in#7_full" |
|
| 1325 |
SELECT * FROM "in#7" |
|
| 1326 |
</code></pre> |
|
| 1327 |
# Trying to insert new rows |
|
| 1328 |
<pre><code class="SQL"> |
|
| 1329 |
CREATE FUNCTION "pg_temp"."INSERT INTO location"() |
|
| 1330 |
RETURNS SETOF "location"."location_id"%TYPE |
|
| 1331 |
LANGUAGE sql |
|
| 1332 |
AS $$ |
|
| 1333 |
INSERT INTO "location" |
|
| 1334 |
("verbatimelevation", "authorlocationcode", "elevation_m", "locationnarrative", "accessconditions", "iscultivated")
|
|
| 1335 |
SELECT |
|
| 1336 |
"in#7"."ACAD.Specimen.verbatimElevation" |
|
| 1337 |
, "in#7"."_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2.result" |
|
| 1338 |
, "in#7"."ACAD.Specimen.verbatimElevation" |
|
| 1339 |
, "in#7"."_merge(1=locality, 3=_label(habitat)).result" |
|
| 1340 |
, "in#7"."ACAD.Specimen.rights" |
|
| 1341 |
, "in#7"."_locationnarrative_is_cultivated(locationnarrative=_join.result" |
|
| 1342 |
FROM "in#7" |
|
| 1343 |
RETURNING "location_id" |
|
| 1344 |
$$; |
|
| 1345 |
</code></pre> |
|
| 1346 |
# Caught exception: MissingCastException: Missing cast to type @double precision@ on column: @elevation_m@ |
|
| 1347 |
# Casting @'elevation_m'@ input to @double precision@ |
|
| 1348 |
<pre><code class="SQL"> |
|
| 1349 |
CREATE FUNCTION "pg_temp"."double(verbatimElevation)"("value" anyelement)
|
|
| 1350 |
RETURNS double precision |
|
| 1351 |
LANGUAGE plpgsql |
|
| 1352 |
STRICT |
|
| 1353 |
AS $$ |
|
| 1354 |
BEGIN |
|
| 1355 |
/* The explicit cast to the return type is needed to make the cast happen |
|
| 1356 |
inside the try block. (Implicit casts to the return type happen at the end |
|
| 1357 |
of the function, outside any block.) */ |
|
| 1358 |
RETURN CAST(value AS double precision); |
|
| 1359 |
EXCEPTION |
|
| 1360 |
WHEN data_exception THEN |
|
| 1361 |
-- Save error in errors table. |
|
| 1362 |
DECLARE |
|
| 1363 |
error_code text := SQLSTATE; |
|
| 1364 |
error text := SQLERRM; |
|
| 1365 |
value text := value; |
|
| 1366 |
"column" text; |
|
| 1367 |
BEGIN |
|
| 1368 |
-- Insert the value and error for *each* source column. |
|
| 1369 |
FOR "column" IN |
|
| 1370 |
SELECT * FROM (VALUES ('verbatimElevation')) AS "c"
|
|
| 1371 |
LOOP |
|
| 1372 |
BEGIN |
|
| 1373 |
INSERT INTO "ACAD"."Specimen.errors" |
|
| 1374 |
("column", "value", "error_code", "error")
|
|
| 1375 |
VALUES ("column", "value", "error_code", "error");
|
|
| 1376 |
EXCEPTION |
|
| 1377 |
WHEN unique_violation THEN NULL; |
|
| 1378 |
END; |
|
| 1379 |
END LOOP; |
|
| 1380 |
|
|
| 1381 |
END; |
|
| 1382 |
|
|
| 1383 |
RETURN NULL; |
|
| 1384 |
END; |
|
| 1385 |
$$; |
|
| 1386 |
</code></pre><pre><code class="SQL"> |
|
| 1387 |
ALTER TABLE "in#7" ADD COLUMN "ACAD.Specimen.verbatimElevation::double" double precision /*"in#7"."ACAD.Specimen.verbatimElevation"::double precision*/ |
|
| 1388 |
</code></pre><pre><code class="SQL"> |
|
| 1389 |
ALTER TABLE "in#7" |
|
| 1390 |
ALTER COLUMN "ACAD.Specimen.verbatimElevation::double" TYPE double precision |
|
| 1391 |
USING "pg_temp"."double(verbatimElevation)"("in#7"."ACAD.Specimen.verbatimElevation")
|
|
| 1392 |
</code></pre> |
|
| 1393 |
# Trying to insert new rows |
|
| 1394 |
<pre><code class="SQL"> |
|
| 1395 |
CREATE FUNCTION "pg_temp"."INSERT INTO location"() |
|
| 1396 |
RETURNS SETOF "location"."location_id"%TYPE |
|
| 1397 |
LANGUAGE sql |
|
| 1398 |
AS $$ |
|
| 1399 |
INSERT INTO "location" |
|
| 1400 |
("verbatimelevation", "authorlocationcode", "elevation_m", "locationnarrative", "accessconditions", "iscultivated")
|
|
| 1401 |
SELECT |
|
| 1402 |
"in#7"."ACAD.Specimen.verbatimElevation" |
|
| 1403 |
, "in#7"."_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2.result" |
|
| 1404 |
, "in#7"."ACAD.Specimen.verbatimElevation::double" |
|
| 1405 |
, "in#7"."_merge(1=locality, 3=_label(habitat)).result" |
|
| 1406 |
, "in#7"."ACAD.Specimen.rights" |
|
| 1407 |
, "in#7"."_locationnarrative_is_cultivated(locationnarrative=_join.result" |
|
| 1408 |
FROM "in#7" |
|
| 1409 |
RETURNING "location_id" |
|
| 1410 |
$$; |
|
| 1411 |
</code></pre><pre><code class="SQL"> |
|
| 1412 |
CREATE TEMP TABLE "in#7_insert_out_pkeys" AS |
|
| 1413 |
SELECT * FROM "pg_temp"."INSERT INTO location"() AS "f" ("location_id")
|
|
| 1414 |
</code></pre> |
|
| 1415 |
# Caught exception: NullValueException: Violated @NOT NULL@ constraint on columns: @source_id@ |
|
| 1416 |
# ********** New iteration ********** |
|
| 1417 |
# Inserting these input columns into @"source"@: |
|
| 1418 |
|
|
| 1419 |
|Output|Input| |
|
| 1420 |
|@'shortname'@|@'ACAD'@| |
|
| 1421 |
|
|
| 1422 |
# Trying to insert new rows |
|
| 1423 |
<pre><code class="SQL"> |
|
| 1424 |
INSERT INTO "source" |
|
| 1425 |
("shortname")
|
|
| 1426 |
SELECT 'ACAD' AS "shortname" |
|
| 1427 |
RETURNING "source_id" |
|
| 1428 |
</code></pre> |
|
| 1429 |
# Trying to insert new rows |
|
| 1430 |
<pre><code class="SQL"> |
|
| 1431 |
CREATE FUNCTION "pg_temp"."INSERT INTO location#1"() |
|
| 1432 |
RETURNS SETOF "location"."location_id"%TYPE |
|
| 1433 |
LANGUAGE sql |
|
| 1434 |
AS $$ |
|
| 1435 |
INSERT INTO "location" |
|
| 1436 |
("verbatimelevation", "authorlocationcode", "elevation_m", "locationnarrative", "accessconditions", "iscultivated", "source_id")
|
|
| 1437 |
SELECT |
|
| 1438 |
"in#7"."ACAD.Specimen.verbatimElevation" |
|
| 1439 |
, "in#7"."_alt(1=id, 2=_join(1=institutionCode, 3=catalogNumber, 2.result" |
|
| 1440 |
, "in#7"."ACAD.Specimen.verbatimElevation::double" |
|
| 1441 |
, "in#7"."_merge(1=locality, 3=_label(habitat)).result" |
|
| 1442 |
, "in#7"."ACAD.Specimen.rights" |
|
| 1443 |
, "in#7"."_locationnarrative_is_cultivated(locationnarrative=_join.result" |
|
| 1444 |
, 1 AS "source_id" |
|
| 1445 |
FROM "in#7" |
|
| 1446 |
RETURNING "location_id" |
|
| 1447 |
$$; |
|
| 1448 |
</code></pre><pre><code class="SQL"> |
|
| 1449 |
CREATE TEMP TABLE "in#7_insert_out_pkeys" AS |
|
| 1450 |
SELECT * FROM "pg_temp"."INSERT INTO location#1"() AS "f" ("location_id")
|
|
| 1451 |
</code></pre> |
|
| 1452 |
# Getting input table pkeys of inserted rows |
|
| 1453 |
<pre><code class="SQL"> |
|
| 1454 |
CREATE TEMP TABLE "in#7_insert_in_pkeys" AS |
|
| 1455 |
SELECT "row_num" FROM "in#7" |
|
| 1456 |
</code></pre> |
|
| 1457 |
# Combining output and input pkeys in inserted order |
|
| 1458 |
<pre><code class="SQL"> |
|
| 1459 |
CREATE TEMP TABLE "location_pkeys" AS |
|
| 1460 |
SELECT |
|
| 1461 |
"in#7_insert_in_pkeys"."row_num" |
|
| 1462 |
, "in#7_insert_out_pkeys"."location_id" AS "out.location_id" |
|
| 1463 |
FROM "in#7_insert_in_pkeys" |
|
| 1464 |
JOIN "in#7_insert_out_pkeys" USING ("_row_num")
|
|
| 1465 |
</code></pre> |
|
| 1466 |
# Setting pkeys of missing rows to @None@ |
|
| 1467 |
<pre><code class="SQL"> |
|
| 1468 |
INSERT INTO "location_pkeys" |
|
| 1469 |
("row_num", "out.location_id")
|
|
| 1470 |
SELECT |
|
| 1471 |
"in#7_full"."row_num" |
|
| 1472 |
, NULL AS "out.location_id" |
|
| 1473 |
FROM "in#7_full" |
|
| 1474 |
LEFT JOIN "location_pkeys" ON "location_pkeys"."row_num" = "in#7_full"."row_num" |
|
| 1475 |
WHERE "location_pkeys"."row_num" IS NULL |
|
| 1476 |
</code></pre> |
|
| 1477 |
# ********** New iteration ********** |
|
| 1478 |
# Inserting these input columns into @"_dateRangeEnd"@: |
|
| 1479 |
|
|
| 1480 |
|Output|Input| |
|
| 1481 |
|@'value'@|@"ACAD.Specimen"."eventDate"@| |
|
| 1482 |
|
|
| 1483 |
# Joining together input tables into temp table |
|
| 1484 |
<pre><code class="SQL"> |
|
| 1485 |
CREATE TEMP TABLE "in#8" AS |
|
| 1486 |
SELECT |
|
| 1487 |
"ACAD.Specimen"."row_num" AS "row_num" |
|
| 1488 |
, "ACAD.Specimen"."eventDate" AS "ACAD.Specimen.eventDate" |
|
| 1489 |
FROM "ACAD.Specimen" |
|
| 1490 |
ORDER BY row_num |
|
| 1491 |
</code></pre>Temp table: @"in#8"@ |
|
| 1492 |
# Defining wrapper function |
|
| 1493 |
<pre><code class="SQL"> |
|
| 1494 |
CREATE TEMP TABLE "_dateRangeEnd(eventDate)" AS |
|
| 1495 |
SELECT |
|
| 1496 |
"in#8"."row_num" |
|
Also available in: Unified diff
Added inputs/ACAD/Specimen/logs/steps.by_col.log.sql