Postgres , handling multiple cursors for one query results

Question

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.

Cursor day_to_process: is listing all the date where the status equal 'S'
Cursor status_to_process : is getting the last status before 'S'

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 ;
    $$;

Laurenz Albe · Accepted Answer · 2020-01-21 13:25:23Z

1

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.

answered Jan 21, 2020 at 13:25

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

M.achaibou Over a year ago

I take off the second cursor and simply put the query in the update , then i tried to add parameters to the first cursor and put it as a record , and fetch it , it worked , but then i have a problem with the return void ( since it an update ) it says not volatile function

Laurenz Albe Over a year ago

You should open a new question for that, with the exact error message.

Laurenz Albe Over a year ago

If you want and you think it might be of value for others, do.

M.achaibou · Accepted Answer · 2020-01-22 11:05:15Z

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 ;
$$;

Collectives™ on Stack Overflow

Postgres , handling multiple cursors for one query results

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related