MIME-Version: 1.0
References: <CA+zig0-suhFUeS7xcJyZ=4HDFXJ3Kn2adXo3B4zHUzDFgMscCw@mail.gmail.com>
 <2537942.1719787874@sss.pgh.pa.us> <CA+zig08QbXa9zkLJuK_QDiBon1Wq6T1gqwyr_4xCcY-i2=ePhA@mail.gmail.com>
In-Reply-To: <CA+zig08QbXa9zkLJuK_QDiBon1Wq6T1gqwyr_4xCcY-i2=ePhA@mail.gmail.com>
From: Francisco Olarte <folarte@peoplecall.com>
Date: Mon, 1 Jul 2024 09:20:11 +0200
Message-ID: <CA+bJJbw=5g2iGHGKUhY+a45fghT=3eCyn2c3CtTNj-tzj+YGcA@mail.gmail.com>
Subject: Re: Passing a dynamic interval to generate_series()
To: Igal Sapir <igal@lucee.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CA%2BbJJbw%3D5g2iGHGKUhY%2Ba45fghT%3D3eCyn2c3CtTNj-tzj%2BYGcA%40mail.gmail.com>
Precedence: bulk

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 wrote show 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.