PostgreSQL. ltree. Инициализация поля parent_code.

[mshurutov]

Ну вот, данные залиты, созданы нужные индексы, ну и все такое.
Таперича нужно инициализировать поле parent_code. Ну и поехали.
Скопитырено отседова.

• Для субъектов федерального уровня родитель... Нет у них родителя.
  

Установка родителя для объектов федерального подчинения единая для всех вариантов заполнения.

kladr=> \timing
Секундомер включен.
kladr=> update kladr set parent_code = '' where level = 1;
UPDATE 86
Время: 134,192 мс

Оба два других будут использовать только subpath из встроенного в PostgreSQL арсенала.

• В лоб, одной функцией
  


kladr=> select initkh();
initkh
--------
189598
(1 строка)

Время: 44216,704 мс
kladr=> select count(*) from kladr where parent_code is NULL;
count
-------
0
(1 строка)

Время: 56,546 мс

[тело ф-ии initkh()]

create or replace function initkh() returns bigint as $$
declare
rowcount bigint;
pcode char(13);
lvl int2;
hrrh ltree;
phrrh ltree;
begin
rowcount := 0;
for hrrh,lvl in select hierarch,level from kladr where level > 1 loop
case lvl
when 2 then
phrrh := subpath(hrrh,0,1);
when 3 then
if subpath(hrrh,1,1) = '000' then
phrrh := subpath(hrrh,0,1);
else
phrrh := subpath(hrrh,0,2);
end if;
when 4 then
if subpath(hrrh,1,2) = '000.000' then
phrrh := subpath(hrrh,0,1);
elsif subpath(hrrh,2,1) = '000' then
phrrh := subpath(hrrh,0,2);
else
phrrh := subpath(hrrh,0,3);
end if;
end case;
rowcount := rowcount+1;
execute 'select code from kladr where hierarch = $1' into pcode using phrrh;
execute 'update kladr set parent_code = $1 where hierarch = $2' using pcode,hrrh;
end loop;
return rowcount;
end;
$$ LANGUAGE plpgsql;


• Изподвыподверта
  


kladr=> update kladr set parent_code = get_parent_code(hierarch,level) where level > 1;
UPDATE 189598
Время: 22990,729 мс
kladr=> select count(*) from kladr where parent_code is NULL;
count
-------
0
(1 строка)

Время: 322,651 мс

Таки пошустрее, однако. Проверочные выборки я приводить не буду, чтобы сократить количество буковок повествования.

[тело ф-ии get_parent_code(hrrh ltree, lvl int2, OUT pcode char(13))]

create or replace function get_parent_code(hrrh ltree, lvl int2, OUT pcode char(13)) as $$
declare phrrh ltree;
begin
case lvl
when 2 then
phrrh := subpath(hrrh,0,1);
when 3 then
if subpath(hrrh,1,1) = '000' then
phrrh := subpath(hrrh,0,1);
else
phrrh := subpath(hrrh,0,2);
end if;
when 4 then
if subpath(hrrh,1,2) = '000.000' then
phrrh := subpath(hrrh,0,1);
elsif subpath(hrrh,2,1) = '000' then
phrrh := subpath(hrrh,0,2);
else
phrrh := subpath(hrrh,0,3);
end if;
else
phrrh := ''::ltree;
end case;
if lvl = 1 then
pcode := '';
else
execute 'select code from kladr where hierarch = $1' into pcode using phrrh;
end if;
end;
$$ LANGUAGE plpgsql;

Вот как-то так. База в наличии имеется. Имеется некоторое количество совершенно детских тестов из этой базы. Я с превеликим удовольствием погоняю более другие запросы, нежели мои (будут в следующем выпуске).

PostgreSQL. ltree. Заливка базы.
PostgreSQL. ltree. Инициализация поля parent_code.
PostgreSQL. ltree. Выборки потомков.