创建heap堆表：主键，分布键(只能行存储)
CREATE TABLE glfmkhjl (
id char(7) NOT NULL,
name char(8) ,
birthday date,
org_no char(6) ,
create_time timestamp,
update_time timestamp,
PRIMARY KEY (id)
)
distributed by (id);
insert into glfmkhjl values('0010603','陈一,'1971-04-17','981000',current_timestamp,current_timestamp),('0010021','陈二','1976-01-17','981000',current_timestamp,current_timestamp),('0010026','王三','1978-02-17','981000',current_timestamp,current_timestamp);
-------------------------------------------------------------
设置为复制表：主键，无分布键
CREATE TABLE glfmwdkz (
org_no char(6) NOT NULL,
org_name varchar(80),
parent_no char(6),
create_time timestamp,
update_time timestamp,
PRIMARY KEY (org_no)
)
DISTRIBUTED REPLICATED;
insert into glfmwdkz values ('981000','总公司','999000',current_timestamp,current_timestamp), ('981010','第一支公司','981000',current_timestamp,current_timestamp);
------------------------------------------------------------
创建列存储表(append-only不支持主键)，支持压缩
create table byftjrn(
jrn_no bigint not null,
tx_date date not null,
acct_no char(22),
org_no char(6),
name varchar(128),
tx_amt decimal(15,2) not null default 0.00,
create_time timestamp,
update_time timestamp
)
with (appendonly=true,orientation=column,COMPRESSTYPE=zlib, compresslevel=5)
distributed by (jrn_no, tx_date);
insert into byftjrn values (1,'2019-12-14','201000000012345','981010','第一公司',1259.12,current_timestamp,current_timestamp),(2,'2019-12-14','201000000036712','981000','大华公司',30000.66,current_timestamp,current_timestamp);
-----------------------------------------------------------
CREATE OR REPLACE FUNCTION get_tjjg(in_brno char(6))
RETURNS char(6) AS $$
declare c_tjjg char(6) default '';
BEGIN
select parent_no into c_tjjg from glfmwdkz where org_no=in_brno;
RETURN c_tjjg;
END;
$$ LANGUAGE plpgsql;
使用了复制表后可以嵌套函数
select *,get_tjjg(org_no) tjjg from glfmkhjl;