Functions for delaying execution

The three functions pg_sleep(double precision), pg_sleep_for(interval), and pg_sleep_until(timestamp_tz) are understood simply. The argument of pg_sleep() is interpreted as the number of seconds for which to sleep. Because each has a formal parameter with a different data type, they could all have been implemented as overloads of a single function. Presumably, somebody chose to use different names somewhat whimsically. The fact that they are functions allows them to be invoked within the same SQL statement, just as the demonstration of the semantic difference between statement_timestamp() and clock_timestamp() does above. It's probably more common to use them within PL/pgSQL code. A procedure would have been more natural here than a function. But this has no practical consequence because the invocation perform pg_sleep(n) comes to the rescue.

Notice that you cannot declare a PL/pgSQL variable with the data type void. (The attempt causes the 0A000 error. This is mapped to the feature_not_supported exception.) If you need to include a pg_sleep() invocation in a select into SQL statement in PL/pgSQL code, then you should typecast its return value to text. (This typecast produces the empty string.)

function pg_sleep() returns void

Here is a simple example:

select (pg_sleep(1.0::double precision)::text = '')::text;

The result is true.

function pg_sleep_for() returns void

Here is a simple example:

select (pg_sleep_for(make_interval(secs=>1))::text = '')::text;

The result is true.

function pg_sleep_until() returns void

Here is a simple example:

select (pg_sleep_until( (clock_timestamp() + make_interval(secs=>1))::timestamptz )::text = '')::text;

The result is true. Notice that pg_sleep_until() function has no overloads other than the one with the single timestamptz parameter. This is yet another reason always to use timestamptz for moment values and only to convert to other data type values on the fly if the need arises.