Возвращать данные из функций Postgres

Я новичок в PostgreSQL, и у меня возникли некоторые проблемы с тем, чтобы заставить его делать то, что я хочу.

Мне нужно создать функцию, которая будет принимать несколько переменных, выполнять несколько внутренних запросов, а затем возвращать набор данных, состоящий из нескольких строк и нескольких столбцов. Я создал несколько тестовых функций, чтобы лучше понять функциональность Postgres, вот одна из них:

 CREATE OR REPLACE FUNCTION sql_with_rows11(id integer) RETURNS character varying AS $BODY$
 declare vid integer;
 declare vendor character varying;
 BEGIN
 vid := (select v_id from public.gc_alerts where a_id = id);
 vendor := (select v_name from public.gc_vendors where v_id = vid);

   RETURN vendor;
   END;
 $BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;
 ALTER FUNCTION sql_with_rows11(integer)
   OWNER TO postgres;

Я знаю, что могу объединить это в один запрос, но это скорее практическое упражнение. Это нормально работает, и я получаю имя поставщика. Однако теперь мне нужно вернуть более одного столбца из таблицы gc_vendors.

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

--------------РЕДАКТИРОВАТЬ

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

 CREATE OR REPLACE FUNCTION sql_with_rows14(IN v_uid character varying, IN lid           integer) 
 RETURNS table (aid int, aname character varying) AS $BODY$
 declare aid integer;
 declare aname character varying;
 BEGIN
 sql_with_rows14.aid := (select a_id from public.gc_alerts where v_id =           sql_with_rows14.v_uid);
 sql_with_rows14.aname := (select a_name from public.gc_alerts where a_id =      sql_with_rows14.aid);

   RETURN;
   END;
 $BODY$
   LANGUAGE plpgsql VOLATILE

Я также пробовал это с RETURN NEXT, но результаты те же.

Когда я запрашиваю его, если запрос возвращает только одну строку, он работает нормально. Однако это не работает для нескольких строк. Я тоже пробовал что-то подобное, с теми же результатами. Ребята, вы можете снова помочь?

Пробовал и это: BEGIN sql_with_rows14.aid: = (выберите a_id из public.gc_alerts); sql_with_rows14.aname: = (выберите a_name из public.gc_alerts);

   RETURN NEXT;
   END;

postgresql function user-defined-functions plpgsql postgresql-9.2
person nick_v1    schedule 23.08.2013    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Мне нужно вернуть более одного столбца из таблицы gc_vendors

Чтобы вернуть одну строку с несколькими столбцами (в отличие от набора строк), вы можете использовать:

RETURNS row_type

.. где row_type - предопределенный составной тип (например, имя таблицы, которое служит таковым автоматически). Или:

RETURNS record

в сочетании с OUT параметрами. Имейте в виду, что параметры OUT видны в теле почти везде, и избегайте конфликты имен.
Используя второй вариант, ваша функция может выглядеть так:

CREATE OR REPLACE FUNCTION sql_with_columns(
      IN  _id integer   -- the IN key word is just noise
    , OUT vid integer
    , OUT vendor text
    ) RETURNS record AS
$func$
BEGIN

SELECT INTO vid  v_id
FROM   public.gc_alerts
WHERE  a_id = id;

SELECT INTO vendor  v_name
FROM   public.gc_vendors
WHERE  v_id = vid;

RETURN;     -- just noise, since OUT parameters are returned automatically

END
$func$ LANGUAGE plpgsql

Как вы упомянули, вам следует объединить оба запроса в один или даже вместо этого использовать простой оператор SQL. Это просто витрина. Все подробности есть в прекрасном руководстве.

Вы можете также использовать

RETURNS TABLE (...)

Or:

RETURNS SETOF row_type

Это позволит вам вернуть набор строк (0, 1 или несколько). Но это не ваш вопрос.

Чтобы получить отдельные столбцы вместо представления записи, вызовите такую ​​функцию с помощью:

SELECT * FROM sql_with_columns(...)

Здесь много примеров SO, попробуйте выполнить поиск с помощью plpgsql и несколько ключевых слов.

При работе с plpgsql также прочтите главу о как вернуться из функции в руководстве.

person Erwin Brandstetter    schedule 23.08.2013
comment
Я считаю окончательный ВОЗВРАТ; можно опустить в функции sql_with_columns. - person bma; 23.08.2013
comment
@bma: Мой комментарий говорит об этом, не так ли? Павел (основной участник plpgsql) предпочитает явное RETURN, даже если оно не требуется, как в примере. - person Erwin Brandstetter; 23.08.2013
comment
Оно делает. Я не должен был упоминать об этом, из вашего шумного комментария было очевидно, что это бесполезно. Прости. - person bma; 23.08.2013

arrow_upward
1
arrow_downward

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

select
    v.v_name, v.* -- or any other columns from gc_alerts or gc_vendors
from public.gc_alerts as a
    inner join public.gc_vendors as v on v.v_id = a.vid
where a.a_id = <your id here>

если вы хотите, чтобы ваша функция возвращала строки, вы можете объявить это как

CREATE OR REPLACE FUNCTION sql_with_rows11(id integer)
RETURNS table(vendor text, v_id int)
as
$$
    select
        v.v_name, v.v_id
    from public.gc_alerts as a
        inner join public.gc_vendors as v on v.v_id = a.vid
    where a.a_id = id
$$ language SQL;

или функция plpgsql:

CREATE OR REPLACE FUNCTION sql_with_rows11(id integer)
RETURNS table(vendor text, vid int)
AS
$$
    declare vid integer;
    declare vendor character varying;
BEGIN
    sql_with_rows11.vid := 1; -- prefix with function name because otherwise it would be declared variables
    sql_with_rows11.vendor := 4;
    return next; 

    sql_with_rows11.vid := 5;
    sql_with_rows11.vendor := 8;
    return next;
END;
$$ LANGUAGE plpgsql;

демонстрация скрипта sql для скрипки с участием :)

person Roman Pekar    schedule 23.08.2013
comment
Привет, Роман, твой ответ очень полезен. Я пытался написать что-то подобное, но у меня проблема с возвратом нескольких строк. У меня есть следующие - person nick_v1; 24.08.2013
comment
@ user2630270 после чего? - person Roman Pekar; 24.08.2013