How to Return the Result of a SELECT Query within a PostgreSQL Function?

Returning Data from a PostgreSQL SELECT Query within a Function

This guide addresses the common challenge of returning the results of a SELECT query within a PostgreSQL function. The key is understanding how to correctly define the return type and use appropriate syntax.

The RETURN QUERY Solution:

The most effective method is using the RETURN QUERY command. This clearly defines the returned columns and their data types. Consider this example:

<code class="language-sql">CREATE OR REPLACE FUNCTION word_frequency(_max_tokens INT)
RETURNS TABLE (txt TEXT, cnt BIGINT, ratio NUMERIC)
AS $$
BEGIN
   RETURN QUERY
   SELECT t.txt, count(*) AS cnt, (count(*)::NUMERIC * 100) / _max_tokens AS ratio
   FROM (
      SELECT txt
      FROM token
      WHERE chartype = 'ALPHABETIC'
      LIMIT _max_tokens
   ) t
   GROUP BY t.txt
   ORDER BY cnt DESC;
END;
$$ LANGUAGE plpgsql;</code>

This function's return type is explicitly a table with txt, cnt, and ratio columns. Note the use of ::NUMERIC for explicit type casting to ensure accurate ratio calculation. The absence of a separate RETURN statement is intentional; RETURN QUERY handles the return value.

Careful Naming:

Avoid naming conflicts between output parameters and query column names. Using table qualification (e.g., t.txt) prevents ambiguity.

Alternative Approach for Relative Share Calculation:

For calculating the relative share of each token, an alternative using window functions offers improved efficiency:

<code class="language-sql">CREATE OR REPLACE FUNCTION word_frequency(_max_tokens INT)
RETURNS TABLE (txt TEXT, abs_cnt BIGINT, relative_share NUMERIC)
AS $$
BEGIN
   RETURN QUERY
   SELECT txt, cnt, ROUND((cnt::NUMERIC * 100) / SUM(cnt) OVER (), 2) AS relative_share
   FROM (
      SELECT txt, COUNT(*) AS cnt
      FROM token
      WHERE chartype = 'ALPHABETIC'
      GROUP BY txt
      ORDER BY cnt DESC
      LIMIT _max_tokens
   ) t
   ORDER BY cnt DESC;
END;
$$ LANGUAGE plpgsql;</code>

This version uses SUM(cnt) OVER () to calculate the total count across all rows, providing a more concise relative share calculation.

Important Note:

Explicit RETURN statements are unnecessary when using RETURNS TABLE or output parameters. The function's return behavior is defined by the RETURNS clause and the query itself.

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