Alex Rivera | Logout

How to return result of a SELECT inside a function in PostgreSQL?

Asked 2011-10-30T15:47:53.953
174

I have this function in PostgreSQL, but I don't know how to return the result of the query:

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;

But I don't know how to return the result of the query inside the PostgreSQL function.

I found that the return type should be SETOF RECORD, right? But the return command is not right.

What is the right way to do this?

sql database postgresql plpgsql return-query
Edit
Report

1 Answer

259

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 c

answered 2011-10-30T15:50:43.813

Your Answer