On 3/28/23 06:43, Julius de Bruijn wrote:
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?
SELECT pg_catalog.pg_get_viewdef('61822'::pg_catalog.oid, true);
pg_get_viewdef
--------------------------------
SELECT up_test.id AS up_id, +
up_test.col_1 AS bool_col,+
up_test.col_2 AS col2 +
FROM up_test;
Find pg_get_viewdef here:
https://www.postgresql.org/docs/current/functions-info.html
Source here:
~/src/backend/utils/adt/ruleutils.c
Thank you!
Julius de Bruijn
Software Engineer
https://www.prisma.io/
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
