How to Return Query Results from a PostgreSQL Function?

Return query results in PostgreSQL function

PostgreSQL uses the RETURN QUERY statement to return query results from within a function. This method is particularly useful when you need the query results as part of the function output.

Example

Consider the following function:

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

This function attempts to return query results, but is missing a crucial part: the RETURN QUERY statement.

Solution

To return query results, use the RETURN QUERY statement as follows:

<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$;</code>

Execute

To execute this function, you can use the following statement:

<code class="language-sql">SELECT * FROM word_frequency(123);</code>

This will return a table containing token the text, count and ratio for each word in the table.

Notes

• Use OUT parameters: The OUT parameters defined in the function header must exactly match the columns returned by the query.
• Name OUT parameters carefully: Use unambiguous names to avoid potential naming conflicts. Avoid using reserved words or primitive data types.
• Use parentheses for integer division: When dividing integers, enclose the division operation in parentheses to minimize rounding errors.
• Consider numeric data types: For operations involving floating point numbers or percentages, using numeric data types provides greater precision.

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