Re: Passing a dynamic interval to generate_series()

Hi Igal:

On Mon, 1 Jul 2024 at 01:17, Igal Sapir <igal@lucee.org> wrote:

> I actually did test the expression that I posted, but it might be casting it twice.  While your examples that you
wroteshow 1 month correctly:
 
> SELECT (interval '1 ' || 'month');
> ?column?     |
> -------------+
> 00:00:01month|

No, it does not, try it like this:
s=> with a(x) as ( SELECT (interval '1 ' || 'month')) select x,
pg_typeof(x) from a;
       x       | pg_typeof
---------------+-----------
 00:00:01month | text
(1 row)

And you'll understand what is happening. Cast to interval has higher
priority then concatenation, so you are selecting a 1 second interval,
casting it to text, '00:00:01', adding 'month' at end.

This can also be noticed because month output would not use ':' and have spaces:
s=> with a(x) as ( SELECT '001.00MONTHS'::interval) select x,
pg_typeof(x) from a;
   x   | pg_typeof
-------+-----------
 1 mon | interval
(1 row)

( I used fractions, uppercase and no spaces on input to show how
interval output normalizes ).

Francisco Olarte.