How to return result of a SELECT inside a function in PostgreSQL?
Use 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$;
Call:
SELECT * FROM word_frequency(123);
Defining the return type explicitly is much more practical than returning a generic record
. This way you don't have to provide a column definition list with every function call. RETURNS TABLE
is one way to do that. There are others. Data types of OUT
parameters have to match exactly what is returned by the query.
Choose names for OUT
parameters carefully. They are visible in the function body almost anywhere. Table-qualify columns of the same name to avoid conflicts or unexpected results. I did that for all columns in my example.
But note the potential naming conflict between the OUT
parameter cnt
and the column alias of the same name. In this particular case (RETURN QUERY SELECT ...
) Postgres uses the column alias over the OUT
parameter either way. This can be ambiguous in other contexts, though. There are various ways to avoid any confusion:
- Use the ordinal position of the item in the SELECT list:
ORDER BY 2 DESC
. Example:- Select first row in each GROUP BY group?
- Repeat the expression
ORDER BY count(*)
. - (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:- Naming conflict between function parameter and result of JOIN with USING clause
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.
Please see the following link for documentation:
https://www.postgresql.org/docs/current/xfunc-sql.html
Example:
CREATE FUNCTION sum_n_product_with_tab (x int)
RETURNS TABLE(sum int, product int) AS $$
SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;