Revision 12516
Added by Aaron Marcuse-Kubitza almost 11 years ago
| vegbien.sql | ||
|---|---|---|
| 862 | 862 |
DELETE FROM taxondetermination |
| 863 | 863 |
USING taxonoccurrence |
| 864 | 864 |
WHERE |
| 865 |
taxonoccurrence.source_id = source_by_shortname($1)
|
|
| 865 |
taxonoccurrence.source_id = (SELECT source_by_shortname($1))
|
|
| 866 | 866 |
AND taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id |
| 867 |
AND taxondetermination.source_id = source_by_shortname('TNRS')
|
|
| 867 |
AND taxondetermination.source_id = (SELECT source_by_shortname('TNRS'))
|
|
| 868 | 868 |
$_$; |
| 869 | 869 |
|
| 870 | 870 |
|
| ... | ... | |
| 2484 | 2484 |
$_$; |
| 2485 | 2485 |
|
| 2486 | 2486 |
|
| 2487 |
-- |
|
| 2488 |
-- Name: ~type._plots_01_count_of_projects; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2489 |
-- |
|
| 2490 |
|
|
| 2491 |
CREATE TABLE "~type._plots_01_count_of_projects" ( |
|
| 2492 |
projects bigint |
|
| 2493 |
); |
|
| 2494 |
|
|
| 2495 |
|
|
| 2496 |
-- |
|
| 2497 |
-- Name: ~type._plots_02_list_of_project_names; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2498 |
-- |
|
| 2499 |
|
|
| 2500 |
CREATE TABLE "~type._plots_02_list_of_project_names" ( |
|
| 2501 |
project_name text |
|
| 2502 |
); |
|
| 2503 |
|
|
| 2504 |
|
|
| 2505 |
-- |
|
| 2506 |
-- Name: ~type._plots_03_count_of_all_plots_in_this_source; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2507 |
-- |
|
| 2508 |
|
|
| 2509 |
CREATE TABLE "~type._plots_03_count_of_all_plots_in_this_source" ( |
|
| 2510 |
plots bigint |
|
| 2511 |
); |
|
| 2512 |
|
|
| 2513 |
|
|
| 2514 |
-- |
|
| 2515 |
-- Name: ~type._plots_06_list_of_plots_with_stem_measurements; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2516 |
-- |
|
| 2517 |
|
|
| 2518 |
CREATE TABLE "~type._plots_06_list_of_plots_with_stem_measurements" ( |
|
| 2519 |
project_name text, |
|
| 2520 |
"SiteCode" text |
|
| 2521 |
); |
|
| 2522 |
|
|
| 2523 |
|
|
| 2524 |
-- |
|
| 2525 |
-- Name: ~type._plots_10_count_of_individuals_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2526 |
-- |
|
| 2527 |
|
|
| 2528 |
CREATE TABLE "~type._plots_10_count_of_individuals_per_plot_in_each_project" ( |
|
| 2529 |
project_name text, |
|
| 2530 |
plotcode text, |
|
| 2531 |
individuals bigint |
|
| 2532 |
); |
|
| 2533 |
|
|
| 2534 |
|
|
| 2535 |
-- |
|
| 2536 |
-- Name: ~type._plots_11_count_of_stems_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2537 |
-- |
|
| 2538 |
|
|
| 2539 |
CREATE TABLE "~type._plots_11_count_of_stems_per_plot_in_each_project" ( |
|
| 2540 |
project_name text, |
|
| 2541 |
plotcode text, |
|
| 2542 |
stems bigint |
|
| 2543 |
); |
|
| 2544 |
|
|
| 2545 |
|
|
| 2546 |
-- |
|
| 2547 |
-- Name: ~type._plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2548 |
-- |
|
| 2549 |
|
|
| 2550 |
CREATE TABLE "~type._plots_12_count_of_verbatim_taxa_per_plot_in_each_project" ( |
|
| 2551 |
project_name text, |
|
| 2552 |
plotcode text, |
|
| 2553 |
taxa bigint |
|
| 2554 |
); |
|
| 2555 |
|
|
| 2556 |
|
|
| 2557 |
-- |
|
| 2558 |
-- Name: ~type._plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2559 |
-- |
|
| 2560 |
|
|
| 2561 |
CREATE TABLE "~type._plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_" ( |
|
| 2562 |
project_name text, |
|
| 2563 |
plotcode text, |
|
| 2564 |
taxon text |
|
| 2565 |
); |
|
| 2566 |
|
|
| 2567 |
|
|
| 2568 |
-- |
|
| 2569 |
-- Name: ~type._plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_e; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2570 |
-- |
|
| 2571 |
|
|
| 2572 |
CREATE TABLE "~type._plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_e" ( |
|
| 2573 |
project_name text, |
|
| 2574 |
plotcode text, |
|
| 2575 |
taxon text, |
|
| 2576 |
individuals bigint |
|
| 2577 |
); |
|
| 2578 |
|
|
| 2579 |
|
|
| 2580 |
-- |
|
| 2581 |
-- Name: ~type._plots_17_count_of_subplots_per_plot_for_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 2582 |
-- |
|
| 2583 |
|
|
| 2584 |
CREATE TABLE "~type._plots_17_count_of_subplots_per_plot_for_each_project" ( |
|
| 2585 |
project_name text, |
|
| 2586 |
plotcode text, |
|
| 2587 |
subplots bigint |
|
| 2588 |
); |
|
| 2589 |
|
|
| 2590 |
|
|
| 2591 | 2487 |
SET search_path = public, pg_catalog; |
| 2592 | 2488 |
|
| 2593 | 2489 |
-- |
| ... | ... | |
| 5894 | 5790 |
FROM ((taxonlabel datasource_taxonlabel |
| 5895 | 5791 |
JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) |
| 5896 | 5792 |
JOIN taxonlabel_relationship accepted_taxonlabel_descendant ON ((accepted_taxonlabel_descendant.ancestor_id = accepted_taxonlabel.taxonlabel_id))) |
| 5897 |
WHERE (((datasource_taxonlabel.source_id = source_by_shortname('IUCN'::text)) AND (datasource_taxonlabel.taxonomicname IS NOT NULL)) AND (accepted_taxonlabel.rank >= 'species'::taxonrank));
|
|
| 5793 |
WHERE (((datasource_taxonlabel.source_id = ( SELECT source_by_shortname('IUCN'::text) AS source_by_shortname)) AND (datasource_taxonlabel.taxonomicname IS NOT NULL)) AND (accepted_taxonlabel.rank >= 'species'::taxonrank));
|
|
| 5898 | 5794 |
|
| 5899 | 5795 |
|
| 5900 | 5796 |
-- |
| ... | ... | |
| 6134 | 6030 |
FROM ((public.top_plot |
| 6135 | 6031 |
JOIN public.locationevent USING (location_id)) |
| 6136 | 6032 |
JOIN public.project USING (project_id)) |
| 6137 |
WHERE ((top_plot.source_id = public.source_by_shortname(("current_schema"())::text)) AND (EXISTS ( SELECT NULL::unknown AS unknown
|
|
| 6033 |
WHERE ((top_plot.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname)) AND (EXISTS ( SELECT NULL::unknown AS unknown
|
|
| 6138 | 6034 |
FROM (((((public.location |
| 6139 | 6035 |
JOIN public.locationevent locationevent_1 USING (location_id)) |
| 6140 | 6036 |
JOIN public.taxonoccurrence USING (locationevent_id)) |
| ... | ... | |
| 6389 | 6285 |
SELECT count(*) AS "totalRecords" |
| 6390 | 6286 |
FROM (public.trait |
| 6391 | 6287 |
JOIN public.taxonoccurrence USING (taxonoccurrence_id)) |
| 6392 |
WHERE (taxonoccurrence.source_id = public.source_by_shortname(("current_schema"())::text));
|
|
| 6288 |
WHERE (taxonoccurrence.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname));
|
|
| 6393 | 6289 |
|
| 6394 | 6290 |
|
| 6395 | 6291 |
-- |
| ... | ... | |
| 6407 | 6303 |
SELECT count(DISTINCT trait.name) AS traits |
| 6408 | 6304 |
FROM (public.trait |
| 6409 | 6305 |
JOIN public.taxonoccurrence USING (taxonoccurrence_id)) |
| 6410 |
WHERE (taxonoccurrence.source_id = public.source_by_shortname(("current_schema"())::text));
|
|
| 6306 |
WHERE (taxonoccurrence.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname));
|
|
| 6411 | 6307 |
|
| 6412 | 6308 |
|
| 6413 | 6309 |
-- |
| ... | ... | |
| 6418 | 6314 |
SELECT DISTINCT trait.name AS trait |
| 6419 | 6315 |
FROM (public.trait |
| 6420 | 6316 |
JOIN public.taxonoccurrence USING (taxonoccurrence_id)) |
| 6421 |
WHERE (taxonoccurrence.source_id = public.source_by_shortname(("current_schema"())::text))
|
|
| 6317 |
WHERE (taxonoccurrence.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname))
|
|
| 6422 | 6318 |
ORDER BY trait.name; |
| 6423 | 6319 |
|
| 6424 | 6320 |
|
| ... | ... | |
| 6431 | 6327 |
count(*) AS measurements |
| 6432 | 6328 |
FROM (public.trait |
| 6433 | 6329 |
JOIN public.taxonoccurrence USING (taxonoccurrence_id)) |
| 6434 |
WHERE (taxonoccurrence.source_id = public.source_by_shortname(("current_schema"())::text))
|
|
| 6330 |
WHERE (taxonoccurrence.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname))
|
|
| 6435 | 6331 |
GROUP BY trait.name |
| 6436 | 6332 |
ORDER BY trait.name; |
| 6437 | 6333 |
|
| ... | ... | |
| 6443 | 6339 |
CREATE VIEW _traits_05_count_taxa AS |
| 6444 | 6340 |
SELECT count(DISTINCT ROW(taxonverbatim.taxonname, taxonverbatim.author)) AS taxa |
| 6445 | 6341 |
FROM public.taxonverbatim |
| 6446 |
WHERE (taxonverbatim.source_id = public.source_by_shortname(("current_schema"())::text));
|
|
| 6342 |
WHERE (taxonverbatim.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname));
|
|
| 6447 | 6343 |
|
| 6448 | 6344 |
|
| 6449 | 6345 |
-- |
| ... | ... | |
| 6462 | 6358 |
CREATE VIEW _traits_06_list_distinct_taxa AS |
| 6463 | 6359 |
SELECT DISTINCT concat_ws(' '::text, taxonverbatim.taxonname, taxonverbatim.author) AS taxonwithauthor
|
| 6464 | 6360 |
FROM public.taxonverbatim |
| 6465 |
WHERE (taxonverbatim.source_id = public.source_by_shortname(("current_schema"())::text))
|
|
| 6361 |
WHERE (taxonverbatim.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname))
|
|
| 6466 | 6362 |
ORDER BY concat_ws(' '::text, taxonverbatim.taxonname, taxonverbatim.author);
|
| 6467 | 6363 |
|
| 6468 | 6364 |
|
| ... | ... | |
| 6486 | 6382 |
trait.units |
| 6487 | 6383 |
FROM (public.trait |
| 6488 | 6384 |
JOIN public.taxonoccurrence USING (taxonoccurrence_id)) |
| 6489 |
WHERE (taxonoccurrence.source_id = public.source_by_shortname(("current_schema"())::text))
|
|
| 6385 |
WHERE (taxonoccurrence.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname))
|
|
| 6490 | 6386 |
ORDER BY trait.name, trait.value, trait.units; |
| 6491 | 6387 |
|
| 6492 | 6388 |
|
| ... | ... | |
| 6501 | 6397 |
FROM ((public.taxonverbatim |
| 6502 | 6398 |
JOIN public.taxondetermination ON (((taxondetermination.taxonverbatim_id = taxonverbatim.taxonverbatim_id) AND taxondetermination.iscurrent))) |
| 6503 | 6399 |
JOIN public.trait USING (taxonoccurrence_id)) |
| 6504 |
WHERE (taxonverbatim.source_id = public.source_by_shortname(("current_schema"())::text))
|
|
| 6400 |
WHERE (taxonverbatim.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname))
|
|
| 6505 | 6401 |
ORDER BY taxonverbatim.taxonname; |
| 6506 | 6402 |
|
| 6507 | 6403 |
|
| 6508 | 6404 |
-- |
| 6405 |
-- Name: ~type._plots_01_count_of_projects; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6406 |
-- |
|
| 6407 |
|
|
| 6408 |
CREATE TABLE "~type._plots_01_count_of_projects" ( |
|
| 6409 |
projects bigint |
|
| 6410 |
); |
|
| 6411 |
|
|
| 6412 |
|
|
| 6413 |
-- |
|
| 6414 |
-- Name: ~type._plots_02_list_of_project_names; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6415 |
-- |
|
| 6416 |
|
|
| 6417 |
CREATE TABLE "~type._plots_02_list_of_project_names" ( |
|
| 6418 |
project_name text |
|
| 6419 |
); |
|
| 6420 |
|
|
| 6421 |
|
|
| 6422 |
-- |
|
| 6423 |
-- Name: ~type._plots_03_count_of_all_plots_in_this_source; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6424 |
-- |
|
| 6425 |
|
|
| 6426 |
CREATE TABLE "~type._plots_03_count_of_all_plots_in_this_source" ( |
|
| 6427 |
plots bigint |
|
| 6428 |
); |
|
| 6429 |
|
|
| 6430 |
|
|
| 6431 |
-- |
|
| 6432 |
-- Name: ~type._plots_06_list_of_plots_with_stem_measurements; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6433 |
-- |
|
| 6434 |
|
|
| 6435 |
CREATE TABLE "~type._plots_06_list_of_plots_with_stem_measurements" ( |
|
| 6436 |
project_name text, |
|
| 6437 |
"SiteCode" text |
|
| 6438 |
); |
|
| 6439 |
|
|
| 6440 |
|
|
| 6441 |
-- |
|
| 6442 |
-- Name: ~type._plots_10_count_of_individuals_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6443 |
-- |
|
| 6444 |
|
|
| 6445 |
CREATE TABLE "~type._plots_10_count_of_individuals_per_plot_in_each_project" ( |
|
| 6446 |
project_name text, |
|
| 6447 |
plotcode text, |
|
| 6448 |
individuals bigint |
|
| 6449 |
); |
|
| 6450 |
|
|
| 6451 |
|
|
| 6452 |
-- |
|
| 6453 |
-- Name: ~type._plots_11_count_of_stems_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6454 |
-- |
|
| 6455 |
|
|
| 6456 |
CREATE TABLE "~type._plots_11_count_of_stems_per_plot_in_each_project" ( |
|
| 6457 |
project_name text, |
|
| 6458 |
plotcode text, |
|
| 6459 |
stems bigint |
|
| 6460 |
); |
|
| 6461 |
|
|
| 6462 |
|
|
| 6463 |
-- |
|
| 6464 |
-- Name: ~type._plots_12_count_of_verbatim_taxa_per_plot_in_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6465 |
-- |
|
| 6466 |
|
|
| 6467 |
CREATE TABLE "~type._plots_12_count_of_verbatim_taxa_per_plot_in_each_project" ( |
|
| 6468 |
project_name text, |
|
| 6469 |
plotcode text, |
|
| 6470 |
taxa bigint |
|
| 6471 |
); |
|
| 6472 |
|
|
| 6473 |
|
|
| 6474 |
-- |
|
| 6475 |
-- Name: ~type._plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6476 |
-- |
|
| 6477 |
|
|
| 6478 |
CREATE TABLE "~type._plots_13_list_of_distinct_verbatim_taxa_in_each_plot_in_" ( |
|
| 6479 |
project_name text, |
|
| 6480 |
plotcode text, |
|
| 6481 |
taxon text |
|
| 6482 |
); |
|
| 6483 |
|
|
| 6484 |
|
|
| 6485 |
-- |
|
| 6486 |
-- Name: ~type._plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_e; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6487 |
-- |
|
| 6488 |
|
|
| 6489 |
CREATE TABLE "~type._plots_14_count_of_indiv_per_verbatim_taxon_per_plot_in_e" ( |
|
| 6490 |
project_name text, |
|
| 6491 |
plotcode text, |
|
| 6492 |
taxon text, |
|
| 6493 |
individuals bigint |
|
| 6494 |
); |
|
| 6495 |
|
|
| 6496 |
|
|
| 6497 |
-- |
|
| 6498 |
-- Name: ~type._plots_17_count_of_subplots_per_plot_for_each_project; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
|
| 6499 |
-- |
|
| 6500 |
|
|
| 6501 |
CREATE TABLE "~type._plots_17_count_of_subplots_per_plot_for_each_project" ( |
|
| 6502 |
project_name text, |
|
| 6503 |
plotcode text, |
|
| 6504 |
subplots bigint |
|
| 6505 |
); |
|
| 6506 |
|
|
| 6507 |
|
|
| 6508 |
-- |
|
| 6509 | 6509 |
-- Name: ~type._traits_01_count_records; Type: TABLE; Schema: public_validations; Owner: -; Tablespace: |
| 6510 | 6510 |
-- |
| 6511 | 6511 |
|
Also available in: Unified diff
bugfix: *.sql: public.source_by_shortname(): need to wrap it in a nested SELECT because Postgres incorrectly does not constant-fold (inline) it, leading to a slowdown when it is therefore run many times. this is done using the steps at wiki.vegpath.org/Postgres_queries#wrap-function-call-in-nested-SELECT .