Using select results as variable in Postgresql-CodePudding

Let's say that I want assign few select statement results in variable, like I would do that in Python. How that query should look like? I tried WITH alias AS () but without success. What I want to archive is to calculate delta between two specific messages.

I looking for something like this:

time1 = ()
time2 = ()

result = time2-time1

CodePudding user response：

You can certainly do this with an anonymous code block. But it would probably be far better to write this as a function.

Something like this might be what you're looking for:

DO $$
DECLARE time1 TIMESTAMP;
DECLARE time2 TIMESTAMP;
BEGIN
    SELECT '2022-02-15 14:52:51.252859 01'::timestamp INTO time1;
    SELECT '2022-02-15 19:52:51.252859 01'::timestamp INTO time2;
    RAISE NOTICE 'result = %', time2 - time1;
END
$$;