Простой способ получить тип возвращаемого значения в виде таблицы SETOF плюс дополнительные поля?

Я пишу хранимую процедуру PL/pgSQL, которая возвращает набор записей; каждая запись содержит все поля существующей таблицы (назовем ее Retailer, которая имеет два поля: retailer_key и retailer_name). Это, конечно, работает:

CREATE FUNCTION proc_Find_retailers
 (IN p_Store_key INT)
   RETURNS SETOF Retailer
   AS $$ ...`

Теперь я хочу обновить sp, чтобы он возвращал два дополнительных поля в «конец» каждой возвращаемой записи. Я могу сделать что-то вроде:

CREATE FUNCTION proc_Find_store
 (IN p_Store_key INT)
   RETURNS TABLE (
      retailer_key int,
      retailer_name varchar(50),
      addl_field_1 int,
      addl_field_2 double precision)
   AS $$ ...

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

CREATE FUNCTION proc_Find_store
 (IN p_Store_key INT)
   RETURNS (SETOF Store,
      addl_field_1 int,
      addl_field_2 double precision)
   AS $$ ...

postgresql plpgsql postgresql-9.2 composite-types
person MichaelF    schedule 23.07.2013    source источник

Ответы (1)


arrow_upward
5
arrow_downward

Вы можете вернуть целую строку как составной тип и добавить еще:

CREATE OR REPLACE FUNCTION f_rowplus()
  RETURNS TABLE (rec demo, add_int int, add_txt text) AS
$func$
SELECT d, 5, 'baz'::text FROM demo d;
$func$  LANGUAGE sql;

Но тогда, когда вы используете простой вызов:

SELECT * FROM f_rowplus();

Вы получаете строку из таблицы demo как отдельный составной тип. Вам нужно позвонить:

SELECT (rec).*,  add_int, add_txt FROM f_rowplus();

чтобы получить все отдельные столбцы. Скобки обязательны.

Postgres здесь немного непоследователен. Если вы создаете функцию с:

CREATE OR REPLACE FUNCTION f_row2()
  RETURNS TABLE (rec demo) AS
...

затем составной тип demo молча преобразуется в отдельные столбцы (декомпозируется). Никакой связи с исходным составным типом не остается. Вы вообще не можете ссылаться на объявленный выходной столбец rec, так как он был заменен столбцами декомпозированного типа. Этот вызов приведет к сообщению об ошибке:

SELECT rec FROM f_row2();

То же самое:

CREATE OR REPLACE FUNCTION f_row3(OUT rec demo)
  RETURNS SETOF demo AS
...

Однако, как только вы добавите какие-либо еще OUT столбцов, составной тип будет сохранен в том виде, в каком он объявлен (а не декомпозирован), и вы сможете:

SELECT rec FROM f_rowplus();

с первой функцией.

Я создал SQL Fiddle, демонстрирующий варианты.

Кроме
При использовании функции, возвращающей несколько столбцов в списке FROM (как табличная функция) и выполняющей разложение в списке SELECT следующим образом:

SELECT (rec).* FROM f_rowplus();

... функция по-прежнему оценивается один раз только - при вызове и разложения в списке SELECT непосредственно следующим образом:

SELECT (f_rowplus()).*;  -- also: different result

... будет оценивать один раз для каждого столбца в возвращаемом типе. Подробности:

person Erwin Brandstetter    schedule 24.07.2013