Gbase8s如何获取DDL语句

在gbase模式下创建如下function ，后续在oracle与gbase模式都可以调用
drop function if exists get_ddl(varchar,varchar,varchar);
-- usage: get_ddl(OBJECT_TYPE, OWNER, OBJECT_NAME)
-- eg: get_ddl('table','gbasedbt','tab1)
-- p_tpye and p_user no use now.
create function get_ddl(p_type varchar(40), p_user varchar(32), p_name varchar(128))
returns lvarchar(16384)
define v_ddl lvarchar(16384);
define v_colname varchar(128);
define v_coltype_name varchar(128);
define v_nullable varchar(128);
define v_defvalue varchar(254);
define v_deftype varchar(10);
define v_ddl_pk lvarchar(16384);
define v_pk_collist lvarchar(16384);
define v_pk_name varchar(128);
on exception
return null;
end exception;

let p_name = lower(p_name);
let v_ddl = 'CREATE TABLE ' || p_name || '(' || chr(13) || chr(10);
let v_ddl_pk = null;
-- colname, coltype, nullable, default_value, default_type_flag
foreach SELECT tmp.colname,tmp.coltype_name,
CASE d.type
WHEN 'L' THEN gbasedbt.get_default_value(tmp.coltype, tmp.extended_id, tmp.collength, d.default::lvarchar(256))::VARCHAR(254)
WHEN 'C' THEN 'current year to second'::VARCHAR(254)
WHEN 'S' THEN 'dbservername'::VARCHAR(254)
WHEN 'U' THEN 'user'::VARCHAR(254)
WHEN 'T' THEN 'today'::VARCHAR(254)
WHEN 'E' THEN de.default::VARCHAR(254)
ELSE NULL::VARCHAR(254)
END AS def_value,
tmp.nullable,
d.type
INTO v_colname, v_coltype_name, v_defvalue, v_nullable, v_deftype
FROM (
SELECT ce.tabid, ce.colno, ce.colname,ce.coltype,ce.extended_id,ce.collength,ce.coltypename2::varchar(128) AS coltype_name,
CASE WHEN ce.coltype > 255 THEN 'NOT NULL' ELSE '' END AS nullable
FROM syscolumnsext ce, systables t
WHERE ce.tabid = t.tabid
AND t.tabname = p_name
ORDER BY ce.colno) tmp LEFT JOIN sysdefaults d ON (tmp.tabid = d.tabid AND tmp.colno = d.colno)
LEFT JOIN sysdefaultsexpr de ON (tmp.tabid = de.tabid AND tmp.colno = de.colno and de.type='T')

-- no column found, return null
if v_colname is null or v_colname = '' then
return null;
end if;

-- default values with function not do.
if v_deftype = 'E' then
let v_defvalue = 'DEFAULT (' || v_defvalue || ')';
elif v_deftype = 'L' and v_coltype_name[1,4] in ('CHAR','VARC','LVAR','NCHA','NVAR') then
let v_defvalue = 'DEFAULT ''' || v_defvalue || '''';
elif v_defvalue is not null then
let v_defvalue = 'DEFAULT ' || v_defvalue;
end if;

-- column: colname coltype default nullable
let v_ddl = v_ddl || ' ' || v_colname || ' ' || v_coltype_name || ' ' || v_defvalue || ' ' || v_nullable || ',' || chr(13) || chr(10);
end foreach;

-- primary key
let v_pk_collist = '';
let v_pk_name = '';
foreach SELECT
so.constrname,
rtrim((select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part1)) || ',' ||
(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part2)) || ',' ||
(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part3)) || ',' ||
(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part4)) || ',' ||
(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part5)) || ',' ||
(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part6)) || ',' ||
(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part7)) || ',' ||
(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part8)) || ',' ||
(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part9)) || ',' ||
(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part10)) || ',' ||
(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part11)) || ',' ||
(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part12)) || ',' ||
(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part13)) || ',' ||
(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part14)) || ',' ||
(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part15)) || ',' ||
(select sc.colname from syscolumns sc where sc.tabid = st.tabid and sc.colno = ABS(si.part16)), ',') as keys
INTO v_pk_name, v_pk_collist
FROM systables st, sysconstraints so, sysindexes si
WHERE st.tabname = p_name
AND st.tabid = so.tabid
AND so.constrtype = 'P'
AND so.idxname = si.idxname
end foreach;

if v_pk_collist = '' or v_pk_collist is null then
let v_ddl = rtrim(v_ddl, ',' || chr(13) || chr(10));
elif v_pk_name[1,1] = 'u' then
let v_ddl = v_ddl || ' PRIMARY KEY(' || v_pk_collist || ')';
else
let v_ddl = v_ddl || ' PRIMARY KEY(' || v_pk_collist || ') CONSTRAINT ' || v_pk_name ;
end if;
let v_ddl = v_ddl || chr(13) || chr(10) || ');';
return v_ddl;
end function;

使用语法：
call get_ddl('table','gbasedbt','t1')

太赞了！

看看啦。

蹲个答案。