[SOLVED] Declaring a cursor in plpgsql fails

Declaring a cursor in plpgsql fails

I wrote a db function for plpgsql (postgre) in supabase.

When declaring a cursor I always get ERROR: 42P01: relation "cursor1" does not exist.

This is how I declare it:

DECLARE
    cursor1 CURSOR FOR 
        SELECT public.spending_cap.cap, public.spending_cap.spent, public.spending_cap.image_cap, public.spending_cap.image_spent, public.spending_cap.id, public.spending_cap.created_by FROM public.spending_cap, public.user_profile WHERE is_parent = true AND public.user_profile.id = public.spending_cap.created_by AND organisation is NULL;
    entry cursor1%rowtype;

I am just declaring cursor1 how can it say that the relation does not exist? If I just execute the SELECT statement, I get results. I use this declared cursor to loop over the same SELECT statement.

Any hints on what I am doing wrong?

Solution

PL/pgSQL doesn't allow %ROWTYPE on cursor variables. The engine of PL/pgSQL is very different from Oracle and it is just out of concept of PL/pgSQL. Probably should not be hard to implement this feature, but it is out of concept. You should to use RECORD type instead:

DECLARE
    cursor1 CURSOR FOR 
        SELECT public.spending_cap.cap, public.spending_cap.spent, public.spending_cap.image_cap, public.spending_cap.image_spent, public.spending_cap.id, public.spending_cap.created_by FROM public.spending_cap, public.user_profile WHERE is_parent = true AND public.user_profile.id = public.spending_cap.created_by AND organisation is NULL;
    entry record;

Try to read related documentation. Although the syntax is similar sometimes, in details there are lot of differences between PL/pgSQL cursors and PL/SQL cursors.