☰

PostgreSQL の関数内で SELECT の結果を返すにはどうすればよいでしょうか? 質問する

tag-icon • tag-icon sql database postgresql plpgsql return-query
PostgreSQL の関数内で SELECT の結果を返すにはどうすればよいでしょうか? 質問する

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、 return コマンドが正しくありません。

これを実行する正しい方法は何ですか?

ベストアンサー1

使用RETURN QUERY:

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

電話：

SELECT * FROM word_frequency(123);

戻り値の型を明示的に定義することは、ジェネリックを返すよりもはるかに実用的ですrecord。この方法では、関数呼び出しごとに列定義リストを提供する必要はありません。RETURNS TABLEこれはその方法の 1 つです。他にも方法はあります。OUTパラメータのデータ型は、クエリによって返されるものと正確に一致する必要があります。

パラメータの名前はOUT慎重に選択してください。パラメータは関数本体のほぼすべての場所で表示されます。競合や予期しない結果を回避するために、同じ名前の列をテーブル修飾します。この例では、すべての列に対してこれを行いました。

しかし、潜在的な名前の衝突OUTパラメータcntと、同じ名前の列エイリアスの間には、次の関係があります。この特定のケース ( RETURN QUERY SELECT ...) では、Postgres はどちらの場合もパラメータよりも列エイリアスを使用しますOUT。ただし、他のコンテキストではあいまいになる可能性があります。混乱を避けるには、さまざまな方法があります。

  1. SELECT リスト内の項目の順序位置を使用します: ORDER BY 2 DESC。例:
  2. 表現を繰り返しますORDER BY count(*)。
  3. (Not required here.) Set the configuration parameter plpgsql.variable_conflict or use the special command #variable_conflict error | use_variable | use_column in the function. See:

Don't use "text" or "count" as column names. Both are legal to use in Postgres, but "count" is a reserved word in standard SQL and a basic function name and "text" is a basic data type. Can lead to confusing errors. I use txt and cnt in my examples, you may want more explicit names.

Added a missing ; and corrected a syntax error in the header. (_max_tokens int), not (int maxTokens) - data type after name.

While working with integer division, it's better to multiply first and divide later, to minimize the rounding error. Or work with numeric or a floating point type. See below.

Alternative

This is what I think your query should actually look like (calculating a relative share per token):

CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
  RETURNS TABLE (txt            text
               , abs_cnt        bigint
               , relative_share numeric)
  LANGUAGE plpgsql 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$;

The expression sum(t.cnt) OVER () is a window function. You could use a CTE instead of the subquery. Pretty, but a subquery is typically cheaper in simple cases like this one (mostly before Postgres 12).

A final explicit RETURN statement is not required (but allowed) when working with OUT parameters or RETURNS TABLE (which makes implicit use of OUT parameters).

round() with two parameters only works for numeric types. count() in the subquery produces a bigint result and a sum() over this bigint produces a numeric result, thus we deal with a numeric number automatically and everything just falls into place.

おすすめ記事