Как вернуть тип строки таблицы плюс дополнительный столбец из функции?

Question

У меня есть таблица, определенная так:

create table users (
  id serial primary key,
  name text,
  email text,
);

... и я хочу написать функцию, которая возвращает строки формы:

(
  id integer,
  name text,
  email text,
  some_other_column boolean,
)

Мне удалось заставить это работать с кодом ниже, но я бы не хотел переопределять столбцы из таблицы пользователей:

create or replace function get_users () 
returns table (
  id integer,
  name text,
  email text,
  some_other_column boolean,
) as $$
    select users.*, true as some_other_column from users;
$$ language sql;

Есть ли способ динамически создать тип строки, делая что-то вроде этого? (postgres жалуется на синтаксическую ошибку в users.*):

create or replace function get_users () 
returns table (
  users.*, 
  some_other_column boolean
) as $$
    select users.*, true as some_other_column from users;
$$ language sql;

Обратите внимание, что следующий запрос, выполняемый напрямую, работает просто отлично:

select users.*, true as some_other_column from users;

---

Конечная цель здесь состоит в том, чтобы в итоге получить функцию, которая может быть вызвана как select * from get_users() это возвращает строки, которые включают и столбцы из существующих таблиц и дополнительные столбцы. Я не хочу, чтобы вызывающая сторона беспокоилась о том, как именно вызвать функцию.

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

Answer 1

Нет. В настоящее время нет способа сделать это (включая стр. 10).

SQL является строго типизированным языком. Когда вы создаете функцию, тип возвращаемого значения должен быть объявлен. Чтобы вернуть набор строк (которые вы можете вызвать с SELECT * FROM srf()):

• Вы можете вернуть анонимные записи (RETURNS SETOF record). Но тогда вы должны предоставить список определений столбцов при каждом вызове.

• вы можете вернуть полиморфный (рядный) тип (RETURNS SETOF anyelement). Но вы должны предоставить тип строки ( составной тип) в качестве параметра функции, и тип строки должен быть каким-то образом зарегистрирован в системе.

  • Рефакторинг функции PL/pgSQL для возврата результатов различных запросов SELECT

• Вы можете использовать любой зарегистрированный тип строки явно, с RETURNS SETOFrowtype, Побочным эффектом является то, что функция теперь зависит от типа строки.

• Вы можете определить возвращаемый тип строки с помощью RETURNS TABLE (...) - где вы можете даже смешивать типы строк ( составные типы) и простые типы. Но простой SELECT * FROM srf() не будет разлагать вложенные типы строк - как показано в ответе Мабу.

Связанные с:

• Возврат строк SETOF из функции PostgreSQL

Все сводится к этому:

Есть ли способ динамически создать тип строки, делая что-то вроде этого?

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

Обычно лучше всего прописать список определений столбцов в RETURNS TABLE () пункт. Это позволяет избежать зависимостей. Если вам нужно быстро зарегистрировать тип строки на основе существующей таблицы без указания ее столбцов, вы можете создать VIEW - или TEMPORARY VIEW если это только для текущей сессии:

CREATE TEMP VIEW v_users_plus AS
SELECT *, NULL::boolean AS some_other_column FROM users;

Это регистрирует тип строки с тем же именем (v_users_plus) в системе, как и для любой другой таблицы или представления. Очевидно, что для невременной функции вам понадобится невременный тип строки.

Answer 2

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

Создание:

create or replace function get_users () 
returns table (
  row_users users, 
  some_other_column boolean
) as $$
    select t, true as some_other_column from users as t;
$$ language sql;

Вызов:

SELECT (row_users).*, some_other_column FROM get_users();

Фактически, вы можете попробовать другой тип возвращаемого значения для динамической структуры, такой как Refcursor или JSON... Это зависит от языка или приложения, которое вы используете.