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 1phFe2-0004ik-Eg for pgsql-general@arkaria.postgresql.org; Tue, 28 Mar 2023 20:10:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1phFe1-0006md-C5 for pgsql-general@arkaria.postgresql.org; Tue, 28 Mar 2023 20:10:13 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ph9bZ-0006mx-JQ for pgsql-general@lists.postgresql.org; Tue, 28 Mar 2023 13:43:17 +0000 Received: from mail-ed1-x52f.google.com ([2a00:1450:4864:20::52f]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1ph9bT-0002hc-19 for pgsql-general@lists.postgresql.org; Tue, 28 Mar 2023 13:43:16 +0000 Received: by mail-ed1-x52f.google.com with SMTP id r11so49926753edd.5 for ; Tue, 28 Mar 2023 06:43:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=prisma.io; s=google; t=1680010989; h=content-transfer-encoding:subject:from:to:content-language :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=DIz0PDzYDZYMDvK0Ac8Vh0j8WDEvpsUOQyBUnTbAyNI=; b=G/t5BX40/pODv96m6NBatotxEHgwB924FVzPCLNj+KRDx+F7uHT6GituWnGlxJ9VqI ovZ5PtWXECrCW0A6oKZFAiHgAhhT2f8SSq7s68nu3tEpPed7uKmesK2B7DRvdPBlrMOk ksNeOR1duTJKnC6/GAlgx1FhKOShfGwqm77jI7Uu5fCxA/J9lEpeKcovW6Tsm0DJh7Ac qfWDfrdYNlxhrD/2m9nWcDHwqJ5z0gnif9RAS8uZGfLTX6IeQUMcDxZT25G5vvGNCdxJ dKSna8U5GUxm2RGXPwNM5iHH1g7J2MgpWJXPSmMFRnWXokS+LmKWFMHgy5Y977VhXrCg 1Axw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; t=1680010989; h=content-transfer-encoding:subject:from:to:content-language :user-agent:mime-version:date:message-id:x-gm-message-state:from:to :cc:subject:date:message-id:reply-to; bh=DIz0PDzYDZYMDvK0Ac8Vh0j8WDEvpsUOQyBUnTbAyNI=; b=6S4gg4gw8wDfFKWs84ff5EAQ9IAH/x2eILU/v488CHue+mbCpP3CY/qgidNq20s+J/ TiS1TVYput9OsRw2TvzAcPzwxqLwkybCbWWBFJKUn+xNAxnif7eKJWd9aq9UGIrdJAm6 KeWCebbLjExbTG2cjkCMJ+vq9Ka8JDiKGuzfjf4uVfr8+Z/phhxKY1Juqmi+hpDKaJMM iXKdjEvmLeJfklBSGzm+f8tQciuwBVI/uWzorBAn83w0Jj3QcvwkAJpGbSjw1JdevRFD zvWNizU9sqsgP5DitYfjKJwCNfT9c7XVIRkALXnc/i/s+lONjNaodJoT4kQl4PpTQF+2 DcNg== X-Gm-Message-State: AAQBX9ffPSaBWUMTzfL+ESoByRUqDSvoEEXrfEVMBJOsVDdRJ+vCMRHJ EpFwzG8XvPxouBiQ4IAwDXPd1RWEtqxCXrxusibz7w== X-Google-Smtp-Source: AKy350Zvhee0BDuVxjPHRxUlkReG5hSI9e/JCyzrWNg13GKXKxOIgBruw4fX4HwYp/99+rmHVfCLAQ== X-Received: by 2002:a17:906:719b:b0:926:fce:c080 with SMTP id h27-20020a170906719b00b009260fcec080mr15154297ejk.17.1680010989240; Tue, 28 Mar 2023 06:43:09 -0700 (PDT) Received: from [10.0.1.30] (ip4d161deb.dynamic.kabel-deutschland.de. [77.22.29.235]) by smtp.gmail.com with ESMTPSA id s12-20020a50d48c000000b005023aaf52desm4176034edi.95.2023.03.28.06.43.08 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Tue, 28 Mar 2023 06:43:08 -0700 (PDT) Message-ID: <25b7f08f-1a5d-669a-1678-9def044df0bf@prisma.io> Date: Tue, 28 Mar 2023 15:43:07 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.9.0 Content-Language: en-US To: pgsql-general@lists.postgresql.org From: Julius de Bruijn Subject: How are the SELECT queries reconstructed in pg_views 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 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/