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

Laurenz Albe · Accepted Answer · 2021-09-29 20:24:35Z

2

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.

answered Sep 29, 2021 at 20:24

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shawn · Accepted Answer · 2021-09-29 20:21:01Z

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, 2021 at 20:21

shawn

1968 bronze badges

1 Comment

user330315 Over a year ago

You don't need a temp table for this

Collectives™ on Stack Overflow

How to select from a variable in Postgresql?

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related