plpgsql Error: RETURN cannot have a parameter in function returning void
database, for-loop, plpgsql, postgresql, sql
Solution
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.
Problem
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?