Considering the following SQL code:
create table test (a int, b int) ;
create type tst as (a int, b int) ;
create function tst (a int, b int) returns tst[] language sql immutable as
$$ select array( select row(a,b) :: tst) ; $$ ;
create index index_test on test using gin (tst(a,b) array_ops) ;
This SQL code works well with Postgres until v16, see dbfiddle, but it doesn't work any more with Postgres v17 and v18, see dbfiddle.
ERROR: type "tst" does not exist
LINE 1: select array( select row(a,b) :: tst) ;
QUERY: select array( select row(a,b) :: tst) ;
CONTEXT: SQL function "tst" during inlining
What has changed with Postgres v17 & v18, and how to fix this issue?
For security reasons, PostgreSQL commit 2af07e2f7 forces the search_path to be empty during CREATE INDEX. That makes the type lookup fail when the function is inlined.
One solution is to write
create function tst (a int, b int) returns tst[] language sql immutable as
$$ select array( select row(a,b) :: myschema.tst) ; $$ ;
where myschema is the schema of the tst type.
The alternative is to define the SQL function using standard-conforming syntax:
CREATE FUNCTION tst (a int, b int) RETURNS tst[] IMMUTABLE
RETURN array[row(a,b) :: tst];
That works, because the function body is parsed during CREATE FUNCTION, but has the disadvantage that the function body is not inlined in the index definition.