CREATE OR REPLACE FUNCTION adempiere.pymssql_ks_建檔(p_資料表 character varying)
    RETURNS text
    LANGUAGE 'plpython3u'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
#drop table adempiere.ks_iteminfo SELECT * FROM adempiere.ks_iteminfo
#SELECT * FROM information_schema.tables WHERE table_name = 'KS_ItemInfo'
#select adempiere.pymssql_ks_建檔('ItemInfo'::VARCHAR)
#ins_sql = plpy.prepare("INSERT INTO adempiere.ks_ItemInfo(欄位,型態)VALUES($1,$2)",["VARCHAR","VARCHAR"])
import pymssql
conn = pymssql.connect(host='192.168.9.243',user='sa',password='vispark',database='iTECTEST')
cursor = conn.cursor(as_dict=True)
#SELECT * FROM information_schema.tables WHERE table_name = 'KS_ItemInfo'
mssql = " SELECT COLUMN_NAME, \
CASE WHEN DATA_TYPE='int'  THEN N'NUMERIC('+CONVERT(VARCHAR(2),NUMERIC_PRECISION)+','+CONVERT(VARCHAR(2),NUMERIC_SCALE)+')' \
WHEN DATA_TYPE='decimal'   THEN N'NUMERIC('+CONVERT(VARCHAR(2),NUMERIC_PRECISION)+','+CONVERT(VARCHAR(2),NUMERIC_SCALE)+')' \
WHEN DATA_TYPE='nvarchar'  THEN N'VARCHAR('+CONVERT(VARCHAR(2),CHARACTER_MAXIMUM_LENGTH)+')' \
WHEN DATA_TYPE='smalldatetime' THEN N'DATE' \
ELSE 'XXXX'+DATA_TYPE \
END AS DATA_TYPE, \
CASE WHEN DATA_TYPE='int'  THEN N'NUMERIC' \
WHEN DATA_TYPE='decimal'   THEN N'NUMERIC' \
WHEN DATA_TYPE='nvarchar'  THEN N'VARCHAR' \
WHEN DATA_TYPE='smalldatetime' THEN N'DATE' \
ELSE 'XXXX'+DATA_TYPE \
END AS TYPE_ONLY \
FROM INFORMATION_SCHEMA.COLUMNS \
WHERE TABLE_NAME ='" + p_資料表 + "' AND TABLE_SCHEMA='dbo' \
ORDER BY ORDINAL_POSITION"
cursor.execute( mssql )
countx=1
create_sql=" CREATE TABLE adempiere.ks_" + p_資料表
insert_sql=" INSERT INTO adempiere.ks_" + p_資料表
insert_no=" VALUES "
insert_type=" "
ins_sql_col=""
for row in cursor:
  if countx==1 :
     create_sql = create_sql+"("
     insert_sql = insert_sql+"("
     insert_no = insert_no+"("
     insert_type = insert_type+"["
     ins_sql_col = ins_sql_col+"["
  else:
    create_sql = create_sql+","
    insert_sql = insert_sql+","
    insert_no = insert_no+","
    insert_type = insert_type+","
    ins_sql_col = ins_sql_col+","
  text01=row['COLUMN_NAME']
  text02=row['DATA_TYPE']
  text03=row['TYPE_ONLY']
  ins_sql_col= ins_sql_col+" row['"+ text01 +"'] "
  create_sql = create_sql+" "+text01+" "+text02
  insert_sql = insert_sql+" "+text01
  insert_type = insert_type+'"'+text03+'"'
  insert_no = insert_no+" $"+str(countx)
  countx=countx+1
        
create_sql = create_sql + ")"
insert_sql = insert_sql + ")"
insert_no = insert_no + ")"
insert_type = insert_type + "]"
ins_sql_col = ins_sql_col + "]"
            
plpy.execute(create_sql)
#cursor.close()
#cursor = conn.cursor(as_dict=True)

insert_sql='"'+insert_sql+insert_no+'"'
#ins_sql = plpy.prepare(insert_sql ,insert_type)
#return (mycode)
mycode = """ins_sql = plpy.prepare("""+insert_sql+""","""+insert_type+""")"""
loc = {}
exec(mycode, globals(), loc)
ins_sql = loc['ins_sql']
            
#return (insert_sql+","+insert_type)
mssql = " SELECT * FROM dbo." + p_資料表
cursor.execute( mssql )
countx=1
#return(ins_sql_col)
for row in cursor:
    cod1="""plpy.execute(ins_sql,"""+ins_sql_col+""")"""
    exec(cod1)
                            
cursor.close()
conn.close()
$BODY$;