CODEWITHSUNDEEP
postgresqlfunctionstored-procedurescursormultiple-cursor
M.achaibou
M.achaibou

Reputation: 101

Postgres , handling multiple cursors for one query results

How can i use two cursor , one based on the output of the other ? basically what i'm trying to get is replace all the status that equal 'S' with the previous values of status.

The error i'm getting is:

ERROR: missing FROM-clause entry for table "day_to_process" Where: PL/pgSQL function scrat.update_status() 

   create or replace function scrat.update_status() returns void
      language plpgsql
    as
    $$
    DECLARE
      day_to_process CURSOR FOR (SELECT distinct inst_status.status_date
                                 FROM scrat.inst_status
                                 WHERE inst_status.status = 'S'
                                 ORDER BY 1);

      status_to_process CURSOR for (select inst_status.status, max(inst_status.status_date)
                                    FROM scrat.inst_status
                                    where inst_status.status <> 'S'
                                      and inst_status.status_date < day_to_process.status_date
                                    group by status
                                    order by 2 desc
                                    limit 1);


      curr_date TEXT;
      curr_status TEXT;


    BEGIN
      OPEN day_to_process;
      OPEN status_to_process;

      LOOP
        FETCH day_to_process INTO curr_date;
        FETCH status_to_process INTO curr_status;

        update scrat.inst_status
        set inst_status.status = status_to_process.status
        where inst_status.status_date = curr_date;
      END LOOP;

    END ;
    $$;

Upvotes: 0

Views: 5032

Answers (2)

M.achaibou
M.achaibou

Reputation: 101

I post my solution here , maybe it will help someone.

since we can not use a cursor like a table name, I took off the second cursor , and simply put the query in the update 

create function scrat.update_status() returns void
  language plpgsql
as
$$
DECLARE
  day_to_process CURSOR FOR (SELECT distinct inst_status.status_date
                             FROM scrat.inst_status
                             WHERE inst_status.status  ='S'
                             ORDER BY 1);

  curr_date date;
BEGIN

  OPEN day_to_process;
  <<day>>
  LOOP
    FETCH day_to_process INTO curr_date;
    exit day
    when not found;

    raise notice 'Processing Date %', curr_date::text;
   update scrat.inst_status
    set status  = (select a.status  from
                                  (select status , max(status_date)
                                FROM scrat.inst_status
                                where status  <> 'S'
                                  and status_date::date < curr_date
                                group by status 
                                order by 2 desc
                                limit 1)a)
    where inst_status.status_date = curr_date;

  END LOOP;
close day_to_process;
END ;
$$;

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246523

You cannot use a cursor name like a table.

You first have to FETCH a result row into a record variable, then you can use that.

Your second cursor declaration won't work as a static cursor declaration, because the value fetched from the first cursor changes.

You should try to do without procedural code and write the whole thing in a single UPDATE statement.

Upvotes: 1

Related Questions