Include multiple conditionals from nested select query

Question

I have a function which returns min_time and max_time which I would like to use in a SELECT query as constraints for a timestamp column. Below is how I can think to do it but I would prefer to not have to run the query twice.

SELECT timestamp, col FROM data
    WHERE timestamp BETWEEN 
        (SELECT min_time FROM get_time_limits()) 
        AND 
        (SELECT max_time FROM get_time_limits());

Is there a way to use multiple columns from a single nested SELECT query as constraints in another query?

Sample table:

CREATE TABLE test(timestamp TIMESTAMPTZ, col INTEGER);

INSERT INTO test (
    SELECT generate_series(
        NOW() - INTERVAL '10 days', 
        NOW(), 
        INTERVAL '1 day'), 
        generate_series(1, 11)
    );

Example min/max time:

'2023-03-23 00:00:00+00'::TIMESTAMPTZ
'2023-03-29 00:00:00+00'::TIMESTAMPTZ

Answer 1

Make your function return a tstzrange then you can use something like this:

SELECT timestamp, col 
FROM data
WHERE timestamp <@ get_time_limits()

I haven't tried, but the following should work using the current function:

SELECT timestamp, col 
FROM data
WHERE timestamp <@ (select tstzrange(min_time, max_time, '[]') from get_time_limits()) 

Answer 2

Just do a join, using the function as a virtual table:

SELECT timestamp, col FROM data, get_time_limits()
    WHERE timestamp BETWEEN min_time AND max_time;