Re: Should we document how column DEFAULT expressions work? - Mailing list pgsql-hackers
From | Bruce Momjian |
---|---|
Subject | Re: Should we document how column DEFAULT expressions work? |
Date | |
Msg-id | ZohYEScvGh3gn136@momjian.us Whole thread Raw |
In response to | Re: Should we document how column DEFAULT expressions work? (David Rowley <dgrowleyml@gmail.com>) |
Responses |
Re: Should we document how column DEFAULT expressions work?
|
List | pgsql-hackers |
On Mon, Jul 1, 2024 at 02:52:42PM +1200, David Rowley wrote: > On Mon, 1 Jul 2024 at 13:41, David G. Johnston > <david.g.johnston@gmail.com> wrote: > > I presume the relatively new atomic SQL functions pose a similar hazard. > > Do you have an example of this? > > > The fact that 'now()'::timestamp fails to fail doesn't help... > > If that's the case, maybe a tiny step towards what Peter proposed is > just to make trailing punctuation fail for timestamp special values in > v18. I dug into this and I have a suggestion at the end. First, the special values like 'now' are the only values that can be optionally quoted: SELECT current_timestamp::timestamptz; current_timestamp ------------------------------- 2024-07-05 15:15:22.692072-04 SELECT 'current_timestamp'::timestamptz; ERROR: invalid input syntax for type timestamp with time zone: "current_timestamp" Also interestingly, "now" without quotes requires parentheses to make it a function call: SELECT 'now'::timestamptz; timestamptz ------------------------------- 2024-07-05 15:17:11.394182-04 SELECT 'now()'::timestamptz; timestamptz ------------------------------- 2024-07-05 15:17:15.201621-04 SELECT now()::timestamptz; now ------------------------------- 2024-07-05 15:17:21.925611-04 SELECT now::timestamptz; ERROR: column "now" does not exist LINE 1: SELECT now::timestamptz; ^ And the quoting shows "now" evaluation at function creation time: CREATE OR REPLACE FUNCTION testnow() RETURNS timestamptz LANGUAGE SQL RETURN 'now'::timestamptz; SELECT testnow(); SELECT pg_sleep(5); SELECT testnow(); testnow ------------------------------- 2024-07-05 15:19:38.915255-04 testnow ------------------------------- 2024-07-05 15:19:38.915255-04 -- same --------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION testnow() RETURNS timestamptz LANGUAGE SQL RETURN 'now()'::timestamptz; SELECT testnow(); SELECT pg_sleep(5); SELECT testnow(); testnow ------------------------------- 2024-07-05 15:20:41.475997-04 testnow ------------------------------- 2024-07-05 15:20:41.475997-04 -- same --------------------------------------------------------------------------- CREATE OR REPLACE FUNCTION testnow() RETURNS timestamptz LANGUAGE SQL RETURN now()::timestamptz; SELECT testnow(); SELECT pg_sleep(5); SELECT testnow(); testnow ------------------------------- 2024-07-05 15:21:18.204574-04 testnow ------------------------------- 2024-07-05 15:21:23.210442-04 -- different I don't think we can bounce people around to different sections to explain this --- I think we need text in the CREATE TABLE ... DEFAULT section. I think the now() case is unusual since there are few cases where function calls can be put inside of single quotes. I have written the attached patch to clarify the behavior. -- Bruce Momjian <bruce@momjian.us> https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Attachment
pgsql-hackers by date: