plpgsql Error: RETURN cannot have a parameter in function returning void

Question

I am trying to extract the count of records corresponding to a specific date and user_ids which do not have corresponding user_ids for the next later date in the database. This is the way I am trying to accomplish it (using plpgsql but not defining a function:

    DO
    $BODY$
    DECLARE
        a date[]:= array(select distinct start_of_period from monthly_rankings where balance_type=2);
        res int[] = '{}';
    BEGIN
        FOR i IN array_lower(a,1) .. array_upper(a,1)-1
        LOOP
            res:=array_append(res,'SELECT COUNT(user_id) from (select user_id from monthly_rankings where start_of_period=a[i] except select user_id from monthly_rankings where start_of_period=a[i+1]) as b');
                    i:=i+1;
            END LOOP;
            RETURN res;
        $BODY$ language plpgsql

I get an Error: could not Retrieve the result : ERROR: RETURN cannot have a parameter in function returning void LINE 11: RETURN res; I am new to this procedural language and cannot spot why the function is returning void. I do assign the values to variables , and I declared empty - not NULL - arrays. Is there a syntax or a more significant reasoning mistake?

Erwin Brandstetter · Answer 1 · 2023-06-13T22:04:33.407

1.) You cannot RETURN from a DO statement at all. You would have to CREATE FUNCTION instead.

2.) You don't need any of this. Use this query, which will be faster by an order of magnitude:

WITH cte AS (
   SELECT DISTINCT start_of_period
        , rank() OVER (ORDER BY start_of_period) AS rn
   FROM   monthly_rankings
   WHERE  balance_type = 2
   )
SELECT c.start_of_period, count(*) AS user_ct
FROM   cte c
JOIN   monthly_rankings m USING (start_of_period)
WHERE  NOT EXISTS (
   SELECT FROM cte c1
   JOIN   monthly_rankings m1 USING (start_of_period)
   WHERE  c1.rn = c.rn + 1
-- AND    m1.balance_type = 2 -- only with matching criteria?
   AND    m1.user_id = m.user_id
   )
-- AND balance_type = 2  -- all user_id from these dates?
GROUP  BY c.start_of_period
ORDER  BY c.start_of_period;

This includes the last qualifying start_of_period. You may want to exclude it like in your PL/pgSQL code.

plpgsql Error: RETURN cannot have a parameter in function returning void

1 Answers1