Can i compare a column in a table to a variable of type cursor?

Question

I declared a cursor to iterate on my employee ids.

DECLARE
    r_employee employee.id%type;
c_employee CURSOR FOR
    SELECT
        distinct employee_id
    FROM
        employee;

Then i used this cursor to select fields in another table "branch" where the employee_id in "branch" table matches the cursor.

SELECT
    COUNT(*),
    employee_id
FROM
    branch
WHERE
    branch.employee_id = r_employee 

GROUP BY
    employee_id

the following error is appearing:

ERROR: column "r_employee" does not exist.

the above code is inside a loop that fetches r_employee from c_employee at each loop. Any clue how to fix this? (using postgresql)

Answer 1

NO you cannot. You must refer to a column within the cursor not to a cursor itself. A cursor contains the results of a query whether the query returns a single column or multiple columns. In this case your declaration of r_employee essentially is a record containing a single variable. But single column or multiple you must refer to the column name within the cursor, or the record you fetched the cursor into. So

SELECT
    COUNT(*),
    employee_id
FROM
    branch
WHERE
    branch.employee_id = r_employee.id   --<< reference the variable within the record >>

GROUP BY
    employee_id;