Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1phFtj-0005LZ-Ny for pgsql-general@arkaria.postgresql.org; Tue, 28 Mar 2023 20:26:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1phFti-0006pm-Jd for pgsql-general@arkaria.postgresql.org; Tue, 28 Mar 2023 20:26:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1phFrC-0007CJ-6E for pgsql-general@lists.postgresql.org; Tue, 28 Mar 2023 20:23:51 +0000 Received: from out5-smtp.messagingengine.com ([66.111.4.29]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1phFr7-0002fE-8T for pgsql-general@lists.postgresql.org; Tue, 28 Mar 2023 20:23:49 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id E54105C005C; Tue, 28 Mar 2023 16:23:41 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute2.internal (MEProxy); Tue, 28 Mar 2023 16:23:41 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:content-transfer-encoding:content-type:content-type:date:date :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to; s=fm1; t= 1680035021; x=1680121421; bh=2zR/5NYHn7/a2zp5MfJ8zS9ZZ9UFFtE+ZxE zB62WNwU=; b=V4RdRVusUmkqZ0uTVZyjC8hu9pfWikNEBRNCSudpBR/nOoJmRXN t7egrzObOasgHmocX7RPSmJRZmr8rZH7qlBY7nnmdYaHZUJxURj3EP2LPSEuYfMU VCyvaJw6vNkGWKTAVqQjgTZyuCSkwxLck0duc9LSiT0m9K7qlc2X79wbTd7UzWTt VqWTaCqO+QQC0/sKn0MJRofc09Fo4WC4jY0bwMZEd+dslJbOYlkIiOHrT2i0TmeS zdi9omk1QzrmKcQqav65r9HN3MIuBmfdGfBv81Z8UdXNGgmFSjK48VkXpZOUGHko V62CA4Ty0T6pBgH6DnomcrTThu+GIiNQkMw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-transfer-encoding:content-type :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t=1680035021; x= 1680121421; bh=2zR/5NYHn7/a2zp5MfJ8zS9ZZ9UFFtE+ZxEzB62WNwU=; b=o X+mlhDTBoUyAqcCRfSFQ8xrgMgENGyRcbJI0y3SLT3/p3Q8ReheJmBniH9KHkI+L TNz82pfqptwluANxww3GIVWZdqN4IF85W/lf/QgqrEbAYUAeNQAnlDtmFngDLUpu SlvFjNTR/pY8W3lluEjlutSC77HUoq9Ol5WFSxA3ou5gksKSpgqs1mz3EQs4T9Wz w8ZGNRRMXyx+UnoFfMcpPBLb4yK7DHfq5Akq41Xm9xe5pwyMdk4Nz8Es+GM9WL0h RFtcmgdklVYK3+eY4llJY4Z1waXzDAbfWnbgd4G/ni/oWbumLESHBkjjfs0yPwPL Qn1xhAaAsYUkZC+YH26GQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrvdehgedgudegkecutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmd enucfjughrpefkffggfgfuvfhfhfgjtgfgsehtjeertddtfeejnecuhfhrohhmpeetughr ihgrnhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtg homheqnecuggftrfgrthhtvghrnhephffguefgjeelgfevieekgfdtgedvledvteefkeeh iedvleelleejffetgfduudejnecuffhomhgrihhnpehpohhsthhgrhgvshhqlhdrohhrgh dpphhrihhsmhgrrdhiohenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgr ihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Tue, 28 Mar 2023 16:23:41 -0400 (EDT) Message-ID: <73b0f3b2-819d-4cb3-6101-cd02c033da5e@aklaver.com> Date: Tue, 28 Mar 2023 13:23:40 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.9.0 Subject: Re: How are the SELECT queries reconstructed in pg_views To: Julius de Bruijn , pgsql-general@lists.postgresql.org References: <25b7f08f-1a5d-669a-1678-9def044df0bf@prisma.io> Content-Language: en-US From: Adrian Klaver In-Reply-To: <25b7f08f-1a5d-669a-1678-9def044df0bf@prisma.io> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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