Revision 5798
Added by Aaron Marcuse-Kubitza over 12 years ago
| inputs/.TNRS/schema.sql | ||
|---|---|---|
| 1 |
CREATE OR REPLACE FUNCTION array_to_string(anyarray, text) |
|
| 2 |
RETURNS text AS |
|
| 3 |
$BODY$ |
|
| 4 |
SELECT pg_catalog.array_to_string($1, $2) |
|
| 5 |
$BODY$ |
|
| 6 |
LANGUAGE sql IMMUTABLE STRICT |
|
| 7 |
COST 100; |
|
| 8 |
|
|
| 1 | 9 |
CREATE TABLE tnrs |
| 2 | 10 |
( |
| 3 | 11 |
"Time_submitted" timestamp with time zone, |
| ... | ... | |
| 50 | 58 |
CREATE INDEX tnrs_accepted_names |
| 51 | 59 |
ON tnrs |
| 52 | 60 |
USING btree |
| 53 |
((NOT "Name_submitted" = ("Accepted_name" || COALESCE(' '::text || "Accepted_name_author", ''::text))) , "Name_submitted" );
|
|
| 61 |
((NOT "Name_submitted" = NULLIF(:schema.array_to_string(ARRAY["Accepted_name", "Accepted_name_author"], ' '::text), ''::text)) , "Name_submitted" );
|
|
| 54 | 62 |
ALTER TABLE tnrs CLUSTER ON tnrs_accepted_names; |
Also available in: Unified diff
inputs/.TNRS/schema.sql: tnrs_accepted_names: Use simpler array_to_string() instead of || and COALESCE to put together the taxonomic name that will be submitted back to TNRS for parsing. Note that this requires defining an IMMUTABLE wrapper function for array_to_string(), because pg_catalog.array_to_string() is declared STABLE but indexes require functions to be IMMUTABLE (http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg156323.html).