pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How are the SELECT queries reconstructed in pg_views
3+ messages / 2 participants
[nested] [flat]

* How are the SELECT queries reconstructed in pg_views
@ 2023-03-28 13:43 Julius de Bruijn <bruijn@prisma.io>
  2023-03-28 20:23 ` Re: How are the SELECT queries reconstructed in pg_views Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Julius de Bruijn @ 2023-03-28 13:43 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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/






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: How are the SELECT queries reconstructed in pg_views
  2023-03-28 13:43 How are the SELECT queries reconstructed in pg_views Julius de Bruijn <bruijn@prisma.io>
@ 2023-03-28 20:23 ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-03-28 20:26   ` Re: How are the SELECT queries reconstructed in pg_views Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Adrian Klaver @ 2023-03-28 20:23 UTC (permalink / raw)
  To: Julius de Bruijn <bruijn@prisma.io>; pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: How are the SELECT queries reconstructed in pg_views
  2023-03-28 13:43 How are the SELECT queries reconstructed in pg_views Julius de Bruijn <bruijn@prisma.io>
  2023-03-28 20:23 ` Re: How are the SELECT queries reconstructed in pg_views Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-03-28 20:26   ` Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Adrian Klaver @ 2023-03-28 20:26 UTC (permalink / raw)
  To: Julius de Bruijn <bruijn@prisma.io>; pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2023-03-28 20:26 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-28 13:43 How are the SELECT queries reconstructed in pg_views Julius de Bruijn <bruijn@prisma.io>
2023-03-28 20:23 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-03-28 20:26   ` Adrian Klaver <adrian.klaver@aklaver.com>

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