Вернуть несколько полей в качестве записи в PostgreSQL с помощью PL/pgSQL

Ответ 1

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

CREATE TYPE my_type AS (f1 varchar(10), f2 varchar(10) /* , ... */ );

CREATE OR REPLACE FUNCTION get_object_fields(name text) 
RETURNS my_type 
AS 
$$

DECLARE
  result_record my_type;

BEGIN
  SELECT f1, f2, f3
  INTO result_record.f1, result_record.f2, result_record.f3
  FROM table1
  WHERE pk_col = 42;

  SELECT f3 
  INTO result_record.f3
  FROM table2
  WHERE pk_col = 24;

  RETURN result_record;

END
$$ LANGUAGE plpgsql; 

Если вы хотите вернуть более одной записи, вам нужно определить функцию как returns setof my_type

Обновить

Другой вариант - использовать RETURNS TABLE() вместо создания TYPE, который был введен в Postgres 8.4

CREATE OR REPLACE FUNCTION get_object_fields(name text) 
  RETURNS TABLE (f1 varchar(10), f2 varchar(10) /* , ... */ )
...

Ответ 2

Не используйте CREATE TYPE для возврата полиморфного результата. Вместо этого используйте тип записи > . Проверьте это:

CREATE FUNCTION test_ret(a TEXT, b TEXT) RETURNS RECORD AS $$
DECLARE 
  ret RECORD;
BEGIN
  -- Arbitrary expression to change the first parameter
  IF LENGTH(a) < LENGTH(b) THEN
      SELECT TRUE, a || b, 'a shorter than b' INTO ret;
  ELSE
      SELECT FALSE, b || a INTO ret;
  END IF;
RETURN ret;
END;$$ LANGUAGE plpgsql;

Обратите внимание на то, что он может при необходимости возвращать столбцы два или три в зависимости от ввода.

test=> SELECT test_ret('foo','barbaz');
             test_ret             
----------------------------------
 (t,foobarbaz,"a shorter than b")
(1 row)

test=> SELECT test_ret('barbaz','foo');
             test_ret             
----------------------------------
 (f,foobarbaz)
(1 row)

Это наносит ущерб коду, поэтому используйте последовательное количество столбцов, но это смехотворно удобно для возвращения необязательных сообщений об ошибках с первым параметром, возвращающим успех операции. Переписано с использованием согласованного количества столбцов:

CREATE FUNCTION test_ret(a TEXT, b TEXT) RETURNS RECORD AS $$
DECLARE 
  ret RECORD;
BEGIN
  -- Note the CASTING being done for the 2nd and 3rd elements of the RECORD
  IF LENGTH(a) < LENGTH(b) THEN
      ret := (TRUE, (a || b)::TEXT, 'a shorter than b'::TEXT);
  ELSE
      ret := (FALSE, (b || a)::TEXT, NULL::TEXT);
   END IF;
RETURN ret;
END;$$ LANGUAGE plpgsql;

Почти до эпической жара:

test=> SELECT test_ret('foobar','bar');
   test_ret    
----------------
 (f,barfoobar,)
(1 row)

test=> SELECT test_ret('foo','barbaz');
             test_ret             
----------------------------------
 (t,foobarbaz,"a shorter than b")
(1 row)

Но как вы разделите это на несколько строк, чтобы ваш выбор ORM мог преобразовывать значения в ваш язык выбора собственных типов данных? Жара:

test=> SELECT a, b, c FROM test_ret('foo','barbaz') AS (a BOOL, b TEXT, c TEXT);
 a |     b     |        c         
---+-----------+------------------
 t | foobarbaz | a shorter than b
(1 row)

test=> SELECT a, b, c FROM test_ret('foobar','bar') AS (a BOOL, b TEXT, c TEXT);
 a |     b     | c 
---+-----------+---
 f | barfoobar | 
(1 row)

Это одна из самых классных и самых недоиспользуемых функций в PostgreSQL. Пожалуйста, распространите это слово.

Ответ 3

Это может быть проще с OUT параметры:

CREATE OR REPLACE FUNCTION get_object_fields(
          name text
    ,OUT user1_id   int
    ,OUT user1_name varchar(32)
    ,OUT user2_id   int
    ,OUT user2_name varchar(32)
) AS 
$func$
BEGIN
    SELECT t.user1_id, t.user1_name
    INTO     user1_id,   user1_name
    FROM   tbl1 t
    WHERE  t.tbl1_id = 42;

    user2_id := user1_id + 43; -- some calculation

    SELECT t.user2_name
    INTO     user2_name
    FROM   tbl2 t
    WHERE  t.tbl2_i = user2_id;
END
$func$ LANGUAGE plpgsql;

• Вам не нужно создавать тип только для этой функции plpgsql. Это может быть полезно, если вы хотите связать несколько функций с одним и тем же типом. Я редко использую его, так как добавлены параметры OUT.

• Как вы могли заметить, нет инструкции RETURN. Параметры OUT возвращаются автоматически, не требуется инструкция RETURN.

• Так как параметры OUT видны повсюду внутри тела функции (и могут использоваться так же, как и любая другая переменная), убедитесь, что столбцы имеют одинаковые имена, чтобы избежать конфликтов имен.

Упрощение еще - или возврат нескольких строк

В большинстве случаев это можно упростить. Иногда запросы в теле функции можно комбинировать, что обычно (не всегда) быстрее. И вы можете использовать RETURNS TABLE() - введенный с Postgres 8.4 (задолго до того, как этот вопрос также был задан).

Пример сверху можно переписать как:

CREATE OR REPLACE FUNCTION get_object_fields(name text)
  RETURNS TABLE (
     user1_id   int
    ,user1_name varchar(32)
    ,user2_id   int
    ,user2_name varchar(32)) AS 
$func$
BEGIN
    RETURN QUERY
    SELECT t1.user1_id, t1.user1_name, t2.user2_id, t2.user2_name
    FROM   tbl1 t1
    JOIN   tbl2 t2 ON t2.user2_id = t1.user1_id + 43
    WHERE  t1.tbl1_id = 42
    LIMIT  1;  -- may be optional
END
$func$ LANGUAGE plpgsql; 

• RETURNS TABLE фактически совпадает с набором параметров OUT в сочетании с RETURNS record, немного короче/более элегантным.

• Основное отличие состоит в том, что эта функция может возвращать 0, 1 или много строк, а первая версия всегда возвращает 1 строку.
  Если вы хотите убедиться, что это возвращает только 0 или 1 строку, добавьте LIMIT 1, как показано.

• RETURN QUERY - очень удобный современный способ прямого возврата результатов запроса.
  Вы можете использовать несколько экземпляров в одной функции для добавления большего количества строк в вывод.

Различные типы строк

Если ваша функция должна динамически возвращать результаты с разными типами строк, в зависимости от ввода, читайте здесь:

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

Ответ 4

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

CREATE OR REPLACE FUNCTION get_object_fields
        (
        name text
        )
RETURNS mytable
AS
$$
        DECLARE f1 INT;
        DECLARE f2 INT;
        …
        DECLARE f8 INT;
        DECLARE retval mytable;
        BEGIN
        -- fetch fields f1, f2 and f3 from table t1
        -- fetch fields f4, f5 from table t2
        -- fetch fields f6, f7 and f8 from table t3
                retval := (f1, f2, …, f8);
                RETURN retval;
        END
$$ language plpgsql; 

Ответ 5

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

CREATE OR REPLACE FUNCTION schemaName.get_two_users_from_school(schoolid bigint)
 RETURNS SETOF record
 LANGUAGE plpgsql
AS $function$
begin

 return query
  SELECT id, name FROM schemaName.user where school_id = schoolid;

end;
$function$

И вызовите эту функцию как: select * from schemaName.get_two_users_from_school(schoolid) as x(a bigint, b varchar);

Ответ 6

вы можете сделать это, используя параметр OUT и CROSS JOIN

CREATE OR REPLACE FUNCTION get_object_fields(my_name text, OUT f1 text, OUT f2 text)
AS $$
SELECT t1.name, t2.name
FROM  table1 t1 
CROSS JOIN table2 t2 
WHERE t1.name = my_name AND t2.name = my_name;
$$ LANGUAGE SQL;

затем используйте его как таблицу:

select get_object_fields( 'Pending') ;
get_object_fields
-------------------
(Pending,code)
(1 row)

или

select * from get_object_fields( 'Pending');
f1    |   f
---------+---------
Pending | code
(1 row)

или

select (get_object_fields( 'Pending')).f1;
f1
---------
Pending
(1 row)