postgres initializing a variable in a do block

Question

On a very basic level in a do block in Postgres I want to call a function and get the value and do necessary updates - but it looks like the first time i call the function from the block it gets initialized and does not update subsequently.

I have tried breaking up the block and have sub blocks - no luck. Below is a very simple example :

do $$
<<outer_block>>
declare
    _counter int := 0 ;
begin
    while _counter < 5
        loop
            declare _now timestamp := getTime(); --now();
            begin
                raise notice 'COUNTER : % TIME STAMP : %' , _counter, _now;
            end;
            _counter := _counter + 1;
            perform pg_sleep(5);
        end loop;
end outer_block $$;

and getTime is ...

create or replace function getTime() returns timestamp   --setof record 
language plpgsql
as 
$$
declare
    _val timestamp := now();
begin
--  _val := now();
    return _val;
end $$;

The output of the main block is the same time stamp.

Is it because Postgres instead of being sequential sends the entire block as an object and therefore the time stamp is the same?

Could I add an insert in the function to a temp table and have a trigger update another table to get the time stamp (or new initialized value ?)

Output of the main block is:

NOTICE:  COUNTER : 0 TIME STAMP : 2021-03-15 19:03:07.73432
NOTICE:  2021-03-15 19:03:07.73432-04
NOTICE:  COUNTER : 1 TIME STAMP : 2021-03-15 19:03:07.73432
NOTICE:  2021-03-15 19:03:07.73432-04
NOTICE:  COUNTER : 2 TIME STAMP : 2021-03-15 19:03:07.73432
NOTICE:  2021-03-15 19:03:07.73432-04
NOTICE:  COUNTER : 3 TIME STAMP : 2021-03-15 19:03:07.73432
NOTICE:  2021-03-15 19:03:07.73432-04
NOTICE:  COUNTER : 4 TIME STAMP : 2021-03-15 19:03:07.73432
NOTICE:  2021-03-15 19:03:07.73432-04
DO

score 0 · Answer 1 · answered Mar 15 '21 at 23:45

Based on Postgres now() timestamp doesn't change, when script works, you can try timeofday() instead of now();

edb=# create or replace function getTime() returns timestamp   --setof record 
language plpgsql
as 
$$                     
declare
    _val timestamp := timeofday();
begin
--  _val := timeofday();
    return _val;
end $$ volatile;
CREATE FUNCTION
edb=# do $$                                                                   
<<outer_block>>
declare
    _counter int := 0 ;
begin
    while _counter < 5
        loop
            declare _now timestamp := getTime(); --now();
            begin
                raise notice 'COUNTER : % TIME STAMP : %' , _counter, _now;
            end;
            _counter := _counter + 1;
            perform pg_sleep(5);
        end loop;
end outer_block $$;
NOTICE:  COUNTER : 0 TIME STAMP : 15-MAR-21 23:44:17.870405
NOTICE:  COUNTER : 1 TIME STAMP : 15-MAR-21 23:44:22.875366
NOTICE:  COUNTER : 2 TIME STAMP : 15-MAR-21 23:44:27.880828
NOTICE:  COUNTER : 3 TIME STAMP : 15-MAR-21 23:44:32.886212
NOTICE:  COUNTER : 4 TIME STAMP : 15-MAR-21 23:44:37.891187
DO

I would recommend `clock_timestamp()` rather than converting the timestamp to `text` and back. — Laurenz Albe, Mar 16 '21 at 00:20
Thank you so much ! I really appreciate the response - i was stuck on trying to do this in a loop and tried now() - but thank you. — Neale Chaudhury, Mar 16 '21 at 00:59

postgres initializing a variable in a do block

1 Answers1