Обсуждение: is there an immutable function to switch from date to character?
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;
On Thu, Apr 25, 2024 at 7:31 AM 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=# 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,'YYYY-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, 'YYYY-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)
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,'YYYY-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, 'YYYY-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)
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
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)
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') $$;
_char(i_date, 'YYYY-Mon-DD') $$;
Thanks!
On Wed, Apr 24, 2024 at 5:54 PM 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:
=# 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