Postgresql帶變數的選擇

我有選擇：

do $$
    declare uid text := '0e58776c-d992-4615-ab73-2408c1a915fe'/span>;
Begin
-- Czas konwersji jednego ekstratku
SELECT sha256, (
        SELECTtimestamp 
            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
                         FROM public.tmask_mda02 
                         WHERE uuid = uid                                   
                         AND params = 'sha256 out'。
end $$。

輸出：

ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT. 丟棄SELECT的結果，請使用PERFORM代替。背景。 PL/pgSQL函式inline_code_block第5行在SQL陳述句中SQL狀態。 42601

如果我使用:

SELECT sha256, (
        SELECTtimestamp 
            FROM public.tmask_mda02 
            WHERE uuid = '0e58776c-d992-4615-ab73-2408c1a915fe' 
            and import = 'COMPLETED' 
            and params = 'sha256 in') ::timestamp - (
                SELECT timestamp 
                        FROM public.tmask_mda02 
                         WHERE uuid = '0e58776c-d992-4615-ab73-2408c1a915fe' 
                         AND export = 'COMPLETED' 
                         and params = 'sha256 out'）。) :timestamp AS Interval
                         FROM public.tmask_mda02 
                         WHERE uuid = '0e58776c-d992-4615-ab73-2408c1a915fe'                                     
                         and params = 'sha256 out'。

輸出是OK的：

"46a9fe69c1f5b10f2a40ddef1a4ba9f7ed0bc024c6b9e9f858cc2d2dcd4bcd42" "-00:00:01.136"

uj5u.com熱心網友回復：

正如錯誤所說，你需要在PLPGSQL塊內為這個查詢設定一個目的地，例如：

do $$
    declare 
    uid text := '0e58776c-d992-4615-ab73-2408c1a915fe'/span>;
    my_output記錄。
     
Begin
-- Czas konwersji jednego ekstratku
SELECT sha256, (
        SELECTtimestamp 
            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'/span>; 
RAISE NOTICE 'my output: %',my_output;
end $$。

uj5u.com熱心網友回復：

你必須在你的函式中回傳

你可以寫

variable_name:= (SELECT....)

然后

RETURN variable_name。

OR

RETURN QUERY 
選擇...

這個鏈接寫出了細節：

如何在PostgreSQL中的函式中回傳SELECT的結果？

CodePudding

你不能從匿名代碼塊中回傳一個值do $$ ... $$。

你必須使用一個適當的函式來實作。如果你不想修改你的永久模式，你可以使用一個臨時模式。

create or replacefunction pg_temp.extract_conversion_time(
    uid文本。
    OUT sha256 text,
    OUT"interval" interval
)
language sql stable strict
as $$
SELECT>
   sha256,
   (
        SELECTtimestamp 
        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
FROM public.tmask_mda02
WHERE uuid = uid
and params = 'sha256 out'/span>
$$;

select * from pg_temp.extract_conversion_time('0e58776c-d992-4615-ab73-2408c1a915fe'/span>) 。

但是我不建議使用timestamp（簡稱timestamp，不含時區）來做任何事情。除非你的所有時間戳都是UTC的，否則你會有關于DST變化時間的丑陋的錯誤。

=>  create table tmask_mda02 (timestamp timestamp, uuid text, sha256 text, import text, export text, params text) 。
=>  insert into tmask_mda02 (timestamp, uuid, sha256, import, export, params) values
('2021-10-31 01: 00', 'testuid', 'testhash', 'COMPLETED', 'COMPLETED', 'sha256 out'）。)
('2021-10-31 04: 00', 'testuid', 'testhash', 'COMPLETED', 'COMPLETED', 'sha256 in'）。)
=> select * from pg_temp。 extract_conversion_time('testuid') 。
testhash|03：00：00。

但真正的區別是：

=> 顯示時區。
歐洲/華沙
=> select '2021-10-31 04。 00'::timestamptz-'2021-10-31 01:00'::timestamptz;
04:00:00?

總是使用timestamptz（帶時區的時間戳的簡稱）。

標籤：

上一篇：如何從一個SQL表中獲取記錄，而忽略那些在某個特定列中有重復值的記錄

下一篇：在Oracle的SQL中通過不同的值進行分組和排序