Hi,
Predating PostgreSQL's json functions, I had been using custom json
functions, which by now have been reduced to wrappers around the native
type, but still using their original signatures so as to not have to
change hundreds of stored procedures. One of these is unfortunately
called "json_object" which is also available as "pg_catalog.json_object".
This was managed, as in working up until version 15, by having these
json functions in a schema with higher search_path precedence.
The 16.x documentation still says the following:
----
However, you can explicitly place |pg_catalog| at the end of your search
path if you prefer to have user-defined names override built-in names.
----
This worked fine in PostgreSQL 15.5:
---
create schema myschema;
create function myschema.json_object(v text) returns json as $$
declare
begin
return to_json(v);
end;
$$ language plpgsql stable;
set search_path = myschema, pg_catalog;
select json_object('hello');
json_object
-------------
"hello"
(1 row)
\df json_object
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-------------+------------------+---------------------+------
myschema | json_object | json | v text | func
pg_catalog | json_object | json | text[] | func
pg_catalog | json_object | json | text[], text[] | func
---
But no longer in PostgreSQL 16.1:
---
create schema myschema;
create function myschema.json_object(v text) returns json as $$
declare
begin
return to_json(v);
end;
$$ language plpgsql stable;
set search_path = myschema, pg_catalog;
select json_object('hello');
ERROR: malformed array literal: "hello"
LINE 1: select json_object('hello');
^
DETAIL: Array value must start with "{" or dimension information.
# select myschema.json_object('hello');
json_object
-------------
"hello"
# \df json_object
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-------------+------------------+---------------------+------
myschema | json_object | json | v text | func
pg_catalog | json_object | json | text[] | func
pg_catalog | json_object | json | text[], text[] | func
---
The most relevant changelog updates I could find mention:
"""
Add SQL/JSON constructors (Nikita Glukhov, Teodor Sigaev, Oleg Bartunov,
Alexander Korotkov, Amit Langote)
The new functions JSON_ARRAY(), JSON_ARRAYAGG(), JSON_OBJECT(), and
JSON_OBJECTAGG() are part of the SQL standard.
"""
but I am not sure what this means in this case, as
pg_catalog.json_object(..) also existed earlier, where the above
search_path selectivity used to work.
Is this intentional? If so, is there a recommendation as to how to work
around this?
Thanks for any suggestions,
Dennis
