Re: How are the SELECT queries reconstructed in pg_views

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(dot)klaver(at)aklaver(dot)com