How to Efficiently Return Result Sets in PostgreSQL Functions?

Returning Result Sets in PostgreSQL Functions

When writing functions in PostgreSQL, it's often necessary to return result sets. The provided function, wordFrequency, is correctly defined with a SETOF RECORD return type. However, the current implementation lacks the appropriate command to return the query's result.

RETURN QUERY

The solution lies in utilizing the RETURN QUERY command, as demonstrated in the following corrected function:

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

Using RETURN TABLE

Defining the return type explicitly using RETURNS TABLE offers several advantages:

• Eliminates the need for a column definition list with each function call.
• Enables specifying the data types of OUT parameters to match the query's return values.

Naming Considerations

Be cautious when naming OUT parameters, as they are visible throughout the function body. To avoid conflicts or confusion, either fully qualify table columns with the same name or follow the suggested naming best practices.

Alternatives

The alternative query provided calculates the 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$;

This version employs a window function and ensures a numeric result for accurate relative share calculation.

The above is the detailed content of How to Efficiently Return Result Sets in PostgreSQL Functions?. For more information, please follow other related articles on the PHP Chinese website!