Records

-- Create a table and insert some data into the table.
gaussdb=# create table emp_rec(
gaussdb(#     empno numeric(4,0) not null,
gaussdb(#     ename varchar(10)
gaussdb(# );
CREATE TABLE
gaussdb=# insert into emp_rec values(111, 'aaa'), (222, 'bbb'), (333, 'ccc');
INSERT 0 3
-- The table 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) |

-- Perform record operations in the function.
gaussdb=# CREATE OR REPLACE FUNCTION regress_record(p_w VARCHAR2) RETURNS VARCHAR2 AS $$
gaussdb$# DECLARE
gaussdb$#     -- Declare a record type.
gaussdb$#     type rec_type is record (name  varchar2(100), epno int);
gaussdb$#     employer rec_type;
gaussdb$#     -- Use %type to declare the record type.
gaussdb$#     type rec_type1 is record (name  emp_rec.ename%type, epno int :=10);
gaussdb$#     employer1 rec_type1;
gaussdb$#     -- Declare a record type with the default value.
gaussdb$#     type rec_type2 is record (
gaussdb$#         name varchar2 not null := 'SCOTT',
gaussdb$#         epno int not null :=10
gaussdb$#     );
gaussdb$#     employer2 rec_type2;
gaussdb$#     CURSOR C1 IS  select ename,empno from emp_rec order by 1 limit 1;
gaussdb$# BEGIN
gaussdb$#     -- Assign a value to a member record variable.
gaussdb$#     employer.name := 'WARD';
gaussdb$#     employer.epno = 18;
gaussdb$#     raise info 'employer name: % , epno:%', employer.name, employer.epno;
gaussdb$# 
gaussdb$#     -- Assign the value of a record variable to another variable.
gaussdb$#     employer1 := employer;
gaussdb$#     raise info 'employer1 name: % , epno: %',employer1.name, employer1.epno;
gaussdb$# 
gaussdb$#     -- Assign the NULL value to a record variable.
gaussdb$#     employer1 := NULL;
gaussdb$#     raise info 'employer1 name: % , epno: %',employer1.name, employer1.epno;
gaussdb$# 
gaussdb$#     -- Obtain the default value of a record variable.
gaussdb$#     raise info 'employer2 name: % ,epno: %', employer2.name, employer2.epno;
gaussdb$# 
gaussdb$#     -- Use a record variable in the FOR loop.
gaussdb$#     for employer in select ename,empno from emp_rec order by 1 limit 1 loop 
gaussdb$#         raise info 'employer name: % , epno: %', employer.name, employer.epno;
gaussdb$#     end loop;
gaussdb$# 
gaussdb$#     -- Use a record variable in the SELECT INTO statement.
gaussdb$#     select ename,empno  into employer2 from emp_rec order by 1 limit 1;
gaussdb$#     raise info 'employer name: % , epno: %', employer2.name, employer2.epno;
gaussdb$# 
gaussdb$#     -- Use a record variable in a cursor.
gaussdb$#     OPEN C1;
gaussdb$#     FETCH C1 INTO employer2;
gaussdb$#     raise info 'employer name: % , epno: %', employer2.name, employer2.epno;
gaussdb$#     CLOSE C1;
gaussdb$#     RETURN employer.name;
gaussdb$# END; $$
gaussdb-# LANGUAGE plpgsql;
CREATE FUNCTION

-- Call this function.
gaussdb=# CALL regress_record('abc');
INFO:  employer name: WARD , epno:18
INFO:  employer1 name: WARD , epno: 18
INFO:  employer1 name: <NULL> , epno: <NULL>
INFO:  employer2 name: SCOTT ,epno: 10
INFO:  employer name: aaa , epno: 111
INFO:  employer name: aaa , epno: 111
INFO:  employer name: aaa , epno: 111
 regress_record 
----------------
 aaa
(1 row)
-- Delete the function.
gaussdb=# DROP FUNCTION regress_record;
DROP FUNCTION
-- Delete the table.
gaussdb=# DROP TABLE emp_rec;
DROP TABLE

-- 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.
gaussdb=# create type rec_type is (c1 int, c2 int);
CREATE TYPE
gaussdb=# set behavior_compat_options = 'proc_outparam_override';
SET
gaussdb=# create or replace function func(a in int) return rec_type is
gaussdb$#     r rec_type;
gaussdb$# begin
gaussdb$#     r.c1:=1;
gaussdb$#     r.c2:=1;
gaussdb$#     return r;
gaussdb$# end;
gaussdb$# /
CREATE FUNCTION
gaussdb=# call func(0);
 c1 | c2 
----+----
  1 |  1
(1 row)
gaussdb=# drop function func;
DROP FUNCTION
gaussdb=# drop type rec_type;
DROP TYPE
-- 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
gaussdb=# set behavior_compat_options = 'proc_outparam_override';
SET
gaussdb=# create or replace function func(a out int) return record is
gaussdb$#     type rc is record(c1 int, c2 int);
gaussdb$#     r rc;
gaussdb$# begin
gaussdb$#     r.c1 := 1;
gaussdb$#     r.c2 := 1;
gaussdb$#     a := 999;
gaussdb$#     return r;
gaussdb$# end;
gaussdb$# /
CREATE FUNCTION
gaussdb=# call func(1);
 func  |  a  
-------+-----
 (1,1) | 999
(1 row)
gaussdb=# drop function func;
DROP FUNCTION

-- Create an A-compatible database and switch to this database.
gaussdb=#  CREATE DATABASE ora_compatible_db DBCOMPATIBILITY 'A';
CREATE DATABASE
gaussdb=#  \c ora_compatible_db;
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "ora_compatible_db" as user "omm".
-- Define the record in the pkg. Use => to assign values to all input parameters of the record constructor.
ora_compatible_db=# 
CREATE or REPLACE package PA_TEST1
AS
 PROCEDURE P_DISPLAY1(a NUMBER, b  VARCHAR);
 TYPE t1 IS record(va int,vb int);
 v1 t1;
END PA_TEST1;
/
ora_compatible_db=# CREATE or REPLACE package body PA_TEST1
AS
  PROCEDURE P_DISPLAY1(a NUMBER, b  VARCHAR) AS
 BEGIN
  v1 := t1(va => 1, vb => 2);
  dbe_output.put_line('pkg test1');
 END;
END PA_TEST1;
/

ora_compatible_db=#  CALL PA_TEST1.P_DISPLAY1(b => 'm', a => 1);
 p_display1 
------------

(1 row)
-- Define the record in the pkg. The input parameter values of the record constructor are not consecutively used =>, and an error message is displayed.
ora_compatible_db=# 
CREATE or REPLACE package PA_TEST1
AS
 PROCEDURE P_DISPLAY1(a NUMBER, b  VARCHAR);
 TYPE t1 IS record(va int,vb int);
 v1 t1;
END PA_TEST1;
/
CREATE or REPLACE package body PA_TEST1
AS
  PROCEDURE P_DISPLAY1(a NUMBER, b  VARCHAR) AS
 BEGIN
  v1 := t1(va => 1, 2);
  dbe_output.put_line('pkg test1');
 END;
END PA_TEST1;
/
ora_compatible_db=# CALL PA_TEST1.P_DISPLAY1(b => 'm', a => 1);
ERROR:  positional argument cannot follow named argument
LINE 1: SELECT "17347.t1"(va => 1, 2)
                                   ^
QUERY:  SELECT "17347.t1"(va => 1, 2)
CONTEXT:  referenced column: 17347.t1
PL/pgSQL function p_display1(numeric,character varying) line 2 at assignment
-- Delete the created database and switch back to the original database.
ora_compatible_db=# \c postgres;
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "postgres" as user "omm".
gaussdb=# DROP DATABASE ora_compatible_db;
DROP DATABASE