Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix under-parenthesized display of AT TIME ZONE constructs.

  1. pg_catalog.pg_get_viewdef pretty-print removes important parentheses

    Duncan Sands <duncan.sands@deepbluecap.com> — 2022-12-01T14:12:09Z

    When pg_catalog.pg_get_viewdef is called with pretty-print enabled, it removes 
parentheses around (DATE + INTERVAL) in the construct "(DATE + INTERVAL) AT TIME 
ZONE TZ", outputting SQL that fails to parse.  Here is an example, showing that 
the SQL output when pretty printing is off does parse, but does not parse when 
pretty printing is on.

-- EXAMPLE BEGINS

$ psql
Pager usage is off.
SET
psql (15.1 (Ubuntu 15.1-1.pgdg22.04+1))
Type "help" for help.

duncan=> CREATE VIEW pg_temp.v AS SELECT ('2022-12-01'::date + INTERVAL '1 day') 
AT TIME ZONE 'Europe/Paris';
CREATE VIEW
duncan=> SELECT pg_catalog.pg_get_viewdef('pg_temp.v'::regclass, false);
                                           pg_get_viewdef 

---------------------------------------------------------------------------------------------------
   SELECT (('2022-12-01'::date + '1 day'::interval) AT TIME ZONE 
'Europe/Paris'::text) AS timezone;
(1 row)

duncan=>   SELECT (('2022-12-01'::date + '1 day'::interval) AT TIME ZONE 
'Europe/Paris'::text) AS timezone;
         timezone
------------------------
  2022-12-02 00:00:00+01
(1 row)

duncan=> SELECT pg_catalog.pg_get_viewdef('pg_temp.v'::regclass, true);
                                          pg_get_viewdef 

-------------------------------------------------------------------------------------------------
   SELECT ('2022-12-01'::date + '1 day'::interval AT TIME ZONE 
'Europe/Paris'::text) AS timezone;
(1 row)

duncan=>   SELECT ('2022-12-01'::date + '1 day'::interval AT TIME ZONE 
'Europe/Paris'::text) AS timezone;
ERROR:  function pg_catalog.timezone(text, interval) does not exist
LINE 1: SELECT ('2022-12-01'::date + '1 day'::interval AT TIME ZONE ...
                                                        ^
HINT:  No function matches the given name and argument types. You might need to 
add explicit type casts.

-- EXAMPLE ENDS

This is annoying because psql's \ev pops up the pretty-printed version, and you 
have to add the parentheses back in by hand in order to have the view definition 
be accepted.

Best wishes, Duncan.

  2. Re: pg_catalog.pg_get_viewdef pretty-print removes important parentheses

    Tom Lane <tgl@sss.pgh.pa.us> — 2022-12-01T15:37:05Z

    Duncan Sands <duncan.sands@deepbluecap.com> writes:
> When pg_catalog.pg_get_viewdef is called with pretty-print enabled, it removes 
> parentheses around (DATE + INTERVAL) in the construct "(DATE + INTERVAL) AT TIME 
> ZONE TZ", outputting SQL that fails to parse.

So it does :-(.  Will fix, thanks for the report!

			regards, tom lane