从程序里使用Postgresql复杂数据类型

通过程序向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')