Привет. Подскажите пожалуйста почему так. Есть 2 функции: to_json и json_populate_record https://www.postgresql.org/docs/current/functions-json.html to_json

применяет пользовательское преобразование типов
> Otherwise, if there is a cast from the SQL data type to json, the cast function will be used to perform the conversion;

json_populate_record не смотрит в пользовательское преобразование типов, и сразу пытается привести json как строку к нужному значению.
> Otherwise, if the JSON value is a string, the contents of the string are fed to the input conversion function for the column's data type.
> Otherwise, the ordinary text representation of the JSON value is fed to the input conversion function for the column's data type.

Я хочу сделать свой CREATE CAST (json AS timestamptz) WITH FUNCTION instant AS IMPLICIT;
И чтобы он работал как с to_json, так и с json_populate_record. Т.е. если сейчас взять json из to_json, то он не разберется функцией json_populate_record
Моя фукнция instant кодирует [«$instant», «2023-04-30»]::json в 2023-04-30::timestamptz и обратно.

Почему json_populate_record не смотрит таблицу преобразований?
Это недоработка и когда-нибудь поправят?

Как автор рекурсивного преобразования вложенных массивов и записей в json[b]_populate_record(), которое было добавлено в PG10 для получения поведения, обратного поведению to_json[b](), считаю скорее недоработкой то, что в json[b]_populate_record() игнорируется наличие CAST в json. Поддержка CAST тогда не была добавлена одновременно с рекурсией, видимо, потому что это могло привести к несовместимостям с уже имевшимся в PG9.6 поведением -- там всегда вызывались input-функций для полей записей. Для вложенных массивов и объектов несовместимостей не возникало, потому что в PG9.6 в принципе из json никак нельзя было получить PG-массивы и записи через input-функции из-за различий в формате. Но даже в самом to_json[b]() для встроенных типов (в т.ч. булевских, числовых и datetime-типов) CAST вызываться тоже не будет: см. json_categorize_type() и datum_to_json() в src/backend/utils/adt/json.c ( https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/json.c#L193 ). Для обхода этого, видимо, придется делать пользовательский клон типа timestampz. CREATE TYPE my_timestamptz; CREATE FUNCTION my_timestamptz_in (cstring, oid, integer) RETURNS my_timestamptz AS 'timestamptz_in' LANGUAGE internal IMMUTABLE STRICT; CREATE FUNCTION my_timestamptz_out (my_timestamptz) RETURNS cstring AS 'timestamptz_out' LANGUAGE internal IMMUTABLE STRICT; CREATE TYPE my_timestamptz ( INPUT = my_timestamptz_in, OUTPUT = my_timestamptz_out, INTERNALLENGTH = 8, PASSEDBYVALUE, ALIGNMENT = double, STORAGE = plain ); CREATE FUNCTION instant(tstz my_timestamptz) RETURNS json AS $$ SELECT json_build_array('$instant', $1::text) $$ LANGUAGE sql; CREATE CAST (my_timestamptz AS json) WITH FUNCTION instant(my_timestamptz) AS IMPLICIT; SELECT to_json('2023-05-09'::my_timestamptz); to_json ---------------------------------------- ["$instant", "2023-05-09 00:00:00+00"]

Спасибо. Есть шанс, что поправят json_populate_record? Или добавят новую функцию, чтобы не ломать обратную совместимость? Может быть можно написать свою функцию на pl/pgsql?

Добавление новой функции вроде from_json[b](any, json[b]) имеет смысл, потому что это было бы наиболее симметрично to_json[b](). И у нас пока нет функции json[b]_populate_array(), хотя внутри вся для этого имеется, поэтому недостающую обработку массивов тоже можно было поместить в from_json[b](). Ждать появления таких новых функций в ванилле стоит, если только туда сообщить о проблеме (а желательно сразу и прикрепить патч) и если наличие проблемы будет ими подтверждено. В PL/PgSQL вряд ли получится сделать обобщенную функцию, так как там надо уметь работать с произвольными типами, и все это должно быть очень медленно из-за необходимости изучения каталога. Для конвертирования единичного поля можно сначала заменить это поле внутри json, преобразовав его в timestamptz-строку, а потом уже весь конвертировать json-объект: json_populate_record( NULL::my_record, js || json_build_object('tstz', instant(js -> 'my_tstz')) ) Но для массивов записей с timestamptz такое работать не будет. Если есть возможность использовать расширения, то предлагаю очень грязный хак с подменой timestamptz_in() в каталоге. Код my_timestamptz_in() приложен в файле. Без C обойтись не получается, потому что тип cstring нельзя использовать в SQL-функциях. CREATE FUNCTION my_timestamptz_in(cstring, oid, int4) RETURNS timestampz IMMUTABLE STRICT LANGUAGE C AS 'my_timestamptz_in'; -- AS 'MODULE_PATHNAME'; -- inside extension script UPDATE pg_type SET typinput = 'my_timestamptz_in'::regproc WHERE typname = 'timestamptz'; CREATE FUNCTION json_to_timestamptz(js json) RETURNS timestamptz AS $$ SELECT ($1 ->> 1)::timestamptz $$ LANGUAGE SQL; SELECT '["$instant", "2023-05-10"]'::timestamptz; timestamptz ------------------------ 2023-05-10 00:00:00+03 CREATE TYPE my_record AS (tstz timestamptz); SELECT * FROM json_populate_record(NULL::my_record, '{ "tstz" : ["$instant", "2023-05-10"] }'); tstz ------------------------ 2023-05-10 00:00:00+03 Ну и еще всегда есть вариант скопировать весь код json_populate_record() в расширение и исправить его, как нужно.

Добавление новой функции вроде from_json[b](any, json[b]) имеет смысл, потому что это было бы наиболее симметрично to_json[b](). И у нас пока нет функции json[b]_populate_array(), хотя внутри вся для этого имеется, поэтому обработку массивов тоже можно было поместить в from_json[b](). Ждать появления таких новых функций в ванилле стоит, если только туда сообщить о проблеме (а желательно сразу и прикрепить патч) и если наличие проблемы будет ими подтверждено. В PL/PgSQL вряд ли получится сделать обобщенную функцию, так как там надо уметь работать с произвольными типами, и все это должно быть очень медленно из-за изучения каталога. Для конвертирования единичного поля можно сначала заменить это поле внутри json, преобразовав его в timestamptz-строку, а потом уже весь конвертировать json-объект: json_populate_record(NULL::my_record, js || json_build_object('tstz', instant(js -> 'my_tstz'))) Но для массивов с timestamptz такое работать не будет. Если есть возможность использовать расширения, то предлагаю очень грязный хак с подменой timestamptz_in() в каталоге. Без C обойтись не получается, потому что тип cstring нельзя использовать в SQL-функциях. my_timestamptz_in(): https://gist.github.com/glukhovn/22ca6b3927476f9433b8bf66de4150f5 CREATE FUNCTION my_timestamptz_in(cstring, oid, int4) RETURNS timestampz IMMUTABLE STRICT LANGUAGE C AS 'my_timestamptz_in'; -- AS 'MODULE_PATHNAME'; -- inside extension script UPDATE pg_type SET typinput = 'my_timestamptz_in'::regproc WHERE typname = 'timestamptz'; CREATE FUNCTION json_to_timestamptz(js json) RETURNS timestamptz AS $$ SELECT ($1 ->> 1)::timestamptz $$ LANGUAGE SQL; SELECT '["$instant", "2023-05-10"]'::timestamptz; timestamptz ------------------------ 2023-05-10 00:00:00+03 CREATE TYPE my_record AS (tstz timestamptz); SELECT * FROM json_populate_record(NULL::my_record, '{ "tstz" : ["$instant", "2023-05-10"] }'); tstz ------------------------ 2023-05-10 00:00:00+03 Ну и еще всегда есть вариант скопировать весь код json_populate_record() в расширение и исправить его, как нужно.