支持OBJECT TYPE功能
atlasdb=# create type demo_typ1 as object (
atlasdb(# a int,
atlasdb(# b int,
atlasdb(# typemember function get_a() return int,
atlasdb(# typemember function get_b() return int,
atlasdb(# typemember procedure reset_value(),
atlasdb(# constructor function demo_typ1(a int, b int) return self as result
atlasdb(# );
CREATE TYPE
atlasdb=# create type body demo_typ1 as $$
atlasdb$# constructor function demo_typ1(a int, b int) return self as result as $demo$
atlasdb$# begin
atlasdb$# self.a = a;
atlasdb$# self.b = b;
atlasdb$# return self;
atlasdb$# end $demo$;
atlasdb$# typemember procedure reset_value() as $reset$
atlasdb$# begin
atlasdb$# self.a = 0;
atlasdb$# self.b = 0;
atlasdb$# end $reset$;
atlasdb$# typemember function get_a() return int as $id$
atlasdb$# begin
atlasdb$# return self.a;
atlasdb$# end $id$;
atlasdb$#
atlasdb$# typemember function get_b() return int as $name$
atlasdb$# begin
atlasdb$# return self.b;
atlasdb$# end $name$;
atlasdb$# $$
atlasdb-# end;
CREATE TYPE
atlasdb=# create table demo_tab1 (col demo_typ1);
CREATE TABLE
atlasdb=# insert into demo_tab1 values(row(1,1));
INSERT 0 1
atlasdb=# select * from demo_tab1;
col
-------
(1,1)
(1 row)
atlasdb=# select col.get_a() from demo_tab1;
get_a
-------
1
(1 row)
atlasdb=# select col.get_b() from demo_tab1;
get_b
-------
1
(1 row)
atlasdb=# select (col).a, (col).b from demo_tab1;
a | b
---+---
1 | 1
(1 row)
atlasdb=# select t.col.get_a() from demo_tab1 t;
get_a
-------
1
(1 row)
atlasdb=# create or replace procedure p1(var demo_typ1) as $$
atlasdb$# begin
atlasdb$# insert into demo_tab1 values(2, var.get_a());
atlasdb$# end $$;
CREATE PROCEDURE
atlasdb=# select * from demo_tab1;
col
-------
(1,1)
(1 row)
atlasdb=# create or replace function f1() return int as $$
atlasdb$# declare
atlasdb$# var demo_typ1;
atlasdb$# ret int;
atlasdb$# begin
atlasdb$# var = var.demo_typ1(5,6);
atlasdb$# select var.get_b() into ret;
atlasdb$# return ret;
atlasdb$# end $$;
CREATE FUNCTION
atlasdb=#
atlasdb=# select f1();
f1
----
6
(1 row)
atlasdb=# create or replace function f2() return int as $$
atlasdb$# declare
atlasdb$# var demo_typ1;
atlasdb$# ret int;
atlasdb$# begin
atlasdb$# var = var.demo_typ1(5,6);
atlasdb$# call var.reset_value();
atlasdb$# select var.get_b() into ret;
atlasdb$# return ret;
atlasdb$# end $$;
CREATE FUNCTION
atlasdb=# select f2();
f2
----
0
(1 row)
atlasdb=# drop type demo_typ1 cascade;
NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to package demo_typ1
drop cascades to column col of table demo_tab1
drop cascades to function p1(demo_typ1)
DROP TYPE
atlasdb=# drop table demo_tab1;
DROP TABLE
atlasdb=# create schema demo_schema;
CREATE SCHEMA
atlasdb=# create type demo_schema.department_t as object (
atlasdb(# deptno numeric(10,0),
atlasdb(# dname char(30));
CREATE TYPE
atlasdb=# create type demo_schema.employee_t as object(
atlasdb(# empid Oid,
atlasdb(# ename text,
atlasdb(# dept demo_schema.department_t,
atlasdb(#
atlasdb(# constructor function employee_t(oid Oid, name text, det demo_schema.department_t) return self as result,
atlasdb(# typemember procedure set_ename(name text),
atlasdb(# typemember function get_name() return text,
atlasdb(# static function get_deptname(name text) return text
atlasdb(# );
CREATE TYPE
atlasdb=# create type body demo_schema.employee_t as $$
atlasdb$#
atlasdb$# constructor function employee_t(oid Oid, name text, det demo_schema.department_t) return self as result as $employee$
atlasdb$# begin
atlasdb$# self.empid = oid;
atlasdb$# self.ename = name;
atlasdb$# self.dept = det;
atlasdb$# return self;
atlasdb$# end $employee$;
atlasdb$#
atlasdb$# typemember procedure set_ename(name text) as $set_ename$
atlasdb$# begin
atlasdb$# ename = name;
atlasdb$# end $set_ename$;
atlasdb$#
atlasdb$# typemember function get_name() return text as $get_name$
atlasdb$# begin
atlasdb$# return ename;
atlasdb$# end $get_name$;
atlasdb$# static function get_deptname(name text) return text as $deptname$
atlasdb$# declare
atlasdb$# dept demo_schema.department_t;
atlasdb$# begin
atlasdb$# dept = self.dept;
atlasdb$# dept.dname = name;
atlasdb$# return dept.dname;
atlasdb$# end $deptname$;
atlasdb$# $$
atlasdb-# end;
CREATE TYPE
atlasdb=# create or replace function make_employee(name text) return demo_schema.employee_t
atlasdb-# as $$
atlasdb$# declare
atlasdb$# emp demo_schema.employee_t;
atlasdb$# begin
atlasdb$# emp = emp.employee_t(13542, 'emp1', row(333,'depart1'));
atlasdb$# call emp.set_ename(name);
atlasdb$# return emp;
atlasdb$# end $$;
CREATE FUNCTION
atlasdb=# select make_employee('emp2');
make_employee
---------------------------------------------------------
(13542,emp2,"(333,""depart1 "")")
(1 row)
atlasdb=# create or replace function employee_name() return text
atlasdb-# as $$
atlasdb$# declare
atlasdb$# emp demo_schema.employee_t;
atlasdb$# begin
atlasdb$# emp = emp.employee_t(13542, 'emp1', row(333,'depart1'));
atlasdb$# return emp.get_name();
atlasdb$# end $$;
CREATE FUNCTION
atlasdb=# select employee_name();
employee_name
---------------
emp1
(1 row)
atlasdb=#
atlasdb=# create or replace function dept_name() return text
atlasdb-# as $$
atlasdb$# declare
atlasdb$# emp demo_schema.employee_t;
atlasdb$# begin
atlasdb$# emp = emp.employee_t(13542, 'emp1', row(333,'depart1'));
atlasdb$# return emp.get_deptname('depart2');
atlasdb$# end $$;
CREATE FUNCTION
atlasdb=# select dept_name();
dept_name
-----------
depart2
(1 row)
atlasdb=#
atlasdb=# drop type demo_schema.department_t cascade;
NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to package employee_t
drop cascades to type demo_schema.employee_t
drop cascades to function make_employee(text)
DROP TYPE
atlasdb=# create type demo_typ2 as object(
atlasdb(# amount money,
atlasdb(# d_time timestamp,
atlasdb(#
atlasdb(# constructor function demo_typ2(a decimal, b text) return self as result,
atlasdb(# typemember function get_result() return demo_typ2,
atlasdb(# typemember function get_time() return timestamp,
atlasdb(# typemember procedure set_time(newt timestamp)
atlasdb(# );
CREATE TYPE
atlasdb=# create type body demo_typ2 as $$
atlasdb$#
atlasdb$# constructor function demo_typ2(self demo_typ2, a decimal, b text) return self as result
atlasdb$# as $typ2$
atlasdb$# begin
atlasdb$# self.amount = a::money;
atlasdb$# d_time = b::timestamp;
atlasdb$# return self;
atlasdb$# end $typ2$;
atlasdb$#
atlasdb$# typemember function get_result() return demo_typ2 as $result$
atlasdb$# begin
atlasdb$# return self;
atlasdb$# end $result$;
atlasdb$#
atlasdb$# typemember function get_time() return timestamp as $time$
atlasdb$# begin
atlasdb$# return d_time;
atlasdb$# end $time$;
atlasdb$#
atlasdb$# typemember procedure set_time(newt timestamp) as $sett$
atlasdb$# begin
atlasdb$# d_time = newt;
atlasdb$# end $sett$;
atlasdb$# $$
atlasdb-# end;
CREATE TYPE
atlasdb=# create function typ2_f1() return demo_typ2 as $$
atlasdb$# declare
atlasdb$# ret demo_typ2;
atlasdb$# begin
atlasdb$# ret = ret.demo_typ2(50.0, '2020-03-18');
atlasdb$# return ret.get_result();
atlasdb$# end $$;
CREATE FUNCTION
atlasdb=# select typ2_f1();
typ2_f1
--------------------------------
($50.00,"2020-03-18 00:00:00")
(1 row)
atlasdb=# create function typ2_f2() return timestamp as $$
atlasdb$# declare
atlasdb$# ret demo_typ2;
atlasdb$# begin
atlasdb$# ret = ret.demo_typ2(50.0, '2020-03-18');
atlasdb$# call ret.set_time(timestamp'2020-03-19');
atlasdb$# return ret.get_time();
atlasdb$# end $$;
CREATE FUNCTION
atlasdb=# select typ2_f2();
typ2_f2
---------------------
2020-03-19 00:00:00
(1 row)
atlasdb=#
atlasdb=# drop function typ2_f2();
DROP FUNCTION
atlasdb=# drop type demo_typ2 cascade;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to package demo_typ2
drop cascades to function typ2_f1()
DROP TYPE
