Message-ID: <df50dda5bad084ae0e8d8a8077d015a86625396a.camel@cybertec.at>
Subject: Re: Unqualified name not resolved in function called from
 materialized view (17.4)
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Peter Wright <pete@flooble.net>, pgsql-bugs@lists.postgresql.org
Date: Tue, 11 Mar 2025 12:58:37 +0100
In-Reply-To: <Z8_4Lf5EKH4ZDwvg@flooble.net>
References: <Z8_4Lf5EKH4ZDwvg@flooble.net>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/df50dda5bad084ae0e8d8a8077d015a86625396a.camel%40cybertec.at>
Precedence: bulk

On Tue, 2025-03-11 at 19:45 +1100, Peter Wright wrote:
> PostgreSQL version: 17.4
> Operating system: Ubuntu 24.04, x86_64, kernel 6.8.0-51-generic
> Description:
>=20
> Given a file "break_it.sql" containing this SQL:
>=20
>   SELECT version();
>   CREATE VIEW things AS SELECT 1 AS id, 'rock' AS thing_name;
>   CREATE FUNCTION num_things() RETURNS INTEGER AS $$ SELECT COUNT(1) FROM=
 things; $$ LANGUAGE SQL STABLE;
>   CREATE MATERIALIZED VIEW thing_report AS SELECT num_things() AS number_=
of_things;
>=20
> Running the following commands with the environment pointing at a Postgre=
SQL 17.4 server:
>=20
>   psql:break_it.sql:4: ERROR:  relation "things" does not exist
>   LINE 1:  SELECT COUNT(1) FROM things;
>                                 ^
>   QUERY:   SELECT COUNT(1) FROM things;
>   CONTEXT:  SQL function "num_things" during inlining
>=20
>=20
> Running the same commands with the environment pointing at a PostgreSQL 1=
6.8
> server does *not* report an error

That is not a bug, it is intentional.
See the first entry in the list of incompatibilities
https://www.postgresql.org/docs/current/release-17.html#RELEASE-17-MIGRATIO=
N

You should fix the function.

Yours,
Laurenz Albe