MIME-Version: 1.0
References: <CAGD6t7+GykxBs=BVeg2un_rQOrjXz==pXt-UWFVTNCgeYCSB5A@mail.gmail.com>
In-Reply-To: <CAGD6t7+GykxBs=BVeg2un_rQOrjXz==pXt-UWFVTNCgeYCSB5A@mail.gmail.com>
From: Steve Baldwin <steve.baldwin@gmail.com>
Date: Thu, 25 Apr 2024 07:35:50 +1000
Message-ID: <CAKE1AibcG-cg54_UmCjc2=heFyks261v7m2F8R4mR3qwqVcRBw@mail.gmail.com>
Subject: Re: is there an immutable function to switch from date to character?
To: Celia McInnis <celia.mcinnis@gmail.com>
Cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000f931e90616de74b5"
Archived-At: <https://www.postgresql.org/message-id/CAKE1AibcG-cg54_UmCjc2%3DheFyks261v7m2F8R4mR3qwqVcRBw%40mail.gmail.com>
Precedence: bulk

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

On Thu, Apr 25, 2024 at 7:31=E2=80=AFAM Celia McInnis <celia.mcinnis@gmail.=
com>
wrote:

> 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
>
> Maybe this is a hack but..

b2bcreditonline=3D# create temporary table junk as select now()::date as
evtdate;
SELECT 1
b2bcreditonline=3D# alter table junk add column chardate text GENERATED
ALWAYS AS (to_char(evtdate,'YYYY-Mon-DD')) STORED;
ERROR:  generation expression is not immutable
b2bcreditonline=3D# create or replace function date_to_text(i_date in date)
returns text immutable language sql as $$ select to_char(i_date,
'YYYY-MM-DD') $$;
CREATE FUNCTION
b2bcreditonline=3D# alter table junk add column chardate text GENERATED
ALWAYS AS (date_to_text(evtdate)) STORED;
ALTER TABLE
b2bcreditonline=3D# select * from junk;
  evtdate   |  chardate
------------+------------
 2024-04-24 | 2024-04-24
(1 row)

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Thu, Apr 25, 2024 at 7:31=E2=80=AF=
AM Celia McInnis &lt;<a href=3D"mailto:celia.mcinnis@gmail.com">celia.mcinn=
is@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin=
g-left:1ex"><div dir=3D"ltr"><div>create temporary table junk as select now=
()::date as evtdate;</div><div><br></div><div>alter table junk add column c=
hardate text GENERATED ALWAYS AS (to_char(evtdate,&#39;YYYY-Mon-DD&#39;)) S=
TORED;</div><div><br></div>ERROR: =C2=A0generation expression is not immuta=
ble<br><br></div></blockquote><div>Maybe this is a hack but..<br><br>b2bcre=
ditonline=3D# create temporary table junk as select now()::date as evtdate;=
<br>SELECT 1<br>b2bcreditonline=3D# alter table junk add column chardate te=
xt GENERATED ALWAYS AS (to_char(evtdate,&#39;YYYY-Mon-DD&#39;)) STORED;<br>=
ERROR: =C2=A0generation expression is not immutable<br>b2bcreditonline=3D# =
create or replace function date_to_text(i_date in date) returns text immuta=
ble language sql as $$ select to_char(i_date, &#39;YYYY-MM-DD&#39;) $$;<br>=
CREATE FUNCTION<br>b2bcreditonline=3D# alter table junk add column chardate=
 text GENERATED ALWAYS AS (date_to_text(evtdate)) STORED;<br>ALTER TABLE<br=
>b2bcreditonline=3D# select * from junk;<br>=C2=A0 evtdate =C2=A0 | =C2=A0c=
hardate<br>------------+------------<br>=C2=A02024-04-24 | 2024-04-24<br>(1=
 row)</div><div>=C2=A0</div></div></div>

--000000000000f931e90616de74b5--