Postgresql select with variable-CodePudding

I have select:

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 $$;

Output:

ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function inline_code_block line 5 at SQL statement SQL state: 42601

If I use:

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';

Output is OK:

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

CodePudding user response：

As the error said, you will need a destination for this query inside the PLPGSQL block, for example:

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 $$;

CodePudding user response：

You have to return in your function.

You can write

variable_name:= (SELECT....)

And then

RETURN variable_name;

RETURN QUERY 
SELECT...

This link writes the details:

How to return result of a SELECT inside a function in PostgreSQL?

CodePudding user response：

You can't return a value from an anonymous code block do $$ ... $$.

You have to use a proper function for that. You can use a temporary schema if you don't want to modify your permanent schema.

create or replace function pg_temp.extract_conversion_time(
    uid text,
    OUT sha256 text,
    OUT "interval" interval
)
language sql stable strict
as $$
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'
$$;

select * from pg_temp.extract_conversion_time('0e58776c-d992-4615-ab73-2408c1a915fe');

But I would not recommend using timestamp (short for timestamp without time zone) for anything. Unless all your timestamps are in UTC, you will have ugly bugs aroud DST change times.

=> 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

But the real difference is:

=> show timezone;
Europe/Warsaw
=> select '2021-10-31 04:00'::timestamptz-'2021-10-31 01:00'::timestamptz;
04:00:00

Always use timestamptz (short for timestamp with time zone).