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_id which do not have corresponding user_id 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:

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 · Accepted Answer · 2023-12-06 07:18:17Z

You cannot RETURN from a DO statement at all. You would have to CREATE FUNCTION instead. Possibly a temporary function, see:
- Is there such thing as a "temp function"?
- How to create a temporary function in PostgreSQL?
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.

Super Kai - Kazuya Ito · Accepted Answer · 2023-12-09 07:28:40Z

I got the same error below:

ERROR: RETURN cannot have a parameter in function returning void

Because the types of RETURNS VOID and RETURN 2; (INTEGER) are not the same in a PL/pgSQL function as shown below:

CREATE FUNCTION my_func() RETURNS VOID AS $$
BEGIN                           -- ↑ Here
RETURN 2; -- INTEGER
END;
$$ LANGUAGE plpgsql;

So, I set RETURNS INTEGER as shown below, then the error was solvd:

CREATE FUNCTION my_func() RETURNS INTEGER AS $$
BEGIN                             -- ↑ Here
RETURN 2; -- INTEGER
END;
$$ LANGUAGE plpgsql;

Or, I returned nothing with or without RETURN; as shown below, then the error was solvd:

CREATE FUNCTION my_func() RETURNS VOID AS $$
BEGIN                           -- ↑ Here
RETURN; -- Nothing
END;
$$ LANGUAGE plpgsql;

Or:

CREATE FUNCTION my_func() RETURNS VOID AS $$
BEGIN                           -- ↑ Here
-- RETURN; -- Nothing
END;
$$ LANGUAGE plpgsql;

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related