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.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.