How to select from a variable in Postgresql?

Question

I'm creating a function and I need to get the largest and smallest results from an average score calculation. So far it looks like this:

CREATE OR REPLACE FUNCTION public.my_func()
    RETURNS NUMERIC
    LANGUAGE plpgsql
    AS $function$
DECLARE
    all_user_average_scores record;
    max_avg numeric;
    min_avg numeric;
BEGIN
    
        SELECT user_uuid, AVG(score) as avg_score
            INTO all_user_average_scores
            FROM user_scores
            GROUP BY user_uuid;

    SELECT max(avg_score) INTO max_raw FROM all_user_average_scores;
    SELECT min(avg_score) INTO min_raw FROM all_user_average_scores;

    -- Do some more stuff here

    RETURN final_result;
END;
$function$;

But I'm getting this error:

ERROR:  relation "all_user_average_scores" does not exist

Any ideas? I've tried the := syntax as well and I get the same result

score 2 · Answer 1 · answered Sep 29 '21 at 20:24

As you have seen, you cannot use a variable like a table. Moreover, SELECT ... INTO will only store the first row of the result set in the variable.

In your case, you don't need a variable at all:

SELECT max(avg_score), min(avg_score) INTO max_raw, min_raw
FROM (SELECT AVG(score) as avg_score
      FROM user_scores
      GROUP BY user_uuid) AS q;

Alternatively, you could use a CTE:

WITH q AS (
   SELECT AVG(score) as avg_score
   FROM user_scores
   GROUP BY user_uuid
)
SELECT max(avg_score), min(avg_score) INTO max_raw, min_raw
FROM q;

For more complicated cases, a temporary table might be the best solution.

score 0 · Accepted Answer · answered Sep 29 '21 at 20:21

0

It turns out you can't save a table in a variable. The solution is to use a temporary table as described in this post

answered Sep 29 '21 at 20:21

shawn

176
6

You don't need a temp table for this – Sep 29 '21 at 21:02

How to select from a variable in Postgresql?

2 Answers2