Рубрика: postgressql

В этом разделе отображаются статьи, которые связаны с тегом postgreesql. Решения проблем, методы исправления ошибок, построение запросов.

Postgresql: перебор всех записей таблицы в функции

postgressql

Задача: обновить все записи одной таблицы, на основании данных другой таблицы. Причем условие сложное, которое просто в запрос update from select не впехнешь

Решение: сделаем функцию, которая собственно будет последовательно перебирать таблицу, и обновлять данные 

CREATE OR REPLACE FUNCTION public.update_epd_cancel()
	RETURNS int4
	LANGUAGE plpgsql
AS $function$
DECLARE
    crsr CURSOR FOR SELECT * FROM refusals;  -- курсор для запроса
	emp RECORD;
	BEGIN
		 OPEN crsr;  -- открываем курсор
		LOOP
        FETCH crsr INTO emp;  -- извлекаем следующую строку в переменную
        EXIT WHEN NOT FOUND;  -- если строк больше нет — выходим
	    update ls set dt_cancel_epd_bill=emp.dt where ls.id=emp.ls; -- обработка строки
        update ls set dt_cancel_epd_bill=emp.dt where ls.id=emp.ls_epd; -- обработка строки
    END LOOP;
    CLOSE crsr;  -- закрываем курсор
    return 0;
	END;
$function$
;
Оставить комментарий , , ,

PostgreSQL: объединение результатов двух запросов по ключевым полям

postgressql

Задача: есть две таблицы, из которых получаются данные сгруппированные по городу и населенному пункту. Необходимо их объединить в один результирующий запрос

Решение: оказывается вполне возможно объединить две выборки посредством LEFT/INNER JOIN. Если схематично, то примерно так:

select
    s1.cnt_all,s2.cnt_no,s1.city,s1.settler
from (select * from aaa) as s1
inner join ((select * from bbb) as s2) on (s1.city=s2.city and s1.settler=s2.settler)

Ну или мой результирующий:

select 
	s1.cnt_all,
	case when s2.cnt_pilot is null then 0 else s2.cnt_pilot end as cnt_pilot, 
	s1.city,
	s1.settler 
from (
	SELECT 
		count(posting_addresses.id) as cnt_all,
		ls_addresses.city,
		ls_addresses.settler 
	FROM posting_addresses
		inner join ls_addresses on ls_addresses.ls=posting_addresses.ls and ls_addresses.address_type=1
		inner join divisions on divisions.area=ls_addresses.area and divisions.id=12	
	where posting_addresses.period='2026-01-01'  
	group by
		ls_addresses.city,
		ls_addresses.settler
	order by cnt_all desc
) as s1
left join (
SELECT 
	count(refusals.id) as cnt_pilot,
	ls_addresses.city,
	ls_addresses.settler 
FROM refusals 
inner join ls_addresses on ls_addresses.ls=refusals.ls and ls_addresses.address_type=1
inner join divisions on divisions.area=ls_addresses.area and divisions.id=12 
where refusals.dt ='2026-01-01'  
group by
	ls_addresses.city,
	ls_addresses.settler
order by cnt_pilot desc
) as s2 on (s1.city=s2.city and s1.settler=s2.settler)
Оставить комментарий ,

PostgreSQL и выборка из полей содержащих JSON

postgressql

Postgre позволяет хранить данные в JSON с колонках с типами json и jsonb, которые по сути отличаются только способом хранения. json — в виде текста (хорошо видно визуально, можно обрабатывать запросы как с «текстом») и jsonb — в виде сжатых бинарных данных (занимает меньше места, можно индексировать. Причем если данные хранятся в виде jsonb, то порядок сохранения ключей в JSON не гарантируется. В принципе вполне удобно хранить так данные, если не возможно заранее определить точно структуру таблицы.

Ну и ниже пример работы с такими данными.

Вставка:

insert into epd_expansion (period,ls,orgid,parties) values ('2025-10-01',1,2,'[4,5,6]')

Выборка:

SELECT * FROM public.epd_expansion where parties is not null;

А вот так например, как можно например сделать выборку всех строк, имеющих в массиве partise число 3:

SELECT * FROM public.epd_expansion where parties@>'[3]'::jsonb;

Оставить комментарий , ,

PostgreSQL: имя таблицы как параметр функции

postgressql

Штатно в PostgreSQL я не нашел способа передавать имя таблицы как переменная у функции. Однако есть возможность это обойти. Например так:

CREATE OR REPLACE FUNCTION public.SetReceived(busname varchar ,idr integer)
	RETURNS boolean
	LANGUAGE plpgsql
AS $function$
	BEGIN
		EXECUTE format(concat('update ',busname,' set received=true where id=',idr));		
		return true;
	END;
$function$
Оставить комментарий , ,

PostgreeSQL: нечеткое сравнение в триггере

postgressql

Ну собственно синтаксис такой-же как и в запросах: оператор LIKE. Соответственно триггер с не чётким сравнением может выглядеть например так:

CREATE OR REPLACE FUNCTION public.on_insert_ls()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$BEGIN
 NEW.id=nextval('ls_id_seq'::regclass);
  if new.ls like '22%' then 
	new.is_epd=true;
   else new.is_epd=false;
  end if;
 RETURN NEW;
END;$function$

Оставить комментарий , ,
1 2