Revision 2940
Added by Aaron Marcuse-Kubitza over 12 years ago
| schemas/functions.sql | ||
|---|---|---|
| 76 | 76 |
LANGUAGE plpgsql IMMUTABLE |
| 77 | 77 |
AS $$ |
| 78 | 78 |
BEGIN |
| 79 |
new.result := ( |
|
| 80 |
SELECT functions.join_strs('; ', value)
|
|
| 81 |
FROM |
|
| 82 |
( |
|
| 83 |
SELECT * |
|
| 84 |
FROM |
|
| 85 |
( |
|
| 86 |
SELECT |
|
| 87 |
DISTINCT ON (value) |
|
| 88 |
* |
|
| 89 |
FROM |
|
| 90 |
(VALUES |
|
| 91 |
(0, new."0") |
|
| 92 |
, (1, new."1") |
|
| 93 |
, (2, new."2") |
|
| 94 |
, (3, new."3") |
|
| 95 |
, (4, new."4") |
|
| 96 |
, (5, new."5") |
|
| 97 |
, (6, new."6") |
|
| 98 |
, (7, new."7") |
|
| 99 |
, (8, new."8") |
|
| 100 |
, (9, new."9") |
|
| 101 |
) |
|
| 102 |
AS v (sort_order, value) |
|
| 103 |
) |
|
| 104 |
AS v |
|
| 105 |
ORDER BY sort_order |
|
| 106 |
) |
|
| 107 |
AS v |
|
| 108 |
); |
|
| 79 |
new.result := _merge(new."0", new."1", new."2", new."3", new."4", new."5", |
|
| 80 |
new."6", new."7", new."8", new."9"); |
|
| 109 | 81 |
RETURN new; |
| 110 | 82 |
END; |
| 111 | 83 |
$$; |
| 112 | 84 |
|
| 113 | 85 |
|
| 114 | 86 |
-- |
| 87 |
-- Name: _merge(text, text, text, text, text, text, text, text, text, text); Type: FUNCTION; Schema: functions; Owner: - |
|
| 88 |
-- |
|
| 89 |
|
|
| 90 |
CREATE FUNCTION _merge("0" text DEFAULT NULL::text, "1" text DEFAULT NULL::text, "2" text DEFAULT NULL::text, "3" text DEFAULT NULL::text, "4" text DEFAULT NULL::text, "5" text DEFAULT NULL::text, "6" text DEFAULT NULL::text, "7" text DEFAULT NULL::text, "8" text DEFAULT NULL::text, "9" text DEFAULT NULL::text) RETURNS text
|
|
| 91 |
LANGUAGE sql IMMUTABLE |
|
| 92 |
AS $_$ |
|
| 93 |
SELECT functions.join_strs('; ', value)
|
|
| 94 |
FROM |
|
| 95 |
( |
|
| 96 |
SELECT * |
|
| 97 |
FROM |
|
| 98 |
( |
|
| 99 |
SELECT |
|
| 100 |
DISTINCT ON (value) |
|
| 101 |
* |
|
| 102 |
FROM |
|
| 103 |
(VALUES |
|
| 104 |
(0, $1) |
|
| 105 |
, (1, $2) |
|
| 106 |
, (2, $3) |
|
| 107 |
, (3, $4) |
|
| 108 |
, (4, $5) |
|
| 109 |
, (5, $6) |
|
| 110 |
, (6, $7) |
|
| 111 |
, (7, $8) |
|
| 112 |
, (8, $9) |
|
| 113 |
, (9, $10) |
|
| 114 |
) |
|
| 115 |
AS v (sort_order, value) |
|
| 116 |
) |
|
| 117 |
AS v |
|
| 118 |
ORDER BY sort_order |
|
| 119 |
) |
|
| 120 |
AS v |
|
| 121 |
$_$; |
|
| 122 |
|
|
| 123 |
|
|
| 124 |
-- |
|
| 115 | 125 |
-- Name: _nullIf(); Type: FUNCTION; Schema: functions; Owner: - |
| 116 | 126 |
-- |
| 117 | 127 |
|
| ... | ... | |
| 454 | 464 |
-- Name: _merge; Type: TRIGGER; Schema: functions; Owner: - |
| 455 | 465 |
-- |
| 456 | 466 |
|
| 457 |
CREATE TRIGGER _merge BEFORE INSERT OR UPDATE ON _merge FOR EACH ROW EXECUTE PROCEDURE _merge(); |
|
| 467 |
CREATE TRIGGER _merge BEFORE INSERT OR UPDATE ON _merge FOR EACH ROW EXECUTE PROCEDURE functions._merge();
|
|
| 458 | 468 |
|
| 459 | 469 |
|
| 460 | 470 |
-- |
Also available in: Unified diff
schemas/functions.sql: Added plain function _merge() and use it in trigger function _merge()