From: | Celia McInnis <celia(dot)mcinnis(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: is there an immutable function to switch from date to character? |
Date: | 2024-04-24 22:05:12 |
Message-ID: | CAGD6t7LsWi4pYPye-eY9V-=WG8FxB+AzJ-ziwNaDK9A27a_WVg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Celia McInnis <celia(dot)mcinnis(at)gmail(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Riku Iki | 2024-04-25 01:44:47 | Preallocation changes in Postgresql 16 |
Previous Message | Tom Lane | 2024-04-24 21:54:56 | Re: is there an immutable function to switch from date to character? |