Revision 5896
Added by Aaron Marcuse-Kubitza about 12 years ago
| vegbien.sql | ||
|---|---|---|
| 405 | 405 |
-- |
| 406 | 406 |
|
| 407 | 407 |
CREATE FUNCTION _taxonlabel_set_matched_label_id(taxonlabel_id integer, matched_label_id integer, matched_label_fit_fraction double precision DEFAULT NULL::double precision) RETURNS integer |
| 408 |
LANGUAGE sql |
|
| 409 |
AS $_$ |
|
| 410 |
UPDATE taxonlabel SET |
|
| 411 |
matched_label_id = $2 |
|
| 412 |
, matched_label_fit_fraction = $3 |
|
| 413 |
WHERE taxonlabel_id = $1 |
|
| 414 |
RETURNING taxonlabel_id |
|
| 415 |
$_$; |
|
| 408 |
LANGUAGE plpgsql |
|
| 409 |
AS $$ |
|
| 410 |
BEGIN |
|
| 411 |
-- Use EXECUTE to avoid caching query plan |
|
| 412 |
EXECUTE ' |
|
| 413 |
UPDATE taxonlabel SET |
|
| 414 |
matched_label_id = '||quote_literal(matched_label_id)||' |
|
| 415 |
, matched_label_fit_fraction = ' |
|
| 416 |
||quote_literal(matched_label_fit_fraction)||' |
|
| 417 |
WHERE taxonlabel_id = '||quote_literal(taxonlabel_id)||' |
|
| 418 |
'; |
|
| 419 |
|
|
| 420 |
RETURN taxonlabel_id; |
|
| 421 |
END; |
|
| 422 |
$$; |
|
| 416 | 423 |
|
| 417 | 424 |
|
| 418 | 425 |
-- |
| ... | ... | |
| 420 | 427 |
-- |
| 421 | 428 |
|
| 422 | 429 |
CREATE FUNCTION _taxonlabel_set_parent_id(taxonlabel_id integer, parent_id integer) RETURNS integer |
| 423 |
LANGUAGE sql |
|
| 424 |
AS $_$ |
|
| 425 |
UPDATE taxonlabel SET parent_id = $2 WHERE taxonlabel_id = $1 |
|
| 426 |
RETURNING taxonlabel_id |
|
| 427 |
$_$; |
|
| 430 |
LANGUAGE plpgsql |
|
| 431 |
AS $$ |
|
| 432 |
BEGIN |
|
| 433 |
-- Use EXECUTE to avoid caching query plan |
|
| 434 |
EXECUTE ' |
|
| 435 |
UPDATE taxonlabel SET parent_id = '||quote_literal(parent_id)||' |
|
| 436 |
WHERE taxonlabel_id = '||quote_literal(taxonlabel_id)||' |
|
| 437 |
'; |
|
| 438 |
|
|
| 439 |
RETURN taxonlabel_id; |
|
| 440 |
END; |
|
| 441 |
$$; |
|
| 428 | 442 |
|
| 429 | 443 |
|
| 430 | 444 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: Functions containing UPDATE statements: Use PL/pgSQL's EXECUTE statement to avoid caching query plans. This is necessary because as the table grows over time, the optimal query plan may change.