From: Tom Lane <tgl@sss.pgh.pa.us>
To: turon.david@seznam.cz
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19084: dump/restore table doesn't work with GENARETED column
 and custom type and cast in function
In-reply-to: <19084-c3dd88cd54fff87a@postgresql.org>
References: <19084-c3dd88cd54fff87a@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Mon, 13 Oct 2025 13:32:33 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <232356.1760369352.1@sss.pgh.pa.us>
Date: Mon, 13 Oct 2025 11:29:12 -0400
Message-ID: <232357.1760369352@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/232357.1760369352%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> CREATE FUNCTION f(data jsonb) RETURNS test_type[] AS $$ SELECT
> array_agg((e->>'item')::test_type) FROM jsonb_array_elements(data->'test')
> AS e $$ LANGUAGE SQL IMMUTABLE;

The problem with this function is that it doesn't work unless
type public.test_type is in the search_path.  Which it is not
during pg_restore (at least not with moderately-recent versions).

You could either schema-qualify the reference to test_type,
or switch the function to new-style SQL:

CREATE FUNCTION f(data jsonb) RETURNS test_type[]
IMMUTABLE
BEGIN ATOMIC
SELECT array_agg((e->>'item')::test_type)
  FROM jsonb_array_elements(data->'test') AS e;
END;

If you use that syntax then the test_type reference is parsed at
function definition time instead of function execution, and
everything is a lot safer.

(The same goes for your other SQL-language function.)

			regards, tom lane