From: Tom Lane <tgl@sss.pgh.pa.us>
To: Celia McInnis <celia.mcinnis@gmail.com>
cc: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Subject: Re: is there an immutable function to switch from date to character?
In-reply-to: <CAGD6t7+GykxBs=BVeg2un_rQOrjXz==pXt-UWFVTNCgeYCSB5A@mail.gmail.com>
References: <CAGD6t7+GykxBs=BVeg2un_rQOrjXz==pXt-UWFVTNCgeYCSB5A@mail.gmail.com>
Comments: In-reply-to Celia McInnis <celia.mcinnis@gmail.com>
	message dated "Wed, 24 Apr 2024 17:31:00 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3768123.1713995696.1@sss.pgh.pa.us>
Date: Wed, 24 Apr 2024 17:54:56 -0400
Message-ID: <3768124.1713995696@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/3768124.1713995696%40sss.pgh.pa.us>
Precedence: bulk

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:

=# 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
=# 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