- o se faiga sili ona mamana ma faigofie pe a fai o gaioiga tutusa "loloto" e faia i luga o faʻamatalaga e fesoʻotaʻi. Ae o le le pulea o le toe faʻafoʻisia o se mea leaga e mafai ona taʻitaʻia ai faasalaga e le gata faiga, po'o (lea e tupu soo) i "'ai" manatua uma avanoa.
DBMS i lenei tulaga galue i luga o mataupu faavae tutusa - "Sa latou fai mai ou te eli, ona ou eli lea". O lau talosaga e le gata e mafai ona faʻagesegese faiga tuaoi, faʻaaogaina i taimi uma punaoa processor, ae faʻapea foi "tuu" le database atoa, "'ai" manatua uma avanoa. puipuiga mai le toe fo'i e le gata - o le matafaioi a le tagata atiaeina lava ia.
I le PostgreSQL, le mafai ona faʻaogaina fesili toe faʻaaogaina e ala i na fa'aalia i le taimi tuai o le version 8.4, ae e mafai lava ona e fa'afeiloa'i i taimi uma fa'atonuga "leai se puipuiga". E fa'afefea ona 'ave'ese oe mai fa'afitauli fa'apenei?
Aua ne'i tusia ni fesili toe fai
Ma tusi mea e le toe fa'afo'i. Fa'afetai, Lau K.O.
O le mea moni, PostgreSQL e maua ai le tele o galuega e mafai ona e faʻaogaina lē fa'aaoga recursion.
Fa'aaoga se auala e matua ese lava ile fa'afitauli
O nisi taimi e mafai ona e vaʻai i le faʻafitauli mai le "itu eseese". Sa ou tuuina atu se faataitaiga o se tulaga faapena i le tusiga - fa'ateleina o se seti o numera e aunoa ma le fa'aogaina o galuega fa'aopoopo masani:
WITH RECURSIVE src AS (
SELECT '{2,3,5,7,11,13,17,19}'::integer[] arr
)
, T(i, val) AS (
SELECT
1::bigint
, 1
UNION ALL
SELECT
i + 1
, val * arr[i]
FROM
T
, src
WHERE
i <= array_length(arr, 1)
)
SELECT
val
FROM
T
ORDER BY -- отбор финального результата
i DESC
LIMIT 1;O lenei talosaga e mafai ona suia i se filifiliga mai tagata tomai faapitoa i le matematika:
WITH src AS (
SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
SELECT
exp(sum(ln(prime)))::integer val
FROM
src;Fa'aaoga le genera_series nai lo fa'aoga
Fa'apea o lo'o tatou feagai ma le galuega o le fa'atupuina o prefix uma e mafai mo se manoa 'abcdefgh':
WITH RECURSIVE T AS (
SELECT 'abcdefgh' str
UNION ALL
SELECT
substr(str, 1, length(str) - 1)
FROM
T
WHERE
length(str) > 1
)
TABLE T;
E te mautinoa e te manaʻomia le toe faʻaleleia iinei?.. Afai e te faʻaaogaina LATERAL и generate_series, ona e le mana'omia lea o le CTE:
SELECT
substr(str, 1, ln) str
FROM
(VALUES('abcdefgh')) T(str)
, LATERAL(
SELECT generate_series(length(str), 1, -1) ln
) X;Suia le fausaga o faamaumauga
Mo se faʻataʻitaʻiga, o loʻo i ai sau laulau o feʻau faʻasalalauga ma fesoʻotaʻiga mai le na tali atu ia ai, poʻo se filo i totonu :
CREATE TABLE message(
message_id
uuid
PRIMARY KEY
, reply_to
uuid
REFERENCES message
, body
text
);
CREATE INDEX ON message(reply_to);
Ia, o se talosaga masani e sii mai uma feʻau i luga o se autu e foliga mai e pei o lenei:
WITH RECURSIVE T AS (
SELECT
*
FROM
message
WHERE
message_id = $1
UNION ALL
SELECT
m.*
FROM
T
JOIN
message m
ON m.reply_to = T.message_id
)
TABLE T;Ae talu ai tatou te manaʻomia i taimi uma le autu atoa mai le aʻa savali, aisea la tatou te le faia ai fa'aopoopo lona ID i fa'amatalaga ta'itasi otometi?
-- добавим поле с общим идентификатором темы и индекс на него
ALTER TABLE message
ADD COLUMN theme_id uuid;
CREATE INDEX ON message(theme_id);
-- инициализируем идентификатор темы в триггере при вставке
CREATE OR REPLACE FUNCTION ins() RETURNS TRIGGER AS $$
BEGIN
NEW.theme_id = CASE
WHEN NEW.reply_to IS NULL THEN NEW.message_id -- берем из стартового события
ELSE ( -- или из сообщения, на которое отвечаем
SELECT
theme_id
FROM
message
WHERE
message_id = NEW.reply_to
)
END;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER ins BEFORE INSERT
ON message
FOR EACH ROW
EXECUTE PROCEDURE ins();
Ole taimi nei e mafai ona fa'aititia uma a tatou fesili toe fa'afo'i ile na'o lenei:
SELECT
*
FROM
message
WHERE
theme_id = $1;Fa'aaogā "fa'atapula'a"
Afai e le mafai ona tatou suia le fausaga o le database mo nisi mafuaaga, seʻi o tatou vaʻai pe o le a le mea e mafai ona tatou faʻalagolago i ai e oʻo lava i le i ai o se mea sese i faʻamaumauga e le oʻo atu ai i le toe faʻafoʻisia e le gata.
Fa'ailoga loloto o le toe fa'afo'i
E na'o le fa'aopoopoina o le fata i le tasi i la'asaga ta'itasi ta'itasi se'ia o'o i se tapula'a matou te manatu e le'o atoatoa:
WITH RECURSIVE T AS (
SELECT
0 i
...
UNION ALL
SELECT
i + 1
...
WHERE
T.i < 64 -- предел
)
Pro: A matou taumafai e faʻasolo, matou te le faia lava se mea e sili atu nai lo le tapulaa faʻamaonia o faʻamatalaga "le loloto".
leaga: E leai se fa'amautinoaga o le a matou le toe fa'atinoina lea lava fa'amaumauga - mo se fa'ata'ita'iga, i le loloto o le 15 ma le 25, ona sosoo ai lea ma le +10 uma. Ma e leai se tasi na folafola mai se mea e uiga i le "lautele".
I le tulaga aloaʻia, o sea toe faʻafoʻisia o le a le gata, ae afai i laʻasaga taʻitasi o le numera o faʻamaumauga e faʻateleina faʻateleina, tatou te iloa lelei uma pe faʻapefea ona muta ...
Leoleo o le "ala"
Matou te faʻaopoopoina faʻamatalaga mea uma na matou feagai i luga o le ala toe faʻafoʻi i totonu o se laina, o se "ala" tulaga ese i ai:
WITH RECURSIVE T AS (
SELECT
ARRAY[id] path
...
UNION ALL
SELECT
path || id
...
WHERE
id <> ALL(T.path) -- не совпадает ни с одним из
)
Pro: Afai ei ai se taamilosaga i totonu o faʻamaumauga, matou te matua le faʻagasolo faʻatasi le faʻamaumauga tutusa i totonu o le ala lava e tasi.
leaga: Ae i le taimi lava e tasi, e mafai lava ona tatou pasia faʻamaumauga uma e aunoa ma le toe faia.
Tapulaa Umi o Auala
Ina ia aloese mai le tulaga o le recursion "seseese" i se loloto le malamalama, e mafai ona tatou tuufaatasia auala e lua muamua. Pe, afai tatou te le manaʻo e lagolagoina fanua e le manaʻomia, faʻaopoopo le tulaga mo le faʻaauauina o le toe faʻaleleia ma se faʻatusatusaga o le umi o le ala:
WITH RECURSIVE T AS (
SELECT
ARRAY[id] path
...
UNION ALL
SELECT
path || id
...
WHERE
id <> ALL(T.path) AND
array_length(T.path, 1) < 10
)
Filifili se metotia i lou tofo!
puna: www.habr.com