MIME-Version: 1.0
References: <CAGD6t7+GykxBs=BVeg2un_rQOrjXz==pXt-UWFVTNCgeYCSB5A@mail.gmail.com>
 <3768124.1713995696@sss.pgh.pa.us>
In-Reply-To: <3768124.1713995696@sss.pgh.pa.us>
From: Celia McInnis <celia.mcinnis@gmail.com>
Date: Wed, 24 Apr 2024 18:05:12 -0400
Message-ID: <CAGD6t7LsWi4pYPye-eY9V-=WG8FxB+AzJ-ziwNaDK9A27a_WVg@mail.gmail.com>
Subject: Re: is there an immutable function to switch from date to character?
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000021d5b0616dede1d"
Archived-At: <https://www.postgresql.org/message-id/CAGD6t7LsWi4pYPye-eY9V-%3DWG8FxB%2BAzJ-ziwNaDK9A27a_WVg%40mail.gmail.com>
Precedence: bulk

--000000000000021d5b0616dede1d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Sorry I pasted in the wrong code, I had wanted a column with the character
version of the date (ie., YYYY-Mon-DD). Steve Baldwin's hack pointed me in
the right direction. Here is the example:

create temporary table junk as select now()::date as evtdate;
SELECT 1

alter table junk add column chardate text GENERATED ALWAYS AS
(cmm_date_to_char(evtdate)) STORED;

select * from junk;
  evtdate   |  chardate
------------+-------------
 2024-04-24 | 2024-Apr-24
(1 row)

where cmm_date_to_char is defined as:

create or replace function cmm_date_to_char(i_date in date) returns text
immutable language sql as $$ select to
_char(i_date, 'YYYY-Mon-DD') $$;

Thanks!

On Wed, Apr 24, 2024 at 5:54=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Celia McInnis <celia.mcinnis@gmail.com> writes:
> > create temporary table junk as select now()::date as evtdate;
> > alter table junk add column chardate text GENERATED ALWAYS AS
> > (to_char(evtdate,'YYYY-Mon-DD')) STORED;
>
> > ERROR:  generation expression is not immutable
>
> Probably not; I think all the available conversion functions
> respond to some combination of datestyle, lc_time, and timezone
> settings.  (Type date doesn't depend on timezone, but that keeps you
> from using anything that shares functionality with timestamptz ...
> and your to_char call promotes the date to timestamptz.)
>
> I find your example not terribly compelling.  Why expend storage
> space on such a column?
>
> If you're bound and determined to do it, writing a wrapper
> function that's labeled immutable should work:
>
> =3D# create function mytochar(date) returns text
> strict immutable parallel safe
> as $$ begin return to_char($1::timestamp, 'YYYY-Mon-DD'); end $$
> language plpgsql;
> CREATE FUNCTION
> =3D# alter table junk add column chardate text GENERATED ALWAYS AS
> (mytochar(evtdate)) STORED;
> ALTER TABLE
>
> It's on you to be sure that the function actually is immutable,
> or at least immutable enough for your use-case.  I believe my
> example is pretty safe: neither datestyle nor timezone should
> affect the timestamp-without-timezone variant of to_char(),
> and this particular format string doesn't depend on lc_time.
>
>                         regards, tom lane
>

--000000000000021d5b0616dede1d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div>Sorry I pasted in the wrong code, I =
had wanted a column with the character version of the date (ie., YYYY-Mon-D=
D). Steve Baldwin&#39;s hack pointed me in the right direction. Here is the=
 example:</div><div><br></div><div>create temporary table junk as select no=
w()::date as evtdate;<br>SELECT 1<br><br>alter table junk add column charda=
te text GENERATED ALWAYS AS (cmm_date_to_char(evtdate)) STORED;<br><br>sele=
ct * from junk;<br>=C2=A0 evtdate =C2=A0 | =C2=A0chardate =C2=A0 <br>------=
------+-------------<br>=C2=A02024-04-24 | 2024-Apr-24<br>(1 row)</div><div=
><br></div><div>where cmm_date_to_char is defined as:</div><div><br></div><=
div>create or replace function cmm_date_to_char(i_date in date) returns tex=
t immutable language sql as $$ select to<br>_char(i_date, &#39;YYYY-Mon-DD&=
#39;) $$;</div><div><br></div><div>Thanks!<br></div></div><br><div class=3D=
"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Wed, Apr 24, 2024 at=
 5:54=E2=80=AFPM Tom Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us" target=
=3D"_blank">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></div><blockquote class=3D"=
gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(20=
4,204,204);padding-left:1ex">Celia McInnis &lt;<a href=3D"mailto:celia.mcin=
nis@gmail.com" target=3D"_blank">celia.mcinnis@gmail.com</a>&gt; writes:<br=
>
&gt; create temporary table junk as select now()::date as evtdate;<br>
&gt; alter table junk add column chardate text GENERATED ALWAYS AS<br>
&gt; (to_char(evtdate,&#39;YYYY-Mon-DD&#39;)) STORED;<br>
<br>
&gt; ERROR:=C2=A0 generation expression is not immutable<br>
<br>
Probably not; I think all the available conversion functions<br>
respond to some combination of datestyle, lc_time, and timezone<br>
settings.=C2=A0 (Type date doesn&#39;t depend on timezone, but that keeps y=
ou<br>
from using anything that shares functionality with timestamptz ...<br>
and your to_char call promotes the date to timestamptz.)<br>
<br>
I find your example not terribly compelling.=C2=A0 Why expend storage<br>
space on such a column?<br>
<br>
If you&#39;re bound and determined to do it, writing a wrapper<br>
function that&#39;s labeled immutable should work:<br>
<br>
=3D# create function mytochar(date) returns text<br>
strict immutable parallel safe<br>
as $$ begin return to_char($1::timestamp, &#39;YYYY-Mon-DD&#39;); end $$<br=
>
language plpgsql;<br>
CREATE FUNCTION<br>
=3D# alter table junk add column chardate text GENERATED ALWAYS AS<br>
(mytochar(evtdate)) STORED;<br>
ALTER TABLE<br>
<br>
It&#39;s on you to be sure that the function actually is immutable,<br>
or at least immutable enough for your use-case.=C2=A0 I believe my<br>
example is pretty safe: neither datestyle nor timezone should<br>
affect the timestamp-without-timezone variant of to_char(),<br>
and this particular format string doesn&#39;t depend on lc_time.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div>
</div>

--000000000000021d5b0616dede1d--