본문 바로가기

PROGRAMING/GO

go string builder

	r.POST("/realtime_contract_data", func(c *gin.Context) {
		var connectionString string = fmt.Sprintf("host=%s user=%s password=%s dbname=%s sslmode=disable", HOST, DB_USER, DB_PASSWORD, DB_DATABASE)
		// Initialize connection object.
		log.Println("AAA")
		//db, err := sql.Open("postgres", connectionString)
		db, err := sqlx.Connect("postgres", connectionString)
		log.Println("BB")
		checkError(err)
		err = db.Ping()
		log.Println(err)
		log.Println("CC")
		var sb strings.Builder
		sb.WriteString(`
		with vw00 as (
			select 
				stock_dt
				,stock_cd
				,curr_time
				,count(*) OVER(
						partition by stock_cd
						, stock_dt
						, ((cast(substring(curr_time,1,2) as integer)-9 )*60 + cast(substring(curr_time,3,2)  as integer) )/b.divide
				) cnt
				,FIRST_VALUE(curr_amt) OVER(
						partition by stock_cd
						, stock_dt
						, ((cast(substring(curr_time,1,2) as integer)-9 )*60 + cast(substring(curr_time,3,2)  as integer) )/b.divide
						order by curr_time asc
				) start_amt
				,last_valUE(curr_amt) OVER(
						partition by stock_cd
						, stock_dt
						, ((cast(substring(curr_time,1,2) as integer)-9 )*60 + cast(substring(curr_time,3,2)  as integer) )/b.divide
						order by curr_time asc		  
				) curr_amt
				,RANK() OVER(
						partition by stock_cd
						, stock_dt
						, ((cast(substring(curr_time,1,2) as integer)-9 )*60 + cast(substring(curr_time,3,2)  as integer) )/b.divide
						order by curr_time		  
				) rnk
				,((cast(substring(curr_time,1,2) as integer)-9 )*60 + cast(substring(curr_time,3,2)  as integer) )/b.divide  mm
				,max(abs(curr_amt)) over (partition by stock_cd
										, stock_dt
										, ((cast(substring(curr_time,1,2) as integer)-9 )*60 + cast(substring(curr_time,3,2)  as integer) )/b.divide 
										) as high_amt 
				,min(abs(curr_amt)) over (partition by stock_cd
										, stock_dt
										, ((cast(substring(curr_time,1,2) as integer)-9 )*60 + cast(substring(curr_time,3,2)  as integer) )/b.divide 
										) as low_amt 
				,(case when trade_qty>0 then trade_qty else 0 end)  offered_trade_qty
				,(case when trade_qty<0 then trade_qty else 0 end)  bid_trade_qty
				,b.divide
			from tb_realtime_contract  a
			cross join (select 5 as  divide ) b`)
		sb.WriteString(` 
			where stock_cd='005930'`)
		sb.WriteString(`
			)
			select 
				stock_dt
				,stock_cd
				,curr_amt
				,mm
				,curr_time
				,start_amt
				,high_amt
				,low_amt
				,offered_trade_qty
				,bid_trade_qty 
				,divide
				,cnt
			from vw00 
			where rnk=1
			order by mm asc
			`)
		//Go through rows
		rows, err := db.Queryx(sb.String())
		mm := []map[string]interface{}{}
		for rows.Next() {
			m := map[string]interface{}{}
			err := rows.MapScan(m)
			mm = append(mm, m)
			if err != nil {
				log.Fatal(err)
			}
		}
		//fmt.Printf("Marshalled data: %s\n", mm)
		c.JSON(200, mm)
	})

var sb strings.Builder
sb.WriteString(`   이런식1 ' )
sb.WriteString(`   이런식2 ' )
sb.WriteString(`   이런식3 ' )
이런식으로 사용했다.

중간에 에러가 나서 뭐가 잘못일까 고민했는데
where 를 앞에 너무 붙여써서 sql 문법에러가 난 것이었다.
문법에러를 명확히 알려주지 않는다. go에서.!!

조건식이 있고 없고를 결국엔 넣어야하는데
if문으로 빼는것이 올은지 고민이 되었다.