Как обернуть существующие функции (включая агрегаты) в новую в Postgres?

Я использую Postgres 9.2 для генерации некоторых данных JSON. Для каждой вложенной таблицы я выполняю этот вложенный набор функций:

SELECT array_to_json(
  coalesce(
    array_agg(
      row_to_json(foo)),
    ARRAY[]::json[])
)
FROM foo

Эффект заключается в создании массива json, каждая строка которого является коллекцией json для строки. Объединение гарантирует, что я получу пустой массив, а не ноль, если таблица пуста. В большинстве случаев foo на самом деле является подзапросом, но я не думаю, что это имеет отношение к вопросу.

Я хочу создать функцию table_to_json_array(expression), чтобы она имела тот же эффект, что и выше:

SELECT table_to_json_array(foo) FROM foo

Мне нужно использовать эти партии, поэтому я планировал создать функцию Postgres, чтобы получить эффект от комбинации этих вызовов для очистки моих запросов. Глядя на документацию, кажется, что мне нужно создать агрегат, а не функцию для приема аргумента таблицы, но похоже, что мне нужно будет заново реализовать array_agg самостоятельно.

Я что-то пропустил (возможно, просто тип, который должна принимать функция)? Какие-либо предложения?


postgresql json function aggregate plpgsql
person Joseph Lord    schedule 04.06.2014    source источник

Ответы (2)


arrow_upward
2
arrow_downward

В большинстве случаев foo на самом деле является подзапросом, но я не думаю, что это имеет отношение к вопросу.

К сожалению, это так. Вы можете создать функцию с аргументом regclass:

create or replace function table_to_json(source regclass)
returns json language plpgsql
as $$
declare
    t json;
begin
    execute format ('
        SELECT
            array_to_json(
                coalesce(array_agg(row_to_json(%s)),
                ARRAY[]::json[]))
        FROM %s', source, source)
        into t;
    return t;
end $$;

select table_to_json('my_table');
select table_to_json('my_schema.my_view');

Но в контексте:

select table_to_json_rec(arg)
from (select * from my_table) arg

аргумент arg имеет тип record. Функции PL/pgSQL не могут принимать запись типа. Единственный способ получить это - функция C, что, я думаю, не вариант. То же самое касается агрегатов (у вас должна быть функция для определения агрегата).

person klin    schedule 04.06.2014
comment
Текстовое представление значения regclass автоматически цитируется там, где это необходимо, поэтому оно должно быть format('.. %s ..', source). %I будет неправильным и потерпит неудачу для идентификаторов, которые должны быть заключены в кавычки. Подробности здесь. - person Erwin Brandstetter; 05.06.2014
comment
Спасибо, источником был текст в первой версии. - person klin; 05.06.2014
comment
Судя по вашим ответам, вы знаете подробности. Я оставляю комментарий для широкой публики, так как это распространенная ошибка. - person Erwin Brandstetter; 05.06.2014

arrow_upward
1
arrow_downward

В Postgres 9.3 добавлена ​​функция json_agg, которая упрощает конкретный запрос, который мне нужен, хотя это не является общим решением проблемы агрегатных функций. Ему по-прежнему нужна функция coalesce, чтобы обеспечить правильный возврат пустого набора.

SELECT coalesce( json_agg(foo), json'[]')
FROM foo

И это работает, даже если foo является подзапросом.

person Joseph Lord    schedule 04.06.2014