How Can I Efficiently Return Query Results from PostgreSQL Functions?

Return query results in PostgreSQL function

PostgreSQL provides two methods for returning query results from functions: OUT parameters and RETURNS TABLE. When using the OUT parameter, the function signature specifies the list of variables that receive the query value. However, this approach prohibits explicit return type definitions and may lead to column name conflicts.

USE RETURNS TABLE

For a more convenient and powerful solution, consider using RETURNS TABLE. This syntax defines the exact return pattern of a function, allowing explicit column names and type definitions.

<code class="language-sql">CREATE OR REPLACE FUNCTION word_frequency(_max_tokens int)
  RETURNS TABLE (txt   text   -- 在函数体中也可见为 OUT 参数
               , cnt   bigint
               , ratio bigint)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY
   SELECT t.txt
        , count(*) AS cnt                 -- 列别名仅在此查询中可见
        , (count(*) * 100) / _max_tokens  -- 我添加了括号
   FROM  (
      SELECT t.txt
      FROM   token t
      WHERE  t.chartype = 'ALPHABETIC'
      LIMIT  _max_tokens
      ) t
   GROUP  BY t.txt
   ORDER  BY cnt DESC;                    -- 潜在的歧义
END
$func$;

SELECT * FROM word_frequency(123);</code>

Note:

• Qualify the column names of OUT parameters using table qualifiers to avoid conflicts.
• Choose OUT parameters and column aliases carefully to prevent ambiguity.
• Avoid using "text" or "count" as column names.
• Consider using numeric data types for more accurate integer division.

An alternative to using window functions

If you need to calculate the relative share of each token, consider using a window function in your query:

<code class="language-sql">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$;</code>

This query uses the over() clause to calculate the relative share of each token.

The above is the detailed content of How Can I Efficiently Return Query Results from PostgreSQL Functions?. For more information, please follow other related articles on the PHP Chinese website!