How to pass daterange to Postgres functions?-CodePudding

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?

CodePudding user response：

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', '[]');

CodePudding user response：

a_horse already provided proper syntax for the function call.

Alternatively, pass literal constants. That should work with every client:

SELECT extract_range('[2018-01-01,2019-01-01)'
                   , '[2018-03-01,2019-03-31)');

If you are unsure about the syntax, just ask Postgres (example with psql):

test=> SELECT daterange('2018-01-01', '2018-12-31', '[]');
        daterange        
-------------------------
 [2018-01-01,2019-01-01)
(1 row)

To be absolutely clear, you can add explicit type casts:

SELECT extract_range('[2018-01-01,2019-01-01)'::daterange
                   , '[2018-03-01,2019-04-01)'::daterange);

But that's optional while function type resolution does not run into ambiguities with overloaded functions.

Note that a default daterange value includes the lower ([) and excludes the upper bound ()).

And yes, it's typically simpler to just pass basic types to the Postgres function and construct ranges inside.

Either way, be clear about which bounds to include.