Порт Oracle decode() с использованием variadic, anyarray и anyelement

Question

Мне нужно перенести из Oracle хранимую процедуру, которая использует decode() широко. То есть я не могу использовать серию CASE WHEN expr THEN expr [...] ELSE как подсказывает гид.

Я хотел создать переменную функцию, но вот проблема: в Oracle функция может принимать любое количество пар ключ-значение, а тип ключа не обязательно совпадает с типом значения:

select decode(0  ,0,'a'  ,1,'b'  ,2,'c'  /*,...*/ ,'dflt') from dual;

Я пытался использовать anyarray:

create or replace function decode(VARIADIC args anyarray) RETURNS text AS $$
  SELECT null::text;
$$ LANGUAGE SQL;

Но это работает только тогда, когда все аргументы имеют одинаковый тип:

select decode(0,0,0); -- ok
select decode('x'::text,'x'::text,'x'::text); -- ok
select decode(0,0,'a'::text); -- No function matches the given name and argument types

Если желаемый синтаксис невозможен, пожалуйста, посоветуйте другой способ передачи expr, набор пар и значение по умолчанию, сохраняя их позиции, как в Oracle.

Answer 1

ограничение

Кажется, именно так реализован PostgreSQL. Чтение документов:

35.4.5. Функции SQL с переменным числом аргументов

Функции SQL могут быть объявлены, чтобы принимать переменные числа аргументов, при условии, что все "необязательные" аргументы имеют один и тот же тип данных. Необязательные аргументы будут переданы функции в виде массива. Функция объявляется путем пометки последнего параметра как VARIADIC; этот параметр должен быть объявлен как тип массива.

JSON

Если вы найдете способ экспортировать ваш смешанный массив в формат JSON из Oracle, то с ним справится JSON-тип PostgreSQL:

CREATE OR REPLACE FUNCTION xdecode(data json)
RETURNS TEXT AS
$BODY$
    -- Your implementation here
    SELECT NULL::TEXT;
$BODY$ LANGUAGE SQL;

Эта функция принимает строку JSON и может выглядеть так:

SELECT xdecode('[1, 2, 3.3, "a", true, null]'::json);

Тип таблицы

Если кортеж аргументов, которые вы хотите декодировать, соответствует TABLE типа, тогда вы можете использовать его:

CREATE TABLE foo(
    x INTEGER,
    y FLOAT,
    z TEXT
);

CREATE OR REPLACE FUNCTION xdecode2(data foo)
RETURNS TEXT AS
$BODY$
    SELECT row_to_json(data)::TEXT;
$BODY$ LANGUAGE SQL;

Тогда этот вызов работает:

SELECT xdecode2((1, 2.1, 'x'))

К сожалению, мы не можем использовать общий RECORD введите в качестве входного аргумента для функции.

CREATE OR REPLACE FUNCTION xdecode3(data RECORD)
RETURNS TEXT AS
$BODY$
    SELECT row_to_json(data)::TEXT;
$BODY$ LANGUAGE SQL;

поднимает:

ERROR: SQL functions cannot have arguments of type record
SQL state: 42P13

Тип anyelement

Как указано @basin, введите RECORD можно эмулировать с помощью anyelement:

CREATE OR REPLACE FUNCTION xdecode4(data anyelement)
RETURNS TEXT AS
$BODY$
    SELECT row_to_json(data)::TEXT;
$BODY$ LANGUAGE SQL;

SELECT xdecode4((1, 2.1, 'x'));

Возвращает:

'{"f1":1,"f2":2.1,"f3":"x"}'