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
Date: Mon, 13 Oct 2025 11:29:12 -0400
Message-ID: <232357.1760369352@sss.pgh.pa.us> (raw)
In-Reply-To: <19084-c3dd88cd54fff87a@postgresql.org>
References: <19084-c3dd88cd54fff87a@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, turon.david@seznam.cz, 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: <232357.1760369352@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox