Version 16.x search_path behavior change?

Поиск
Список
Период
Сортировка
От Dennis
Тема Version 16.x search_path behavior change?
Дата
Msg-id f2b90664-32e8-4e2a-906b-099fe8692739@iletsel.nl
обсуждение исходный текст
Ответы Re: Version 16.x search_path behavior change?
Список pgsql-general
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







В списке pgsql-general по дате отправления:

Предыдущее
От: Johnathan Tiamoh
Дата:
Сообщение: Unable to start postgresql-14
Следующее
От: "Wilma Wantren"
Дата:
Сообщение: Re: Re: Changing a schema's name with function1 calling function2