Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qgJ5l-00F8o6-UA for pgsql-general@arkaria.postgresql.org; Wed, 13 Sep 2023 06:11:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qgJ5k-00EyjW-Pc for pgsql-general@arkaria.postgresql.org; Wed, 13 Sep 2023 06:11:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qgJ5k-00EyjE-Bm for pgsql-general@lists.postgresql.org; Wed, 13 Sep 2023 06:11:12 +0000 Received: from mail-ed1-x529.google.com ([2a00:1450:4864:20::529]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qgJ5e-004mnd-Cf for pgsql-general@lists.postgresql.org; Wed, 13 Sep 2023 06:11:11 +0000 Received: by mail-ed1-x529.google.com with SMTP id 4fb4d7f45d1cf-52bcd4db4c0so1267408a12.0 for ; Tue, 12 Sep 2023 23:11:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20230601.gappssmtp.com; s=20230601; t=1694585465; x=1695190265; darn=lists.postgresql.org; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=ktlElH75DMfXE0ni4vrVvEcaSBl901++wMW7c/bSZ0s=; b=vhc62j/bXL21wQerRfpSaQS0VxWX0dtAjK8KdKdD4hiNvMqAYlNbgnBfbgd2D5eaHP VVamMSYlNShVNgmPEYRy8igVCa0W9Dvgah6tUos2z74QITIvPUte34D+0VL8B9GfndG4 ZKtcONEo+uzRhzCxZirMEmCK1RctISxn9AWETf9NlKH+Jn+k/W/52l11hhW97CmA9QZl lHnxw9BBpFxACMWn9BnieoeuzZMQN3bzb5Jqzw2DZNNAAa+VyC7l7uS0MOOHqaxtngA4 cFJk7YeviX68ROfvaRfMrh8pG7YMb98IMTA/457gkWVvHta521Fuf31qRRd2S4rwbJ+A m1CA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1694585465; x=1695190265; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=ktlElH75DMfXE0ni4vrVvEcaSBl901++wMW7c/bSZ0s=; b=VSjlXl6q+Tvw49POmocKAsszatALcsyXLPXSKPT/XkiA8YTb9jVOi8ArjI4M7et6QZ nAiF6S7V/vHU3HKIHSU+to8bSeaGkSo3X5WxXPVL5W2AsJnz5eLk0lFt81NFjqynmIbV a74JxfzvdNg6W+hNFdaOc/qjBvjQ8LPGy+ZsxdELPSFhojqyrxRMKCicw7TuyBSO0hXo 09Mm8ojHp+MMjHPkU49Jtdh9kReu7H1A9oKUu/tyX1r1ZDq856oYa/RjNye62BoCEl93 PskYf/8PnOwnwAKvSx5Amjn3qJv9J7Yl0s36RV2/A89/1Vm5IWDnR+90erT10XNOWeMz /iPA== X-Gm-Message-State: AOJu0YzJad6utQWr5qKbkyR9byGFYd4B/1pw3Jr/VG3z1l2ouNi95Zel VZhG5nsPM2KQJHoueZlqgeKuWA== X-Google-Smtp-Source: AGHT+IGNBPsfdJB38o2U6CNpJg3ilbEo7PjeIPXFfv8IHS6KCv5svtNa5G3ARUv+0pLaaafnoKcxIw== X-Received: by 2002:aa7:d28e:0:b0:52a:38c3:1b4b with SMTP id w14-20020aa7d28e000000b0052a38c31b4bmr2359974edq.15.1694585464585; Tue, 12 Sep 2023 23:11:04 -0700 (PDT) Received: from localhost.localdomain (178-190-169-82.adsl.highway.telekom.at. [178.190.169.82]) by smtp.gmail.com with ESMTPSA id g2-20020a50ee02000000b0051e2670d599sm6802750eds.4.2023.09.12.23.11.04 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Tue, 12 Sep 2023 23:11:04 -0700 (PDT) Message-ID: Subject: Re: Unqualified relations in views From: Laurenz Albe To: Pete O'Such , pgsql-general@lists.postgresql.org Date: Wed, 13 Sep 2023 08:11:03 +0200 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.48.4 (3.48.4-1.fc38) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, 2023-09-13 at 01:58 -0400, Pete O'Such wrote: > For a view, how does one show what schema was used to qualify a relation,= when > the query used to create the view originally left the relation unqualifie= d? >=20 > The qualification of the view query seems static in all uses of the view. > Using pg_get_viewdef() returns the unqualified relation, but Postgres alw= ays > executes a qualified version of the view query, seemingly determined upon > creation of the view. >=20 > That implies the final qualifier is stored by Postgres, but I don't know = how > to show it. PostgreSQL resolves tables and other objects according to the setting of "search_path" at CREATE VIEW time. The query itself is stored in parsed fo= rm. pg_get_viewdef() deparses the query and only prepends a schema name if the schema is not on the "search_path". So the solution is to set "search_path= " empty: SET search_path =3D ''; SELECT pg_get_viewdef('myschema.myview'); Yours, Laurenz Albe