Re: is there an immutable function to switch from date to character?

2024-04-24 Thread Celia McInnis 
Sorry I pasted in the wrong code, I had wanted a column with the character
version of the date (ie., -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, '-Mon-DD') $$;

Thanks!

On Wed, Apr 24, 2024 at 5:54 PM Tom Lane  wrote:

> Celia McInnis  writes:
> > create temporary table junk as select now()::date as evtdate;
> > alter table junk add column chardate text GENERATED ALWAYS AS
> > (to_char(evtdate,'-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, '-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
>

Re: is there an immutable function to switch from date to character?

2024-04-24 Thread Tom Lane 
Celia McInnis  writes:
> create temporary table junk as select now()::date as evtdate;
> alter table junk add column chardate text GENERATED ALWAYS AS
> (to_char(evtdate,'-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, '-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

Re: is there an immutable function to switch from date to character?

2024-04-24 Thread Steve Baldwin 
On Thu, Apr 25, 2024 at 7:31 AM Celia McInnis 
wrote:

> create temporary table junk as select now()::date as evtdate;
>
> alter table junk add column chardate text GENERATED ALWAYS AS
> (to_char(evtdate,'-Mon-DD')) STORED;
>
> ERROR:  generation expression is not immutable
>
> Maybe this is a hack but..

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

is there an immutable function to switch from date to character?

2024-04-24 Thread Celia McInnis 
create temporary table junk as select now()::date as evtdate;

alter table junk add column chardate text GENERATED ALWAYS AS
(to_char(evtdate,'-Mon-DD')) STORED;

ERROR:  generation expression is not immutable