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
