Блог

У меня есть два варианта

 do $
    declare 
    uid text := 'b5974eec-7078-4ae7-923c-36af96905423';
    my_output record;
     
Begin
-- Czas konwersji jednego ekstratku
SELECT sha256, (
        SELECT timestamp 
            FROM public.tmask_mda02 
            WHERE uuid = uid
            AND import = 'COMPLETED' 
            AND params = 'sha256 in')::timestamp - (
                SELECT timestamp 
                        FROM public.tmask_mda02 
                         WHERE uuid = uid
                         AND export = 'COMPLETED' 
                         AND params = 'sha256 out')::timestamp AS Interval
                         INTO my_output 
                         FROM public.tmask_mda02 
                         WHERE uuid = uid                                   
                         AND params = 'sha256 out'; 
RAISE NOTICE 'Dlugosc_przetwarzania: %',my_output;
end $;
 

Выход есть:

Dlugosc_przetwarzania: (89b253f28435aea23fc68c042066e988d236f2f312583255c0352ea512701daf,-00:00:01.145)

 SELECT DISTINCT uuid FROM public.tmask_mda02 WHERE export = 'COMPLETED' AND timestamp::text LIKE '2021-09-22%';
 

Выход:

«02de41c7-488f-4dc9-bb33-b76b05426cde»
«0b82e409-e37f-4b34-a90a-c5b57d63d759»
«19d38112-efb0-4f67-97aa-493d0cf9a1a4»
«1e8e5745-6885-4e18-89a1-3c841efd48ac»
«223cfb85-b8eb-4ea1-8f14-fbd565f68344»
«27fcaadc-f487-4bfe-bee8-00b98375844b»
«311e0cc8-f398-4134-855f-b64631254af5»
«31f856fb-c814-4214-9612-1703f247de2b»

Мне нужно использовать select в переменной uid:

 do $
    declare 
 

         uid text := (SELECT DISTINCT uuid FROM public.tmask_mda02 WHERE export = 'COMPLETED' AND timestamp::text LIKE '2021-09-22%');
 

     my_output record;
     
Begin
-- Czas konwersji jednego ekstratku
SELECT sha256, (
        SELECT timestamp 
            FROM public.tmask_mda02 
            WHERE uuid = uid
            AND import = 'COMPLETED' 
            AND params = 'sha256 in')::timestamp - (
                SELECT timestamp 
                        FROM public.tmask_mda02 
                         WHERE uuid = uid
                         AND export = 'COMPLETED' 
                         AND params = 'sha256 out')::timestamp AS Interval
                         INTO my_output 
                         FROM public.tmask_mda02 
                         WHERE uuid = uid                                   
                         AND params = 'sha256 out'; 
RAISE NOTICE 'Dlugosc_przetwarzania: %',my_output;
end $;