Re: Function not found while creating partial index

On Sun, 2026-02-01 at 13:56 -0500, Chris Hanks wrote:
> Hello! The following series of statements results in an unexpected (to me) error on 18.0 and 18.1:
>
> CREATE FUNCTION my_function_1(n integer) RETURNS integer AS 'SELECT n' LANGUAGE SQL IMMUTABLE;
> CREATE FUNCTION my_function_2(n integer) RETURNS integer AS 'SELECT my_function_1(n)' LANGUAGE SQL IMMUTABLE;
> CREATE SCHEMA my_schema;
> CREATE TABLE my_schema.my_table (my_number integer);
> CREATE INDEX my_index ON my_schema.my_table (my_number) WHERE my_function_2(my_number) = 4;
>
> The CREATE INDEX fails with the following error:
>
> ERROR:  function my_function_1(integer) does not exist
> LINE 1: SELECT my_function_1(n)
>                ^
> HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
> QUERY:  SELECT my_function_1(n)
> CONTEXT:  SQL function "my_function_2" during inlining
>
> SQL state: 42883
>
> If I change the definition of my_function_2 to 'SELECT public.my_function_1(n)' then the index builds successfully, so that's my current workaround.

That's not the workaround, that's the proper solution.

See the documentation (https://www.postgresql.org/docs/current/sql-createindex.html)

> While CREATE INDEX is running, the search_path is temporarily changed to pg_catalog, pg_temp.

Yours,
Laurenz Albe