Как вернуть результат из функции в PostgreSQL?

у меня есть эта функция в PostgreSQL, но я не знаю как вернуть результат запроса:

CREATE OR REPLACE FUNCTION wordFrequency(maxTokens INTEGER)
  RETURNS SETOF RECORD AS
$$
BEGIN
    SELECT text, count(*), 100 / maxTokens * count(*)
    FROM (
        SELECT text
    FROM token
    WHERE chartype = 'ALPHABETIC'
    LIMIT maxTokens
    ) as tokens
    GROUP BY text
    ORDER BY count DESC
END
$$
LANGUAGE plpgsql;

но я не знаю, как вернуть результат запроса внутри функции PostgreSQL.

я обнаружил, что возвращаемый тип должен быть SETOF RECORD, да? Но команда возврата не является правильной.

Как правильно это сделать?

1 60
sql postgresql plpgsql return trailing-return-type

1 ответ:

использовать RETURN QUERY:

CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
  RETURNS TABLE (
    txt   text   -- visible as OUT parameter inside and outside function
  , cnt   bigint
  , ratio bigint) AS
$func$
BEGIN
   RETURN QUERY
   SELECT t.txt
        , count(*) AS cnt  -- column alias only visible inside
        , (count(*) * 100) / _max_tokens  -- I added brackets
   FROM  (
      SELECT t.txt
      FROM   token t
      WHERE  t.chartype = 'ALPHABETIC'
      LIMIT  _max_tokens
      ) t
   GROUP  BY t.txt
   ORDER  BY cnt DESC;  -- note the potential ambiguity 
END
$func$  LANGUAGE plpgsql;

звоните:

SELECT * FROM word_frequency(123);

объяснение:

  • это много более практично явно определить тип возвращаемого значения, чем просто объявить его как запись. Таким образом, вам не нужно предоставлять список определений столбцов с каждым вызовом функции. RETURNS TABLE это один из способов сделать это. Есть и другие. Типы данных OUT параметры должны точно соответствовать тому, что возвращается запросом.

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

    но обратите внимание на потенциал имя между OUT параметр cnt и псевдоним столбца с тем же именем. В данном конкретном случае (RETURN QUERY SELECT ...) Postgres использует псевдоним столбца над OUT параметр в любом случае. Однако это может быть неоднозначно в других контекстах. Существуют различные способы, чтобы избежать какой-либо путаницы:

    1. используйте порядковый номер элемента в списке выбора:ORDER BY 2 DESC. Образец:
    2. повторить выражение ORDER BY count(*).
    3. (не применимо здесь.) Установите конфигурацию параметр plpgsql.variable_conflict или используйте специальную команду #variable_conflict error | use_variable | use_column на функции. Образец:

  • не используйте "текст" и "количество" в качестве имен столбцов. Оба являются законными для использования в Postgres, но "count" - это зарезервированное слово в стандартном SQL и имя базовой функции и "текст" является основным типом данных. Может привести к запутанным ошибкам. Я использую txt и cnt в моих примерах.

  • добавлены недостающие ; и исправлена синтаксическая ошибка в заголовке. (_max_tokens int), а не (int maxTokens) -тип после имя.

  • при работе с целочисленным делением лучше сначала умножить, а потом разделить, чтобы минимизировать ошибку округления. Еще лучше: работайте с numeric (или с плавающей точкой). Видеть под.

альтернатива

вот что я думаю ваш запрос должен на самом деле выглядеть так (вычисление a относительная доля на маркер):

CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
  RETURNS TABLE (
    txt            text
  , abs_cnt        bigint
  , relative_share numeric) AS
$func$
BEGIN
   RETURN QUERY
   SELECT t.txt
        , t.cnt
        , round((t.cnt * 100) / (sum(t.cnt) OVER ()), 2)  -- AS relative_share
   FROM  (
      SELECT t.txt
           , count(*) AS cnt
      FROM   token t
      WHERE  t.chartype = 'ALPHABETIC'
      GROUP  BY t.txt
      ORDER  BY cnt DESC
      LIMIT  _max_tokens
      ) t
   ORDER  BY t.cnt DESC;
END
$func$  LANGUAGE plpgsql;

выражение sum(t.cnt) OVER () это функция окна. Ты мог бы использовать CTE вместо подзапроса-довольно, но подзапрос, как правило, дешевле в простых случаях, как этот.

окончательный явно RETURN заявление не требуются (но разрешено) при работе с OUT параметры или RETURNS TABLE (что делает неявное использование OUT параметры).

round() С двумя параметрами работает только для numeric типы. count() в подзапросе выдает bigint и sum() этой bigint производит numeric результат, таким образом, мы имеем дело с numeric количество автоматически и все просто встает на свои места.

80