Arrays and Records

Arrays

Use of Array Types

Before the use of arrays, an array type needs to be defined:

Define an array type immediately after the AS keyword in a stored procedure. The method is as follows:

TYPE array_type IS VARRAY(size) OF data_type;

In the preceding information:

array_type: indicates the name of the array type to be defined.
VARRAY: indicates the array type to be defined.
size: indicates the maximum number of members in the array type to be defined. The value is a positive integer.
data_type: indicates the types of members in the array type to be created.

NOTE:

In MogDB, an array automatically increases. If an access violation occurs, a null value is returned, and no error message is reported.

The scope of an array type defined in a stored procedure takes effect only in this storage process.

It is recommended that you use one of the preceding methods to define an array type. If both methods are used to define the same array type, MogDB prefers the array type defined in a stored procedure to declare array variables.

data_type can also be a record type defined in a stored procedure (anonymous blocks are not supported), but cannot be an array or collection type defined in a stored procedure.

MogDB supports the access of contents in an array by using parentheses, and the extend, count, first, and last functions.

NOTE: If the stored procedure contains the DML statement (SELECT, UPDATE, INSERT, or DELETE), DML statements can access array elements only using brackets. In this way, it may be separated from the function expression area.

record

record Variables

Perform the following operations to create a record variable:

Define a record type and use this type to declare a variable.

Syntax

For the syntax of the record type, see [Figure 1](#Syntax of the record type).

Figure 1 Syntax of the record type

syntax-of-the-record-type

The above syntax diagram is explained as follows:

record_type: record name
field: record columns
datatype: record data type
expression: expression for setting a default value

NOTE: In MogDB:

When assigning values to record variables, you can:

Declare a record type and define member variables of this type when you declare a function or stored procedure.

Assign the value of a record variable to another record variable.

Use SELECT INTO or FETCH to assign values to a record type.

Assign the NULL value to a record variable.

The INSERT and UPDATE statements cannot use a record variable to insert or update data.

Just like a variable, a record column of the compound type does not have a default value in the declaration.

date_type can also be the record type, array type, and collection type defined in the stored procedure (anonymous blocks are not supported).

Example

The table used in the following example is defined as follows:
mogdb=# \d emp_rec
                Table "public.emp_rec"
  Column  |              Type              | Modifiers
----------+--------------------------------+-----------
 empno    | numeric(4,0)                   | not null
 ename    | character varying(10)          |
 job      | character varying(9)           |
 mgr      | numeric(4,0)                   |
 hiredate | timestamp(0) without time zone |
 sal      | numeric(7,2)                   |
 comm     | numeric(7,2)                   |
 deptno   | numeric(2,0)                   |

-- Perform array operations in the function.
mogdb=# CREATE OR REPLACE FUNCTION regress_record(p_w VARCHAR2)
RETURNS
VARCHAR2  AS $$
DECLARE

   -- Declare a record type.
   type rec_type is record (name  varchar2(100), epno int);
   employer rec_type;

   -- Use %type to declare the record type.
   type rec_type1 is record (name  emp_rec.ename%type, epno int not null :=10);
   employer1 rec_type1;

   -- Declare a record type with a default value.
   type rec_type2 is record (
         name varchar2 not null := 'SCOTT',
         epno int not null :=10);
    employer2 rec_type2;
    CURSOR C1 IS  select ename,empno from emp_rec order by 1 limit 1;

BEGIN
      -- Assign a value to a member record variable.
     employer.name := 'WARD';
     employer.epno = 18;
     raise info 'employer name: % , epno:%', employer.name, employer.epno;

      -- Assign the value of a record variable to another variable.
     employer1 := employer;
     raise info 'employer1 name: % , epno: %',employer1.name, employer1.epno;

      -- Assign the NULL value to a record variable.
     employer1 := NULL;
     raise info 'employer1 name: % , epno: %',employer1.name, employer1.epno;

      -- Obtain the default value of a record variable.
     raise info 'employer2 name: % ,epno: %', employer2.name, employer2.epno;

      -- Use a record variable in the FOR loop.
      for employer in select ename,empno from emp_rec order by 1  limit 1
          loop
               raise info 'employer name: % , epno: %', employer.name, employer.epno;
          end loop;

      -- Use a record variable in the SELECT INTO statement.
      select ename,empno  into employer2 from emp_rec order by 1 limit 1;
      raise info 'employer name: % , epno: %', employer2.name, employer2.epno;

      -- Use a record variable in a cursor.
      OPEN C1;
      FETCH C1 INTO employer2;
      raise info 'employer name: % , epno: %', employer2.name, employer2.epno;
      CLOSE C1;
      RETURN employer.name;
END;
$$
LANGUAGE plpgsql;

-- Invoke the function.
mogdb=# CALL regress_record('abc');

-- Delete the function.
mogdb=# DROP FUNCTION regress_record;

Issue