Re: Should we document how column DEFAULT expressions work?

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(dot)g(dot)johnston(at)gmail(dot)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(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.