时间戳

首页 > 解决方案 > Postgresql 选择变量

sql - Postgresql 选择变量

问题描述

我有选择：

do $$
    declare uid text := '0e58776c-d992-4615-ab73-2408c1a915fe';
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
                         FROM public.tmask_mda02 
                         WHERE uuid = uid                                   
                         AND params = 'sha256 out'; 
end $$;

输出：

错误：查询没有结果数据的目的地 提示：如果要丢弃 SELECT 的结果，请改用 PERFORM。上下文：PL/pgSQL 函数 inline_code_block 第 5 行 SQL 语句 SQL 状态：42601

如果我使用：

SELECT sha256, (
        SELECT timestamp 
            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';

输出正常：

“46a9fe69c1f5b10f2a40ddef1a4ba9f7ed0bc024c6b9e9f858cc2d2dcd4bcd42”“-00:00:01.136”

标签： sqlpostgresql

解决方案

正如错误所说，您将需要在 PLPGSQL 块内的此查询的目标，例如：

do $$
    declare 
    uid text := '0e58776c-d992-4615-ab73-2408c1a915fe';
    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 'my output: %',my_output;
end $$;

推荐阅读