Postgres Pro
Downloads
Home   >   mailing lists

Thread: How are the SELECT queries reconstructed in pg_views

How are the SELECT queries reconstructed in pg_views

From
Julius de Bruijn
Date:
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/

Re: How are the SELECT queries reconstructed in pg_views

From
Adrian Klaver
Date:
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@aklaver.com

Re: How are the SELECT queries reconstructed in pg_views

From
Adrian Klaver
Date:
On 3/28/23 13:23, Adrian Klaver wrote:
> On 3/28/23 06:43, Julius de Bruijn wrote:
>> Hi,

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

I should have added I got pg_get_viewdef() from the below :

\d+ information_schema.views


...

  CASE
             WHEN pg_has_role(c.relowner, 'USAGE'::text) THEN 
pg_get_viewdef(c.oid)
             ELSE NULL::text
         END::information_schema.character_data AS view_definition,

...

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


-- 
Adrian Klaver
adrian.klaver@aklaver.com