Revision 7827
Added by Aaron Marcuse-Kubitza over 11 years ago
| inputs/.TNRS/schema.sql | ||
|---|---|---|
| 1 |
CREATE OR REPLACE FUNCTION concat_delim(IN delim text, VARIADIC text[]) |
|
| 2 |
RETURNS text AS |
|
| 3 |
$BODY$ |
|
| 4 |
SELECT NULLIF(array_to_string($2, $1), ''::text) |
|
| 5 |
$BODY$ |
|
| 6 |
LANGUAGE sql IMMUTABLE |
|
| 7 |
COST 100; |
|
| 8 |
COMMENT ON FUNCTION concat_delim(text, text[]) IS 'Similar to concat() but separates elements with a delimeter'; |
|
| 9 |
|
|
| 10 |
|
|
| 1 | 11 |
CREATE TABLE tnrs |
| 2 | 12 |
( |
| 3 | 13 |
"Time_submitted" timestamp with time zone, |
| ... | ... | |
| 49 | 59 |
RETURNS trigger AS |
| 50 | 60 |
$BODY$ |
| 51 | 61 |
BEGIN |
| 52 |
new."Accepted_scientific_name" = NULLIF(array_to_string(ARRAY[
|
|
| 53 |
NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), new."Accepted_name") |
|
| 62 |
new."Accepted_scientific_name" = concat_delim(' '
|
|
| 63 |
, NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), new."Accepted_name")
|
|
| 54 | 64 |
, new."Accepted_name" |
| 55 | 65 |
, new."Accepted_name_author" |
| 56 |
], ' '), '');
|
|
| 66 |
); |
|
| 57 | 67 |
new."Max_score" = GREATEST( |
| 58 | 68 |
new."Overall_score"::double precision |
| 59 | 69 |
, new."Family_score"::double precision |
Also available in: Unified diff
inputs/.TNRS/schema.sql: tnrs_populate_derived_fields(): Use new concat_delim()