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.
Figure 1 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 openGauss:
- 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.
- The data type can be the record type, array type, or set type defined in a stored procedure (anonymous blocks are not supported).
Examples
The table definition used in the following is defined as follows:
openGauss=# \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.
openGauss=# 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;
-- Call this function.
openGauss=# CALL regress_record('abc');
-- Delete the function.
openGauss=# DROP FUNCTION regress_record;
Feedback