PG 生成c#实体类的函数

赠人玫瑰手有余香

 

CREATE OR REPLACE FUNCTION "public"."fun_Generate_Entity"(class_name text, tables_name text)
RETURNS "pg_catalog"."text" AS $BODY$
DECLARE
str_Result text;


s_cName varchar(64);
s_ctype varchar(64);
s_cisnull varchar(64);
s_cdesc varchar(64);
begin
--开发仔专用函数 by czj
--生成实体类(c#)
str_Result='public class '||class_name||' {';
str_Result=str_Result||CHR(10);--换行符
DECLARE cur CURSOR FOR

SELECT
a.attname as cName,
format_type(a.atttypid,a.atttypmod) as ctype,
a.attnotnull as cisnull,
col_description(a.attrelid,a.attnum) as cdesc
FROM pg_class as c,pg_attribute as a
WHERE
a.attrelid = c.oid
and a.attnum>0
and upper(c.relname) = upper( tables_name);


begin
FOR cur_result IN cur
LOOP


s_cName :=coalesce( cur_result.cName,'');
s_ctype := coalesce(cur_result.ctype,'');
s_cisnull := coalesce(cur_result.cisnull,false);
s_cdesc := coalesce(cur_result.cdesc,'');
raise notice 's_ctype %',s_ctype;

CONTINUE when s_cName like 'pg.dropped';--pg删除标识




str_Result=str_Result||'/// <summary>';
str_Result=str_Result||CHR(10);--换行符
str_Result=str_Result||'/// '||s_cdesc;
str_Result=str_Result||CHR(10);--换行符
str_Result=str_Result||'/// <summary>';
str_Result=str_Result||CHR(10);--换行符
str_Result=str_Result||'[Description("'||s_cdesc||'")]';
str_Result=str_Result||CHR(10);--换行符
if(s_ctype like '%char%')
then

s_ctype=' string ';
end if;
if(s_ctype like '%number%' or s_ctype like '%numeric%')
then

s_ctype=' double ';
end if;

if(s_ctype like '%date%' or s_ctype like '%timestamp%')
then

s_ctype=' string ';
end if;
str_Result=str_Result||'public '||s_ctype||' '||s_cName||' { get; set; }';

str_Result=str_Result||CHR(10);--换行符

end loop;

str_Result=str_Result||'}';
end ;


return str_Result;
end ;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100