PostgreSQL : syntax error in while using variable

Question

I am trying to find cumulative sum of a column. Know that, window function can achieve this, however I want to achieve this using variables. This is my code.

DECLARE csum INTEGER := 0;
SELECT 
    employee_id,
    department_id,
    boss_id,
    name,
    salary,
    (csum := csum + salary) AS cum_salary
FROM employees

I get the below error message. How do i fix this ?

org.postgresql.util.PSQLException: ERROR: syntax error at or near "INTEGER"
  Position: 14
DECLARE csum INTEGER := 0;
             ^
SELECT

EDIT : I was trying to achieve something similar I have done in MySQL. However, realised that PostgreSQL doesn't support this. Thanks.

onl if you use a function, there you can declare, while i don't see why — nbk, Sep 26 '22 at 18:11

score 0 · Accepted Answer · answered Sep 26 '22 at 17:47

0

No need for a hack with variables (which don't exist in standard SQL or Postgres to begin with).

This can easily be done using a window function. However a cumulative sum only makes sense if you also provide a sort order for the rows.

SELECT 
    employee_id,
    department_id,
    boss_id,
    name,
    salary,
    sum(salary) over (order by ????)
FROM employees

answered Sep 26 '22 at 17:47

I know of this method, I am trying to specifically achieve it with variables. – spb Sep 26 '22 at 17:50
4

@spb: then you should have written that in your question. But as I wrote: there is no such thing as variables in standard SQL or Postgres. The only other alternative is to implement this with a slow and extremely inefficient PL/pgSQL loop. But that is going to be a **lot** slower and much more complex. What's wrong with using window functions? All modern DBMS support them. – Sep 26 '22 at 17:54

PostgreSQL : syntax error in while using variable

1 Answers1