0

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
ilwl
  • 35
  • 1
  • 6
  • There is no trace of an *array* in your code example. You mean a *set*? Seems like you mingled terms ... – Erwin Brandstetter Jun 29 '22 at 17:48
  • @ErwinBrandstetter, wouldn't "`TYPE TYPE_EXAMPLE IS TABLE OF CUR_FILMS2%ROWTYPE;`" be a declaration of an array? It's called formally by collection in Oracle. – ilwl Jun 29 '22 at 18:03
  • That's a table variable (as implied by `TABLE OF `). There is no direct equivalent in PL/pgSQL. And there is no equivalent `TYPE` declaration in the `DECLARE` section either. Row types for table objects are defined implicitly - for immediate use. Just use the name of the table as type name. – Erwin Brandstetter Jun 29 '22 at 18:07

1 Answers1

1

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Tyvm for your answer, Erwin. Is it alright to declare the cursor inside the DECLARE clause and use it inside the `FOR` loop either? Just like `FOR _rec IN CURSOR_DEFINED_PREVIOUSLY(arguments) LOOP ... END LOOP;`. – ilwl Jun 29 '22 at 18:07
  • @ilwl: Yes, there is a [code example in the manual](https://www.postgresql.org/docs/current/plpgsql-cursors.html#PLPGSQL-CURSOR-FOR-LOOP). But, frankly, the cursor seems like needless overhead in this scenario. Place the query in the `FOR` loop directly like demonstrated. – Erwin Brandstetter Jun 29 '22 at 18:09