Records

The table definition used in the following is defined as follows:
gaussdb=# \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.
gaussdb=# 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.
gaussdb=# CALL regress_record('abc');

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

-- Example of the function expression return record (the compatibility parameter proc_outparam_override must be enabled)
set behavior_compat_options = 'proc_outparam_override';
create or replace package pkg is
type rec_type is record(c1 int, c2 int); 
function test1(col1 out int,col2 out int) 
return rec_type;
end pkg;
/
create or replace package body pkg 
as  
function test1(col1 out int, col2 out int) return rec_type 
is   
r rec_type;  
begin    
r.c1:=300;    
r.c2:=400;    
col1:=100;    
col2:=200;    
return r;  
end;
end pkg;
/
declare  
res pkg.rec_type:=pkg.rec_type();  
a int;  
b int;
begin  
res:=pkg.test1(a,b);  
raise info 'a: %, b: %',a,b;  
raise info '%', res;
end;
/

-- If the compatibility parameter proc_outparam_override is enabled, the data of the defined record type is returned. The out parameter is not required for the function.
create type rec_type is (c1 int, c2 int);
create or replace function func(a in int) return rec_type
as declare
r rec_type;
begin
r.c1:=1;
r.c2:=2;
return r;
end;
/
call func(0);

-- If the compatibility parameter proc_outparam_override is enabled, the data of the undefined record type is returned and at least one out parameter is required for the function
create or replace function func(a in int) return record
as declare
type rc is record(c1 int);
r rc;
begin
r.c1:=1;
a:=1;
return r;
end;
/
call func(1);