pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Julius de Bruijn <bruijn@prisma.io>
To: pgsql-general@lists.postgresql.org
Subject: How are the SELECT queries reconstructed in pg_views
Date: Tue, 28 Mar 2023 15:43:07 +0200
Message-ID: <25b7f08f-1a5d-669a-1678-9def044df0bf@prisma.io> (raw)

Hi,

We at Prisma are implementing developer tooling for PostgreSQL
database amongst the others. One part of our tooling is the migration
of schema changes to the database. We do that by diffing the schema
from the file system against the one we introspect from the database;
storing the changes to a migration file.

We are right now extending this to cover database views, and right now
I'm looking for ways to compare the SQL written by the user against
the SQL definition found in the pg_views view in the database.

We do have a good SQL parser, which makes minor differences such as
whitespace or newlines not matter in the comparison. What makes things
more difficult is how PostgreSQL reconstructs the SELECT query before
storing it to the database, as is written in the documentation:

https://www.postgresql.org/docs/current/view-pg-views.html

I haven't been able to find exactly what changes PostgreSQL does when
reconstructing the query, but I've successfully been able to create
views where the resulting query differs from what I wrote. Is there
any documentation for this feature where I can learn more about what
happens before the query is stringified to the information schema? Or,
even better, is there a way for me to send a query to the database and
as a result get back a reconstructed query?

Thank you!

Julius de Bruijn
Software Engineer
https://www.prisma.io/






view thread (3+ messages)  latest in thread

Message-ID: <25b7f08f-1a5d-669a-1678-9def044df0bf@prisma.io>
Permalink:  ../25b7f08f-1a5d-669a-1678-9def044df0bf@prisma.io/
Also on:    postgresql.org/message-id/25b7f08f-1a5d-669a-1678-9def044df0bf@prisma.io

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: bruijn@prisma.io
  Subject: Re: How are the SELECT queries reconstructed in pg_views
  In-Reply-To: <25b7f08f-1a5d-669a-1678-9def044df0bf@prisma.io>
  References: <25b7f08f-1a5d-669a-1678-9def044df0bf@prisma.io>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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