Is there a way to declare an array variable which accepts a cursor ROWTYPE as its datatype in Postgr-CodePudding

I am facing a problem in PL/pgSQL while trying to convert some procedures from Oracle to Postgres RDBMS. At the original procedure in PL/SQL, one of these procedures has declared within the DECLARE clause: a bounded cursor, a TYPE which is a TABLE OF ROWTYPE of that bounded cursor and a variable which takes the datatype of this predefined TYPE.

In a few words (as far as I've understood), a bounded cursor and an ARRAY of its type - meaning that each position of that array is actually a ROWTYPE of that cursor - are declared. I have tried to research ways to get around this in Postgres but no success, yet.

This isn't the original I am trying to migrate from Oracle to Postgres, but I think it clarifies what I am trying to reach:

CREATE OR REPLACE PROCEDURE schema.procedure_name (arguments)
AS
$BODY$
  DECLARE
    CUR_FILMS2 CURSOR (year INTEGER) for 
      SELECT * 
      FROM film 
      WHERE release_year = year;
    TYPE TYPE_EXAMPLE IS TABLE OF CUR_FILMS2%ROWTYPE;
    V_TYPE_EXAMPLE    TYPE_EXAMPLE;
    ...

  BEGIN
    OPEN  CUR_FILMS2(2022);
    FETCH CUR_FILMS2 BULK COLLECT INTO V_TYPE_EXAMPLE;
    CLOSE CUR_FILMS2;
    ...

    -- Later on, that variable is iterated to execute some code beneath.
    FOR I IN 1 .. V_TYPE_EXAMPLE.COUNT LOOP
        ...
    END LOOP;
  END;
$BODY$

I have already thought about the RECORD datatype using, but it only accepts one record per time, and as far as I know there isn't a sort of TABLE variable in Postgres. The Postgres version I am using is PostgreSQL 14.3.

What is the easiest way to get around this problem? Is there a way?

CodePudding user response：

Postgres does not have table variables - and no BULK COLLECT for cursors in PL/pgSQL. You might work with temporary tables.

Seems like you can replace all the overhead with a plain FOR loop (and its implicit cursor) using a generic record variable for the row type:

CREATE OR REPLACE PROCEDURE schema.procedure_name (_year int) AS
$func$
DECLARE
   _rec record;
BEGIN
   FOR _rec IN
      SELECT * 
      FROM film 
      WHERE release_year = _year
   LOOP
       ...
   END LOOP;
END
$func$;

See: