select * from pg_tables where schemaname='public';
select * from information_schema.columns
where table_name = 'member';
获得主键:
select distinct pg_constraint.conname as pk_name,pg_attribute.attname as colname,pg_type.typname as typename from
pg_constraint inner join pg_class
on pg_constraint.conrelid = pg_class.oid
inner join pg_attribute on pg_attribute.attrelid = pg_class.oid
and ( pg_attribute.attnum = pg_constraint.conkey[1] or
pg_attribute.attnum = pg_constraint.conkey[2] or
pg_attribute.attnum = pg_constraint.conkey[3] or
pg_attribute.attnum = pg_constraint.conkey[4] or
pg_attribute.attnum = pg_constraint.conkey[5] or
pg_attribute.attnum = pg_constraint.conkey[6]
)
inner join pg_type on pg_type.oid = pg_attribute.atttypid
where lower(pg_class.relname) = '您的table名称'
and pg_constraint.contype='p'
好吧,上面的是一些基础的背景,那么现在来新建一个测试表,然后对所有基础类型进行一些测试:
create table test2(
tsmallint SMALLINT,
tinteger INTEGER,
tbigint BIGINT,
treal real,
tdecimal DECIMAL,
tnumberic NUMERIC,
tserial serial,
tbigserial bigserial,
tvarchar varchar(99),
tchar char(12),
ttext text,
ttimestamp TIMESTAMP,
tdate date,
ttime time,
tinterval interval,
tboolean boolean,
tbit bit(5),
tbitvarying bit VARYING(5),
PKEY serial PRIMARY KEY
);
获取postgresql里面存储过程信息:
DatabaseMetaData dbMeta=_conn.getMetaData();
ResultSet rs_procList=dbMeta.getProcedures(catalogName,schemaName,null);
DataTable dt_paras= DataTableHelper.rs2datatable(rs_procList);
rs_procList.close();
for (DataRow dritem: dt_paras.getRows()){
//ResultSet rs_paraList=dbMeta.getProcedureColumns(catalogName,schemaName,)
}
dt_paras.Print();
return null;
执行以后获得结果如下:
=======》》》当前包含列名:
【procedure_cat】 【procedure_schem】 【procedure_name】 【?column?】 【?column?】 【?column?】 【remarks】 【procedure_type】 【specific_name】
=======》》》当前包含内容:
行号【0】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:convert_to_integer 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:convert_to_integer_16522
行号【1】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:func_test1 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:func_test1_16462
行号【2】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:somefuncname 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:somefuncname_16502
行号【3】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:test2_add 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:test2_add_16469
行号【4】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:test2_add 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:test2_add_16470
行号【5】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:test2_deletelist 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:test2_deletelist_16533
行号【6】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:test2_getrecord 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:test2_getrecord_16543
行号【7】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:test2_gettopnbycondition 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:test2_gettopnbycondition_16537
行号【8】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:test2_update 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:test2_update_16471
行号【9】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:testsetof 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:testsetof_16545
行号【10】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:testtable_deletelist 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:testtable_deletelist_16563
行号【11】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:testtable_getrecord 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:testtable_getrecord_16561
行号【12】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:testtable_gettopnbycondition 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:testtable_gettopnbycondition_16562
行号【13】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:testtable_insert 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:testtable_insert_16564
行号【14】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:testtable_update 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:testtable_update_16565
行号【15】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:tools_str2array 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:tools_str2array_16525
行号【16】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:tools_str2intarray 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:tools_str2intarray_16526
行号【17】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:tools_stringify 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:tools_stringify_16527
行号【18】:
【procedure_cat】: 【procedure_schem】:public 【procedure_name】:tools_stringify 【?column?】: 【remarks】: 【procedure_type】:2 【specific_name】:tools_stringify_16528
=======》》》打印结束
当然你也可以用这个方式来获取:
SELECT n.nspname as "Schema",
p.proname as "Name",
pg_catalog.pg_get_function_result(p.oid) as "Result data type",
pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
CASE
WHEN p.proisagg THEN 'agg'
WHEN p.proiswindow THEN 'window'
WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
ELSE 'normal'
END as "Type"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname <> 'pg_catalog'
AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;
结果如下:
翻译成为文字,大约是:
=======》》》当前包含列名:
【Schema】 【Name】 【Result data type】 【Argument data types】 【Type】
=======》》》当前包含内容:
行号【0】:
【Schema】:public 【Name】:convert_to_integer 【Result data type】:integer 【Argument data types】:v_input text 【Type】:normal
行号【1】:
【Schema】:public 【Name】:func_test1 【Result data type】:record 【Argument data types】:id integer, name integer, OUT status boolean, OUT msg character varying 【Type】:normal
行号【2】:
【Schema】:public 【Name】:somefuncname 【Result data type】:integer 【Argument data types】: 【Type】:normal
行号【3】:
【Schema】:public 【Name】:test2_add 【Result data type】:record 【Argument data types】:tsmallint smallint, tinteger integer, tbigint bigint, treal real, tdecimal numeric, tnumberic numeric, tvarchar character varying, tchar character, ttext text, ttimestamp timestamp without time zone, tdate date, ttime time without time zone, tinterval interval, tboolean boolean, tbit bit, tbitvarying bit varying, OUT _status boolean, OUT _msg character varying 【Type】:normal
行号【4】:
【Schema】:public 【Name】:test2_add 【Result data type】:record 【Argument data types】:tsmallint smallint, tinteger integer, tbigint bigint, treal real, tdecimal numeric, tnumberic numeric, tvarchar character varying, tchar character, ttext text, ttimestamp timestamp without time zone, tdate date, ttime time without time zone, tinterval unknown, tboolean boolean, tbit bit, tbitvarying bit varying, OUT _status boolean, OUT _msg character varying 【Type】:normal
行号【5】:
【Schema】:public 【Name】:test2_deletelist 【Result data type】:record 【Argument data types】:ids character varying, OUT status boolean, OUT msg character varying 【Type】:normal
行号【6】:
【Schema】:public 【Name】:test2_getrecord 【Result data type】:SETOF test2 【Argument data types】:_id integer 【Type】:normal
行号【7】:
【Schema】:public 【Name】:test2_gettopnbycondition 【Result data type】:SETOF test2 【Argument data types】:_topn integer, _columns character varying, _where character varying, _orderby character varying 【Type】:normal
行号【8】:
【Schema】:public 【Name】:test2_update 【Result data type】:record 【Argument data types】:tsmallint smallint, tinteger integer, tbigint bigint, treal real, tdecimal numeric, tnumberic numeric, tvarchar character varying, tchar character, ttext text, ttimestamp timestamp without time zone, tdate date, ttime time without time zone, tinterval interval, tboolean boolean, tbit bit, tbitvarying bit varying, pkey integer, OUT _status boolean, OUT _msg character varying 【Type】:normal
行号【9】:
【Schema】:public 【Name】:testsetof 【Result data type】:SETOF record 【Argument data types】: 【Type】:normal
行号【10】:
【Schema】:public 【Name】:testtable_deletelist 【Result data type】:record 【Argument data types】:ids character varying, OUT status boolean, OUT msg character varying 【Type】:normal
行号【11】:
【Schema】:public 【Name】:testtable_getrecord 【Result data type】:SETOF testtable 【Argument data types】:_id integer 【Type】:normal
行号【12】:
【Schema】:public 【Name】:testtable_gettopnbycondition 【Result data type】:SETOF testtable 【Argument data types】:_topn integer, _columns character varying, _where character varying, _orderby character varying 【Type】:normal
行号【13】:
【Schema】:public 【Name】:testtable_insert 【Result data type】:record 【Argument data types】:tname character varying, age integer, birthday timestamp without time zone, OUT tid integer, OUT _status boolean, OUT _msg character varying 【Type】:normal
行号【14】:
【Schema】:public 【Name】:testtable_update 【Result data type】:record 【Argument data types】:tname character varying, age integer, birthday timestamp without time zone, tid integer, OUT _status boolean, OUT _msg character varying 【Type】:normal
行号【15】:
【Schema】:public 【Name】:tools_str2array 【Result data type】:character varying[] 【Argument data types】:_originstr character varying, _delimeter character varying 【Type】:normal
行号【16】:
【Schema】:public 【Name】:tools_str2intarray 【Result data type】:integer[] 【Argument data types】:_originstr character varying, _delimeter character varying 【Type】:normal
行号【17】:
【Schema】:public 【Name】:tools_stringify 【Result data type】:text 【Argument data types】:_array character varying[], _delimeter character varying 【Type】:normal
行号【18】:
【Schema】:public 【Name】:tools_stringify 【Result data type】:text 【Argument data types】:_array integer[], _delimeter character varying 【Type】:normal
=======》》》打印结束
为了可以清楚inout out及各种参数的表示方式,现在特地做了一个demo以供对照:
=======》》》当前包含列名:
【Schema】 【Name】 【Result data type】 【Argument data types】 【Type】
=======》》》当前包含内容:
行号【0】:
【Schema】:public 【Name】:convert_to_integer 【Result data type】:integer 【Argument data types】:v_input text 【Type】:normal
行号【1】:
【Schema】:public 【Name】:func_test1 【Result data type】:record 【Argument data types】:id integer, name integer, OUT status boolean, OUT msg character varying 【Type】:normal
行号【2】:
【Schema】:public 【Name】:somefuncname 【Result data type】:integer 【Argument data types】: 【Type】:normal
行号【3】:
【Schema】:public 【Name】:test2_deletelist 【Result data type】:record 【Argument data types】:ids character varying, OUT status boolean, OUT msg character varying 【Type】:normal
行号【4】:
【Schema】:public 【Name】:test2_getlistbycondition 【Result data type】:SETOF record 【Argument data types】:INOUT pageindex integer, INOUT pagesize integer, _columns character varying, _where character varying, _orderby character varying, OUT _totalcount integer, OUT _totalpages integer 【Type】:normal
行号【5】:
【Schema】:public 【Name】:test2_getpagebycondition 【Result data type】:SETOF record 【Argument data types】:INOUT pageindex integer, INOUT pagesize integer, _columns character varying, _where character varying, _orderby character varying, OUT _totalcount integer, OUT _totalpages integer, OUT _refcursor refcursor 【Type】:normal
行号【6】:
【Schema】:public 【Name】:test2_getrecord 【Result data type】:SETOF test2 【Argument data types】:_id integer 【Type】:normal
行号【7】:
【Schema】:public 【Name】:test2_gettopnbycondition 【Result data type】:SETOF test2 【Argument data types】:_topn integer, _columns character varying, _where character varying, _orderby character varying 【Type】:normal
行号【8】:
【Schema】:public 【Name】:test2_insert 【Result data type】:record 【Argument data types】:__tsmallint smallint, __tinteger integer, __tbigint bigint, __treal real, __tdecimal numeric, __tnumberic numeric, __tvarchar character varying, __tchar character, __ttext text, __ttimestamp timestamp without time zone, __tdate date, __ttime time without time zone, __tinterval interval, __tboolean boolean, __tbit bit, __tbitvarying bit varying, OUT __pkey integer, OUT _status boolean, OUT _msg character varying 【Type】:normal
行号【9】:
【Schema】:public 【Name】:test2_update 【Result data type】:record 【Argument data types】:__tsmallint smallint, __tinteger integer, __tbigint bigint, __treal real, __tdecimal numeric, __tnumberic numeric, __tvarchar character varying, __tchar character, __ttext text, __ttimestamp timestamp without time zone, __tdate date, __ttime time without time zone, __tinterval interval, __tboolean boolean, __tbit bit, __tbitvarying bit varying, __pkey integer, OUT _status boolean, OUT _msg character varying 【Type】:normal
行号【10】:
【Schema】:public 【Name】:testsetof 【Result data type】:SETOF record 【Argument data types】: 【Type】:normal
行号【11】:
【Schema】:public 【Name】:testtable_deletelist 【Result data type】:record 【Argument data types】:ids character varying, OUT status boolean, OUT msg character varying 【Type】:normal
行号【12】:
【Schema】:public 【Name】:testtable_getrecord 【Result data type】:SETOF testtable 【Argument data types】:_id integer 【Type】:normal
行号【13】:
【Schema】:public 【Name】:testtable_gettopnbycondition 【Result data type】:SETOF testtable 【Argument data types】:_topn integer, _columns character varying, _where character varying, _orderby character varying 【Type】:normal
行号【14】:
【Schema】:public 【Name】:testtable_insert 【Result data type】:record 【Argument data types】:tname character varying, age integer, birthday timestamp without time zone, OUT tid integer, OUT _status boolean, OUT _msg character varying 【Type】:normal
行号【15】:
【Schema】:public 【Name】:testtable_update 【Result data type】:record 【Argument data types】:tname character varying, age integer, birthday timestamp without time zone, tid integer, OUT _status boolean, OUT _msg character varying 【Type】:normal
行号【16】:
【Schema】:public 【Name】:tools_str2array 【Result data type】:character varying[] 【Argument data types】:_originstr character varying, _delimeter character varying 【Type】:normal
行号【17】:
【Schema】:public 【Name】:tools_str2intarray 【Result data type】:integer[] 【Argument data types】:_originstr character varying, _delimeter character varying 【Type】:normal
行号【18】:
【Schema】:public 【Name】:tools_stringify 【Result data type】:text 【Argument data types】:_array character varying[], _delimeter character varying 【Type】:normal
行号【19】:
【Schema】:public 【Name】:tools_stringify 【Result data type】:text 【Argument data types】:_array integer[], _delimeter character varying 【Type】:normal
=======》》》打印结束
接下来就是看看基础类型的字符串如何了:
有了这个东西我们就可以编写postgres的相关模板了。