Рубрика: Linux

Задача: вызываемая функция должна вернуть таблицу.

Решение:

DROP FUNCTION get_count_np_by_ls(date,bigint,bigint,boolean); 

CREATE OR REPLACE FUNCTION public.get_count_np_by_ls(pr date, porog1 bigint, porog2 bigint, epd boolean)
 RETURNS TABLE(name character varying, cnt numeric)
 LANGUAGE plpgsql
AS $function$
	BEGIN	
       if epd=true then
		return QUERY
					select
						arname as brname,
						sum(acnt) as gcnt 
					 from (
					select * from ( 
					SELECT 
						areas.name as arname,
						count(*) as acnt
					FROM public.posting_addresses
					inner join ls_addresses on ls_addresses.ls=posting_addresses.ls and ls_addresses.address_type=1 and ls_addresses.area between 1 and 26
					inner join areas on areas.id=ls_addresses.area
					inner join ls on ls.id=posting_addresses.ls
					where 
						posting_addresses.period=pr and 
						ls.dt_cancel_epd_bill<=pr  
						group by areas.name,ls_addresses.city,ls_addresses.settler) as zx 
					where 
						zx.acnt between porog1 and porog2
					) as res
					group by brname;
		else
		return QUERY
					select
						arname as brname,
						sum(acnt) as gcnt 
					 from (
					select * from ( 
					SELECT 
						areas.name as arname,
						count(*) as acnt
					FROM public.posting_addresses
					inner join ls_addresses on ls_addresses.ls=posting_addresses.ls and ls_addresses.address_type=1 and ls_addresses.area between 1 and 26
					inner join areas on areas.id=ls_addresses.area
					inner join ls on ls.id=posting_addresses.ls
					where 
						posting_addresses.period=pr 
						group by areas.name,ls_addresses.city,ls_addresses.settler) as zx 
					where 
						zx.acnt between porog1 and porog2
					) as res
					group by brname;
		end if;
	END;	
$function$
;

Вызывается так:

SELECT * from get_count_np_by_ls('2026-05-01',1,1,true)

Внимание! Частой ошибкой является вызов select get_count_np_by_ls(‘2026-05-01’,1,1,true). В этом случае столбцы «схлопываются» в один