通过程序向PostgreSQL中插入复杂的数据类型,如下图:
1 支持数组的接口程序
1.1 创建测试函数
drop function if exists data_type_test_v1(text,text,text[],text[],text);
drop function if exists data_type_test_v2(text,text,text,text,text);
drop function if exists point_test_line(text[]);
drop function if exists point_test_lseg(text[]);
create or replace function point_test_line(text[])
returns line
as $$
select cast(array_to_string(array_agg($1),',') as line)
$$ language sql;
create or replace function point_test_lseg(text[])
returns lseg
as $$
select cast(array_to_string(array_agg($1),',') as lseg)
$$ language sql;
create or replace function data_type_test_v1(text,text,text[],text[],text)
returns table(f1 json,f2 jsonb,f3 line,f4 lseg,f5 macaddr)
as $$
select cast($1 as json),
cast($2 as jsonb),
point_test_line($3),
point_test_lseg($4),
cast($5 as macaddr)
$$ language sql;
1.2 使用方法
select * from data_type_test_v1('{"a":1,"b":"abc"}','{"a":1,"b":"abc"}',array['(-1,0)','(1,0)'],array['(-1,0)','(1,0)'],'12:34:56:78:90:ab')
2 不支持数组的接口程序(JDBC for PostgreSQL)
2.1 创建测试函数
create or replace function data_type_test_v2(text,text,text,text,text)
returns table(f1 json,f2 jsonb,f3 line,f4 lseg,f5 macaddr)
as $$
select cast($1 as json),
cast($2 as jsonb),
cast($3 as line),
cast($4 as lseg),
cast($5 as macaddr)
$$ language sql;
2.2 使用方法
select * from data_type_test_v2('{"a":1,"b":"abc"}','{"a":1,"b":"abc"}','((-1,0),(1,0))','((-1,0),(1,0))','12:34:56:78:90:ab')