pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_catalog.pg_get_viewdef pretty-print removes important parentheses
2+ messages / 2 participants
[nested] [flat]
Commits from this thread:
2022-12-01 1dd6700f44 a711b36e5b de0ff6088e Fix under-parenthesized display of AT TIME ZONE constructs.
* pg_catalog.pg_get_viewdef pretty-print removes important parentheses
@ 2022-12-01 14:12 Duncan Sands <duncan.sands@deepbluecap.com>
0 siblings, 1 reply; 2+ messages in thread
From: Duncan Sands @ 2022-12-01 14:12 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: pg_catalog.pg_get_viewdef pretty-print removes important parentheses
@ 2022-12-01 15:37 Tom Lane <tgl@sss.pgh.pa.us>
parent: Duncan Sands <duncan.sands@deepbluecap.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2022-12-01 15:37 UTC (permalink / raw)
To: Duncan Sands <duncan.sands@deepbluecap.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-12-01 15:37 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-12-01 14:12 pg_catalog.pg_get_viewdef pretty-print removes important parentheses Duncan Sands <duncan.sands@deepbluecap.com>
2022-12-01 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox