How to pass daterange to Postgres functions?

Issue

Postgres documentation says there’s a built-in daterange type but it doesn’t seem to work when I write a function in DBeaver. It’s not highlighted or recognized as such.

I need to pass 2 dateranges in the format of [2018-01-01, 2018-2-28] to a Postgres function for range extraction, etc.

Declaration:

create or replace function extract_range(outer_range daterange, inner_range daterange);

Call:

select extract_range(['2018-01-01', '2018-12-31'], ['2018-03-01', '2018-3-31']);

The function compiles but throws an error at the call:

syntax error at or near "["

What’s the correct call?

Or should I just pass them as a string, and then parse/convert/cast inside the function?

Solution

As documented in the manual the syntax to create a range is to use the range type name as a "creator" function, e.g. to create a daterange you have to use:

daterange('2018-01-01', '2018-12-31', '[]') 

The third parameter defines if the edges are inclusive or exclusive. You didn’t specify what you want there.

So you need:

select extract_range(daterange('2018-01-01', '2018-12-31', '[]'),
                     daterange('2018-03-01', '2018-03-31', '[]');

Answered By – a_horse_with_no_name

Answer Checked By – Clifford M. (AngularFixing Volunteer)